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 look at how to aggregate data by a geography column. BigQuery refuses to
GROUP BY using geography, so let’s consider alternatives, and learn about under-appreciated
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.
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
ST_GeogFromGeoJson support two common requests
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.
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:
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. …