Visualizing BigQuery geospatial data in Colab

Key Concepts

• Geospatial Data: Location-based data used for analysis and visualization.
• BigQuery: Google's fully-managed, serverless data warehouse that enables scalable analysis over petabytes of data.
• Colab Notebook: A free cloud-based Jupyter notebook environment that allows for Python code execution and collaboration.
• GeoPandas: A Python library that extends Pandas to handle geospatial data.
• PyDeck: A Python library for creating interactive geospatial visualizations using deck.gl.
• Scatter Plot: A visualization that displays data points as individual markers on a map.
• Choropleth Map: A thematic map that uses color to represent statistical data aggregated over geographic areas.
• Heatmap: A visualization that uses color intensity to represent the density of data points.
• GeoJSON: A standard format for encoding geographic data structures.
• H3 Geospatial Indexing: A hierarchical, hexagonal geospatial indexing system.
• Polygon: A closed two-dimensional shape defined by a series of connected line segments.
• Point: A single location defined by latitude and longitude coordinates.
• Line: A path between two points.
• GEOGRAPHY data type: A BigQuery data type for storing geospatial data.

BigQuery Geospatial Visualization in Colab

Introduction

The video demonstrates how to leverage BigQuery's geospatial capabilities to analyze and visualize location data within a Colab notebook. It focuses on using Python libraries like GeoPandas and PyDeck to create various map visualizations from public datasets. The presenter emphasizes the value of geospatial data for gaining insights in various fields, such as tracking deliveries, analyzing customer behavior, and understanding environmental patterns.

Setup and Authentication

1. Authentication: The Colab environment is authenticated with a Google Cloud Project (GCP) using google.colab.auth. This allows access to data residing in BigQuery. The user needs to enter their GCP project ID.
2. Google Maps Platform (GMP) API Key (Optional): If a GMP API key is available, it can be stored as a Colab Secret and used as the base map for visualizations. Otherwise, a default basemap is used.
3. API Enablement: The BigQuery API and, optionally, the Maps JavaScript API are enabled for the GCP project using gcloud commands.
4. Library Installation: Necessary Python packages, including geopandas, pydeck, h3-py, and branca, are installed using pip.
5. Helper Function: A helper function, display_pydeck_map, is defined to simplify the creation and display of PyDeck maps, handling the GMP API key configuration.

Visualizing Bike Share Stations with a Scatter Plot

1. Data Retrieval: The %%bigquery magic command is used to query the bikeshare_station_info table from a public BigQuery dataset. The query selects station details and the station_geom column, which contains the location data.
2. GeoDataFrame Creation: The query results are saved as a GeoPandas GeoDataFrame named gdf_sanfrancisco_bike_stations. The station_geom column is of the geometry type.
3. Coordinate Extraction: Longitude (x) and latitude (y) coordinates are extracted from the geometry objects in the GeoDataFrame to create separate columns.
4. ScatterplotLayer Definition: A ScatterplotLayer is defined using PyDeck. It specifies the GeoDataFrame, longitude and latitude columns, point radius, point color, and pickability (for hover information).
5. Map Display: A view state is defined, and the map is displayed using the display_pydeck_map helper function.

Visualizing Neighborhood Boundaries with a GeoJSONLayer

1. Data Retrieval: The boundaries table from the San Francisco neighborhoods public dataset is queried to retrieve neighborhood names and their geometries into a GeoDataFrame.
2. GeoJSONLayer Creation: A GeoJSONLayer is created, passing the GeoDataFrame directly. PyDeck automatically converts the shapely geometry objects to GeoJSON.
3. Layer Configuration: Line and fill colors, and line width are configured for the GeoJSONLayer.
4. Map Display: The map is displayed, showing the outlines of all the neighborhoods in San Francisco.

Creating a Choropleth Map with a PolygonLayer

1. Area Calculation: A new column is added to the GeoDataFrame containing the area of each neighborhood in square kilometers.
2. Spatial Join: A spatial join is performed using GeoPandas' sjoin to count the number of bike stations that fall within each neighborhood.
3. Density Calculation: The density of bike stations per square kilometer is calculated for each neighborhood and joined back to the GeoDataFrame.
4. Polygon Column Creation: A new polygon column is created by extracting the exterior coordinates from each neighborhood's geometry. This is done because PolygonLayer requires an array of points, not a GeoJSON object.
5. Color Mapping: The branca library is used to create a linear color map from light blue to dark red, based on the minimum and maximum station densities.
6. Fill Color Column Creation: A fill_color column is created by applying the color map to the stations_per_sq_km values for each neighborhood, adding some transparency.
7. PolygonLayer Definition: A PolygonLayer is defined, pointing to the prepared GeoDataFrame, specifying the polygon column for the shapes and the fill_color column for the fill. Line colors are also set.
8. Map Display: The map is displayed, visually showing the density of bike stations across different neighborhoods using color intensity.

Visualizing SFPD Incident Reports with a Heatmap

1. Data Retrieval: The sfpd_incidents table is queried for the year 2015, selecting the unique key and location geometry object, which is constructed using the WKT location string.
2. H3 Cell Indexing: The h3-py library is used to calculate the H3 cell index at a resolution of 9 for each incident's latitude and longitude.
3. Aggregation: The data is grouped by the H3 cell index, and the number of incidents in each cell is counted.
4. GeoDataFrame Creation: A new GeoDataFrame is created with the H3 cell index, the center coordinates of each H3 cell (calculated using cell_to_latlng), and the total number of incidents in each cell.
5. Data Preparation: A helper function is created to transform the aggregated GeoDataFrame into a list of dictionaries with latitude, longitude, and weight (number of incidents) for the HeatmapLayer.
6. HeatmapLayer Definition: A HeatmapLayer is defined, providing the prepared heatmap data. The position columns (latitude and longitude) and the weight field (number of incidents) are specified. Opacity, radius, and aggregation method are adjusted.
7. Map Display: The heatmap is displayed, showing the hotspots of SFPD incidents in 2015 across the city.

Conclusion

The video provides a comprehensive walkthrough of using BigQuery's geospatial capabilities with Colab, GeoPandas, and PyDeck. It demonstrates how to create various visualizations, including scatter plots, choropleth maps, and heatmaps, to explore and analyze location data. The presenter encourages viewers to try the notebook themselves and explore the provided resources for further learning. The importance of cleaning up resources after the tutorial is also emphasized.