Divide the query to improve cost and performanceLet’s take a look at a typical query where we find some spatial objects in some region. E.g. consider the first query in Dekart.xyz’s…Oct 11, 20241Oct 11, 20241
Migrating Geospatial Data to BigQueryA review of common hurdles one may stumble at when moving data from another system to BigQuery Geospatial.Nov 3, 20231Nov 3, 20231
ST_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?Jul 20, 2023Jul 20, 2023
Subdivide 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…Feb 26, 2023Feb 26, 2023
Analyze Fitbit Exercise inBigQueryHow to upload and analyze Fitbit activity’s track data in BigQuery.Jan 8, 2023Jan 8, 2023
Extra 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…Apr 19, 20222Apr 19, 20222
BigQuery Geospatial query tricksToday we’ll explore two useful tricks that came up during a discussion on how to implement some complex query.Sep 27, 20213Sep 27, 20213
Published inGeospatial AnalyticsSquaring the sphereLet’s talk about geographical bounding boxes.Sep 17, 20212Sep 17, 20212
HowTo: Group By Geography columnLet’s look at how to aggregate data by a geography column. BigQuery refuses to GROUP BY using geography, so let’s consider alternatives, …May 21, 20212May 21, 20212
BigQuery geospatial visualization: more options, more dataBigQuery geospatial visualization story has been updated with important features.May 7, 2021May 7, 2021
Invert polygons for fun and new functionalityLet’s create aggregate version of ST_Intersection function. BigQuery has ST_Union_Agg, but no ST_Intersection_Agg — let’s make our version.Jan 26, 2021Jan 26, 2021
Simple automated data cleaning in BigQueryToday’s note is handy when doing geospatial or other types of visualization and the dimension you are interested in has huge outliers …Nov 3, 2020Nov 3, 2020
New 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…Oct 15, 20202Oct 15, 20202
Published inThe StartupCall PostgreSQL from BigQuery for extra GIS powersToday we’ll have a complex script that ties together new features of BigQuery to transparently talk from BigQuery Script to a PostgreSQL…May 20, 20201May 20, 20201
Yet another GeoJson to Ndjson converterYou might have already seen many ways to convert GeoJson files to something BigQuery can understand. Let’s invent one more wheel!Mar 10, 2020Mar 10, 2020
Mapping statistics between different spatial hierarchiesToday’s recipe discusses a following problem: you have some geospatial statistics collected for one set of spatial hierarchy, but you need…Jan 10, 2020Jan 10, 2020
Be careful with ABS functionToday’s short notice is not about GIS, just about BigQuery. Let’s fix a reliability bug!Dec 17, 2019Dec 17, 2019
Nearest neighbor using BQ ScriptingWe’ve already discussed Nearest Neighbors problem, now let’s do it with BigQuery Scripting.Dec 15, 2019Dec 15, 2019
Puppies & BigQuery: Analyzing Geospatial DataLet’s try to repeat the following analysis in BigQuery and see how easy it is to do geospatial analysis in BigQuery GIS and BigQuery…Dec 13, 2019Dec 13, 2019