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

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?

Click here to read more