Create a Colab notebook

This tutorial builds a Colab notebook to visualizegeospatial analytics data. You can open a prebuilt version of thenotebook in Colab, Colab Enterprise, orBigQuery Studio by clicking the links at the top of the GitHub version ofthe tutorial—BigQuery Geospatial Visualization in Colab.

1. Open Colab.

   Open Colab

2. In theOpen notebook dialog, clickNew notebook.

3. ClickUntitled0.ipynb and change the name of the notebook tobigquery-geo.ipynb.

4. SelectFile> Save.

Authenticate with Google Cloud and Google Maps

This tutorial queries BigQuery datasets and uses the Google MapsJavaScript API. To use these resources, you authenticate theColab runtime with Google Cloud and the Maps API.

1. Get your Google Maps API key by following the instructions on theUse APIkeys page inthe Google Maps documentation.

2. Switch to your Colab notebook and then clickvpn_key Secrets.

3. ClickAdd new secret.

4. ForName, enterGMP_API_KEY.

5. ForValue, enter the Maps API key value you generated previously.

6. Close theSecrets panel.

7. To insert a code cell, clickaddCode.

8. To authenticate with the Maps API, enter the following code:

   # Authenticate with the Google Maps JavaScript API.GMP_API_SECRET_KEY_NAME="GMP_API_KEY"#@param {type:"string"}ifGMP_API_SECRET_KEY_NAME:GMP_API_KEY=userdata.get(GMP_API_SECRET_KEY_NAME)ifGMP_API_SECRET_KEY_NAMEelseNoneelse:GMP_API_KEY=None

9. When prompted, clickGrant access to give the notebook access to yourkey, if you agree.

10. Clickplay_circle_filled Runcell.

    After you complete the authorization flow, no output is generated in yourColab notebook. The check mark beside the cell indicates thatthe code ran successfully.

Install Python packages and import data science libraries

In addition to thecolabtools (google.colab)Python modules, this tutorial uses several other Python packages and datascience libraries.

In this section, you install thepydeck andh3 packages.pydeckprovides high-scale spatial rendering in Python, powered bydeck.gl.h3-py provides Uber's H3 HexagonalHierarchical Geospatial Indexing System in Python.

You then import theh3 andpydeck libraries and the following Pythongeospatial libraries:

• geopandas to extend thedatatypes used bypandas to allow spatialoperations on geometric types.
• shapely formanipulation and analysis of individual planar geometric objects.
• branca to generate HTMLand JavaScript colormaps.
• geemap.deckfor visualization withpydeck andearthengine-api.

After importing the libraries, you enable interactive tables forpandasDataFrames in Colab.

Install thepydeck andh3 packages

1. To insert a code cell, clickaddCode.

2. To install thepydeck andh3 packages, enter the following code:

   # Install pydeck and h3.!pip install pydeck>=0.9 h3>=4.2

3. Clickplay_circle_filled Runcell.

   After you complete the installation, no output is generated in yourColab notebook. The check mark beside the cell indicates thatthe code ran successfully.

Import the Python libraries

1. To insert a code cell, clickaddCode.

2. To import the Python libraries, enter the following code:

   # Import data science libraries.importbrancaimportgeemap.deckasgmdkimporth3importpydeckaspdkimportgeopandasasgpdimportshapely

3. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

Enable interactive tables for pandas DataFrames

1. To insert a code cell, clickaddCode.

2. To enablepandas DataFrames, enter the following code:

   # Enable displaying pandas data frames as interactive tables by default.fromgoogle.colabimportdata_tabledata_table.enable_dataframe_formatter()

3. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

Create a shared routine

In this section, you create a shared routine that renders layers on a base map.

1. To insert a code cell, clickaddCode.

2. To create a shared routine for rendering layers on a map, enter thefollowing code:

   # Set Google Maps as the base map provider.MAP_PROVIDER_GOOGLE=pdk.bindings.base_map_provider.BaseMapProvider.GOOGLE_MAPS.value# Shared routine for rendering layers on a map using geemap.deck.defdisplay_pydeck_map(layers,view_state,**kwargs):deck_kwargs=kwargs.copy()# Use Google Maps as the base map only if the API key is provided.ifGMP_API_KEY:deck_kwargs.update({"map_provider":MAP_PROVIDER_GOOGLE,"map_style":pdk.bindings.map_styles.GOOGLE_ROAD,"api_keys":{MAP_PROVIDER_GOOGLE:GMP_API_KEY},})m=gmdk.Map(initial_view_state=view_state,ee_initialize=False,**deck_kwargs)forlayerinlayers:m.add_layer(layer)returnm

3. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

Create a scatter plot

