Dekart

Overture Maps Examples

Collection of kepler.gl maps created from Overture Data in BigQuery public dataset using BigQuery SQL and Dekart. Each example includes a SQL query and a visualized map.

Segment

The Overture Maps segment table represents paths, roads, and transportation segments, storing their geospatial data as LineStrings along with attributes like class, surface, speed limits, and access restrictions​.

Nevada Roads by Speed and Class

View interactive map

-- Step 1: Get the geometry of Nevada
WITH nevada_geometry AS (
  SELECT
    geometry
  FROM
    `bigquery-public-data.overture_maps.division_area`
  WHERE
    country = 'US'
    AND region = 'US-NV'
    AND subtype = 'region'
)

-- Step 2: Select roads within Nevada
SELECT
  s.geometry,
  s.class,
  s.road,
  SAFE_CAST(JSON_EXTRACT_SCALAR(s.road, '$.restrictions.speed_limits[0].max_speed.value') AS INT64) AS speed_limit
FROM
  `bigquery-public-data.overture_maps.segment` AS s,
  nevada_geometry AS ng
WHERE
  s.subtype = 'road'
  and class not in ('track', 'driveway', 'path', 'footway', 'sidewalk', 'pedestrian', 'cycleway', 'steps', 'crosswalk', 'bridleway', 'alley')
  AND ST_WITHIN(s.geometry, ng.geometry)

Open query in editor

Berlin Roads

View interactive map

WITH berlin_boundary AS (
    SELECT geometry
    FROM `bigquery-public-data.overture_maps.division_area`
    WHERE LOWER(names.primary) = "berlin"
    AND country = "DE"
)
SELECT s.id, s.geometry, s.class, s.subtype
FROM `bigquery-public-data.overture_maps.segment` s
JOIN berlin_boundary b
ON ST_CONTAINS(b.geometry, s.geometry)  -- Spatial filter for roads inside Berlin boundary
WHERE s.subtype = 'road'
AND s.class IN ('primary', 'secondary', 'tertiary');

Open query in editor

Nevada highways and main roads

View interactive map

-- Step 1: Get the simplified geometry of Nevada
WITH nevada_geometry AS (
  SELECT
    ST_SIMPLIFY(geometry, 0.01) AS geometry
  FROM
    OVERTURE_MAPS__DIVISIONS.CARTO.DIVISION_AREA
  WHERE
    country = 'US'
    AND region = 'US-NV'
    AND subtype = 'region'
)

-- Step 2: Select main roads and highways within simplified Nevada geometry and convert geometry to WKT
SELECT
  ST_ASWKT(s.geometry) AS geo,
  s.class
FROM
  OVERTURE_MAPS__TRANSPORTATION.CARTO.SEGMENT AS s
  JOIN nevada_geometry AS ng ON ST_WITHIN(s.geometry, ng.geometry)
WHERE
  s.subtype = 'road'
  AND s.class IN ('primary', 'secondary', 'tertiary', 'trunk', 'motorway')

Open query in editor

Germany & France Road Networks

View interactive map

WITH country_boundaries AS (
    -- Define the boundaries for Germany and France
    SELECT geometry, country
    FROM `bigquery-public-data.overture_maps.division_area`
    WHERE country IN ("DE", "FR")
    AND subtype = "country"  -- Ensure we're selecting the entire country boundaries
),
filtered_roads AS (
    -- Filter the roads inside the Germany and France boundaries that are accessible to cars, including main roads but excluding highways
    SELECT s.id, s.geometry, s.class, s.subtype, b.country,
           ST_LENGTH(s.geometry) / 1000 AS road_length_km  -- Convert road length to kilometers
    FROM `bigquery-public-data.overture_maps.segment` s
    JOIN country_boundaries b
    ON ST_CONTAINS(b.geometry, s.geometry)  -- Spatial filter for roads inside Germany and France boundaries
    WHERE s.subtype = 'road'
    AND s.class IN ('primary', 'secondary', 'tertiary')  -- Main roads for traffic, excluding highways
),
hexagonized_roads AS (
    -- Assign each road segment to an H3 hexagon at level 7
    SELECT
        id,
        country,
        `bqcarto.h3.ST_ASH3`(ST_CENTROID(s.geometry), 7) AS h3_hexagon,  -- H3 hexagon for each road segment at level 5
        road_length_km  -- Use the length in kilometers
    FROM filtered_roads s
)
-- Aggregate the total length of roads for each hexagon and country
SELECT country, h3_hexagon, SUM(road_length_km) AS total_road_length_km
FROM hexagonized_roads
GROUP BY country, h3_hexagon
ORDER BY total_road_length_km DESC;

