Javier de la Torre • Oct 25, 2019

New spatial functions in BigQuery, starting with Uber H3-js

As you may have seen in last week’s announcement, we are now working very closely with the Google BigQuery team to support the creation of a next generation spatial data infrastructure stack.

BigQuery GIS does have support for some spatial functions, but this is a limited set that we believe will continue growing over time. In the meantime, if you need to perform a spatial operation that is not supported out of the box, you can create your own User Defined Functions. UDFs in BigQuery can be programmed in Javascript, for which there are a lot of spatial libraries already available. In order to use them, you have to make slight modifications, push them to cloud storage, and then reference them, which can be quite time consuming. Right now, every user of BigQuery would have to do the same, which is also a bit of a pain.

Luckily, BigQuery works as if it were a huge multitenant database, where all the databases of all users are on the same server, and there are only permissions separating them. This means you can create a database and give permissions to the entire world, so that everyone can use it for doing joins or calling functions. This is how the public data project works and the reason why we are collaborating to add location datasets. This means there is no need to install anything, allowing you to use these functions directly on your queries. For example:

SELECT jslibs.h3.ST_H3(ST_GEOGPOINT(-74.00,40.7128),7)

And all of these UDFs functions are now available as an Open Source project and live directly on BigQuery for anybody to use.

BigQuery JS Libs: A repository of pre-packaged libraries to be used as functions inside BigQuery.

Here is a more complex SQL example:

WITH data as (
	SELECT jslibs.h3.compact(
  	jslibs.h3.ST_H3_POLYFILLFROMGEOG(tract_geom,11)) as geo 
  	FROM `bigquery-public-data.geo_census_tracts`.census_tracts_new_york 
  	WHERE geo_id='36081000100')

SELECT jslibs.h3.ST_H3_BOUNDARY(h3) as h3geo 
FROM data,UNNEST(geo) as h3

Where Can You Run These functions?

The functions are deployed and ready to use, so they can be used as if they were just another regular function: jslibs.h3.ST_H3. Again, no need to install anything, just use them on your regular SQL on BigQuery.

As most of these functions are for spatial operations, it is useful to visualize them. For visualization, you can use BigQuery Geo Viz, QGIS or our CARTOframes library! Check it out in Google Collab.

What About Information Security?

Just a quick note that although all the functions are stored in a CARTO BigQuery project, when you use them in your SQL we do not get to “see” or know that the functions are being used, so there is no need to worry from a security perspective.

Starting with Uber H3

The open source project H3 is a hexagonal hierarchical geospatial indexing system, which is really useful when working with grid structures, and we use it extensively at CARTO as part of our Data Observatory.

For this reason, we ported most of the H3 JS library API into BigQuery, allowing you to run the majority of H3 API functions. The best way to know which functions have been ported is just to look in the Github repo.

We have ported one single function (buffer) from the open source library Turf.JS to make buffers. Lots of Data Scientists need to do buffers in BigQuery so here they are:

SELECT jslibs.turf.ST_BUFFER(ST_GEOGPOINT(-74.00,40.7128),1,'kilometers',10) as geo

We have also added some support for Quadkeys and S2 libraries, but this is just the beginning.

If you have proposals for new functions to make available or a specific library that you would like to see ported please send us a Pull Request on the project or send us a note.

About the author

Javier de la Torre is founder and Chief Strategy Officer of CARTO. One of the pioneers of location intelligence, Javier founded the company with a vision to democratize data analysis and visualization. Under his leadership, CARTO has grown from a groundbreaking idea into one of the fastest growing geospatial companies in the world.
In 2007, he founded Vizzuality, a renowned geospatial company dedicated to bridging the gap between science and policy making by the better use of data.

More posts from Javier de la Torre