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;
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)