Open query in editor

Road density US

View interactive map

WITH country_boundaries AS (
    -- Define the boundaries for the US
    SELECT geometry, country
    FROM `bigquery-public-data.overture_maps.division_area`
    WHERE country = "US"
    AND subtype = "country"  -- Ensure we're selecting the entire country boundaries
),
filtered_roads AS (
    -- Filter the roads inside the US boundaries that are accessible to cars, including main roads but excluding highways
    SELECT s.id, s.geometry, s.class, s.subtype, b.country,
           ST_LENGTH(s.geometry) / 1000 AS road_length_km  -- Convert road length to kilometers
    FROM `bigquery-public-data.overture_maps.segment` s
    JOIN country_boundaries b
    ON ST_CONTAINS(b.geometry, s.geometry)  -- Spatial filter for roads inside US boundaries
    WHERE s.subtype = 'road'
    AND s.class IN ('primary', 'secondary', 'tertiary')  -- Main roads for traffic, excluding highways
),
hexagonized_roads AS (
    -- Assign each road segment to an H3 hexagon at level 6
    SELECT
        id,
        country,
        `bqcarto.h3.ST_ASH3`(ST_CENTROID(s.geometry), 6) AS h3_hexagon,  -- H3 hexagon for each road segment at level 5
        road_length_km  -- Use the length in kilometers
    FROM filtered_roads s
)
-- Aggregate the total length of roads for each hexagon and country
SELECT country, h3_hexagon, SUM(road_length_km) AS total_road_length_km
FROM hexagonized_roads
GROUP BY country, h3_hexagon
ORDER BY total_road_length_km DESC;

Open query in editor

Joining GPS probes with road geometry

View interactive map

-- Step 1: Generate H3 indexes for road geometries
WITH brandenburg_gate AS (
  SELECT ST_GEOGPOINT(13.3777, 52.5163) AS location
),
road_segments AS (
  SELECT id, geometry
  FROM `bigquery-public-data.overture_maps.segment`
  WHERE ST_DISTANCE(geometry, (SELECT location FROM brandenburg_gate)) <= 10000
  AND subtype = 'road'
),
road_h3_cells AS (
  SELECT id AS road_id,
         geometry,  -- Include geometry in the result
         bqcarto.h3.ST_ASH3(ST_LINEINTERPOLATEPOINT(geometry, ratio), 12) AS h3_index
  FROM road_segments,
  UNNEST(GENERATE_ARRAY(0, 1, 0.01)) AS ratio -- Generate H3 for road geometries
),

-- Step 2: Generate H3 indexes for dekart-dev.strava.streams points
strava_h3_cells AS (
  SELECT bqcarto.h3.LONGLAT_ASH3(lng, lat, 12) AS h3_index, velocity_smooth
  FROM `dekart-dev.strava.streams`
  WHERE ST_DISTANCE(
    ST_GEOGPOINT(lng, lat), (SELECT location FROM brandenburg_gate)
  ) <= 10000
)

-- Step 3: Join road geometries with strava points based on H3 index
SELECT
  r.road_id,
  ANY_VALUE(r.geometry) AS geometry,  -- Use ANY_VALUE() to select a representative geometry
  COUNT(s.h3_index) AS num_strava_points,
  AVG(s.velocity_smooth) AS avg_velocity_smooth,
  MAX(s.velocity_smooth) AS max_velocity_smooth
FROM road_h3_cells r
LEFT JOIN strava_h3_cells s
ON r.h3_index = s.h3_index
GROUP BY r.road_id;

Open query in editor

Division Area

The Overture Maps division_area table contains boundary polygons for administrative areas, such as cities, countries, and neighborhoods, along with related attributes like subtype, population, and country codes​.

Berlin Boundary

View interactive map

  SELECT id, geometry, names, subtype, country, region
