def import_transit_data(local_db: PostgreSQL): """ Import SEPTA, NJT, and PATCO stops & lines. This code lives in another repo: https://github.com/aaronfraint/philly-transit-data """ transit_data = TransitData() stops, lines = transit_data.all_spatial_data() # Import transit stops local_db.import_geodataframe(stops, "regional_transit_stops") # Massage the lines before importing # - reset index and then explode so all are singlepart lines line_gdf = lines.reset_index() line_gdf = line_gdf.explode() line_gdf["explode_idx"] = line_gdf.index line_gdf = line_gdf.reset_index() local_db.import_geodataframe(line_gdf, "regional_transit_lines") # Reproject from 4326 to 26918 local_db.table_reproject_spatial_data("regional_transit_lines", 4326, 26918, "LINESTRING") local_db.table_reproject_spatial_data("regional_transit_stops", 4326, 26918, "POINT")
def import_production_sql_data(remote_db: PostgreSQL, local_db: PostgreSQL): """ Copy data from DVRPC's production SQL database to a SQL database on localhost. Note - this connection will only work within the firewall. """ data_to_download = [ ("transportation", ["pedestriannetwork_lines", "pedestriannetwork_points", "pa_centerline", "CircuitTrails", ]), ("structure", ["points_of_interest"]), ("boundaries", ["municipalboundaries"]), ] for schema, table_list in data_to_download: for table_name in table_list: # Extract the data from the remote database and rename the geom column query = f"SELECT * FROM {schema}.{table_name};" gdf = remote_db.query_as_geo_df(query, geom_col="shape") gdf = gdf.rename(columns={"shape": "geometry"}).set_geometry("geometry") # Check if we have multipart geometries. # If so, explode them (but keep the index) multipart = False for geom_type in gdf.geom_type.unique(): if "Multi" in geom_type: multipart = True if multipart: print(f"EXPLODING {table_name}") gdf = gdf.explode() gdf['explode'] = gdf.index gdf = gdf.reset_index() # Save to the local database local_db.import_geodataframe(gdf, table_name.lower())