def _test_import_csv_matches(db: PostgreSQL, csv: DataForTest):
# Import a CSV file
df = pd.read_csv(csv.PATH_URL)
# Get the number of rows in the raw dataframe
csv_row_count, _ = df.shape
# Get the number of rows in the new SQL table
query = f"""
SELECT COUNT(*) FROM {csv.NAME}
"""
db_table_row_count = db.query_as_single_item(query)
# Confirm that the dataframe & SQL table have matching rowcounts
assert csv_row_count == db_table_row_count
def assign_stops_to_street_intersections(db: PostgreSQL):
# 3) Assign closest street node ID to all SEPTA bus stops near study area
# -----------------------------------------------------------------------
df = db.query_as_df(
"""
select gid
from septa_bus_stops_fall_2019
where
st_dwithin(
st_transform(geom, 26918),
(select st_collect(geom) from study_bounds sb),
30
)
"""
)
db.table_add_or_nullify_column("septa_bus_stops_fall_2019", "nearest_node", "int")
for _, row in tqdm(df.iterrows(), total=df.shape[0]):
gid = row[0]
query = f"""
select
n.streetnodeid
from
septa_bus_stops_fall_2019 s,
street_nodes n
where
s.gid = {gid}
order by
st_distance(st_transform(s.geom, 26918), n.geom) asc
limit 1
"""
node_id = db.query_as_single_item(query)
update = f"""
update septa_bus_stops_fall_2019
set nearest_node = {node_id}
where gid = {gid}
"""
db.execute(update)
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 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)