Geometry format conversion functions are used to convert geometric values from internal formats to WKT or WKB formats, or to swap the X and Y coordinate order. The current version of OceanBase AI 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 the value of an internal geometry type to its WKB representation and return the binary result. The syntax is as follows:
ST_AsBinary(g [, options]), ST_AsWKB(g [, options])
The function return value is a geographic coordinate (latitude, longitude), and its order is specified by the spatial reference system applied to the geometric parameters. Specifying the optional options parameter can override the default axis order.
SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@geo, 'axis-order=long-lat')));
ST_AsText and ST_AsWKT
The ST_AsText() and ST_AsWKT() functions convert the value of an internal geometry type to its WKT representation and return a string result. The syntax is as follows:
ST_AsText(g [, options]), ST_AsWKT(g [, options])
The function return value consists of geographic coordinates (latitude and longitude), and their order is specified by the spatial reference system applied to the geometric parameters. You can override the default axis order by specifying the optional options parameter.
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 return value of a geometric parameter in a geometry conversion function is never NULL, except in the following cases:
- If the syntax of any geometric parameter is incorrect, the
ER_GIS_INVALID_DATAerror occurs. - If any geometry parameters are located in an undefined spatial reference system, the axes will be output in the order they appear in the geometry, and the warning
ER_WARN_SRS_NOT_FOUND_AXIS_ORDERwill be issued. - By default, geographic coordinates (latitude and longitude) are parsed in the order specified by the spatial reference system's geometric parameters. You can override the default axis order by specifying the optional
optionsparameter.optionsis a comma-separated list ofkey=valuepairs. The only validkeyvalue isaxis-order, which can be set tolat-long,long-lat, orsrid-defined(the default). If theoptionsparameter isNULL, the return value isNULL. An error is returned if the option parameter is invalid.