In this section, you create a scatter plot of all bike share stations in theSan Francisco Ford GoBike Share public dataset by retrieving data from thebigquery-public-data.san_francisco_bikeshare.bikeshare_station_info table. Thescatter plot is created using alayerand ascatterplot layerfrom thedeck.gl framework.

Scatter plots are useful when you need to review a subset of individual points(also known asspot checking).

The following example demonstrates how to use a layer and a scatterplot layerto render individual points as circles.

1. To insert a code cell, clickaddCode.

2. To query the San Francisco Ford GoBike Share public dataset, enter thefollowing code. This code uses the%%bigquery magic functionto run the query and return the results in a DataFrame:

   # Query the station ID, station name, station short name, and station# geometry from the bike share dataset.# NOTE: In this tutorial, the denormalized 'lat' and 'lon' columns are# ignored. They are decomposed components of the geometry.%%bigquerygdf_sf_bikestations--project{GCP_PROJECT_ID}--use_geodataframestation_geomSELECTstation_id,name,short_name,station_geomFROM`bigquery-public-data.san_francisco_bikeshare.bikeshare_station_info`

3. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   Job ID 12345-1234-5678-1234-123456789 successfully executed: 100%

4. To insert a code cell, clickaddCode.

5. To get a summary of the DataFrame, including columns and data types,enter the following code:

   # Get a summary of the DataFramegdf_sf_bikestations.info()

6. Clickplay_circle_filled Runcell.

   The output should look like the following:

   <class 'geopandas.geodataframe.GeoDataFrame'>RangeIndex: 472 entries, 0 to 471Data columns (total 4 columns):#   Column        Non-Null Count  Dtype---  ------        --------------  -----0   station_id    472 non-null    object1   name          472 non-null    object2   short_name    472 non-null    object3   station_geom  472 non-null    geometrydtypes: geometry(1), object(3)memory usage: 14.9+ KB

7. To insert a code cell, clickaddCode.

8. To preview the first five rows of the DataFrame, enter the following code:

   # Preview the first five rowsgdf_sf_bikestations.head()

9. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   

Rendering the points requires you to extract the longitude and latitude as x and y coordinates from thestation_geom column in the bike share dataset.

Sincegdf_sf_bikestations is ageopandas.GeoDataFrame, coordinates are accessed directly from itsstation_geom geometry column. You can retrieve the longitude using the column's.x attribute and the latitude using its.y attribute. Then, you can store them in new longitude and latitude columns.

1. To insert a code cell, clickaddCode.

2. To extract the longitude and latitude values from thestation_geom column,enter the following code:

   # Extract the longitude (x) and latitude (y) from station_geom.gdf_sf_bikestations["longitude"]=gdf_sf_bikestations["station_geom"].xgdf_sf_bikestations["latitude"]=gdf_sf_bikestations["station_geom"].y

3. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

4. To insert a code cell, clickaddCode.

5. To render the scatter plot of bike share stations based on the longitude andlatitude values you extracted previously, enter the following code:

   # Render a scatter plot using pydeck with the extracted longitude and# latitude columns in the gdf_sf_bikestations geopandas.GeoDataFrame.scatterplot_layer=pdk.Layer("ScatterplotLayer",id="bike_stations_scatterplot",data=gdf_sf_bikestations,get_position=['longitude','latitude'],get_radius=100,get_fill_color=[255,0,0,140],# Adjust color as desiredpickable=True,)view_state=pdk.ViewState(latitude=37.77613,longitude=-122.42284,zoom=12)display_pydeck_map([scatterplot_layer],view_state)

6. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   

Visualize polygons

Geospatial analytics lets you analyze and visualize geospatial data inBigQuery by usingGEOGRAPHY data types and GoogleSQLgeography functions.

TheGEOGRAPHY data typein geospatial analytics is a collection of points, linestrings, andpolygons, which is represented as a point set, or a subset of the surface of theEarth. AGEOGRAPHY type can contain objects such as the following:

• Points
• Lines
• Polygons
• Multipolygons

For a list of all supported objects, see theGEOGRAPHY typedocumentation.

If you are provided geospatial data without knowing the expected shapes, you canvisualize the data to discover the shapes. You can visualize shapes byconverting the geographic data toGeoJSON format. Youcan then visualize theGeoJSON data using aGeoJSON layerfrom thedeck.gl framework.

In this section, you query geographic data in the San Francisco Neighborhoodsdataset and then visualize the polygons.

1. To insert a code cell, clickaddCode.

