Geometry format conversion functions

Geometry format conversion functions are used to convert geometry values from internal geometry format to WKT or WKB format, or to swap the order of X and Y coordinates. The current version of OceanBase Database supports the following geometry format conversion functions: ST_AsBinary(), ST_AsWKB, ST_AsText(), and ST_AsWKT().

ST_AsBinary and ST_AsWKB

ST_AsBinary() and ST_AsWKB() convert values in internal geometry format to their WKB representation and return the binary result. The syntax is as follows:

ST_AsBinary(g [, options]), ST_AsWKB(g [, options])

The returned value contains geographic coordinates (latitude and longitude), whose order is specified by the spatial reference system applied to the geometry parameter. You can specify the optional options parameter to override the default axis order.

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@geo, 'axis-order=long-lat')));

ST_AsText and ST_AsWKT

ST_AsText() and ST_AsWKT() convert values in internal geometry format to their WKT representation and return the string result. The syntax is as follows:

ST_AsText(g [, options]), ST_AsWKT(g [, options])

The returned value contains geographic coordinates (latitude and longitude), whose order is specified by the spatial reference system applied to the geometry parameter. You can specify the optional options parameter to override the default axis order.

obclient [test]> SET @geo = 'LINESTRING(0 6,6 12,12 18)';
Query OK, 0 rows affected

obclient [test]>  SELECT ST_AsText(ST_GeomFromText(@geo));
+----------------------------------+
| ST_AsText(ST_GeomFromText(@geo)) |
+----------------------------------+
| LINESTRING(0 6,6 12,12 18)       |
+----------------------------------+
1 row in set

Considerations

The returned value of the geometry parameter is not NULL in geometry format conversion functions, except in the following cases:

• If the syntax of any geometry parameter is incorrect, an error ER_GIS_INVALID_DATA is returned.
• If any geometry parameter is in an undefined spatial reference system, the axes will be output in the order they appear in the geometry, and an alert ER_WARN_SRS_NOT_FOUND_AXIS_ORDER will be generated.
• By default, geographic coordinates (latitude and longitude) are parsed in the order specified by the spatial reference system of the geometry parameter. You can specify the optional options parameter to override the default axis order. options is a comma-separated list of key=value pairs. The key can only be axis-order, and the allowed values are lat-long, long-lat, and srid-defined (default). If the options parameter is NULL, the returned value is NULL. If the options parameter is invalid, an error is returned.