10,000 Dutch Bridges: A Foray into Using BigQuery Connector
Explore how to use our Google BigQuery connector to import large public datasets for geospatial analysis.
One of my favorite parts about working at CARTO is getting to test out the new features that our product team is constantly shipping. A month or so ago it was our Kepler connector and now it’s our Google BigQuery connector that I get to play with.
BigQuery has a number of great public datasets like the US Forest Service Forest Inventory or Google’s Public COVID-19 Dataset. These are very interesting but the data I was most interested in exploring was the OpenStreetMap Public Dataset. How can you be a geonerd and not want to play with this data? The Overpass API is great don’t get me wrong. But the prospect of seamlessly importing OSM features into my CARTO maps with pure SQL (and no local downloading in between) was just too much to pass up. In this blog post I’ll show you what I did to import the geometries for 10 000 Dutch bridges into my CARTO map.
Step 1: Query OpenStreetMap public dataset in BigQuery
The first step of getting BigQuery OSM data is querying the dataset on the BigQuery SQL Query editor. OSM has a nice tutorial with example queries to get started. I used their query for selecting bridges in the Netherlands:
Step 2: Save Query Results as a personal dataset in your account
The next thing I had to do was click ‘Save Results’.
This opens up a modal that allows you to save the results in a variety of ways including as a BigQuery table.
Now the results from my original query exist as a separate table dutch_bridges and 10000_dutch_bridges (self-explanatory). I think of this functionality as working just like Postgres’ CREATE TABLE AS [query].
Step 3: Query your personal dataset from CARTO’s BigQuery connector
So now that your dataset exists in your personal BigQuery account you can query it from CARTO’s BigQuery connector. I SELECTed all of my data gave it a name for CARTO and let it run. I had originally tested it with just 10. It took hardly any longer to run it with 10 000.
Next thing you know I had 10 000 Dutch Bridges on my CARTO map.
This functionality is so seamless. I’m incredibly excited to take this further.
How to Get Started
The BigQuery Connector beta version is currently available for CARTO Enterprise users. If you have a CARTO Enterprise account you can find this connector when creating a new dataset from your account dashboard. If you can’t find it or if you have any questions simply reach out to your Customer Success Manager. If you don’t have an account yet and would like to learn more about working with CARTO and BigQuery contact our team and we’ll be happy to answer your questions.
Want to start using CARTO with BigQuery connector?