2. To query the geographic data from thebigquery-public-data.san_francisco_neighborhoods.boundaries table in theSan Francisco Neighborhoods dataset, enter the following code. This codeuses the%%bigquery magic functionto run the query and return the results in a DataFrame:

   # Query the neighborhood name and geometry from the San Francisco# neighborhoods dataset.%%bigquerygdf_sanfrancisco_neighborhoods--project{GCP_PROJECT_ID}--use_geodataframegeometrySELECTneighborhood,neighborhood_geomASgeometryFROM`bigquery-public-data.san_francisco_neighborhoods.boundaries`

3. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   Job ID 12345-1234-5678-1234-123456789 successfully executed: 100%

4. To insert a code cell, clickaddCode.

5. To get a summary of the DataFrame, enter the following code:

   # Get a summary of the DataFramegdf_sanfrancisco_neighborhoods.info()

6. Clickplay_circle_filled Runcell.

   The results should look like the following:

   <class 'geopandas.geodataframe.GeoDataFrame'>RangeIndex: 117 entries, 0 to 116Data columns (total 2 columns):#   Column        Non-Null Count  Dtype---  ------        --------------  -----0   neighborhood  117 non-null    object1   geometry      117 non-null    geometrydtypes: geometry(1), object(1)memory usage: 2.0+ KB

7. To preview the first row of the DataFrame, enter the following code:

   # Preview the first rowgdf_sanfrancisco_neighborhoods.head(1)

8. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   

   In the results, notice that the data is a polygon.

9. To insert a code cell, clickaddCode.

10. To visualize the polygons, enter the following code.pydeck is used toconvert eachshapely object instance in the geometry column intoGeoJSONformat:

    # Visualize the polygons.geojson_layer=pdk.Layer('GeoJsonLayer',id="sf_neighborhoods",data=gdf_sanfrancisco_neighborhoods,get_line_color=[127,0,127,255],get_fill_color=[60,60,60,50],get_line_width=100,pickable=True,stroked=True,filled=True,)view_state=pdk.ViewState(latitude=37.77613,longitude=-122.42284,zoom=12)display_pydeck_map([geojson_layer],view_state)

11. Clickplay_circle_filled Runcell.

    The output is similar to the following:

    

Create a choropleth map

If you are exploring data with polgyons that are difficult to convert toGeoJSON format, you can use apolygon layerfrom thedeck.gl framework instead. A polygon layer can process input data ofspecific types such as an array of points.

In this section, you use a polygon layer to render an array of points and usethe results to render a choropleth map. The choropleth map shows the density ofbike share stations by neighborhood by joining data from the San FranciscoNeighborhoods dataset with the San Francisco Ford GoBike Share dataset.

1. To insert a code cell, clickaddCode.

2. To aggregate and count the number of stations per neighborhood and to createapolygon column that contains an array of points, enter the followingcode:

   # Aggregate and count the number of stations per neighborhood.gdf_count_stations=gdf_sanfrancisco_neighborhoods.sjoin(gdf_sf_bikestations,how='left',predicate='contains')gdf_count_stations=gdf_count_stations.groupby(by='neighborhood')['station_id'].count().rename('num_stations')gdf_stations_x_neighborhood=gdf_sanfrancisco_neighborhoods.join(gdf_count_stations,on='neighborhood',how='inner')# To simulate non-GeoJSON input data, create a polygon column that contains# an array of points by using the pandas.Series.map method.gdf_stations_x_neighborhood['polygon']=gdf_stations_x_neighborhood['geometry'].map(lambdag:list(g.exterior.coords))

3. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

4. To insert a code cell, clickaddCode.

5. To add afill_color column for each of the polygons, enter the followingcode:

   # Create a color map gradient using the branch library, and add a fill_color# column for each of the polygons.colormap=branca.colormap.LinearColormap(colors=["lightblue","darkred"],vmin=0,vmax=gdf_stations_x_neighborhood['num_stations'].max(),)gdf_stations_x_neighborhood['fill_color']=gdf_stations_x_neighborhood['num_stations'] \.map(lambdac:list(colormap.rgba_bytes_tuple(c)[:3])+[0.7*255])# force opacity of 0.7

6. Clickplay_circle_filled Runcell.

   After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

7. To insert a code cell, clickaddCode.

8. To render the polygon layer, enter the following code:

   # Render the polygon layer.polygon_layer=pdk.Layer('PolygonLayer',id="bike_stations_choropleth",data=gdf_stations_x_neighborhood,get_polygon='polygon',get_fill_color='fill_color',get_line_color=[0,0,0,255],get_line_width=50,pickable=True,stroked=True,filled=True,)view_state=pdk.ViewState(latitude=37.77613,longitude=-122.42284,zoom=12)display_pydeck_map([polygon_layer],view_state)

9. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   

Create a heatmap

Choropleths are useful when you have meaningful boundaries that are known. Ifyou have data with no known meaningful boundaries, you can use a heatmap layerto render its continuous density.

