def _test_hexagon_overlay(db: PostgreSQL, shp: DataForTest):
hex_table_name = "test_hexagons"
# Make a hexagon layer
db.make_hexagon_overlay(
hex_table_name, table_to_cover=shp.NAME, desired_epsg=2272, hexagon_size=5
)
assert hex_table_name in db.all_spatial_tables_as_dict()
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)
