ST_AsMVTEx function for reducing MVT size and improving visualization efficiency - PolarDB

Generates a binary mapbox vector tile (MVT) from row data, with built-in element filtering to reduce tile size and improve visualization efficiency.

ST_AsMVTEx works like ST_AsMVT but filters out vector elements that have little effect on display, based on relationships among elements. Two additional parameters control this behavior: scale_factor sets the filtering intensity, and mvt_size_limit caps the total number of elements in the output tile.

Syntax

bytea ST_AsMVTEx(anyelement row);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor, int4 mvt_size_limit);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor, int4 mvt_size_limit, text name);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor, int4 mvt_size_limit, text name, int4 extent);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor, int4 mvt_size_limit, text name, int4 extent, text geom_name);
bytea ST_AsMVTEx(anyelement row, int4 scale_factor, int4 mvt_size_limit, text name, int4 extent, text geom_name, text feature_id_name);

Return value

Returns a bytea value — a binary MVT representing a tile layer. The tile content is determined by the set of input rows.

Parameters

Parameter	Type	Description	Default
`row`	anyelement	Row data with at least one geometry column.	—
`scale_factor`	int4	Filtering intensity. Larger values filter more elements, producing a smaller MVT. Valid values: `1` to `extent - 1`.	`1`
`mvt_size_limit`	int4	Maximum number of vector elements in the output MVT. After filtering, if the element count still exceeds this limit, the system randomly discards the excess.	`2147483647`
`name`	text	Layer name.	`default`
`extent`	int4	Tile extent in screen space, as defined by the MVT specification.	`4096`
`geom_name`	text	Name of the geometry column in the row data.	First geometry column
`feature_id_name`	text	Name of the Feature ID column. The Feature ID is not set for `NULL` or negative values. The first column matching the name and a valid type (`smallint`, `integer`, or `bigint`) is used as the Feature ID; subsequent matching columns are added as properties. JSON properties are not supported.	—

Usage notes

A large extent value degrades system performance.
ST_AsMVTEx is not suitable for datasets that consist of large planes.

Examples

The examples below use a table with one polygon, one line, and 9,998 points. All examples use ST_AsMVTGeom to prepare geometries and ST_AsMVTEx to generate the tile.

Set up the example table:

-- Create a vector data table and insert one plane, one line, and 9998 points.
CREATE TABLE example_table(id integer, geom Geometry);
INSERT INTO example_table(id, geom) VALUES
  (1, ST_MakeEnvelope(150, 75, 170, 80, 4326));
INSERT INTO example_table(id, geom) VALUES
  (2, ST_GeomFromText('LINESTRING(-160 -70, 160 -70)', 4326));
INSERT INTO example_table(id, geom)
SELECT i,
  ST_SetSRID(ST_MakePoint((random() * 20) + 150, (random() * 10) + 70), 4326)
FROM generate_series(3, 10000) i;

Example 1: Use the default filtering (scale_factor=1)

WITH mvtgeom AS
(
      SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)) FROM example_table
      WHERE geom && ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)
)
SELECT length(ST_AsMVTEx(mvtgeom.*, 1)) FROM mvtgeom;

Output:

length
-------
  54237
(1 row)

Example 2: Increase filtering to reduce tile size (scale_factor=4)

WITH mvtgeom AS
(
      SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)) FROM example_table
      WHERE geom && ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)
)
SELECT length(ST_AsMVTEx(mvtgeom.*, 4)) FROM mvtgeom;

Output:

length
-------
  39211
(1 row)

Example 3: Cap the element count at 100 (scale_factor=4, mvt_size_limit=100)

WITH mvtgeom AS
(
      SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)) FROM example_table
      WHERE geom && ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)
)
SELECT length(ST_AsMVTEx(mvtgeom.*, 4, 100)) FROM mvtgeom;

Output:

length
-------
   1117
(1 row)

Example 4: Set a custom tile resolution and layer name

Sets scale_factor to 4, mvt_size_limit to 100, tile resolution to 10000 × 10000, and layer name to layer_name.

WITH mvtgeom AS
(
      SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(1, 1, 0), 4326), 10000) FROM example_table
      WHERE geom && ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)
)
SELECT length(ST_AsMVTEx(mvtgeom.*, 4, 100, 'layer_name', 10000)) FROM mvtgeom;

Output:

length
-------
   1220
(1 row)

Example 5: Specify geometry and Feature ID columns

Sets scale_factor to 4, mvt_size_limit to 100, tile resolution to 10000 × 10000, layer name to layer_name, geometry column to geom_field, and Feature ID column to id_field.

WITH mvtgeom AS
(
      SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(1, 1, 0), 4326), 10000) AS geom_field, id AS id_field FROM example_table
      WHERE geom && ST_Transform(ST_TileEnvelope(1, 1, 0), 4326)
)
SELECT length(ST_AsMVTEx(mvtgeom.*, 4, 100, 'layer_name', 10000, 'geom_field', 'id_field')) FROM mvtgeom;

Output:

length
-------
   1520
(1 row)