FROM `bigquery-public-data.overture_maps.division_area`
WHERE names.primary = 'Berlin'
AND country = 'DE'

Open query in editor

Regions and Cities in France

View interactive map

SELECT
  division_id,
  names.primary AS division_name,
  subtype,
  geometry
FROM
  `bigquery-public-data.overture_maps.division_area`
WHERE
  country = 'FR' -- ISO code for France
  AND subtype IN ('region', 'city') -- Filtering for regions and cities
ORDER BY
  subtype;

Open query in editor

Land Use

The Overture Maps land_use table represents different types of land use, such as residential, agricultural, industrial, and others, by storing their spatial data as polygons or multipolygons, along with attributes like subtype, class, surface, and names.

Berlin Playgrounds

View interactive map

SELECT id, subtype, class, geometry, surface, level
FROM `bigquery-public-data.overture_maps.land_use`
WHERE ST_WITHIN(geometry, ST_GEOGFROMTEXT('POLYGON((13.08835 52.33826, 13.76116 52.33826, 13.76116 52.67551, 13.08835 52.67551, 13.08835 52.33826))'))
AND LOWER(class) = 'playground'

Open query in editor

All parks in London

View interactive map

SELECT
    id,
    geometry,
    names.primary AS primary_name,
    subtype,
    country,
    region
FROM
    `bigquery-public-data.overture_maps.division_area`
WHERE
    names.primary = "London"
    AND country = 'GB'  -- ISO code for the United Kingdom
    AND subtype = 'locality';  -- Ensure we are selecting a city or locality

Open query in editor

Ukraine Schools

View interactive map

WITH ukraine_boundary AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE LOWER(names.primary) = 'ukraine'
  AND subtype = 'country'
)

SELECT l.id, l.names.primary, l.geometry, l.subtype, l.class
FROM `bigquery-public-data.overture_maps.land_use` l, ukraine_boundary u
WHERE ST_WITHIN(l.geometry, u.geometry)
AND LOWER(l.subtype) = 'education'
AND LOWER(l.class) = 'school';

Open query in editor

Places

The place table in the Overture Maps dataset contains points of interest (POIs) such as businesses, amenities, and public facilities.

London EV Charging Density

View interactive map

WITH london_boundary AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE LOWER(names.primary) = 'london'
  AND country = 'GB'
),
ev_charging_stations AS (
  SELECT
    p.geometry
  FROM
    `bigquery-public-data.overture_maps.place` AS p,
    london_boundary AS lb
  WHERE
    ST_WITHIN(p.geometry, lb.geometry)
    AND LOWER(p.categories.primary) LIKE '%charging%'
)
SELECT
  bqcarto.h3.ST_ASH3(ev.geometry, 6) AS h3_cell,
  COUNT(*) AS station_count
FROM
  ev_charging_stations ev
GROUP BY
  h3_cell;

Open query in editor

Las Vegas EV Charging

View interactive map

  WITH las_vegas_boundary AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE names.primary = "Las Vegas"
  AND region = "US-NV"
  AND subtype = "locality"
)

SELECT p.id, p.geometry, p.names.primary AS station_name, p.addresses, p.websites, p.phones
FROM `bigquery-public-data.overture_maps.place` AS p, las_vegas_boundary AS lv
WHERE (p.categories.primary LIKE "%charging%" OR p.categories.primary LIKE "%ev%")
AND ST_WITHIN(p.geometry, lv.geometry)

Open query in editor

UK pubs density

View interactive map

WITH uk_boundary AS (
  SELECT geometry
  FROM `bigquery-public-data.overture_maps.division_area`
  WHERE LOWER(country) = 'gb' AND subtype = 'country' AND class = 'land'
),
pubs AS (
  SELECT p.id, p.geometry, p.names.primary
  FROM `bigquery-public-data.overture_maps.place` AS p, uk_boundary
  WHERE p.categories.primary = 'pub'
  AND ST_WITHIN(p.geometry, uk_boundary.geometry)
)
SELECT bqcarto.h3.ST_ASH3(p.geometry, 8) AS h3_index, COUNT(p.id) AS pub_count
FROM pubs AS p
GROUP BY h3_index
ORDER BY pub_count DESC

Open query in editor

Edit this page on GitHub

← Build from Source
Snowflake Kepler.gl Examples →