def classify_centerlines(
db: PostgreSQL,
schema: str,
tbl: str,
new_col: str = "sidewalk"
):
# Get a list of all centerlines we want to iterate over.
oid_query = f"""
SELECT objectid FROM {schema}.{tbl}
"""
# But first... check if the new_col exists
# If so, iterate over null features
# Otherwise, make the column and operate on the entire dataset
column_already_existed = new_col in db.table_columns_as_list(tbl, schema=schema)
if column_already_existed:
print("Picking up where last left off...")
oid_query += f"""
WHERE {new_col} IS NULL
"""
else:
print("Analyzing for the first time...")
db.table_add_or_nullify_column(tbl, new_col, "FLOAT", schema=schema)
# Hit the database
oid_list = db.query_as_list(oid_query)
# pop the results out of tuples into a simple list
oid_list = [x[0] for x in oid_list]
query_template = f"""
SELECT
SUM(
ST_LENGTH(
ST_INTERSECTION(sw.geom, (SELECT ST_BUFFER(c.geom,25)))
)
)
FROM
{schema}.sidewalks sw, {schema}.centerlines c
where
c.objectid = OID_PLACEHOLDER
AND
ST_INTERSECTS(sw.geom, (SELECT ST_BUFFER(c.geom,25)))
AND
sw.line_type = 1
AND
(
ST_LENGTH(
ST_INTERSECTION(sw.geom, (SELECT ST_BUFFER(c.geom,25)))
) > 25
OR ST_LENGTH(sw.geom) <= 25
)
"""
for oid in tqdm(oid_list, total=len(oid_list)):
oid_query = query_template.replace("OID_PLACEHOLDER", str(oid))
sidwalk_length_in_meters = db.query_as_single_item(oid_query)
if not sidwalk_length_in_meters:
sidwalk_length_in_meters = 0
update_query = f"""
UPDATE {schema}.{tbl} c
SET {new_col} = {sidwalk_length_in_meters}
WHERE objectid = {oid}
"""
db.execute(update_query)
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 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)
