Spatial format conversion functions are used to convert spatial data between different representations, such as converting geometry objects to text or binary formats.
OceanBase Database 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 geographic data interchange format based on JavaScript Object Notation (JSON). The GeoJSON standard primarily supports the following geometry types: point (Point), multi-point (MultiPoint), line string (LineString), multi-line string (MultiLineString), polygon (Polygon), and multi-polygon (MultiPolygon).
Syntax:
ST_AsGeoJSON(g [, max_dec_digits [, options]])
Parameter description:
g: This is the main parameter of the function. It specifies the geometry object to be converted to GeoJSON. The geometry objectgmust be valid.max_dec_digits(optional): This parameter specifies the precision of the output GeoJSON string, that is, the maximum number of decimal places to display for coordinate points.- If you do not specify this parameter, the default value is the maximum value (232 - 1).
- The minimum value is 0.
options(optional): This is a 3-bit switch that specifies whether to output the bounding box (bounding box) and coordinate reference system (CRS) information in the JSON.- 0: The default value. All switches are turned off.
- 1: Outputs the bounding box.
- 2: Outputs the short format of the CRS (
EPSG:srid). - 4: Outputs the long format of the CRS (
urn:ogc:def:crs:EPSG::srid). If you specify both the long and short formats, the long format is used.
The following examples show how to use the ST_AsGeoJSON function to convert a geometry object to a GeoJSON string. The flag parameter of the function controls the output GeoJSON string.
Example 1:
-- Use the flag parameter to control the options of the GeoJSON output. The flag value ranges from 0 (binary 000) to 7 (binary 111).
obclient> 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 (for example, the bounding box or coordinate system).
The return 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
Example 2:
obclient> 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 the bounding box information.
The return 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
Example 3:
obclient> 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 the simple CRS information.
The return 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
Example 4:
obclient> 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 the bounding box information and the simple CRS information.
The return 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
Example 5:
obclient> 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 the detailed CRS information.
The return 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
Example 6:
obclient> 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 the detailed CRS information and the bounding box.
The return 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
Example 7:
obclient> 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. The detailed CRS information is included.
The return 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
Example 8:
obclient> 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. The detailed CRS information and the bounding box are included.
The return 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
_ST_AsMVT
The _ST_AsMVT function aggregates all rows of a table that contain 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);
Parameter description:
table_name.*: This parameter specifies all the columns returned by the query to build the vector tile. Usually, you use the asterisk (*) to select all columns in a table. This parameter must point to an existing table that contains spatial data columns.text name(optional): This is the name of the vector layer. In the generated MVT, this name is used to identify the corresponding layer.integer extent(optional): This parameter specifies the pixel range of the vector tile. This integer defines the spatial range of the tile, which is usually 4096 or 256.text geom_name(optional): This is the name of the column in the table that stores the geometry data.text feature_id_name(optional): This is the name of the column in the table that stores the feature ID.
Example:
obclient> 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 to a Mapbox Vector Tile (MVT) vector tile. An inline query (subquery) q is constructed. It contains only one column c1 with a constant value of 1 and a POINT geometry object geom created by using the ST_GeomFromText function.
The result set of the subquery q.* is used as the input of the _ST_AsMVT function. The result set 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_AsMVTfunction and converts the result to a hexadecimal string. The parameters of the function are as follows:'test'is the layer name,4096is the tile range, and'geom'specifies the geom column for generating MVT data.
The final result is a hexadecimal string that contains the value 1 of field c1 and the POINT(25 17) geometry object.
The return result is as follows:
+-----+----------------------------------------------------------------------+
| TG1 | hex(_ST_AsMVT(q.*, 'test', 4096, 'geom')) |
+-----+----------------------------------------------------------------------+
| TG1 | 1A200A0474657374120B12020000180122030932221A026331220228012880207802 |
+-----+----------------------------------------------------------------------+
1 row in set
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 geometric objects conform to the MVT specification and are suitable for tiling rendering. If a geometric object extends beyond the rectangular boundary defined by bounds but remains within the buffer specified by buffer, its clipping behavior is determined by the clip_geom parameter. The origin of the MVT coordinate system is at the top-left corner, aligning with the conventions of screen coordinate systems. Ultimately, the function outputs valid geometric objects compatible 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);
Parameter explanations:
geom: The input geometry to be converted.geommust be a valid geometry.bounds: A 2D bounding box defining the tile's spatial extent. Typically, this is a rectangular area defined by four values (xmin, ymin, xmax, ymax).integer extent=4096(optional): The pixel extent 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's 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 geometric data that extends beyond the tile's boundary but remains within the buffer to reduce the output tile's size. The default value istrue, meaning that parts exceeding the boundary will be clipped.
Example 1:
obclient> 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 to MVT format:
First, we define a POLYGON geometry as input. Then, we set the bounds to 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 parts exceeding bounds will not be clipped. Finally, we use the ST_AsText function to convert the converted geometry to text format.
The 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
Example 2:
obclient> 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, we use the same input geometry and bounds as in Example 1, but we set clip_geom to true. The ST_AsMVTGeom function will clip the geometric data that extends beyond bounds.
The 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