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
-- 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)
Berlin Roads
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');
Nevada highways and main roads
-- 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')
Germany & France Road Networks
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;
Road density US
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;
Joining GPS probes with road geometry
-- 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;
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
SELECT id, geometry, names, subtype, country, region
FROM `bigquery-public-data.overture_maps.division_area`
WHERE names.primary = 'Berlin'
AND country = 'DE'
Regions and Cities in France
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;
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
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'
All parks in London
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
Ukraine Schools
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';
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
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;
Las Vegas EV Charging
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)
UK pubs density
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