Version: V1.0.0

Spatial format conversion functions

Spatial format conversion functions are used to convert between different spatial data representations, such as converting geometry objects to text or binary formats.

seekdb currently supports the following spatial format conversion functions: ST_AsGeoJSON, _ST_AsMVT, and ST_AsMVTGeom.

ST_AsGeoJSON

The ST_AsGeoJSON function converts a geometry object to a GeoJSON-formatted string. GeoJSON is a geospatial data exchange format based on JSON (JavaScript Object Notation). The GeoJSON standard primarily supports the following geometry types: Point, MultiPoint, LineString, MultiLineString, Polygon, and MultiPolygon.

Syntax:

ST_AsGeoJSON(g [, max_dec_digits [, options]])

Parameter description:

g: The main parameter of the function, representing the geometry object to be converted to GeoJSON. The geometry object g must be valid.
max_dec_digits (optional): Controls the precision of the output GeoJSON string, i.e., the maximum number of decimal places to display for coordinate points.
- If not specified, the default value is the maximum (2³² - 1).
- The minimum specified value is 0.
options (optional): A 3-bit switch that controls whether to output bounding box (bounding box) and other information in the JSON.
- 0: Default value, all options are disabled.
- 1: Enable the output of bounding boxes.
- 2: Enable the output of simple coordinate reference system (CRS) information in the short format (EPSG:srid).
- 4: Enable the output of detailed coordinate reference system (CRS) information in the long format (urn:ogc:def:crs:EPSG::srid). If both the long and short formats are enabled, the long format will be used.

In the following examples, the ST_AsGeoJSON function converts a geometry object to a GeoJSON-formatted string. Different queries demonstrate how to use the flag parameter of the ST_AsGeoJSON function to control the output GeoJSON string.

Example 1:

-- Use the flag parameter to control the options for GeoJSON output. The value of the flag ranges from 0 (binary 000) to 7 (binary 111).
SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,0);

Use the ST_AsGeoJSON function to convert a point geometry object to GeoJSON without any additional attributes (such as a bounding box or coordinate system).

The returned result is as follows:

+---------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,0) |
+---------------------------------------------------------------------+
| {"type": "Point", "coordinates": [12.2, 11.1]}                      |
+---------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 2:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,1);

Use the ST_AsGeoJSON function and set the flag parameter to 1 to include bounding box information.

The returned result is as follows:

+----------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,1)              |
+----------------------------------------------------------------------------------+
| {"bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+----------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 3:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,2);

Use the ST_AsGeoJSON function and set the flag parameter to 2 to include simple coordinate reference system (CRS) information.

The returned result is as follows:

+--------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,2)                                          |
+--------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "EPSG:4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 4:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,3);

Use the ST_AsGeoJSON function and set the flag parameter to 3 to include bounding box information and simple coordinate reference system (CRS) information.

The returned result is as follows:

+------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,3)                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "EPSG:4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 5:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,4);

Use the ST_AsGeoJSON function and set the flag parameter to 4 to include detailed coordinate reference system (CRS) information.

The returned result is as follows:

+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,4)                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 6:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,5);

Use the ST_AsGeoJSON function and set the flag parameter to 5 to include detailed coordinate reference system (CRS) information and bounding box.

The returned result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,5)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 7:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,6);

Use the ST_AsGeoJSON function and set the flag parameter to 6. The output is the same as when the flag parameter is set to 4, which includes detailed coordinate reference system (CRS) information.

The returned result is as follows:

+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,6)                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 8:

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,7);

Use the ST_AsGeoJSON function and set the flag parameter to 7. The output is the same as when the flag parameter is set to 5, which includes detailed coordinate reference system (CRS) information and bounding box.

The returned result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,7)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

_ST_AsMVT

The _ST_AsMVT function aggregates all rows in a table containing geographic information system (GIS) data and returns a mapbox vector tile encoded in Google Protocol Buffers (Protobuf).

Syntax:

_ST_AsMVT(table_name.*, text name, integer extent, text geom_name, text feature_id_name);

Parameters:

