Adding new road geometries to a database

This is a guide that explains how to add streets that are missing from our database that are present in OSM. This usually occurs because of new construction or if the categorization of the road in OSM does not match our heuristic for roads that should be included.

1. Go to an OSM editor and find the area where you want to add streets.
2. Create a list of streets that aren't in the database that should be (you're recording the way_ids).
   • If using iD editor, use ctrl-I to get more info on the street (including the way_id, which you'll need).
   • You can compare the OSM editor map to both the Sidewalk road network and Google Maps (which will allow you to see which streets even have imagery).
   • To make things easier on myself, once I record the way_id, I change the street to be a "primary road", which makes it easy to see which streets I've already recorded; making these edits doesn't matter, since we aren't going to save the edits anyway.
3. Check the database for any streets that may have been accidentally added that were already in the database. You can use the query below, filling in the () with the way_ids. Remove any of those from the list.

   SELECT * FROM osm_way_street_edge WHERE osm_way_id IN ();
   

4. Export the area's OSM data and load into QGIS. If it's a small area, you should just be able to click "Export" on openstreetmap.org. If it says the area is too large, you can use something like bbbike. You should only need the roads/lines.
5. Filter for the list of roads that you recorded.
   • Right click on the layer and click "Filter...". Type "osm_id" IN (), filling in the () with your list of way_ids.
   • Note that the "osm_id" attribute may a string datatype (depending on where you downloaded from), so you may need to wrap all the way_ids in single quotes.
6. Jump into the creating a database for a new city wiki page at the Split lines along intersections section. Start working through these instructions, since they will add the appropriate metadata to the streets so that they can be added to the database. Make sure to skip sections that don't apply, like creating a new blank database, adding the tutorial street to the database, etc. Follow along with the sub-bullets below carefully; these are modifications to the instructions for our specific use case.
   1. You might need to connect to a local copy of the database so you can grab the regions to intersect with to get region_ids. If you know that all the streets you are adding are in a single region, you can just assign the region_id manually by adding a new attribute to the roads layer.
   2. When creating the road_id, make it row_number() + <max-street-edge-id> instead of just row_number(). Fill in <max-street-edge-id> by checking for the largest street_edge_id in the database with SELECT MAX(street_edge_id) FROM street_edge;. Write the max street_edge_id down, you'll need it a few more times during this process.
   3. Note that you're going to get everything working in a local copy of the database first. We will use a faster process to move everything over to the test/prod databases once we know everything is working locally.
   4. When adding to the street_edge_priority table, replace WHERE deleted = FALSE with WHERE street_edge_id > <old-max-street-edge-id> so that you only add the new streets.
   5. After you add to the street_edge_region table, skip all the step until you get to deleting the qgis_road and qgis_node tables. Delete those tables, then continue with the Removing streets with no imagery section.
   6. When making creating street_edge_endpoints.csv, filter for only the new streets you added using WHERE street_edge_id > <old-max-street-edge-id>.
   7. Once you finish the removing streets with no imagery section, you can stop following the instructions on that page. The rest of what you need to do is on this page.
7. Run these queries to mark streets outside the currently available regions as deleted:

   UPDATE street_edge
   SET deleted = TRUE
   FROM street_edge_region
   INNER JOIN region ON street_edge_region.region_id = region.region_id
   WHERE street_edge.street_edge_id = street_edge_region.street_edge_id
       AND region.deleted = TRUE
       AND street_edge.street_edge_id > <old-max-street-edge-id>;
   
   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;
   

8. Load the dev environment for the relevant city and check the distance left in the affected neighborhoods on the landing page's choropleth. Then run TRUNCATE TABLE region_completion; and reload the landing page. The new streets you added to the database should have increased the distance remaining in those neighborhoods. This is just to check that everything worked correctly.
9. It would be a good idea to do some more testing to make sure that everything works correctly. Go to a neighborhood where you added streets and keep clicking Jump -> I cannot go in the direction you want me to walk. This way you can quickly mark a bunch of streets in the neighborhood as audited. You can keep going until you see that you've been asked to audit a few streets that you recognize as streets that you just added to the database. If you aren't seeing them, keep doing this through the entire neighborhood, then check /labelMap and look at the black lines indicating the audited streets. Do you see the streets that you added in there? If so, you know that it worked and you're ready to add the data to the actual databases.
10. Put the data into the test/prod databases.
    1. Start by making a CSV of the new data added to each of the tables using the queries below. I usually just run the query using Valentina Studio and then click the "Export Result" button. But there are ways to create a CSV from the command line as well. Fill in the <old-max-street-edge-id> as you have throughout.

       SELECT region_id, data_source, name, geom, deleted FROM region WHERE region_id > <old-max-region-id>;
       SELECT street_edge_id, geom, x1, y1, x2, y2, way_type, deleted, timestamp FROM street_edge WHERE street_edge_id > <old-max-street-edge-id>;
       SELECT street_edge_priority_id, street_edge_id, priority FROM street_edge_priority WHERE street_edge_id > <old-max-street-edge-id>;
       SELECT street_edge_region_id, street_edge_id, region_id FROM street_edge_region WHERE street_edge_id > <old-max-street-edge-id>;
       SELECT osm_way_street_edge_id, osm_way_id, street_edge_id FROM osm_way_street_edge WHERE street_edge_id > <old-max-street-edge-id>;
       

    2. scp the CSVs over to the server where you have access to the database.
    3. Start by signing in to the test database for the relevant city and run the queries below. Fill in the /path/to/file.csv appropriately. If your CSVs do not have a header row, remove HEADER from the queries.

       \copy region(region_id, data_source, name, geom, deleted) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
       \copy street_edge(street_edge_id, geom, x1, y1, x2, y2, way_type, deleted, timestamp) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
       \copy street_edge_priority(street_edge_priority_id, street_edge_id, priority) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
       \copy street_edge_region(street_edge_region_id, street_edge_id, region_id) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
       \copy osm_way_street_edge(osm_way_street_edge_id, osm_way_id, street_edge_id) FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
       

    4. Do the same test to make sure everything worked that you did for your local dev environment (starting with loading the landing page, running TRUNCATE TABLE region_completion;, then loading again).
    5. If everything worked correctly, do the same process for the prod server!