Census areas in the UK within tiles of multiple resolutions

In this example we are going to use the ST_TILEENVELOPE function, which returns the bounding geometry of quadkey tile given its 3D coordinates (x, y and resolution), to showcase the extent of quadkey tiles at different resolutions. For this purpose we are using the United Kingdom census areas dataset from CARTO’s Data Observatory.

Taking as input the longitude and latitude from a geographic point in the city of London, the resulting map depicts the belonging census areas for different tile resolutions (from 11 to 15 zoom levels).

carto-un

WITH census_data AS (
    SELECT geom, geoid
    FROM `carto-do-public-data.gbr_cdrc.geography_gbr_outputarea_2011`
),
point_data AS (
    SELECT -0.10481368396112065 AS longitude, 51.51493384150647 AS latitude, resolution
    FROM UNNEST (GENERATE_ARRAY(11, 15)) AS resolution
),
quadkey_tiles AS (
    SELECT `carto-un`.carto.QUADINT_TOZXY(
            `carto-un`.carto.QUADINT_FROMLONGLAT(longitude, latitude, resolution)
        ) AS index
    FROM point_data
)
SELECT ANY_VALUE(census.geom) AS area, MAX(index.z) AS level
FROM quadkey_tiles
JOIN census_data AS census
ON
ST_INTERSECTS(`carto-un`.carto.ST_TILEENVELOPE(index.z, index.x, index.y), census.geom)
GROUP BY census.geoid;

carto-un-eu

WITH census_data AS (
    SELECT geom, geoid
    FROM `carto-do-public-data.gbr_cdrc.geography_gbr_outputarea_2011`
),
point_data AS (
    SELECT -0.10481368396112065 AS longitude, 51.51493384150647 AS latitude, resolution
    FROM UNNEST (GENERATE_ARRAY(11, 15)) AS resolution
),
quadkey_tiles AS (
    SELECT `carto-un-eu`.carto.QUADINT_TOZXY(
            `carto-un-eu`.carto.QUADINT_FROMLONGLAT(longitude, latitude, resolution)
        ) AS index
    FROM point_data
)
SELECT ANY_VALUE(census.geom) AS area, MAX(index.z) AS level
FROM quadkey_tiles
JOIN census_data AS census
ON
ST_INTERSECTS(`carto-un-eu`.carto.ST_TILEENVELOPE(index.z, index.x, index.y), census.geom)
GROUP BY census.geoid;

manual

WITH census_data AS (
    SELECT geom, geoid
    FROM `carto-do-public-data.gbr_cdrc.geography_gbr_outputarea_2011`
),
point_data AS (
    SELECT -0.10481368396112065 AS longitude, 51.51493384150647 AS latitude, resolution
    FROM UNNEST (GENERATE_ARRAY(11, 15)) AS resolution
),
quadkey_tiles AS (
    SELECT carto.QUADINT_TOZXY(
            carto.QUADINT_FROMLONGLAT(longitude, latitude, resolution)
        ) AS index
    FROM point_data
)
SELECT ANY_VALUE(census.geom) AS area, MAX(index.z) AS level
FROM quadkey_tiles
JOIN census_data AS census
ON
ST_INTERSECTS(carto.ST_TILEENVELOPE(index.z, index.x, index.y), census.geom)
GROUP BY census.geoid;

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.