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.
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:
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:
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.
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 ConferenceWatch 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 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.
Simpler data workflows for Snowflake usersNews
Please fill out the below form and we'll be in touch real soon.