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
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.
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.
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.
Data Scientist Isaac Brodsky presented Uber's H3 project at this year's Spatial Data Science Conference
Watch the recording today!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.
Having brought together more than 12,000 experts online in 2020 & 2021, we’re beyond excited to host our long awaited, in-person Spatial Data Science Conference in Lond...
NewsWe’re thrilled to announce we will be attending, sponsoring and presenting at Snowflake Summit 2022 taking place June 13-16 in Las Vegas, Nevada!
NewsWithin the last few years, Spain has been focused on increasing electric vehicle incentives and production; and in 2021, Spain announced that they planned to invest 4.3 bil...
NewsPlease fill out the below form and we'll be in touch real soon.