{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# NYC Subway With BigQuery #"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
" \n",
" \n",
" "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"import geopandas as gpd\n",
"from shapely import wkt\n",
"from google.oauth2 import service_account\n",
"from google.cloud import bigquery\n",
"\n",
"from lets_plot import *\n",
"LetsPlot.setup_html()"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"def load_gdf(client, *, geometry_name, query):\n",
" df = client.query(query).result().to_dataframe()\n",
" df[geometry_name] = df[geometry_name].apply(wkt.loads)\n",
" return gpd.GeoDataFrame(df, geometry=geometry_name)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [],
"source": [
"# You should add your own bigquery_credentials.json file\n",
"# See https://cloud.google.com/docs/authentication/getting-started\n",
"credentials = service_account.Credentials.from_service_account_file(\n",
" '../data/bigquery_credentials.json',\n",
" scopes=['https://www.googleapis.com/auth/cloud-platform'],\n",
")\n",
"client = bigquery.Client(credentials=credentials, project=credentials.project_id)"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"boroughs_gdf = load_gdf(client, geometry_name='borough_geom', query='''\n",
" SELECT borough_name, borough_geom\n",
" FROM `bigquery-public-data.new_york_subway.geo_nyc_borough_boundaries`\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {},
"outputs": [],
"source": [
"stations_gdf = load_gdf(client, geometry_name='station_geom', query='''\n",
" SELECT station_name, line, station_geom\n",
" FROM `bigquery-public-data.new_york_subway.stations`\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [],
"source": [
"lines_gdf = load_gdf(client, geometry_name='line_geom', query='''\n",
" SELECT ny_s.line, ST_MakeLine(ARRAY(\n",
" SELECT station_geom\n",
" FROM `bigquery-public-data.new_york_subway.stations`\n",
" WHERE line = ny_s.line\n",
" )) AS line_geom\n",
" FROM(\n",
" SELECT line \n",
" FROM `bigquery-public-data.new_york_subway.stations`\n",
" ) AS ny_s\n",
" GROUP BY line\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
" \n",
" "
],
"text/plain": [
""
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"ggplot() + \\\n",
" geom_polygon(aes(fill='borough_name'), data=boroughs_gdf, color='black', alpha=.15, \\\n",
" tooltips=layer_tooltips().line('@borough_name')) + \\\n",
" geom_path(aes(color='line'), data=lines_gdf, size=2) + \\\n",
" geom_point(data=stations_gdf, shape=1, size=1.5, color='black', \\\n",
" tooltips=layer_tooltips().line('@station_name')) + \\\n",
" ggtitle('NYC Subway') + \\\n",
" ggsize(800, 600) + \\\n",
" theme(legend_position='none', axis_title='blank', axis_text='blank', axis_ticks='blank', \\\n",
" axis_line='blank', axis_tooltip='blank')"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.10"
},
"pycharm": {
"stem_cell": {
"cell_type": "raw",
"metadata": {
"collapsed": false
},
"source": []
}
}
},
"nbformat": 4,
"nbformat_minor": 2
}