Kyle Pennell

10,000 Dutch Bridges: A Foray into Using BigQuery Connector

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:

-- 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
Screenshot of BigQuery SQL Query Editor


Step 2: Save Query Results as a personal dataset in your account

The next thing I had to do was click ‘Save Results’.

Screenshot of Save Results Button


This opens up a modal that allows you to save the results in a variety of ways, including as a BigQuery table.

Screenshot of Saving Query Results as a BigQuery Table


Screenshot of Saving Query Results 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.

Screenshot showing the BigQuery Connector within CARTO


Screenshot showing the BigQuery Connector within CARTO


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?

Click here to read more
About the author
Kyle Pennell

Solutions Engineer at CARTO

More posts from Kyle Pennell

Related Posts

Ready to optimize your territories with Location Intelligence?

Close circle icon

Contact us

Please fill out the below form and we'll be in touch real soon.