NYC Subway With BigQuery¶

def load_gdf(client, *, geometry_name, query):
    df = client.query(query).result().to_dataframe()
    df[geometry_name] = df[geometry_name].apply(wkt.loads)
    return gpd.GeoDataFrame(df, geometry=geometry_name)

# You should add your own bigquery_credentials.json file
# See https://cloud.google.com/docs/authentication/getting-started
credentials = service_account.Credentials.from_service_account_file(
    '../data/bigquery_credentials.json',
    scopes=['https://www.googleapis.com/auth/cloud-platform'],
)
client = bigquery.Client(credentials=credentials, project=credentials.project_id)

boroughs_gdf = load_gdf(client, geometry_name='borough_geom', query='''
    SELECT borough_name, borough_geom
    FROM `bigquery-public-data.new_york_subway.geo_nyc_borough_boundaries`
''')

stations_gdf = load_gdf(client, geometry_name='station_geom', query='''
    SELECT station_name, line, station_geom
    FROM `bigquery-public-data.new_york_subway.stations`
''')

lines_gdf = load_gdf(client, geometry_name='line_geom', query='''
    SELECT ny_s.line, ST_MakeLine(ARRAY(
        SELECT station_geom
        FROM `bigquery-public-data.new_york_subway.stations`
        WHERE line = ny_s.line
        )) AS line_geom
    FROM(
        SELECT line 
        FROM `bigquery-public-data.new_york_subway.stations`
        ) AS ny_s
    GROUP BY line
''')