Spatial format conversion functions are used to convert between different spatial data representation formats, such as converting a geometric object to text or binary format.
OceanBase AI 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 into a string in GeoJSON format. GeoJSON is a geospatial data exchange format based on JavaScript Object Notation (JSON). The GeoJSON standard primarily supports the following geometric types: Point, MultiPoint, LineString, MultiLineString, Polygon, and MultiPolygon.
The syntax is as follows:
ST_AsGeoJSON(g [, max_dec_digits [, options]])
Parameter description:
g: The main parameter of the function, representing the geometric object to be converted into GeoJSON. The geometric objectgmust be valid.max_dec_digits(optional): The maximum number of decimal places to display for a point. This parameter specifies the precision of the GeoJSON string output.- If not specified, the maximum value (232 - 1) is used by default.
- The minimum specified value is 0.
options(optional): three-bit switch for controlling whether to output information such as the bounding box and CRS in JSON.- 0: The default value. All are disabled.
- 1: Enable Bbox output.
- 2: Enable CRS short format output (
EPSG:srid). - 4: Enable the long format output of CRS (
urn:ogc:def:crs:EPSG::srid). If both long and short formats are enabled, the long format is used.
In the following examples, the ST_AsGeoJSON function converts a geometric object into a string in GeoJSON format. Different queries demonstrate how to use the flag parameter of the ST_AsGeoJSON function to control the output GeoJSON string.
Example 1 is as follows:
-- 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).
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 into GeoJSON without any additional attributes, such as a 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 is as follows:
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 is as follows:
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 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 is as follows:
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 Boundary Box Information and Simple Coordinate Reference System (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 is as follows:
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 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 is as follows:
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 a Boundary 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 is as follows:
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, which gives the same output as setting it to 4, including Detailed Coordinate Reference System (CRS) information.
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 is as follows:
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. This value produces the same output as setting it to 5, which includes detailed Coordinate Reference System (CRS) information and a bounding box.
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 row data containing geographic information system (GIS) information from a table and returns a mapbox vector tile encoded in binary format using Google Protocol Buffers (Protobuf).
The syntax is as follows:
_ST_AsMVT(table_name.*, text name, integer extent, text geom_name, text feature_id_name);
Parameter description:
table_name.*: This specifies all columns returned by the query, which are used to build vector tiles. Typically, an asterisk (*) is used to select all columns in a table. This parameter must point to an existing table that contains spatial data columns.text name(optional): The name of the vector layer. This name is used to identify the corresponding layer in the generated MVT.integer extent(optional): The pixel range of the vector tile. This integer defines the spatial range of the tile, typically being common values such as 4096 or 256.text geom_name(optional): the name of the column in the table that contains the geometry data.text feature_id_name(optional): The name of the column that uniquely identifies the feature in the table.
Here is an 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 into vector tiles in Mapbox Vector Tile (MVT) format. We construct an inline query (subquery) q that contains only a column c1 with the constant value 1 and a POINT geometry object geom created using the ST_GeomFromText function.
Then, the result set q.* from the subquery is used as 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_AsMVTfunction and converts the result into a hexadecimal string. In the function parameters,'test'is the layer name,4096is the tile range, and'geom'specifies the geom column used to generate the MVT data.
The final result shows the encoded hexadecimal string, which contains the value 1 for field c1 and the geometric object POINT(25 17).
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 a spatial object to the corresponding coordinate system according to the Mapbox Vector Tile (MVT) standard. This function ensures that the converted geometric object complies with the MVT specification and is suitable for tile rendering. If a geometric object exceeds the rectangular boundary defined by bounds but is still within the buffer zone set by buffer, its clipping behavior is determined by the clip_geom parameter. The origin of the MVT coordinate system is in the upper-left corner, which is consistent with the convention of the screen coordinate system. Ultimately, the function outputs a valid geometric object that is compatible with the MVT specification, which is typically used as an input parameter for the ST_AsMVT function.
The syntax is as follows:
ST_AsMVTGeom(geometry geom, box2d bounds, integer extent=4096, integer buffer=256, boolean clip_geom=true);
Parameter description:
geom: The input geometric object to be transformed.geommust be a valid geometric object.bounds: A two-dimensional box that defines the tile's boundaries. Typically, this box is a rectangular area defined by four values (xmin, ymin, xmax, ymax), representing the spatial extent of the tile.integer extent=4096(optional): the pixel range of the vector tile. The default value is 4096, which 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 that specifies 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 istrue, indicating that parts exceeding the boundary will be clipped.
Example 1 is as follows:
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 is used to convert a given POLYGON geometry object to MVT format:
First, we define a POLYGON geometry object as the input. Next, we define the tile's bounds as a 4096*4096 pixel area; set the extent to 4096 to represent the tile size; set buffer to 0 to indicate no extra buffer zone outside the tile boundaries; and set clip_geom to false to mean that geometric parts exceeding bounds will not be clipped. Finally, use the ST_AsText function to convert the transformed geometric object into text format.
The return 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 is as follows:
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, the same input geometry object and bounds are used, but clip_geom is set to true. The ST_AsMVTGeom function will clip the geometry beyond the bounds.
The return 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