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]])
The parameters are described as follows:
g: 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_digits: the precision of the output GeoJSON string, namely, the maximum number of decimal places of the coordinates. This parameter is optional.- If this parameter is not specified, the default value
2<sup>32</sup> - 1is used. - The minimum value is
0.
- If this parameter is not specified, the default value
options: a three-bit switch that specifies whether to include the bounding box and coordinate reference system (CRS) information in the JSON output.0: specifies not to include the bounding box and CRS information in the output.1: specifies to include the bounding box in the output.2: specifies to include the CRS information in the short format, namely,EPSG:srid, in the output.4: specifies to include the CRS information in the long format, namely,urn:ogc:def:crs:EPSG::srid, in the output. If both the long format and short format are enabled, the long format prevails.
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 CRS information.
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 has the same effect as the value 4, namely 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 has the same effect as the value 5, namely 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);
The parameters are described as follows:
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 name: 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 extent: 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_name: the name of the spatial column in the table. This parameter is optional.text feature_id_name: 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 determines whether to clip the geometry object 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 that is 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);
The parameters are described as follows:
geom: the input geometry object to be converted, which must be valid.bounds: 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=4096: 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=256: 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=true: a Boolean value that specifies 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 specifies to clip the part beyond tile bounds.
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, to specify not to clip the part outside the area defined by bounds. 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 the area defined by 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