Today we’ll explore two useful tricks that came up during a discussion on how to implement some complex query.

First one is about OUTER spatial JOIN. As you might know, BigQuery does not yet implement optimized spatial OUTER JOIN. What if we do need one, something like


Let’s talk about geographical bounding boxes.

Bounding boxes are easy when you are on a 2D plane. E.g. PostGIS has box2d type that describes rectangular geometry. It is usually described by low-left and up-right points. …


Let’s look at how to aggregate data by a geography column. BigQuery refuses to using geography, so let’s consider alternatives, and learn about under-appreciated function.

Lacking the ability to GROUP BY a geography column, what can one use right now? A very common solution is to do…


If you looked at BigQuery native visualization options some time ago, you might have tried BigQuery GeoViz and found it limited. But a lot of things have changed over the time, and I want to summarize major things that might help you to visualize Geospatial data in BigQuery.

More data

First, BigQuery…


Today’s story is inspired by a feature request opened for BigQuery GIS. The request, as I understand it, is to provide aggregate version of or . BigQuery GIS has , but no . …


Today’s note is not specific for BQ GIS, but is handy when doing geospatial or other types of visualization and the dimension you are interested in has big outliers. Such extreme values might make charts ugly, or squeeze the choropleth map’s effective color range.

As an example, you might have…


Last time I showed how to use a PostgreSQL instance with PostGIS to fix invalid geometries, now I’m glad to introduce new BigQuery native options that simplify loading geospatial data, including invalid geometries.

The new signatures of and support two common requests

Planar WKT

If you worked with BigQuery GIS…


Today we’ll have a complex script that ties together new features of BigQuery to transparently talk from BigQuery Script to a PostgreSQL instance. With Dynamic SQL we can build BigQuery SQL queries dynamically, and here we’ll build and execute PostgreSQL queries dynamically in BigQuery.

A practical motivating example here is…


You might have already seen many ways to convert GeoJson files to something BigQuery can understand. Let’s invent one more wheel!

E.g. this python script from Lak Lakshmanan:

Or using org2org tools to produce CSV file:

I also have a few node.js scripts to convert both ways between GeoJson or…


Today’s recipe discusses the following problem: you have some geospatial statistics collected for one set of spatial hierarchy, but you need to do some data analysis using another hierarchy. …

Michael Entin

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