Creating database for a new city

This is intended to be a nearly comprehensive guide for how to download a new city's data from OpenStreetMap, transform it to be in the format we need for Project Sidewalk, and put it into a fresh database. It is a work in progress, and is only based off of my (Mikey's) experience creating the database for Newberg, OR; Columbus, OH; and Mexico City thus far.

Prepare the road network using QGIS

Downloading road network data

We typically use https://extract.bbbike.org/ to download the data from OpenStreetMap. Just make a bounding box that encompasses the entire city (but try to make it as small as possible while still getting the whole city). You can see the boundaries of a city using lots of different tools, including Google Maps. An ESRI Shapefile or OSM XML work as output formats; if the data looks messed up in one format, try it in another format.

If the city is too large to use bbbike, another option that accepts a wider range would be the HOT Export Tool. You can make a many-sided polygon to cover the area you're looking for. Or you can import a geojson file containing a polygon that you want to query for.

The HOT Export Tool also has a limit, so you might have to learn how to use the Overpass API for a very large area.

Downloading data on city limits / neighborhoods

First try and find the data available for download on the city's website. This was super easy for DC, as they have a big open data initiative, but other places (especially smaller places like Newberg, OR) may not have that data readily available for download online. If you can't find it online, it is best to contact the city to ask for this information, because I can't imagine how they wouldn't have those files somewhere.
If you need to get in touch with the city, you can try and find the boundary data elsewhere on the internet in the meantime. This is the site I've used for this: https://osm-boundaries.com/. I've found that the boundary data can also be out of date (can cross-reference with what the boundary looks like in Google Maps). If the boundary in this open dataset seems to match up with Google Maps exactly, I wouldn't worry so much about getting the exact data from the city government.

Install QGIS

https://www.qgis.org/en/site/forusers/download.html

Importing a shapefile into QGIS

Go to Layer -> Add layer -> Add vector layer, and find the .shp file

Make sure projection is EPSG:4326, WGS 84

Check the CRS (coordinate reference system) by going to the properties for each layer you add and checking the Information tab
If the CRS is not EPSG:4326, WGS 84, exit the properties, right click on the layer and choose Export -> Save Features As... Pick the correct CRS there. This will tranform the shapefile and save a new version in the correct CRS.

Create neighborhoods if we have none, or split up neighborhoods if they are too large

You'll only need to do this if you can't track down a dataset with neighborhoods for the city or if the neighborhoods you have turn out to be way too large (in the sum of the lengths of the streets). For neighborhoods that are too large, I typically wait until I get to the end of the process, then check the sum of the lengths of the streets in the neighborhood. Then I will start from the beginning, splitting up the neighborhoods that are too large on the 2nd attempt. I start over just to prevent us from splitting up neighborhoods that we don't need to. Click the arrow to expand if you need to do this.

