Puppies & BigQuery: Analyzing Geospatial Data

Let’s try to repeat the following analysis in BigQuery and see how easy it is to do geospatial analysis in BigQuery GIS and BigQuery public datasets. It uses a few publicly available datasets to find highest density of pets in Seattle area.

We’ll follow the general path of this article, with modifications as needed.

First, we’ll skip all the installation steps, nothing needs to be installed! OK, I’m cheating a bit here — I’ll use bq command line to upload data, and this requires Google Cloud SDK. But you can upload data in UI too, and with some extra mouse clicks avoid any installation completely.

As preparation step, lets open BigQuery UI and create demo dataset for intermediate results:

Second, the article describes loading shape file. We can do this — see https://medium.com/@mentin/loading-large-spatial-features-to-bigquery-geography-2f6ceb6796df but we’ll skip it for this article, as BigQuery public dataset already has the tables we need, bigquery-public-data.geo_us_boundaries.zip_codes for zip codes, and bigquery-public-data.geo_us_census_places.places_washington for city boundaries:

To find out zip codes within Seattle area, we’ll join and compute area of intersection, just like the original article:

Third, we’ll need pet licenses data — download from https://data.seattle.gov/Community/Seattle-Pet-Licenses/jguv-t9rb as CSV, and upload to BigQuery using UI or with the following command:

Let’s examine our uploaded data, the particular column of interest is ZIP — interestingly it was uploaded as STRING! Let’s figure out why --autodetect did not load it as INT64:

We’ll get an error similar to one below (your specific error may vary):

Aha, some zip codes in the pets database were entered in ZIP+4 form. We’ll only need first part, so let’s do a simple ETL with pets data:

Finally, we are ready to join this data and visualize it in BQ GeoViz:

We now get a picture similar to the one obtained in the article:

Hi, I'm TL of BigQuery GIS project. Posting small recipes and various notes for BQ GIS users.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store