DuckDB function ST_AsMVT

Make a Mapbox Vector Tile from a set of geometries and properties The function takes as input a row type (STRUCT) containing a geometry column and any number of property columns. It returns a single binary BLOB containing the Mapbox Vector Tile.

The function has the following signature:

ST_AsMVT(row STRUCT, layer_name VARCHAR DEFAULT 'layer', extent INTEGER DEFAULT 4096, geom_column_name VARCHAR DEFAULT NULL, feature_id_column_name VARCHAR DEFAULT NULL) -> BLOB

• The first argument is a struct containing the geometry and properties.

• The second argument is the name of the layer in the vector tile. This argument is optional and defaults to 'layer'.

• The third argument is the extent of the tile. This argument is optional and defaults to 4096.

• The fourth argument is the name of the geometry column in the input row. This argument is optional. If not provided, the first geometry column in the input row will be used. If multiple geometry columns are present, an error will be raised.

• The fifth argument is the name of the feature id column in the input row. This argument is optional. If provided, the values in this column will be used as feature ids in the vector tile. The column must be of type INTEGER or BIGINT. If set to negative or NULL, a feature id will not be assigned to the corresponding feature.

The input struct must contain exactly one geometry column of type GEOMETRY. It can contain any number of property columns of types VARCHAR, FLOAT, DOUBLE, INTEGER, BIGINT, or BOOLEAN.

Example:

SELECT ST_AsMVT({'geom': geom, 'id': id, 'name': name}, 'cities', 4096, 'geom', 'id') AS tile
FROM cities;

This example creates a vector tile named 'cities' with an extent of 4096 from the 'cities' table, using 'geom' as the geometry column and 'id' as the feature id column.

However, you probably want to use the ST_AsMVTGeom function to first transform and clip your geometries to the tile extent. The following example assumes the geometry is in WebMercator ("EPSG:3857") coordinates. Replace {z}, {x}, and {y} with the appropriate tile coordinates, {your table} with your table name, and {tile_path} with the path to write the tile to.

COPY (
    SELECT ST_AsMVT({{
        "geometry": ST_AsMVTGeom(
            geometry,
            ST_Extent(ST_TileEnvelope({z}, {x}, {y})),
            4096,
            256,
            false
        )
    }})
    FROM {your table} WHERE ST_Intersects(geometry, ST_TileEnvelope({z}, {x}, {y}))
) to {tile_path} (FORMAT 'BLOB');
```.

Usage

ST_AsMVT(col0, col1, col2, col3, col4)

Arguments

col0

ANY

col1

VARCHAR

col2

INTEGER

col3

VARCHAR

col4

VARCHAR

Value

BLOB

Overloads

• ST_AsMVT(col0 = ANY)

• ST_AsMVT(col0 = ANY, col1 = VARCHAR)

• ST_AsMVT(col0 = ANY, col1 = VARCHAR, col2 = INTEGER)

• ST_AsMVT(col0 = ANY, col1 = VARCHAR, col2 = INTEGER, col3 = VARCHAR)

• ST_AsMVT(col0 = ANY, col1 = VARCHAR, col2 = INTEGER, col3 = VARCHAR, col4 = VARCHAR)

Provided by

The spatial extension (LOAD spatial;).