Revealing new neighborhoods

This is a guide for when we start a city with only a subset of it's neighborhoods, but we now want to expand to more of those neighborhoods. If the street/neighborhood data are not already present in the database, then this method will not work.

1. First, we want to check for streets in the new neighborhoods that do not have GSV imagery. We have a script for that, but we need to get the street data first. Run the following query and save it in a file named street_edge_endpoints.csv and put it in the root directory of the Sidewalk repo. Make sure to fill in the region_id filter with the IDs of the neighborhoods you want to add.

   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
   WHERE region_id IN ();
   

2. Run make dev and then python2 check_streets_for_imagery.py in that container (you don't need to do it in this container, but all the Python libraries are already installed there so this is easiest if you have the dev environment set up already). This could take awhile, but progress is shown in the terminal and is saved periodically. If it stops moving forward, just restart it. It outputs streets_with_no_imagery.csv in the same directory.

3. Now we mark the streets in the new neighborhoods as deleted = FALSE, except for the streets listed in streets_with_no_imagery.csv. Grab the street IDs from there and fill it in the query below. Also make sure to fill in the region IDs again. If there are a lot of rows in the CSV, consider using a text editor like Sublime Text that can find/replace newline characters with commas; this will make it fast to put those IDs into the query.

   UPDATE street_edge
   SET deleted = FALSE
   FROM street_edge_region
   WHERE street_edge.street_edge_id = street_edge_region.street_edge_id
       AND region_id IN ()
       AND street_edge.street_edge_id NOT IN ();
   

4. Add these new streets to the street_edge_priority table. Just run the query below, nothing extra needs to be filled in.

   INSERT INTO street_edge_priority (street_edge_id, priority)
       SELECT street_edge.street_edge_id, 1
       FROM street_edge
       LEFT JOIN street_edge_priority ON street_edge.street_edge_id = street_edge_priority.street_edge_id
       WHERE deleted = FALSE
           AND priority IS NULL;
   

5. Set the regions as deleted = FALSE; again, fill in the region IDs.

   UPDATE region SET deleted = FALSE WHERE region_id IN ();
   

6. Truncate the region_completion table. Next time the landing page loads, this will be automatically filled in with the new neighborhoods.

   TRUNCATE TABLE region_completion;
   

7. Log in and go to the admin dashboard. Click the Clear Play cache button. This will reset the cached value for the full distance for the neighborhood. The percent complete on the landing page should now be correct.

8. You can then delete the street_edge_endpoints.csv and streets_with_no_imagery.csv files.