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