In the following example, you query data in thebigquery-public-data.san_francisco_sfpd_incidents.sfpd_incidents table in theSan Francisco Police Department (SFPD) Reports dataset. The data is used tovisualize the distribution of incidents in 2015.

For heatmaps, it is recommended that you quantize and aggregate the data beforerendering. In this example, the data is quantized and aggregated usingCartoH3 spatial indexing.The heatmap is created using aheatmap layerfrom thedeck.gl framework.

In this example, quantizing is done using theh3 Python library to aggregatethe incident points into hexagons. Theh3.latlng_to_cell function is used tomap the incident's position (latitude and longitude) to an H3 cell index. An H3resolution of nine provides sufficient aggregated hexagons for the heatmap.Theh3.cell_to_latlng function is used to determine the center of eachhexagon.

1. To insert a code cell, clickaddCode.

2. To query the data in the San Francisco Police Department (SFPD) Reportsdataset, enter the following code. This code uses the%%bigquery magicfunction to run thequery and return the results in a DataFrame:

   # Query the incident key and location  data from the SFPD reports dataset.%%bigquerygdf_incidents--project{GCP_PROJECT_ID}--use_geodataframelocation_geographySELECTunique_key,location_geographyFROM(SELECTunique_key,SAFE.ST_GEOGFROMTEXT(location)ASlocation_geography,# WKT string to GEOMETRYEXTRACT(YEARFROMtimestamp)ASyear,FROM`bigquery-public-data.san_francisco_sfpd_incidents.sfpd_incidents`incidents)WHEREyear=2015

3. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   Job ID 12345-1234-5678-1234-123456789 successfully executed: 100%

4. To insert a code cell, clickaddCode.

5. To compute the cell for each incident's latitude and longitude, aggregatethe incidents for each cell, construct ageopandas DataFrame, and add thecenter of each hexagon for the heatmap layer, enter the following code:

   # Compute the cell for each incident's latitude and longitude.H3_RESOLUTION=9gdf_incidents['h3_cell']=gdf_incidents.geometry.apply(lambdageom:h3.latlng_to_cell(geom.y,geom.x,H3_RESOLUTION))# Aggregate the incidents for each hexagon cell.count_incidents=gdf_incidents.groupby(by='h3_cell')['unique_key'].count().rename('num_incidents')# Construct a new geopandas.GeoDataFrame with the aggregate results.# Add the center of each hexagon for the HeatmapLayer to render.gdf_incidents_x_cell=gpd.GeoDataFrame(data=count_incidents).reset_index()gdf_incidents_x_cell['h3_center']=gdf_incidents_x_cell['h3_cell'].apply(h3.cell_to_latlng)gdf_incidents_x_cell.info()

6. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   <class 'geopandas.geodataframe.GeoDataFrame'>RangeIndex: 969 entries, 0 to 968Data columns (total 3 columns):#   Column         Non-Null Count  Dtype--  ------         --------------  -----0   h3_cell        969 non-null    object1   num_incidents  969 non-null    Int642   h3_center      969 non-null    objectdtypes: Int64(1), object(2)memory usage: 23.8+ KB

7. To insert a code cell, clickaddCode.

8. To preview the first five rows of the DataFrame, enter the following code:

   # Preview the first five rows.gdf_incidents_x_cell.head()

9. Clickplay_circle_filled Runcell.

   The output is similar to the following:

   

10. To insert a code cell, clickaddCode.

11. To convert the data into a JSON format that can be used byHeatmapLayer,enter the following code:

    # Convert to a JSON format recognized by the HeatmapLayer.def_make_heatmap_datum(row)->dict:return{"latitude":row['h3_center'][0],"longitude":row['h3_center'][1],"weight":float(row['num_incidents']),}heatmap_data=gdf_incidents_x_cell.apply(_make_heatmap_datum,axis='columns').values.tolist()

12. Clickplay_circle_filled Runcell.

    After you run the code, no output is generated in your Colabnotebook. The check mark beside the cell indicates that the code ransuccessfully.

13. To insert a code cell, clickaddCode.

14. To render the heatmap, enter the following code:

    # Render the heatmap.heatmap_layer=pdk.Layer("HeatmapLayer",id="sfpd_heatmap",data=heatmap_data,get_position=['longitude','latitude'],get_weight='weight',opacity=0.7,radius_pixels=99,# this limitation can introduce artifacts (see above)aggregation='MEAN',)view_state=pdk.ViewState(latitude=37.77613,longitude=-122.42284,zoom=12)display_pydeck_map([heatmap_layer],view_state)

15. Clickplay_circle_filled Runcell.

    The output is similar to the following: