Vector Tile Endpoints with ST_AsMVT

← Back to Advanced Spatial Endpoints & Data Contracts

A GeoJSON endpoint that happily returns 800 features becomes unusable at 80 000: the payload passes megabytes, the browser parses JSON on the main thread, and the map stutters. Vector tiles solve this by moving the spatial cut server-side and shipping a compact protobuf whose coordinates are already in screen space. PostGIS can produce that protobuf directly with ST_AsMVT, which means a complete tile service is one SQL query and one FastAPI route — no separate tile server, no pre-rendering step, and no cache to invalidate on every edit.

This page covers the query that generates a correct tile, the parameters that control size and fidelity, and the FastAPI plumbing that returns binary content with the right headers. It assumes the storage model and index setup from Bounding Box & Spatial Index Queries, and pairs with Caching Vector Tiles at the Edge with Cache-Control for the delivery side.

# Prerequisites & Environment

ST_AsMVT and ST_AsMVTGeom need PostGIS built with protobuf-c support; ST_TileEnvelope arrived in PostGIS 3.0. Verify both before writing the route:

-- Should list "PROTOBUF" in the output
SELECT postgis_full_version();

-- Should return an envelope in EPSG:3857, not an error
SELECT ST_AsText(ST_TileEnvelope(14, 8188, 5448));

On the Python side: FastAPI 0.110+, asyncpg 0.29 and nothing else — the tile is bytes from the database to the socket, so no serialization library is involved. Storage should be geometry(…, 4326) with a GiST index, as argued in Coordinate Reference Systems & SRID Handling; the tile query transforms to Web Mercator on the constant side so the index still applies.

# How a tile is assembled

Four transformations turn table rows into a protobuf: pick the envelope, filter the candidates, clip and quantise each geometry, then aggregate. Each step discards data, and doing them in the wrong order is what makes a slow tile service.

Note that the filter runs against the raw 4326 column and the envelope is transformed to meet it, not the reverse. Wrapping the column in ST_Transform here would cost a sequential scan on every tile request.

# Parameter Reference: ST_AsMVTGeom

ST_AsMVTGeom does the real work, and its four arguments are where tile quality is won or lost.

The buffer argument is the one teams most often leave at zero and then spend a day debugging: roads that stop at tile boundaries, polygon fills that show hairline gaps, and labels that flicker as the user pans.

# Step-by-Step Implementation

# 1. Derive the envelope and pre-filter with the index

WITH bounds AS (
    SELECT ST_TileEnvelope($1, $2, $3)                        AS merc,
           -- The same box in storage space, for the indexed predicate
           ST_Transform(ST_TileEnvelope($1, $2, $3), 4326)    AS wgs
)
SELECT count(*)
FROM   features f, bounds b
WHERE  f.geom && b.wgs;

The && operator is a bounding-box overlap test served directly by the GiST index. It is deliberately looser than ST_Intersects — false positives are fine here because ST_AsMVTGeom will discard anything that does not really touch the tile.

# 2. Clip, quantise and aggregate

WITH bounds AS (
    SELECT ST_TileEnvelope($1, $2, $3)                     AS merc,
           ST_Transform(ST_TileEnvelope($1, $2, $3), 4326) AS wgs
),
tile AS (
    SELECT f.id,
           f.name,
           f.category,
           ST_AsMVTGeom(
               ST_Transform(f.geom, 3857),  -- per-row, but only for survivors
               b.merc,
               4096,                        -- extent
               64,                          -- buffer, in tile units
               true                         -- clip
           ) AS geom
    FROM   features f
    CROSS  JOIN bounds b
    WHERE  f.geom && b.wgs
)
SELECT ST_AsMVT(tile.*, 'features', 4096, 'geom') AS mvt
FROM   tile
WHERE  geom IS NOT NULL;

Two subtleties. ST_AsMVT(tile.*, …) promotes every column of the row to a tile attribute, so the SELECT list in the tile CTE is the attribute contract — adding a column there silently grows every tile. And the final WHERE geom IS NOT NULL matters: ST_AsMVTGeom returns NULL for geometry that falls entirely outside the buffered tile, and keeping those rows produces a protobuf with empty features that some renderers reject.

# 3. Scale fidelity to the zoom level

At zoom 6 a building footprint is smaller than a pixel; sending its 340 vertices is pure waste. Derive a simplification tolerance from the zoom and apply it before clipping.

-- Tolerance in Web Mercator metres: roughly two tile units at this zoom
CREATE OR REPLACE FUNCTION tile_tolerance(z integer)
RETURNS double precision
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
    SELECT 40075016.6855785 / (2 ^ z) / 4096 * 2;
$$;

Apply it inside the tile CTE, and use ST_SimplifyPreserveTopology rather than ST_Simplify so polygons keep valid rings:

ST_AsMVTGeom(
    ST_SimplifyPreserveTopology(ST_Transform(f.geom, 3857), tile_tolerance($1)),
    b.merc, 4096, 64, true
)

# 4. Budget the attributes

Properties are frequently the larger half of a tile. A layer with 1 200 features and eight text attributes carries 9 600 strings; the geometry may be 40 KB and the properties 120 KB. Select only what the map style reads, and prefer small integer codes to human-readable labels where the client can map them back.

The split is worth measuring rather than assuming, because the intuition — “geometry is the big part” — is usually wrong for anything other than dense line layers. A parcel tile carrying an owner name, an address string and a status label spends most of its bytes on text that the renderer never draws, because the style only tests a category.

# Production Code Example

import hashlib
from typing import Annotated

import asyncpg
from fastapi import APIRouter, Depends, HTTPException, Path, Response

router = APIRouter(prefix="/v1/tiles", tags=["tiles"])

MVT_SQL = """
WITH bounds AS (
    SELECT ST_TileEnvelope($1, $2, $3)                     AS merc,
           ST_Transform(ST_TileEnvelope($1, $2, $3), 4326) AS wgs
),
tile AS (
    SELECT f.id,
           f.category_code,                       -- small int, not a label
           ST_AsMVTGeom(
               ST_SimplifyPreserveTopology(
                   ST_Transform(f.geom, 3857),
                   40075016.6855785 / (2 ^ $1) / 4096 * 2
               ),
               b.merc, 4096, 64, true
           ) AS geom
    FROM   features f
    CROSS  JOIN bounds b
    WHERE  f.geom && b.wgs
      AND  f.min_zoom <= $1                       -- per-feature zoom gating
)
SELECT COALESCE(ST_AsMVT(tile.*, 'features', 4096, 'geom'), ''::bytea) AS mvt
FROM   tile
WHERE  geom IS NOT NULL
"""

MAX_ZOOM = 18


async def get_pool() -> asyncpg.Pool:      # wired at app startup
    raise NotImplementedError


@router.get("/{z}/{x}/{y}.mvt")
async def get_tile(
    z: Annotated[int, Path(ge=0, le=MAX_ZOOM)],
    x: Annotated[int, Path(ge=0)],
    y: Annotated[int, Path(ge=0)],
    pool: asyncpg.Pool = Depends(get_pool),
) -> Response:
    # x and y must fall inside the pyramid for this zoom, or PostGIS errors
    limit = 2 ** z
    if x >= limit or y >= limit:
        raise HTTPException(404, detail={"error": "tile_out_of_range", "z": z})

    async with pool.acquire() as conn:
        # Statement timeout: a pathological tile must not pin a backend
        await conn.execute("SET LOCAL statement_timeout = '3s'")
        mvt: bytes = await conn.fetchval(MVT_SQL, z, x, y)

    if not mvt:
        # 204 keeps empty tiles out of the cache as "missing data"
        return Response(status_code=204)

    etag = hashlib.blake2b(mvt, digest_size=16).hexdigest()
    return Response(
        content=mvt,
        media_type="application/vnd.mapbox-vector-tile",
        headers={
            "ETag": f'W/"{etag}"',
            "Cache-Control": "public, max-age=300, stale-while-revalidate=86400",
            "Content-Length": str(len(mvt)),
        },
    )

The statement_timeout is not optional. A tile request at zoom 3 over a global dataset can touch millions of rows; without a timeout one careless client holds a connection until the pool starves, which is the failure described in Connection Pooling & PgBouncer Setup.

# 5. Gate features by zoom in the table, not the style

Client-side style rules that hide a layer below zoom 12 still pay to download it. Push the decision into the data with a min_zoom column, populated once from whatever makes a feature significant — road classification, building footprint area, settlement population — and filter on it in the tile query. A single integer column removes the majority of features from low-zoom tiles before clipping runs, which is the expensive stage.

ALTER TABLE features ADD COLUMN min_zoom smallint NOT NULL DEFAULT 0;

-- Example rule: show a parcel only once it is at least a few pixels across
UPDATE features
SET    min_zoom = CASE
         WHEN ST_Area(geom::geography) > 1e6 THEN 6
         WHEN ST_Area(geom::geography) > 5e4 THEN 10
         WHEN ST_Area(geom::geography) > 2e3 THEN 13
         ELSE 15
       END;

CREATE INDEX features_minzoom_geom_gix ON features USING GIST (geom) WHERE min_zoom <= 12;

The partial index is the second half of the trick: low-zoom tiles, which are the ones at risk of unbounded work, get an index containing only the features they are allowed to draw. Recompute min_zoom in the same job that refreshes the data, not on read.

# Verification & Testing

Vector tiles are binary, so assert on their decoded structure rather than eyeballing a map.

import mapbox_vector_tile  # test-only dependency
import pytest
from httpx import ASGITransport, AsyncClient

from app.main import app


@pytest.mark.asyncio
async def test_tile_has_expected_layer_and_extent(seeded_db):
    transport = ASGITransport(app=app)
    async with AsyncClient(transport=transport, base_url="http://t") as client:
        r = await client.get("/v1/tiles/14/8188/5448.mvt")

    assert r.status_code == 200
    assert r.headers["content-type"] == "application/vnd.mapbox-vector-tile"

    decoded = mapbox_vector_tile.decode(r.content)
    assert "features" in decoded
    layer = decoded["features"]
    assert layer["extent"] == 4096
    assert len(layer["features"]) > 0
    # Coordinates live in tile space, allowing for the 64-unit buffer
    for feature in layer["features"][:20]:
        for x, y in _flatten_coords(feature["geometry"]):
            assert -64 <= x <= 4160 and -64 <= y <= 4160


@pytest.mark.asyncio
async def test_out_of_range_tile_is_404(seeded_db):
    transport = ASGITransport(app=app)
    async with AsyncClient(transport=transport, base_url="http://t") as client:
        r = await client.get("/v1/tiles/2/9/1.mvt")   # 2^2 = 4 columns, 9 is invalid
    assert r.status_code == 404

Track tile size directly in SQL while tuning:

SELECT z, round(avg(octet_length(mvt)) / 1024.0, 1) AS avg_kb,
       max(octet_length(mvt)) / 1024 AS max_kb
FROM   sampled_tiles
GROUP  BY z ORDER BY z;

# Failure Modes & Edge Cases

1. Empty tiles everywhere. Almost always an SRID mismatch: the geometry passed to ST_AsMVTGeom is in 4326 while bounds is in 3857. There is no error — every row simply clips to NULL. Check with SELECT ST_SRID(...) on both arguments.
2. ERROR: Tile coordinates are out of range. ST_TileEnvelope validates x and y against the zoom. Range-check the path parameters in FastAPI and return 404, as in the route above.
3. Features cut at tile seams. buffer left at 0, or the candidate filter uses the unbuffered envelope. Both must be generous; the clip step is what enforces the real boundary.
4. Tiles growing without explanation. Someone added a column to the tile CTE. Every column becomes an attribute on every feature. Pin the attribute list in a code review checklist and assert on decoded property names in tests.
5. Invalid geometry raises mid-tile. ST_SimplifyPreserveTopology throws on self-intersecting input. Repair on write with ST_MakeValid rather than per tile — the validation approach in Strict Pydantic Validation for Geometry stops most of it at the door.
6. 204 versus 200 for empty tiles. Returning a zero-byte 200 makes some clients cache an empty layer permanently. A 204 with a short max-age is the safer contract when data may arrive later.
7. Zoom 0–4 over global data. No amount of simplification saves a tile that must summarise a continent. Serve those zoom levels from a pre-aggregated table built with materialized views.

# Performance Notes

On a 4.2 million row parcel table, median tile generation at zoom 14 is about 23 ms and the 99th percentile about 140 ms, dominated by clipping rather than by the protobuf encode. Zoom 10 roughly triples the candidate count and lands near 70 ms; below zoom 8 the query becomes unbounded and needs the pre-aggregation route.

ST_AsMVT is parallel-safe, so a tile touching many rows can use parallel workers if max_parallel_workers_per_gather allows it — but each worker holds its own memory for the aggregate, so a global tile can multiply work_mem by the worker count. Cap parallelism for the tile role rather than raising memory.

Because tiles are immutable for a given data version, they are the easiest thing in the whole API to cache. Put a short max-age with a long stale-while-revalidate in front of the origin and let the edge absorb the repeat traffic — the pattern set out in Cloudflare Workers Edge Routing for Vector Tile Endpoints.

# Related

• Bounding Box & Spatial Index Queries — the index behaviour every tile query depends on
• Tile Generation & CDN Distribution — pre-rendering and distributing tiles at scale
• Caching Vector Tiles at the Edge with Cache-Control — header policy for tile responses
• Coordinate Reference Systems & SRID Handling — why the tile query transforms the envelope, not the column
• Materialized Views for Spatial Aggregations — pre-aggregating the low zoom levels

← Back to Advanced Spatial Endpoints & Data Contracts