# Census areas in the UK within tiles of multiple resolutions

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUx7fNjcfw9KvGNf1JTaW%2Fadvanced%20banner.png?alt=media&#x26;token=ea2ec56e-2c6a-4c54-bae4-561b2fa33b7b" alt="Advanced difficulty banner" width="175"><figcaption></figcaption></figure></div>

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](https://carto.com/spatial-data-catalog/browser/geography/drd_output_area_73f0b4a3/) 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).

{% tabs %}
{% tab title="carto-un" %}

```sql
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;
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
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;
```

{% endtab %}

{% tab title="manual" %}

```sql
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;
```

{% endtab %}
{% endtabs %}

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/qOvut7RuK6GXsOSyMsJJ/uk-census-areas.png" alt=""><figcaption></figcaption></figure>

<img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/GWtJUWkKFcCzxcPMkgpe/eu-flag-website.png" alt="EU flag" data-size="line"> This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.