BigQuery Kepler.gl Maps Examples

-- CTE for retrieving the latest population data for each geo_id in specified countries
WITH latest_population AS (
SELECT
  geo_id,
  MAX(last_updated) AS last_updated  -- Finds the most recent update date for each geo_id
FROM
  `bigquery-public-data.worldpop.population_grid_1km` AS pop
WHERE
  alpha_3_code IN ('CYP', 'CZE', 'DNK', 'EST', 'FIN', 'FRA', 'DEU', 'GRC', 'HUN', 'IRL', 'ITA', 'LVA', 'LTU', 'LUX', 'MLT', 'NLD', 'POL', 'PRT', 'ROU', 'SVK', 'SVN', 'ESP', 'SWE', 'AUT', 'BEL', 'BGR', 'HRV', 'NOR') -- Filters for a list of European countries
GROUP BY
  geo_id
),

-- CTE to join the latest population data with the population grid
current_population AS (
SELECT
  pop.geo_id,
  pop.population,
  pop.geog
FROM
  `bigquery-public-data.worldpop.population_grid_1km` AS pop
JOIN
  latest_population
  ON pop.geo_id = latest_population.geo_id
  AND pop.last_updated = latest_population.last_updated -- Joins on the most recent data point
),

-- CTE to create H3 indices for each geographic location at resolution 7
h3_indices AS (
SELECT
  p.population,
  h3
FROM
  current_population AS p
CROSS JOIN
  UNNEST(bqcarto.h3.ST_ASH3_POLYFILL(p.geog, 7)) as h3 -- Uses the H3 polyfill to convert geographies to H3 indices
)

-- Main SELECT statement to sum population by H3 index
SELECT
  h3,
  SUM(population) AS population -- Aggregates population by H3 index
FROM
  h3_indices
GROUP BY
  h3;

with latest as (
SELECT
    max(last_updated) as last_updated,
    geo_id
FROM
    `bigquery-public-data.worldpop.population_grid_1km` AS pop
WHERE
    alpha_3_code in ('CYP', 'CZE', 'DNK', 'EST', 'FIN', 'FRA', 'DEU', 'GRC', 'HUN', 'IRL', 'ITA', 'LVA', 'LTU', 'LUX', 'MLT', 'NLD', 'POL', 'PRT', 'ROU', 'SVK', 'SVN', 'ESP', 'SWE', 'AUT', 'BEL', 'BGR', 'HRV')
    and population >10000
group by geo_id
)

SELECT
population,
geog,
pop.last_updated,
from
    `bigquery-public-data.worldpop.population_grid_1km` as pop,
    latest
WHERE
    latest.last_updated = pop.last_updated
    and pop.geo_id = latest.geo_id

-- Common Table Expression (CTE) to define boundaries based on specific tags
WITH boundary AS (
  SELECT
    geometry
  FROM
    `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons` AS features
  WHERE
    -- Filtering for features in Berlin, Germany using ISO3166-2 tags
    ('ISO3166-2', 'DE-BE') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    AND
    -- Additional filtering for administrative level
    ('admin_level', '4') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
),

-- CTE to get the latest population data intersecting with the defined boundary
population_latest AS (
  SELECT
    geo_id,
    MAX(last_updated) AS last_updated
  FROM
    `bigquery-public-data.worldpop.population_grid_1km` AS pop,
    boundary
  WHERE
    -- Checking for intersections between population grid and boundary
    ST_INTERSECTS(geog, geometry)
  GROUP BY
    geo_id
)

-- Main SELECT to retrieve geographical data and population
SELECT
  geog,
  population
FROM
  `bigquery-public-data.worldpop.population_grid_1km` AS pop,
  population_latest
WHERE
  -- Joining on geo_id and last_updated to filter the latest data points
  population_latest.geo_id = pop.geo_id AND
  population_latest.last_updated = pop.last_updated;

with country as (
SELECT
      ST_SIMPLIFY(geometry, 1000) as geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
    ('boundary', 'administrative') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    AND ('admin_level', '2') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    AND ('ISO3166-1', 'DE') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
), schools as (
SELECT
      geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
    ('amenity', 'school') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
)

select schools.geometry from schools join country on (ST_INTERSECTS(schools.geometry, country.geometry))

