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.
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:
-- Define bounding_area by selecting administrative boundary multipolygon. One can use hardcoded OSM relation ID here. WITH bounding_area as (SELECT geometry from `bigquery-public-data.geo_openstreetmap.planet_features` WHERE feature_type="multipolygon" AND ('name:en', 'Netherlands') in (SELECT (key, value) from unnest(all_tags)) AND ('boundary', 'administrative') in (SELECT (key, value) from unnest(all_tags)) AND ('admin_level', '3') in (SELECT (key, value) from unnest(all_tags)) ) SELECT feature_type, osm_id, osm_timestamp, planet_features.geometry FROM `bigquery-public-data.geo_openstreetmap.planet_features` planet_features, bounding_area WHERE ('man_made', 'bridge') IN (SELECT (key, value) FROM UNNEST(all_tags)) -- Select features with 'man_made=bridge' tag AND ST_DWithin(bounding_area.geometry, planet_features.geometry, 0) -- Filter only features within bounding_area LIMIT 10; -- I changed this to 10,000
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].
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.
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?Click here to read more
Our primary goal at CARTO is to be the world’s leading Location Intelligence platform, empowering our clients with the best data and the best spatial analysis. We frequentl...Core Tech
Please fill out the below form and we'll be in touch real soon.