PinnedIndex of postsFunctions How to build new or enhance existing geospatial functions ST_Subdivide ST_Simplify ST_Intersection_Agg ST_Multi Any Postgres function ST_Transform Performance Performance tips Spatial join performance check Spatial join rewrite Spatial predicate choice How to GROUP BY <Geography> Tutorials and query examples Hurricanes Load and analyze Fitbit data Load, clean and visualize pets data Spatial statistics remapping1 min read1 min read
Jul 20ST_Transform in BigQuery — Project Any Coordinates to Lng/Lat and BackBigQuery only supports WGS84 geodesic coordinates, but my data is in another coordinate system. How can I convert it? We’ve seen how to project data when loading it to BigQuery, and it is still the best option. But what if the data is already in BigQuery, and we want to…Bigquery2 min readBigquery2 min read
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 inGeospatial 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. You can ask for geometry’s bounding box…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. Frankly, I still wonder about a use case when this would be needed, but let’s create…Google Big Query3 min readGoogle Big Query3 min read
