Vector Tiles Enriched with DWH Query Results

Overview

CleverMaps can now return the result of a DWH query as vector tiles. Their features already carry the query values, for example a metric computed per district.

Until now, a map client that wanted to colour polygons by a metric had to:

1. fetch the vector tiles of the polygon dataset,

2. execute the DWH query,

3. collect the primary keys of the features in each tile and fetch the matching rows of the query result,

4. join the rows to the features itself.

With enriched vector tiles, the client executes the DWH query and uses the vectorTiles link of the response as the tile URL of a map layer. The join moves from every client to the server.

The feature builds on Ad-hoc Vector Tile Rendering from DWH Datasets. An enriched tile has the same layer and geometry as the plain tile.

Key Benefits

• No client-side join: one request per tile, with no extra calls to the query result.

• Works with existing queries: a keyed DWH query gets the vectorTiles link automatically. There is no new query syntax.

• Fast: the server joins two results that are already cached, the plain vector tile and the query result. A tile request never executes the query again.

• Smaller tiles: features without a row in the query result are left out.

Data Model

The examples on this page use this data model:

The district dataset stores its geometry in a property of type geometry. It references its vector tile dataset in ref.geometry:

{
    "name": "district",
    "type": "dataset",
    "title": "Districts",
    "ref": {
        "type": "dwh",
        "subtype": "geometryPolygon",
        "geometry": "district_vt",
        "primaryKey": "districtcode",
        "properties": [
            { "name": "districtcode", "title": "District code", "column": "districtcode", "type": "string" },
            { "name": "name", "title": "District name", "column": "name", "type": "string" },
            { "name": "wkt", "title": "WKT polygon", "column": "wkt", "type": "geometry" }
        ]
    }
}

The urlTemplate of district_vt points to the plain tile endpoint, /rest/projects/{projectId}/dwh/{dwhClusterId}/tiles/district/{z}/{x}/{y}.vector.pbf.

Note: A dataset is tileable if it meets all of these conditions:

• it is a dwh dataset of subtype geometryPolygon or geometryLine,

• it has a property of type geometry,

• it references a vector tile dataset in ref.geometry.

A dwh dataset without ref.geometry is valid, but no tiles are served for it until a vector tile dataset is attached.

How It Works

1. Submit a keyed DWH query

A query is keyed when it meets all of these conditions:

• it has exactly one result set filter, with operator in or eq,

• the filter is on the primary key of the dataset (district.districtcode),

• all plain properties of the query come from that dataset.

Metrics may aggregate data from other datasets through the data model. Here turnover is the expression of the metric turnover_metric, aggregated from baskets through shops to district:

POST /rest/projects/{projectId}/dwh/{dwhClusterId}/queries

{
    "properties": [
        { "id": "districtcode", "type": "property", "value": "district.districtcode" },
        { "id": "district_name", "type": "property", "value": "district.name" },
        {
            "id": "turnover",
            "type": "function_sum",
            "content": [{ "type": "property", "value": "baskets.amount" }]
        }
    ],
    "resultSetFilter": [
        { "propertyId": "districtcode", "operator": "in", "value": [] }
    ]
}

An empty value list means all districts. To limit the result to some districts, list their primary keys, for example "value": ["E07000178", "E07000179"].

2. Read the links of the response

The query is executed asynchronously. The 202 Accepted response contains links:

{
    "executionDetail": {},
    "links": [
        {
            "rel": "self",
            "href": "/rest/projects/f76on62tb6bpitbb/dwh/cmstd1/queries/i:f76on62tb6bpitbb:Xk3pQ9vT2mLr8YwZ1aBc0g"
        },
        {
            "rel": "vectorTiles",
            "href": "/rest/projects/f76on62tb6bpitbb/dwh/cmstd1/queries/i:f76on62tb6bpitbb:Xk3pQ9vT2mLr8YwZ1aBc0g/tiles/district/{z}/{x}/{y}.vector.pbf"
        }
    ]
}

• self is the same as the Location header: the query result as JSON rows. A keyed result is read by key, as in /rest/projects/{projectId}/dwh/{dwhClusterId}/queries/i:{projectId}:{dwhCacheId}?filter=E07000178&filter=E07000179.

• vectorTiles is a URI template. Fill in {z}, {x} and {y}. The dataset in the path (district) is already filled in.

• If the query lists primary keys, both links carry them as filter parameters, for example /rest/projects/{projectId}/dwh/{dwhClusterId}/queries/i:{projectId}:{dwhCacheId}/tiles/district/{z}/{x}/{y}.vector.pbf?filter=E07000178&filter=E07000179.

• Any other query gets only the self link. Examples are an aggregation grouped by a non-key property, a query keyed by an H3 grid, or a query keyed by a dataset that is not tileable.

3. Get an enriched vector tile

GET /rest/projects/f76on62tb6bpitbb/dwh/cmstd1/queries/i:f76on62tb6bpitbb:Xk3pQ9vT2mLr8YwZ1aBc0g/tiles/district/8/127/84.vector.pbf
Authorization: Bearer {token}

The response is a Mapbox vector tile (application/vnd.mapbox-vector-tile). The client can style its features directly, for example colour districts by turnover.

How the Enrichment Works

Plain tile: geometry and primary key

A tile is a square of the map ({z}/{x}/{y}). It contains every feature of the dataset that reaches into it. The plain tile of district (GET /rest/projects/{projectId}/dwh/{dwhClusterId}/tiles/district/8/127/84.vector.pbf) carries only the geometry and the primary key of each district. Shown as JSON (the tile itself is binary protobuf, values are illustrative):

