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()