Spatial format conversion functions convert spatial data between different formats, such as converting geometry objects to text or binary format.
OceanBase Database 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), multipoint (MultiPoint), linestring (LineString), multilinestring (MultiLineString), polygon (Polygon), and multipolygon (MultiPolygon).
Syntax:
ST_AsGeoJSON(g [, max_dec_digits [, options]])
Parameter description:
g: This is the main parameter of the function, indicating 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, i.e., 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 box (bounding box) and coordinate reference system (CRS) information in the JSON.- 0: Default value, all switches are off.
- 1: Enable output of bounding box.
- 2: Enable output of simple CRS information in the short format (
EPSG:srid). - 4: Enable output of detailed CRS information in the long format (
urn:ogc:def:crs:EPSG::srid). If both the long and short formats are enabled, the long format is used for output.
In the following examples, the ST_AsGeoJSON function converts a geometry object to a GeoJSON 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 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 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:
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 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 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:
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 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 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:
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 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, which includes 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:
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 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 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);
Parameter description:
table_name.*: This represents all columns returned by the query, used to build the vector tile. Typically, the 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): 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, which is usually 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 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. 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, we use the result set q.* of the subquery as the input to 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 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 displays the encoded hexadecimal string, which includes 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 coordinate system defined by the Mapbox Vector Tile (MVT) standard. This function ensures that the converted geometry adheres to the MVT specification and is suitable for tile rendering. If a geometry extends beyond the bounds rectangle but remains within the buffer zone, 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 a valid geometry compatible with the MVT specification, typically used as an input parameter for the ST_AsMVT function.
Syntax:
ST_AsMVTGeom(geometry geom, box2d bounds, integer extent=4096, integer buffer=256, boolean clip_geom=true);
Parameter descriptions:
geom: The input geometry to convert.geommust be a valid geometry.bounds: A 2D box defining the tile's boundary. 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 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 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 the geometry 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 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 the input. Then, we set the tile's bounds to a 4096*4096 pixel area; set extent to 4096 to indicate the tile size; set buffer to 0, indicating no additional buffer outside the tile boundary; and set clip_geom to false, indicating that the geometry 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 geometry 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