Spatial format conversion functions are used to convert spatial data from one format to another, such as converting geometry objects to text or binary format.
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 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]])
Parameters:
g: This is the main parameter of the function, representing the geometry object to be converted to GeoJSON. The geometry objectgmust be valid.max_dec_digits(optional): This parameter controls the precision of the output GeoJSON string, that is, the maximum number of decimal places to display for coordinate points.- If not specified, the default value is the maximum value (232 - 1).
- The minimum specified value is 0.
options(optional): This is a 3-bit switch that controls whether to output bounding boxes (bounding box) and coordinate reference systems (CRS) in the JSON.- 0: Default value, all switches are off.
- 1: Enable the output of bounding boxes.
- 2: Enable the output of simple CRS in short format (
EPSG:srid). - 4: Enable the output of detailed CRS in long format (
urn:ogc:def:crs:EPSG::srid). If both long and short formats are enabled, the output will be in long format.
In the following examples, the ST_AsGeoJSON function converts a geometry object to a GeoJSON string. The flag parameter of the ST_AsGeoJSON function is used to control the output GeoJSON string.
Example 1:
-- Use the flag parameter to control the options for 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 (such as bounding boxes or coordinate systems).
The 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 bounding box information.
The 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 simple coordinate reference system (CRS) information.
The 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 bounding box information and simple coordinate reference system (CRS) information.
The 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 detailed coordinate reference system (CRS) information.
The 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 detailed coordinate reference system (CRS) information and bounding box.
The 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, which includes detailed coordinate reference system (CRS) information.
The 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, which includes detailed coordinate reference system (CRS) information and bounding box.
The 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 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, used to build the vector tile. Typically, an asterisk (*) is used to select all columns from 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 is the pixel range of the vector tile. This integer defines the spatial range of the tile, typically 4096 or 256.text geom_name(optional): This is the name of the column in the table that represents the geometry data.text feature_id_name(optional): This is the name of the column in the table that represents the feature unique identifier (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) format vector tile. We construct an inline query (subquery) q that contains a column c1 with a constant value of 1 and a POINT geometry object geom 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')): Call the_ST_AsMVTfunction and convert the result to a hexadecimal string. The parameters of the function include'test'as the layer name,4096as the tile range, and'geom'specifying the geom column for generating MVT data.
The final result shows the encoded hexadecimal string, containing the value 1 of field c1 and the POINT(25 17) geometry object.
The 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. This function ensures that the converted geometry objects conform to the MVT specification and are suitable for tile rendering. If a geometry 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 screen coordinate system. Ultimately, the function outputs valid geometry 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 object to be converted.geommust be a valid geometry object.bounds: A two-dimensional box defining the tile boundary. Typically, 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 extends beyond the tile boundary but remains within the buffer to reduce the size of the output tile. The default value istrue, meaning that parts exceeding the boundary are 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 object to the MVT format:
First, we define a POLYGON geometry object as input. Then, we set the bounds of the tile 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 geometry parts exceeding bounds will not be clipped. Finally, we use the ST_AsText function to convert the converted geometry object to a 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 object and bounds as in Example 1, but we set clip_geom to true. The ST_AsMVTGeom function will clip the geometry parts that extend 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