Spatial format conversion functions convert spatial data between representation formats, such as converting geometry objects to the text or binary format.
OceanBase Database supports the following spatial format conversion functions: ST_AsGeoJSON(), _ST_AsMVT(), and ST_AsMVTGeom().
ST_AsGeoJSON
ST_AsGeoJSON() converts a geometry object into a GeoJSON string. GeoJSON is a geospatial data interchange format based on JavaScript Object Notation (JSON). GeoJSON supports the following geometry types: Point, MultiPoint, LineString, MultiLineString, Polygon, and MultiPolygon.
The syntax is as follows:
ST_AsGeoJSON(g [, max_dec_digits [, options]])
where:
gindicates the geometry object to be converted into a GeoJSON string, which must be a valid object. This is the main parameter of the function.max_dec_digitsindicates the precision of the output GeoJSON string. This parameter is optional.options/flagindicates the additional formatting options, which depend on the database system. For example, you may specify an option to include the bounding box in the output. This parameter is optional.
In the following examples, ST_AsGeoJSON() converts a geometry object into a GeoJSON string. The parameter flag is used to control the GeoJSON string returned by the ST_AsGeoJSON() function.
Example 1 is as follows:
-- Use the flag parameter to control the GeoJSON output. The value of flag ranges from 0 (000 in binary) to 7 (111 in binary).
obclient> SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,0);
Call ST_AsGeoJSON() to convert a point into a GeoJSON string without any additional properties, such as the bounding box or coordinate reference system (CRS).
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);
Call ST_AsGeoJSON() and set flag to 1 to include the bounding box in the output.
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);
Call ST_AsGeoJSON() and set flag to 2 to include simple CRS information in the output.
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);
Call ST_AsGeoJSON() and set flag to 3 to include the bounding box and simple CRS information in the output.
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);
Call ST_AsGeoJSON() and set flag to 4 to include detailed CRS information in the output.
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);
Call ST_AsGeoJSON() and set flag to 5 to include detailed CRS information and the bounding box in the output.
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);
Call ST_AsGeoJSON() and set flag to 6, which is the same as the value 4, to include detailed CRS information in the output.
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);
Call ST_AsGeoJSON() and set flag to 7, which is the same as the value 5, to include detailed CRS information and the bounding box in the output.
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
_ST_AsMVT() aggregates rows that contain a spatial column and returns a binary Mapbox Vector Tile (MVT) tile encoded by 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);
where
table_name.*uses all columns returned by the query to build the MVT tile. An asterisk (*) is usually used to select all columns in a table. The target table must exist and contain a spatial column.text nameindicates the name of the vector layer. This parameter is optional. In the generated MVT tile, this name is used to identify the corresponding layer.integer extentindicates the pixel extent of the MVT tile. This parameter is optional. This integer defines the spatial extent of the MVT tile and is usually a common value, such as 4096 or 256.text geom_nameindicates the name of the spatial column in the table. This parameter is optional.text feature_id_nameindicates the name of the feature ID column in the table. This parameter is optional.
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, _ST_AsMVT() converts spatial data in the query result set into a MVT tile. The inner join query (subquery) q is created, containing only column c1 with the constant value 1 and a spatial column with a POINT object named geom created by using ST_GeomFromText().
The result set q.* of the subquery is then used as the input of the _ST_AsMVT() function.
'TG1': a simple string that is used as the first field in the final output.hex(_ST_AsMVT(q.*, 'test', 4096, 'geom')): calls the_ST_AsMVT()function and converts the result to a hexadecimal string. In the function,'test'is the layer name,4096is the tile extent, and'geom'is the column used to generate the MVT tile.
The final output displays an encoded hexadecimal string that represents the value 1 of column c1 and the geometry 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
ST_AsMVTGeom() transforms a geometry object into the coordinate space of a MVT tile. This function ensures that the transformed geometry object conforms to the MVT specification and is suitable for tile rendering. If the geometry object exceeds the rectangular bounds defined by bounds but is still within the buffer specified by buffer, the function clips the geometry object or not based on the value of clip_geom. The origin of the MVT coordinate system is in the upper-left corner, which conforms to the custom of the screen coordinate system. The function outputs a valid geometry object that is compatible with the MVT specification and usually used as the input of 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);
where:
geomindicates the input geometry object to be converted, which must be valid.boundsindicates a 2D box that defines tile bounds. Usually, this box is a rectangular area defined by four values (xmin, ymin, xmax, and ymax), representing the spatial extent of the tile.integer extent=4096indicates the pixel extent of the MVT tile. The default value is 4096. The value must be a non-negative integer. This parameter is optional.integer buffer=256indicates the size of the buffer outside tile bounds. The default value is 256 pixels. The value must be a non-negative integer. This parameter is optional.boolean clip_geom=trueis a Boolean value that indicates whether to clip off the geometry data outside tile bounds but in the buffer to reduce the size of the output tile. This parameter is optional. The default value istrue, which indicates that the part beyond tile bounds is 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, ST_AsMVTGeom() transforms a POLYGON object to a MVT tile.
First, define a POLYGON object as the input. Then, set bounds to an area of 4096 × 4096 pixels. Set extent to 4096 to define the size of the tile. Set buffer to 0, indicating that there is no buffer outside tile bounds. Set clip_geom to false, indicating that the part outside bounds is not clipped. Finally, call the ST_AsText() function to convert the geometry object into the 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));
This example uses the same input geometry object and bounds settings as Example 1, but sets clip_geom to true. The ST_AsMVTGeom() function clips the part outside 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