def import_shapefiles(folder: Path, db: PostgreSQL):
""" Import all shapefiles within the folder into SQL.
This includes:
- nj_centerline.shp
- TIM_Microzones_poi_surface.SHP
"""
endings = [".shp", ".SHP"]
for ending in endings:
for shp_path in folder.rglob(f"*{ending}"):
idx = len(ending) * -1
pg_name = shp_path.name[:idx].replace(" ", "_").lower()
db.import_geodata(pg_name, shp_path, if_exists="replace")
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 database_1():
# Set up the database
db = PostgreSQL("test_from_ward",
verbosity="minimal",
**configurations()["localhost"])
# Import CSV and shapefile data sources
db.import_csv(test_csv_data.NAME,
test_csv_data.PATH_URL,
if_exists="replace")
db.import_geodata(test_shp_data.NAME,
test_shp_data.PATH_URL,
if_exists="replace")
# Yield to the test
yield db
# Don't tear down this database!!!
# This is done later as part of test_final_cleanup.py
# Delete temp shapefiles
test_shp_data.flush_local_data()
test_csv_data.flush_local_data()