-- Create a CTE (Common Table Expression) named 'boundary' to define the geographical boundaries for Nevada (US-NV)
WITH boundary AS (
    SELECT ST_SIMPLIFY(geometry, 1000) as geometry
    FROM `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
    WHERE
        ('boundary', 'administrative') IN (  -- Select features marked as administrative boundaries
            SELECT (key, value)
            FROM UNNEST(all_tags)
        )
        AND ('ISO3166-2', 'US-NV') IN (  -- Focus on features tagged specifically for Nevada
            SELECT (key, value)
            FROM UNNEST(all_tags)
        )
    LIMIT 1  -- Ensure only one boundary is selected, assuming it's the outermost boundary of Nevada
)

SELECT
    ways.geometry as geom
FROM `bigquery-public-data.geo_openstreetmap.planet_ways` as ways, boundary
WHERE ST_Intersects(ways.geometry, boundary.geometry)
AND (
    SELECT value
    FROM UNNEST(ways.all_tags) as tag
    WHERE tag.key = 'highway'
) IN ('motorway', 'trunk', 'primary', 'secondary', 'tertiary', 'unclassified', 'residential')
AND NOT EXISTS (
    SELECT 1
    FROM UNNEST(ways.all_tags) as tag
    WHERE tag.key = 'service' AND tag.value IN ('parking_aisle', 'driveway', 'parking_lot', 'service')
)

-- Create a CTE (Common Table Expression) named 'boundary' to define the geographical boundaries for Nevada (US-NV)
WITH boundary AS (
    SELECT ST_SIMPLIFY(geometry, 1000) as geometry
    FROM `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
    WHERE
        ('boundary', 'administrative') IN (  -- Select features marked as administrative boundaries
            SELECT (key, value)
            FROM UNNEST(all_tags)
        )
        AND ('ISO3166-2', 'US-NV') IN (  -- Focus on features tagged specifically for Nevada
            SELECT (key, value)
            FROM UNNEST(all_tags)
        )
    LIMIT 1  -- Ensure only one boundary is selected, assuming it's the outermost boundary of Nevada
)

-- Main query to select parking amenities that intersect with the Nevada boundary
SELECT
    osm.geometry,  -- Select geometry of each feature
    (
        SELECT value  -- Retrieve the 'access' attribute of the parking amenity
        FROM UNNEST(all_tags)
        WHERE key = 'access'
    ) AS access
FROM
    `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons` osm, boundary  -- Join the main table with the boundary CTE
WHERE
    ST_INTERSECTS(osm.geometry, boundary.geometry)  -- Select only those features that intersect with the Nevada boundary
    AND ('amenity', 'parking') IN (  -- Focus on features tagged as parking amenities
        SELECT (key, value)
        FROM UNNEST(all_tags)
    )

SELECT
    -- getting name from all_tags nested struct
    (
      SELECT value
      FROM UNNEST(all_tags)
      WHERE key = 'name'
    ) AS name,
    -- fixing edges crossing antimeridian
    ST_ASGEOJSON(
      -- simplifying geometry for smaller size
      ST_SIMPLIFY(geometry, 1000)
    ) AS geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
    -- filtering for administrative boundaries
    ('boundary', 'administrative') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    -- getting admin_level=4
    AND ('admin_level', '4') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    -- filtering for country code prefixed with US
    AND 'US' IN (
      SELECT SUBSTR(value, 0, 2)
      FROM UNNEST(all_tags)
      WHERE key = 'ISO3166-2'
    )

with country as (
SELECT
      ST_SIMPLIFY(geometry, 1000) as geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
    ('boundary', 'administrative') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    AND ('admin_level', '2') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
    AND ('ISO3166-1', 'UA') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
), schools as (
SELECT
      geometry
FROM
`bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`
WHERE
    ('amenity', 'school') IN (
      SELECT (key, value)
      FROM UNNEST(all_tags)
    )
)

select schools.geometry from schools join country on (ST_INTERSECTS(schools.geometry, country.geometry))

WITH BoundingPolygon AS (
  SELECT ST_GEOGFROMTEXT('POLYGON ((19.011407561798503 47.45616485157483, 19.011407561798503 47.34036843210035, 19.20169809527555 47.34036843210035, 19.20169809527555 47.45616485157483, 19.011407561798503 47.45616485157483))') AS polygon
),
landuse_areas AS (
  SELECT
    geometry AS landuse_geometry,
    (
      SELECT value
      FROM UNNEST(all_tags)
      WHERE key = 'landuse'
    ) AS landuse_type
  FROM
    `bigquery-public-data.geo_openstreetmap.planet_features_multipolygons`,
    BoundingPolygon
  WHERE
    EXISTS (
      SELECT 1
      FROM UNNEST(all_tags) AS tag
      WHERE tag.key = 'landuse'
    )
    AND ST_WITHIN(geometry, polygon)
),
buildings AS (
  SELECT
    geometry AS building_geometry
  FROM
    `bigquery-public-data.geo_openstreetmap.planet_features`,
    BoundingPolygon
  WHERE
    'building' IN (SELECT key FROM UNNEST(all_tags))
    AND ST_WITHIN(geometry, polygon)
)

SELECT
  landuse.landuse_geometry,
  landuse.landuse_type
FROM
  landuse_areas landuse
LEFT JOIN
  buildings ON ST_INTERSECTS(landuse.landuse_geometry, buildings.building_geometry)
WHERE
  buildings.building_geometry IS NULL

SELECT
    district,
    latitude,
    longitude
from `bigquery-public-data.chicago_crime.crime`
WHERE  Rand() < 13 / 100.0

SELECT * FROM `bigquery-public-data.geo_us_roads.all_roads_17` where mtfcc_feature_class_code = 'S1630'