How we generate intersections for Who's On First
This is work in progress. Specifically we are trying to figure out to operationalize generating intersections. In other words this is wet paint. Proceed with caution.
- Find source data
- Prepare and import source data into Postgres with
shp2pgsql - Convert all the road segments to unified line strings
- Find the list of unique street names
- Dissolve line segments for each street name in to a single linestring
- Store linestrings in a new table
- Find all the intersections for the set of linestrings as points
- Geneate Voronoi polygons from the set of resulting points
- Clip polygons to a container Who's On First polygon (typically a locality)
Something something something. Databases. Something something something. Schemas need to be written. Something something something.
Database A is where source data is stored. It contains a geom field that is whatever shp2pgsql decides it needs and an arbitrary number of properties.
Database B is where the union of line segments (for roads) derived from database A is stored. It contains three fields: a WOF ID representing the locality for roads; a street name; a geom field that is a LineString.
Database C is where all the intersections derived from database B are stored. It contains four fields: a WOF ID representing the locality for each intersections; street name X and street name Y (the intersection); a geom field that is a Point.
Database D is where all the Voronoi polygons derived from the points in database C are stored. It contains four fields: a WOF ID representing the locality for each intersections; street name X and street name Y (the intersection); a geom field that is a Polygon.
TBD: Whether database D contains only the raw Voronoi polygons or the clipped (to a WOF locality) polygons. Probably the latter.
Run the whosonfirst_postgis::default Chefworks recipe. If you don't work at Mapzen basically that means "install PostGIS".
createdb roads
psql -c "CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; CREATE EXTENSION btree_gist;" roads
Download https://www.census.gov/cgi-bin/geo/shapefiles/index.php?year=2016&layergroup=Roads
shp2pgsql -s 4326 tl_2016_06037_roads.shp la_roads > la_roads.sql
psql -d roads -f la_roads.sql
SELECT fullname, COUNT(gid) AS count FROM la_roads GROUP BY fullname HAVING fullname != ''
SELECT ST_AsText(ST_Union(geom)) FROM la_roads WHERE fullname = '{FULLNAME}';
This is as far as we've gotten...