A Primer on JavaScript UDFs for Spatial Analysis in BigQuery
Google BigQuery has several dozen native geography functions for doing common things with spatial data. This includes things like spatial joins (using something like st_contains), parsing geographies in well-known text (with st_geogfromtext), or measuring distance (with the aptly named st_distance). While these functions do cover a lot of common use cases, sometimes you need a function that isn’t there yet. Thankfully, BigQuery is very easy to extend using stored procedures and user-defined functions (UDFs).
CARTO’s BigQuery Spatial Extension enhances BigQuery’s spatial capabilities through approximately 60 stored procedures and UDFs. These give BigQuery users powerful SQL functions for things like clustering, gridding, enrichment, transformations, constructors, and more. These functions generally leverage libraries like PostGIS, H3, S2, Placekey, Turf.js and CARTO’s Data Observatory to achieve all of this new functionality. This makes BigQuery far more capable as a GIS than it would be on its own.
But while the CARTO BigQuery Spatial Extension has added (and will continue to add) a lot of new functions to BigQuery, sometimes you might need a function that isn’t there yet. BigQuery makes this easy with a nicely designed interface for creating UDFs in SQL or JavaScript. This piece will cover how to create user-defined JavaScript geographic functions in BigQuery.
Creating a Point Interpolation UDF in JavaScript
I recently ran into a situation where I needed to break a long highway linestring geometry up into individual snippets for analysis. Things like weather, population density, and cell phone service quality can vary quite a bit across a long stretch of highway and I needed to work with smaller pieces of that highway in my analysis.
If I were working with PostGIS, I would turn to ST_LineInterpolatePoints for this. But I wanted to work with data in BigQuery and not have to ETL data back and forth to Postgres. This called for a BigQuery UDF! This function would need to take a linestring geometry as an argument and return a specific number of points along that linestring.
As I already mentioned, BigQuery UDFs can be created in SQL or JavaScript. The great part about using JavaScript is that you can leverage external libraries like D3, Turf, or any other npm package. Below is the function I created to take a linestring and return points. There’s quite a bit to grok here so I’ll include a lot of comments.
When I plot these points on a map, they look as expected (10 here):
Tips and Takeaways
The possibilities that UDFs open up are pretty wild to think about. Almost anything that can be performed in a node app or CodePen can be achieved in the massively scalable BigQuery SQL environment. There are a few challenges to work with though.
The verbosity of the queries and function declaration takes getting used to. Declaring types, copying the libraries into a GCP bucket, converting back and forth between a WKT string vs. a geography, and unnesting and cross joining the arrays can be tricky. Like anything in programming, I recommend starting simple and gradually adding complexity.
Another challenging aspect is the lack of console.log for working with JavaScript. If you have some bug in your JavaScript, it’s going to be hard to figure that out if you’re in BigQuery. For this reason, I recommend copying the function over to a codepen to test it out and be able to console.log to your heart’s content. Here’s the codepen I used for this:
This makes it possible to debug your JavaScript so that you’re not fighting with the SQL and JavaScript at the same time.
Finally, one last limitation with JavaScript UDFs is timeouts. I was able to create 100,000 points in this UDF in one minute but it timed out when I tried one million. Making my JavaScript more efficient would likely raise this cap quite a bit but it’s worth knowing about.
Hope this piece helps you create many useful UDFs.