Feb 26Subdivide and Conquer Any GeometryLet’s write a geospatial function that BigQuery does not currently provide: analogue of PostGIS’s ST_Subdivide. We’ll use SQL UDF, and learn a few tricks along the way. ST_Subdivide breaks a shape into pieces. There are various reasons to do it, sometimes it is needed to ensure each shape has low…Bigquery4 min readBigquery4 min read
Jan 8Analyze your Fitbit Exercise in BigQueryThis is more of personal notes how to upload and analyze Fitbit activity’s track data in BigQuery. Fitbit offers reasonable basic analysis of activities, but SQL and details are always better! Another motivation was an error I’ve made when tracking exercise: Fitbit does not have skiing exercise type, and one…Bigquery3 min readBigquery3 min read
Apr 19, 2022Extra geography simplification in BigQueryWe had a discussion with a customer about BigQuery’s ST_Simplify that turned into a helper function I want to share. BigQuery has ST_Simplify method, but it was not enough — there was a lot of noise remaining after ST_Simplify, which made rendering of the result too slow. This is caused…Bigquery2 min readBigquery2 min read
Sep 27, 2021Two BigQuery Geospatial query tricksToday 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 SELECT…Bigquery3 min readBigquery3 min read
Published in Geospatial Analytics·Sep 17, 2021Squaring the sphereLet’s talk about geographical bounding boxes and how they differ from geometric ones. Bounding boxes are easy when you are on a 2D plane. E.g. PostGIS has a box2d type that describes rectangular geometry. It is usually described by low-left and up-right points. …Bigquery4 min readBigquery4 min read
May 21, 2021HowTo: Group By Geography columnLet’s look at how to aggregate data by a geography column. BigQuery refuses to GROUP BY using geography as key, so let’s consider alternatives, and learn about under-appreciated ANY_VALUE function. Lacking the ability to GROUP BY a geography column, what can one use right now? A very common solution is…Bigquery3 min readBigquery3 min read
May 7, 2021BigQuery geospatial visualization newsIf 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…Bigquery3 min readBigquery3 min read
Jan 26, 2021Invert polygons for fun and new functionalityToday’s story is inspired by a feature request opened for BigQuery GIS. The request, as I understand it, is to provide aggregate version of ST_Intersect or ST_Intersection. BigQuery GIS has ST_Union_Agg, but no ST_Intersection_Agg. …Google Big Query3 min readGoogle Big Query3 min read
Nov 3, 2020Simple automated data cleaning in BigQueryToday’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…Google Big Query3 min readGoogle Big Query3 min read
Oct 15, 2020New options for BigQuery GIS geospatial data ingestionLast 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_GeogFromText and ST_GeogFromGeoJson support two common requests Planar WKT If you worked with BigQuery GIS…Google Big Query4 min readGoogle Big Query4 min read