table_name.*: This represents all columns returned by the query, which are used to build the vector tile. Typically, an asterisk (*) is used to select all columns in the table. This parameter must point to an existing table that contains spatial data columns.
text name (optional): The name of the vector layer. In the generated MVT, this name is used to identify the corresponding layer.
integer extent (optional): The pixel range of the vector tile. This integer defines the spatial range of the tile, typically 4096 or 256.
text geom_name (optional): The name of the column in the table that represents the geometry data.
text feature_id_name (optional): The name of the column in the table that represents the feature ID.

Example:

SELECT 'TG1', hex(_ST_AsMVT(q.*, 'test', 4096, 'geom')) FROM (SELECT 1 AS c1, ST_GeomFromText('POINT(25 17)')AS geom) AS q;

In this example, the _ST_AsMVT function converts the spatial data in the query result set into a Mapbox Vector Tile (MVT) format. We construct an inline query (subquery) q that contains a column c1 with a constant value of 1 and a POINT geometry object created by the ST_GeomFromText function.

Then, the result set q.* of the subquery is used as the input for the _ST_AsMVT function, which includes:

'TG1': A simple string used as the first field in the query result set.
hex(_ST_AsMVT(q.*, 'test', 4096, 'geom')): Calls the _ST_AsMVT function and converts the result to a hexadecimal string. The parameters 'test' is the layer name, 4096 is the tile range, and 'geom' specifies the geom column for generating MVT data.

The final result displays an encoded hexadecimal string containing the value 1 of field c1 and the POINT(25 17) geometry object.

The returned result is as follows:

+-----+----------------------------------------------------------------------+
| TG1 | hex(_ST_AsMVT(q.*, 'test', 4096, 'geom'))                            |
+-----+----------------------------------------------------------------------+
| TG1 | 1A200A0474657374120B12020000180122030932221A026331220228012880207802 |
+-----+----------------------------------------------------------------------+
1 row in set (0.001 sec)

ST_AsMVTGeom

The ST_AsMVTGeom function converts spatial objects to the Mapbox Vector Tile (MVT) standard in the corresponding coordinate system. This function ensures that the converted geometry objects conform to the MVT specification and are suitable for tile rendering. If a geometry object is outside the bounds rectangle but within the buffer setting, its clipping behavior is determined by the clip_geom parameter. The origin of the MVT coordinate system is at the top-left corner, consistent with the screen coordinate system. Ultimately, the function outputs valid geometry objects that comply with the MVT specification, typically used as input parameters for the ST_AsMVT function.

Syntax:

ST_AsMVTGeom(geometry geom, box2d bounds, integer extent=4096, integer buffer=256, boolean clip_geom=true);

Parameters:

geom: The input geometry object to be converted. geom must be a valid geometry object.
bounds: A two-dimensional box defining the tile boundary. Usually, this box is a rectangular area defined by four values (xmin, ymin, xmax, ymax) representing the spatial range of the tile.
integer extent=4096 (optional): The pixel range of the vector tile. The default value is 4096, and it must be a non-negative integer.
integer buffer=256 (optional): The size of the buffer added outside the tile boundary. The default value is 256 pixels, and it must be a non-negative integer.
boolean clip_geom=true (optional): A boolean value indicating whether to clip geometry data that is outside the tile boundary but within the buffer to reduce the size of the output tile. The default value is true, meaning that parts exceeding the boundary are clipped.

Example 1:

SELECT ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, false));

In this example, the ST_AsMVTGeom function converts a given POLYGON geometry object to the MVT format:

First, we define a POLYGON geometry object as the input. Then, we define the bounds of the tile as a 4096*4096 pixel area; set the extent to 4096, indicating the tile size; set the buffer to 0, indicating no additional buffer outside the tile boundary; and set clip_geom to false, indicating that geometry parts exceeding bounds will not be clipped. Finally, the ST_AsText function is used to convert the converted geometry object to text format.

The returned result is as follows:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, false)) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 4101,0 4096,10 4091,10 4096,0 4101))                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Example 2:

SELECT ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, true));

In this example, the same input geometry object and bounds are used, but this time clip_geom is set to true. The ST_AsMVTGeom function will clip the geometry parts exceeding bounds.

The returned result is as follows:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, true)) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((10 4096,0 4096,10 4091,10 4096))                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

ST_AsGeoJSON​

_ST_AsMVT​

ST_AsMVTGeom​

Contents

ST_AsGeoJSON

_ST_AsMVT

ST_AsMVTGeom