Install the digitizing tools plugin
Make sure you can snap vertices together: Project -> Snapping Options -> set units to degrees & tolerance to ~0.001
Toggle editing for the polygons you want to split up
Click on "Split Features" in the Digitizing toolbar
To do the split, left click either on a vertex of the polygon (you know it's on a vertex because a purple box shows up on the cursor) or fully outside the polygon. Then continue to left click to add more vertices to a linestring that indicates where the cut should be.
After adding your final vertex (again on a vertex of the polygon or completely outside the polygon), you can right click to complete the cut.
You should now be able to see that the polygon has been split in two, both with identical attributes in the attributes table. If the neighborhood was called "Blah", then you'll probably want to rename the two parts into something like "Blah North" and "Blah South".
If you want to split the polygon into more parts, you can continue to do so! Just follow the same process again to split one of those smaller polygons into two even smaller ones.

Create a new unique index for the regions

Go to the properties for the region layer -> Fields -> Field Calculator
Give the output field the name region_id, choose row number to output and click Ok.

Import the street data

If it is in shapefile format, you should be able to just import the roads.shp file. If it is in the OSM format, you should be able to select just the "lines" (shouldn't need the multilinestrings).

Remove streets outside the city

This isn't strictly necessary, but you will definitely want to do it for large cities (or if you just requested the road network for a very large bounding box). It will drastically improve the running time of the algorithm that splits streets at intersections. You can do this by selecting features by area and deleting them.

Just make sure to check every time you highlight some streets to verify that none of them are actually part of the road network of the city. A caveat: if a street only intersects a neighborhood polygon just a tiny bit near it's endpoint, it is actually best to just delete that street. That little tiny piece of a street would end up being a street that someone has to audit, and it is a bit of a confusing experience for the user.

Filter the streets

In QGIS, right click on the polylines layer and click on "Filter"
Add the line "type" IN ('trunk', 'primary', 'secondary', 'tertiary', 'residential', 'unclassified', 'pedestrian', 'living_street')
NOTE if you downloaded data in another format, the attribute might be called "highway" instead of "type".

Split lines along intersections

Processing -> Toolbox -> QGIS geoalgorithms -> Vector overlay tools -> Split lines with lines (use roads for input and split layers)

Filter within city limits and apply `region_id` to streets

Go to Vector -> Geoprocessing tools -> Intersection
Input layer should be the newly split roads, overlay layer the neighborhood polygons

Make sure you have single-linestrings not multi-linestrings

Use the Processing->Toolbox->Multipart to Singleparts tool.

TODO (not currently doing this) Split lines based on their length; no longer than 250ft?

Get an estimate for how long 250 ft is in degrees (possibly using this site).
Processing -> Toolbox -> v.split.length

Create a new unique index for the streets

Go to the properties for the streets layer -> Fields -> Field Calculator
Give the output field the name road_id, choose row number to output and click Ok.

Get the endpoints of the newly split streets

Install the "Locate points along lines" plugin
Use the plugin with roads as the input layer, a large interval (1 is good), checking "keep attributes" and "Add endpoints"

Create a new unique index for the nodes in the same way as for the streets (called `node_id`)

Add the latitude and longitude to the endpoints of the streets

Go to Vector -> Geometry Tools -> Add Geometry Attributes (they will be added as attributes called 'xcoord' and 'ycoord')

Create a blank database:

Make sure you have psql version 12.5. Our servers use CentOS which uses that version psql. We may not be able to restore database dumps created using a vastly different version of psql.

createdb -T template0 <new-db-name>
psql -d <new-db-name> -a -f schema.sql

You can find schema.sql in the db directory of the repo.

Put the data in the database

Connect your database to QGIS by going to Layer -> Add Layer -> Add PostGISLayers...
Go to Database -> DB Manager -> DB Manager -> PostGIS -> your database
Click on Import Layer/File, choose the layer with your nodes, put it in the "sidewalk" schema, call it qgis_node, fill in the primary key with node_id, make sure to check "Do not promote to multi-part".
Click on Import Layer/File, choose the layer with your roads, put it in the "sidewalk" schema, call it qgis_road, fill in the primary key with road_id, make sure to check "Do not promote to multi-part".
Click on Import Layer/File, choose the layer with your polygons, put it in the "sidewalk" schema, call it qgis_region, fill in the primary key region_id, but DO NOT check "Do not promote to multi-part".

Getting the database tables filled correctly

Now we take the data that we put into the database and reformat it and put it into the correct tables so that we can actually run Project Sidewalk using this database. This is all in the form of SQL queries.

Fill in the street_edge table using the qgis_road and qgis_node tables (note that type might be highway depending on data source).

INSERT INTO sidewalk.street_edge (street_edge_id, geom, x1, y1, x2, y2, way_type, deleted, timestamp)
    SELECT min_max_road.road_id, min_max_road.geom,
           n1.xcoord, n1.ycoord, n2.xcoord, n2.ycoord,
           min_max_road.type, FALSE, now()
    FROM (
        SELECT qgis_road.road_id, qgis_road.geom, qgis_road.type,
               MAX(node_id) AS max_node_id, MIN(node_id) AS min_node_id
        FROM sidewalk.qgis_road
        INNER JOIN sidewalk.qgis_node ON qgis_road.road_id = qgis_node.road_id
        GROUP BY qgis_road.road_id
    ) "min_max_road"
    INNER JOIN sidewalk.qgis_node n1 ON min_max_road.max_node_id = n1.node_id
    INNER JOIN sidewalk.qgis_node n2 ON min_max_road.min_node_id = n2.node_id;

Fill in the osm_way_street_edge table to link streets to their original OSM ways.

INSERT INTO sidewalk.osm_way_street_edge (osm_way_id, street_edge_id)
    SELECT osm_id, road_id FROM sidewalk.qgis_road;

Add the tutorial street edge from DC into the database. We are just giving it a new street edge id that should be added to the cityparams.conf file.

INSERT INTO sidewalk.street_edge(street_edge_id, geom, x1, y1, x2, y2, way_type, deleted, timestamp)
    SELECT MAX(street_edge_id) + 1, '0102000020E6100000040000007C9E3F6D544453C00A2E56D460784340ECF7C43A554453C02B685A6265784340F29A5775564453C0C4D2C08F6A784340F73DEAAF574453C0B0CBF09F6E784340', -77.067653, 38.940455, -77.067852, 38.940876, 'tertiary', FALSE, '2015-11-17 04:20:19.46+00'
    FROM sidewalk.street_edge;

Initialize the street_edge_priority table

INSERT INTO sidewalk.street_edge_priority (street_edge_id, priority)
    SELECT street_edge_id, 1
    FROM sidewalk.street_edge
    WHERE deleted = FALSE;

Fill in the region table using the qgis_region table. NOTE fill in the data_source column in a way that seems appropriate. The description column should hold the name of the neighborhood; you can always fix that manually later. We also need to fill in the region_property table here, because our schema is out of date. Once we update the seed schema, we should be able to remove the region_type_id column and the inserts to the region_type table altogether.
```
INSERT INTO sidewalk.region (region_id, region_type_id, data_source, description, geom, deleted)
    SELECT region_id, 2, 'a URL or email address or something', region_name, geom, FALSE
    FROM sidewalk.qgis_region;
```
```
INSERT INTO sidewalk.region_property (region_id, key, value)
    SELECT region_id, 'Neighborhood Name', description
    FROM sidewalk.region;
```

Fill in the street_edge_region table.

First fill in the streets for the new city

INSERT INTO sidewalk.street_edge_region (street_edge_id, region_id)
    SELECT road_id, region_id
    FROM sidewalk.qgis_road;

Then fill in the tutorial street with an arbitrary region_id. This is a hack that probably needs to be updated somehow.

INSERT INTO sidewalk.street_edge_region (street_edge_id, region_id)
    SELECT MAX(street_edge_id), 1
    FROM sidewalk.street_edge;

Fix the primary key sequences for tables that had data in the schema by running these queries:

SELECT setval('sidewalk.label_type_label_type_id_seq', (SELECT MAX(label_type_id) from sidewalk.label_type));
SELECT setval('sidewalk.mission_type_mission_type_id_seq', (SELECT MAX(mission_type_id) from sidewalk.mission_type));
SELECT setval('sidewalk.role_role_id_seq', (SELECT MAX(role_id) from sidewalk.role));
SELECT setval('sidewalk.survey_category_option_survey_category_option_id_seq', (SELECT MAX(survey_category_option_id) from sidewalk.survey_category_option));
SELECT setval('sidewalk.survey_question_survey_question_id_seq', (SELECT MAX(survey_question_id) from sidewalk.survey_question));
SELECT setval('sidewalk.tag_tag_id_seq', (SELECT MAX(tag_id) from sidewalk.tag));

A temporary fix to deal with some issues with the evolutions files:
- Edit conf/evolutions/default/68.sql, adding the line SELECT setval('mission_type_mission_type_id_seq', (SELECT MAX(mission_type_id) from "mission_type")); after the "!Ups" line.
- Edit conf/evolutions/default/72.sql, adding the line SELECT setval('tag_tag_id_seq', (SELECT MAX(tag_id) from "tag")); after the "!Ups" line.
- Visit the landing page using an incognito window (but don't go to the audit page yet). If the page successfully loads, then the fix worked. Revert the two previous changes you made to the evolutions files since we don't need them anymore.
Create logins for both Mikey and Jon. Set those accounts to both be "Owner" in the database (again, don't visit anything but the landing page yet).
Delete the qgis_* table from the databases.

Removing streets with no imagery

Create a CSV from the following query, naming it street_edge_endpoints.csv. Note that if you are starting with only a subset of the neighborhoods, you probably want to filter for just those regions in this query.
```
SELECT street_edge.street_edge_id, region_id, x1, y1, x2, y2, geom
FROM street_edge
INNER JOIN street_edge_region ON street_edge.street_edge_id = street_edge_region.street_edge_id;
```
Run the check_streets_for_imagery.py file (python2 check_streets_for_imagery.py). Depending on the city, this could take a very long time. You can leave it to run in the background. But it will occasionally fail to connect with the Google Maps servers and quit. Just restart the script and it will start from where it left off. When it is completely finished, you will have a CSV named streets_with_no_imagery.csv.
Mark the deleted column as TRUE in the street_edge table for all of the IDs listed in streets_with_no_imagery.csv.
```
UPDATE street_edge
SET deleted = TRUE
WHERE street_edge_id IN ();
```

Delete the entries in the street_edge_priority table for all deleted streets:

DELETE FROM street_edge_priority
USING street_edge
WHERE street_edge_priority.street_edge_id = street_edge.street_edge_id
    AND deleted = TRUE;

Force the region_completion table to update by running the query TRUNCATE TABLE region_completion;
Note that the total street distance (which is used to calculate percentage of the city that is complete on the landing page) does not update automatically when the streets are marked as deleted. If you are doing this on your dev environment, you can just restart the web server to invalidate the cache. If this is being done on a live server, you can use the 'Clear Play cache' button on the admin page.

If you want to start with a subset of neighborhoods (needs testing)

Figure out the region_ids of the regions you want to start with. For the example queries, I'll use regions 1, 2, and 3.
Make sure that tutorial street is in one of the regions that is not deleted by modifying it's entry in the street_edge_region table.
Mark the other regions as deleted (UPDATE region SET deleted = TRUE WHERE region_id NOT IN (1, 2, 3);).

Mark the streets outside those regions as deleted:

UPDATE street_edge
SET deleted = TRUE
FROM street_edge_region
WHERE street_edge.street_edge_id = street_edge_region.street_edge_id
    AND street_edge_region.region_id NOT IN (1, 2, 3);

Delete the entries in the street_edge_priority table for streets outside those regions:

DELETE FROM street_edge_priority
USING street_edge
WHERE street_edge_priority.street_edge_id = street_edge.street_edge_id
    AND street_edge.deleted = TRUE;

Truncate the region_completion table (TRUNCATE TABLE region_completion;).

Changes to config/cityparams.conf file and other configuration changes

For the lat/lng bounds, I typically use Google Maps to get the lat-lng for the center of the city. Then I play around with the bound parameters a bit, refreshing to look at a map on the website until I find that I can pan an amount that feels similar to how much you can pan on the servers for other cities.
You'll need to create a new Google Analytics ID for this city. Login at analytics.google.com, click on "Admin" in the bottom left, create a new account and call it "Project Sidewalk <new-city-name>". Try to copy the same setup parameters as for the other accounts. Once you create the account you should get a new ID that you can add to the cityparams.conf file. You'll need to make a separate property for the prod and test server. When you create a property, you'll then create a data stream; the data stream is what will give you your Google Analytics ID.
You'll also want to create a new directory in public/assets that has the name of the city-id for the new city. Add any files there that the other cities have. As of this writing, it is just a skyline.png; all cities have the same skyline except for Seattle.
You'll need to log in to the Google Cloud Console, click on APIs & Services -> Credentials. Click on the main API key, and add the future URLs for the test and prod servers to the list of websites that can be used with the API key.
You'll then want to fill in any params in the cityparams.conf file that have no value for your city.
- update-offset-hours can be set to any integer, we just use it to spread out work on the server.
- excluded-tags can be an empty list for now, but you'll want to go through the tags and remove any that aren't relevant to your municipality before deployment. If you're unsure, you can copy the config for a city with similar infrastructure.
- landing-page-url should eventually be filled in, but you can start with an empty string to test out the page locally.
- mapathon-event-link is not required.
- old-google-analystics-id is not required.
Once you've made the changes above, you can visit the audit page and test to make sure the entire site works.

Get a server set up with this database

This is internal documentation for setting up the server at UW. We do not have experience setting up a dedicated server elsewhere, so we won't be able to assist with this step, unfortunately!

Start by creating a dump of the database: pg_dump -Fc <new-database-name> > <new-dump-name>

Then email Jason (jhowe@cs.washington.edu) asking to set up a new server for this city. Here is a template email: Hey Jason,

I believe we are ready to roll out a Seattle test server! Here are the specs I remember needing...

The URL should be sidewalk-sea-test.cs.washington.edu
We would like a redirect from sidewalk-seattle-test.cs.washington.edu to the above
We want it running the develop branch of https://github.com/ProjectSidewalk/SidewalkWebpage (including the same auto-deploy webhook as sidewalk-newberg-test (on any push to develop)
I have attached a database dump to use.
The environment variable "SIDEWALK_CITY_ID" should be set to "seattle-wa"
The environment variable "ENV_TYPE" should be set to "test"
Make sure I have access to the database on barb
Would like the same permissions on the database as for the others (which was full r/w permission).

Setting up pano scraper (optional)

This is section is only necessary if the goal of setting up this database is to create a dataset for training computer vision models. You can skip if your goal is just to collect accessibility data through Project Sidewalk. Much of these instructions will need to be adapted for your own situation either way.

Make sure the production server is set up before setting up the pano scraper.
Log on to the sftp UW server (ask Mikey how to get on it) and create a new directory for the city.
In your own file system, create a file called log.csv with the same headers we have for the scraper logs for other cities. Make sure the file does not end in a newline with the command truncate -s -1 log.csv.
Log on to the EC2 Instance (ask Mikey how to get on it). Edit the /var/spool/cron/crontabs/ubuntu and add a new entry for the new city. Just copy one of the other commands and change the storage location on the sftp server and the new city's URL.
Change the hour that the scraper runs for each city (2nd column in the crontab file) to space them out evenly throughout the day.
On the SFTP server with the panos, create a new directory sidewalk_panos/Panoramas/scrapes_dump_<city-name>.
In that directory, run put /<local>/<path>/<to>/log.csv to upload that file to the remote server.

Setting up Uptime Robot to ping servers (optional)

Log in to Uptime Robot
Click "Add New Monitor", choose the HTTP(s) monitor type at a 5 minute interval.
Set the URL to use https and set it to the signIn endpoint (e.g., https://sidewalk-pittsburgh-test.cs.washington.edu/signIn).
Share prod server alerts with Jon, Mikey, and the ML Lab, but share test server alerts with just Mikey.