def clip_inputs(db: PostgreSQL, state: str, municipality: str = None, buffer_meters: float = None): if state.upper() == "NJ": opposite_state = "Pennsylvania" else: opposite_state = "New Jersey" # Build up a SQL query that might buffer and/or include # a single municipality name if buffer_meters: place_query = f"SELECT st_union(st_buffer(geom, {buffer_meters})) " else: place_query = "SELECT st_union(geom) " place_query += f""" FROM public.municipalboundaries WHERE state = '{state.upper()}' """ if municipality: place_query += f" AND UPPER(mun_label) = '{municipality.upper()}' " # Make a database schema for this clip if municipality: schema = municipality.lower().replace(" ", "_") else: schema = state.lower() db.execute(f""" DROP SCHEMA IF EXISTS {schema} CASCADE; CREATE SCHEMA {schema}; """) data_to_clip = [ ("pedestriannetwork_lines", "sidewalks", "LineString"), ("regional_pois", "points_of_interest", "Point"), ("sw_nodes", "sw_nodes", "Point"), ("regional_transit_stops", "transit_stops", "Point"), (f"{state}_centerline", "centerlines", "LineString") ] for src_name, new_name, geom_type in data_to_clip: print(f"Clipping {src_name}") # Clip query will respect the buffer provided, # but will NOT include features from the 'opposite_state' clip_query = f""" SELECT * FROM public.{src_name} t WHERE ST_INTERSECTS(t.geom, ({place_query})) AND NOT ST_INTERSECTS(geom, (select st_collect(geom) from public.regional_counties where state_name = '{opposite_state}')) """ db.make_geotable_from_query(clip_query, new_name, geom_type, 26918, schema=schema)
def merge_sidewalks_and_trails(db: PostgreSQL): query = """ SELECT src, geom FROM trail_merged UNION SELECT src, geom FROM sidewalk_merged """ db.make_geotable_from_query(query, "sidewalks_and_trails", geom_type="LINESTRING", epsg=26918)
def prep_data(db: PostgreSQL): """ 1) Import necessary shapefiles to PostGIS 2) Extract nodes from street segments within study area 3) Assign closest street node ID to all SEPTA bus stops near study area """ # 1) Import necessary shapefiles to PostGIS # ----------------------------------------- all_tables = db.all_tables_as_list() for sql_tablename, shp_path_suffix in [ ("philly_streets", "philly complete streets/philly_complete_streets.shp"), ("septa_bus_stops_fall_2019", "Fall_2019_Stops_By_Route/Fall_2019_Stops_By_Route.shp"), ("study_bounds", "Draft_Study_Area_Extent/U_CIty_Study_Area_Dissolve_2.shp"), ]: if sql_tablename not in all_tables: full_path = GIS_FOLDER / shp_path_suffix db.import_geodata(sql_tablename, full_path) # 2) Extract nodes from street segments within study area # ------------------------------------------------------- point_query = """ with raw as ( select st_startpoint(geom) as startpoint, st_endpoint(geom) as endpoint from philly_streets where st_intersects( geom, (select st_collect(geom) from study_bounds) ) ), merged_data as ( select startpoint as geom from raw union select endpoint as geom from raw ) select row_number() over() as streetnodeid, geom from merged_data group by geom """ db.make_geotable_from_query(point_query, "street_nodes", "POINT", 26918)
def generate_islands(db: PostgreSQL, schema: str): """ Use the sidewalk layer to merge intersecting geometries. The output is a layer with one feature per 'island' """ query = f""" SELECT ST_COLLECTIONEXTRACT( UNNEST(ST_CLUSTERINTERSECTING(geom)), 2 ) AS geom FROM {schema}.sidewalks """ db.make_geotable_from_query(query, "islands", "MULTILINESTRING", 26918, schema=schema)
def add_segmentation_to_sidewalks(db: PostgreSQL): # Split the sidewalks wherever they intersect a trail # --------------------------------------------------- sidewalk_split = """ select globalid, (st_dump( st_split( s.geom, (select st_collect(geom) from ped_trails t where st_intersects(s.geom, t.geom) ) ) )).geom from pedestriannetwork_lines s """ db.make_geotable_from_query(sidewalk_split, "sidewalk_splits", geom_type="LINESTRING", epsg=26918) # Merge the split sidewalks with any sidewalks that didn't get split # ------------------------------------------------------------------ sidewalk_merge = """ select 'sidewalk - raw' as src, geom from pedestriannetwork_lines where not st_within(geom, (select st_buffer(st_collect(geom), 0.5) from sidewalk_splits) ) union select 'sidewalk - split' as src, geom from sidewalk_splits """ db.make_geotable_from_query(sidewalk_merge, "sidewalk_merged", geom_type="LINESTRING", epsg=26918)
def _test_make_geotable_from_query(db: PostgreSQL, shp: DataForTest): new_geotable = "test_make_geotable_multilinestring" query = f""" SELECT ST_UNION(geom) AS geom FROM {shp.NAME} """ # Make a new geotable db.make_geotable_from_query(query, new_geotable, geom_type="MULTILINESTRING", epsg=shp.EPSG) # Confirm that the new table's EPSG matches the expected value epsg = db.all_spatial_tables_as_dict()[new_geotable] assert epsg == shp.EPSG
def create_new_geodata(db: PostgreSQL): """ 1) Merge DVRPC municipalities into counties 2) Filter POIs to those within DVRPC counties """ pa_counties = "('Bucks', 'Chester', 'Delaware', 'Montgomery', 'Philadelphia')" nj_counties = "('Burlington', 'Camden', 'Gloucester', 'Mercer')" # Add regional county data regional_counties = f""" select co_name, state_name, (st_dump(st_union(geom))).geom from public.municipalboundaries m where (co_name in {pa_counties} and state_name ='Pennsylvania') or (co_name in {nj_counties} and state_name = 'New Jersey') group by co_name, state_name """ db.make_geotable_from_query( regional_counties, "regional_counties", "Polygon", 26918, schema="public" ) # Clip POIs to those inside DVRPC's region regional_pois = """ select * from public.points_of_interest where st_intersects(geom, (select st_collect(geom) from public.regional_counties)) """ db.make_geotable_from_query( regional_pois, "regional_pois", "Point", 26918, schema="public" )
def hexagon_summary(db: PostgreSQL): db.make_hexagon_overlay("hexagons", "regional_counties", 26918, 3) for colname in [ "islands", "poi_min", "poi_median", "poi_max", "cl_len", "sw_len" ]: db.table_add_or_nullify_column("hexagons", colname, "FLOAT") for state, schema in [("New Jersey", "nj"), ("Pennsylvania", "pa")]: print(f"Processing {state}") hex_query = f""" SELECT * FROM hexagons WHERE st_intersects( st_centroid(geom), (select st_collect(geom) from regional_counties where state_name = '{state}' ) ) """ db.make_geotable_from_query(hex_query, "hexagon_summary", "POLYGON", 26918, schema=schema) uid_query = f""" SELECT uid FROM {schema}.hexagon_summary """ uid_list = db.query_as_list(uid_query) for uid in tqdm(uid_list, total=len(uid_list)): uid = uid[0] geom_subquery = f"select geom from {schema}.hexagon_summary where uid = {uid}" # Get the number of islands # ------------------------- island_update = f""" update {schema}.hexagon_summary h set islands = ( select count(island_geom) from ( SELECT ST_COLLECTIONEXTRACT( UNNEST(ST_CLUSTERINTERSECTING(geom)), 2 ) AS geom FROM {schema}.sidewalks sw where st_within(sw.geom, h.geom) ) as island_geom ) where h.uid = {uid} """ db.execute(island_update) # Get the min and max distance to nearest school # ---------------------------------------------- q_network = f""" select min(n_1_school), median(n_1_school), max(n_1_school) from {schema}.access_results where n_1_school < 180 and st_intersects( geom, ({geom_subquery}) ) """ poi_result = db.query_as_list(q_network) poi_min, poi_med, poi_max = poi_result[0] # # Replace "None" values with a dummy number if str(poi_min) == "None": poi_min = "NULL" if str(poi_med) == "None": poi_med = "NULL" if str(poi_max) == "None": poi_max = "NULL" # Get the centerline length # ------------------------- cl_query = f""" select sum(st_length(st_intersection( geom, ({geom_subquery}) ))) as cl_len from {schema}.centerlines where st_intersects(geom, ({geom_subquery})) """ cl_results = db.query_as_list(cl_query) cl_len = cl_results[0][0] if str(cl_len) == "None": cl_len = 0 # Get the sidewalk length # ------------------------- sw_query = f""" select sum(st_length(st_intersection( geom, ({geom_subquery}) ))) as sw_len from {schema}.sidewalks where st_intersects(geom, ({geom_subquery})) """ sw_results = db.query_as_list(sw_query) sw_len = sw_results[0][0] if str(sw_len) == "None": sw_len = 0 # Update the table with the results # --------------------------------- update_query = f""" UPDATE {schema}.hexagon_summary SET poi_min = {poi_min}, poi_median = {poi_med}, poi_max = {poi_max}, cl_len = {cl_len}, sw_len = {sw_len} WHERE uid = {uid} """ db.execute(update_query) # Combine state-specific hexagons into one final summary layer # ------------------------------------------------------------ query = """ SELECT * FROM nj.hexagon_summary UNION SELECT * FROM pa.hexagon_summary """ db.make_geotable_from_query(query, "hexagon_summary", "POLYGON", 26918)
def add_segmentation_to_trails(db: PostgreSQL): """ Split the trail layer wherever it intersects a sidewalk """ # Make a filtered version of the trail data that pedestrians can use # ------------------------------------------------------------------ trail_query = """ SELECT * FROM circuittrails WHERE circuit = 'Existing' AND (facility NOT LIKE '%%Bicycle%%' OR facility IS NULL); """ db.make_geotable_from_query(trail_query, "ped_trails", geom_type="LINESTRING", epsg=26918) # Split the trails wherever they intersect a sidwalk # -------------------------------------------------- trail_split = """ select globalid, (st_dump( st_split( t1.geom, (select st_collect(geom) from pedestriannetwork_lines p1 where st_intersects(t1.geom, p1.geom) ) ) )).geom from ped_trails t1 """ db.make_geotable_from_query(trail_split, "trail_splits", geom_type="LINESTRING", epsg=26918) # Merge the split trails with any trails that didn't get split # ------------------------------------------------------------ trail_merge = """ select 'trail - raw' as src, geom from ped_trails where not st_within(geom, (select st_buffer(st_collect(geom), 1.5) from trail_splits ts2) ) union select 'trail - split' as src, geom from trail_splits """ db.make_geotable_from_query(trail_merge, "trail_merged", geom_type="LINESTRING", epsg=26918)
def prepare_trail_data(db: PostgreSQL): # Filter down to only the existing trails # --------------------------------------- trail_query = " SELECT * FROM circuittrails WHERE circuit = 'Existing' " db.make_geotable_from_query( trail_query, "existing_trails", geom_type="LINESTRING", epsg=26918 ) # Figure out if each segment should be included # --------------------------------------------- db.table_add_or_nullify_column("existing_trails", "sw_coverage", "FLOAT") uid_list = db.query_as_list("SELECT uid FROM existing_trails") # Template to get the % covered by sidewalk features query_template = """ select sum( st_length( st_intersection(geom, (select st_buffer(geom, 10) from existing_trails where uid = UID) ) ) ) / (select st_length(geom) from existing_trails where uid = UID) from pedestriannetwork_lines where st_dwithin( st_startpoint(geom), (select geom from existing_trails where uid = UID), 10 ) or st_dwithin( st_endpoint(geom), (select geom from existing_trails where uid = UID), 10 ) """ for uid in tqdm(uid_list, total=len(uid_list)): uid = uid[0] query = query_template.replace("UID", str(uid)) result = db.query_as_single_item(query) if not result: result = 0 update_query = f""" UPDATE existing_trails SET sw_coverage = {result} WHERE uid = {uid}; """ db.execute(update_query)