{
    "layers": [
        {
            "name": "district_vt",
            "extent": 4096,
            "keys": ["districtcode"],
            "values": [
                { "string_value": "E07000177" }, { "string_value": "E07000178" },
                { "string_value": "E07000179" }, { "string_value": "E07000180" }
            ],
            "features": [
                { "type": "POLYGON", "tags": [0, 0], "geometry": [9, 1520, 310, 26, "…", 15] },
                { "type": "POLYGON", "tags": [0, 1], "geometry": [9, 2048, 1024, 26, "…", 15] },
                { "type": "POLYGON", "tags": [0, 2], "geometry": [9, 2168, 1980, 18, "…", 15] },
                { "type": "POLYGON", "tags": [0, 3], "geometry": [9, 640, 2210, 26, "…", 15] }
            ]
        }
    ]
}

• The layer is named after the vector tile dataset in ref.geometry (district_vt).

• keys and values are dictionaries shared by the whole layer. A feature refers to them by index pairs in tags: [0, 1] means districtcode = E07000178.

• geometry holds drawing commands in tile coordinates (0–4096). A district that crosses the tile edge is clipped. The rest of its polygon is in the neighbouring tile, with the same districtcode.

Without enrichment, the client reads districtcode from each feature, fetches the rows by key from the self link and joins them itself.

Enriched tile: query values on each feature

When an enriched tile is requested, the server:

1. loads the plain tile above. If it is not cached yet, it is rendered and cached as described in Ad-hoc Vector Tile Rendering from DWH Datasets;

2. reads the primary keys of its features;

3. looks up only those keys in the cached query result, and only the keys in the filter parameters if the link has any;

4. builds a new tile with the same layer and the same geometries. Each feature gets the values of its row as attributes, and features without a row are left out.

The tile is an inner join of the plain tile and the query result: it contains only the districts that are in both. A district without any data to aggregate has no row in the query result. In this example, E07000179 has no baskets. Its feature is left out, and its key and name do not appear in values either.

The same tile for the query above ("value": []):

{
    "layers": [
        {
            "name": "district_vt",
            "extent": 4096,
            "keys": ["districtcode", "district_name", "turnover"],
            "values": [
                { "string_value": "E07000177" }, { "string_value": "Cherwell" }, { "double_value": 2210450.0 },
                { "string_value": "E07000178" }, { "string_value": "Oxford" }, { "double_value": 1854320.5 },
                { "string_value": "E07000180" }, { "string_value": "Vale of White Horse" }, { "double_value": 1320775.25 }
            ],
            "features": [
                { "type": "POLYGON", "tags": [0, 0, 1, 1, 2, 2], "geometry": [9, 1520, 310, 26, "…", 15] },
                { "type": "POLYGON", "tags": [0, 3, 1, 4, 2, 5], "geometry": [9, 2048, 1024, 26, "…", 15] },
                { "type": "POLYGON", "tags": [0, 6, 1, 7, 2, 8], "geometry": [9, 640, 2210, 26, "…", 15] }
            ]
        }
    ]
}

Decoded to GeoJSON, the features have these properties:

{ "districtcode": "E07000177", "district_name": "Cherwell", "turnover": 2210450.0 }
{ "districtcode": "E07000178", "district_name": "Oxford", "turnover": 1854320.5 }
{ "districtcode": "E07000180", "district_name": "Vale of White Horse", "turnover": 1320775.25 }

Rules for the attributes:

• Attribute names are the property ids of the query, not the dataset property names. The primary key is an attribute only if the query selects it, as districtcode does here.

• A null value in a row of the query result has no attribute, because a vector tile cannot store null. Treat a missing attribute as null. For example, a district without a name would have no district_name. The feature itself stays in the tile, because the district has a row.

• Whole numbers are stored as integers and decimal numbers as doubles. Text and dates are stored as strings, booleans as booleans.

• The geometry is copied unchanged from the plain tile.

Filters

The values of the result set filter are not part of the query result id. The queries with "value": ["E07000178"] and "value": ["E07000178", "E07000179"] share one cached result and the same i: id. They differ only in the filter parameters, which the server adds to their links. For tile 8/127/84 above:

Long polling

Like GET /rest/projects/{projectId}/dwh/{dwhClusterId}/queries/{dwhCacheId}, the tile endpoint implements long polling. If the query or the plain tile is still being computed, the server holds the request for up to 30 seconds. A map can request its tiles right after submitting the query. The requests complete as soon as the result is ready.

Response Codes

Note: The plain tile endpoint GET /rest/projects/{projectId}/dwh/{dwhClusterId}/tiles/{datasetName}/{z}/{x}/{y}.vector.pbf now also answers 204 for an empty tile, instead of 404. On both endpoints, 204 means "nothing to draw here" and 404 means "not available yet". The plain endpoint also answers 400 for a dataset that is not tileable, for example a vector tile dataset served from Mapbox.

Limitations

1. Keyed queries over tileable datasets only: other queries get no vectorTiles link. Datasets whose tiles are served by Mapbox are not supported.

2. Result size: the keyed query result is limited to 100,000 rows, as for any keyed query.

3. Timestamp primary keys are not supported.

4. No geometry generalization: the limitations of ad-hoc vector tile rendering apply.

5. Cached results: the tile link works only while the query result is cached. After a data load, submit the query again. It returns the same link.

6. Authorization: access requires a JWT Bearer token and the CleverMaps project roles. Public tokens are not supported.

See Also

• Ad-hoc Vector Tile Rendering from DWH Datasets

• Datasets

• Metrics