def _test_transfer_data_tabular(db1: PostgreSQL, db2: PostgreSQL, csv: DataForTest):
table_name = csv.NAME
# Make sure that this table does not exist in the 2nd database
if table_name in db2.all_tables_as_list():
db2.table_delete(table_name)
# Transfer to the 2nd database
db1.transfer_data_to_another_db(table_name, db2)
# Confirm there is now a table in the DB
assert table_name in db2.all_tables_as_list()
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 _test_import_csv(db: PostgreSQL, csv: DataForTest):
# Confirm the CSV is now a table in the DB
assert csv.NAME in db.all_tables_as_list()
