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)