You can create geometry functions to construct geometric shapes in various formats, such as Well-Known Text (WKT) or Well-Known Binary (WKB). The current version of OceanBase AI Database supports the following geometry creation functions: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), ST_GeometryFromWKB(), and _ST_MakePoint.
ST_GeomFromText(): Creates a geometric shape from a WKT-formatted string.ST_GeometryFromText(): Similar toST_GeomFromText(), this function creates a geometric shape from a WKT-formatted string. Different database systems may provide functions with different names, but their functionality is similar.ST_GeomFromWKB(): Creates a geometric shape from binary data in WKB format.ST_GeometryFromWKB(): Similar toST_GeomFromWKB(), this function creates a geometric shape from WKB-encoded binary data._ST_MakePoint: Creates a new point geometry object.
ST_GeomFromText and ST_GeometryFromText
The ST_GeomFromText() and ST_GeometryFromText() functions take a Well-Known Text (WKT) representation and an optional Spatial Reference Identifier (SRID) as parameters, and return the corresponding geometric shape. For more information about the WKT format, see Spatial data formats.
The ST_GeomFromText() function accepts a WKT value of any geometry type as its first parameter. Other functions provide constructors for specific types to create geometry values of each type. The syntax of the function is as follows:
ST_GeomFromText(wkt [, srid [, options]]), ST_GeometryFromText(wkt [, srid [, options]])
For example:
obclient [test]> SET @geo = "MULTILINESTRING((10 10, 11 11), (9 9, 10 10))";
Query OK, 0 rows affected
obclient [test]> SELECT ST_AsText(ST_GeomFromText(@geo));
+--------------------------------------------+
| ST_AsText(ST_GeomFromText(@geo)) |
+--------------------------------------------+
| MULTILINESTRING((10 10,11 11),(9 9,10 10)) |
+--------------------------------------------+
1 row in set
ST_GeomFromWKB and ST_GeometryFromWKB
The ST_GeomFromWKB() and ST_GeometryFromWKB() functions take a BLOB parameter containing a Well-Known Binary (WKB) representation and an optional Spatial Reference Identifier (SRID), and return the corresponding geometric shape. For more information about the WKB format, see Spatial data formats.
The syntax of the function is as follows:
ST_GeomFromWKB(wkb [, srid [, options]]), ST_GeometryFromWKB(wkb [, srid [, options]])
_ST_MakePoint
_ST_MakePoint is used to create a point object. This function is typically used in applications that process map data or require precise representation of geographic locations.
The syntax is as follows:
_ST_MakePoint(x, y, [z])
Parameter description:
x: The X-coordinate value.y: The value of the Y coordinate.z: Optional. The Z-coordinate value.- If you do not enter
z, the output point is a two-dimensional (2D) point of the form(x, y). - If you enter
z, the output point is a three-dimensional (3D) point of the form(x, y, z).
- If you do not enter
Note
_ST_MakePointdoes not currently support creating four-dimensional (4D) points.- The
_ST_MakePointfunction accepts integer (int), floating-point (double), and string inputs, converting them into double-precision floating-point numbers (double) to create a point object.
Example:
Creates a 2D point.
obclient [test]> SELECT ST_ASTEXT(_st_makepoint(10, 20));The return result is as follows:
+----------------------------------+ | ST_ASTEXT(_st_makepoint(10, 20)) | +----------------------------------+ | POINT(10 20) | +----------------------------------+ 1 row in setCreates a 3D point.
obclient [test]> SELECT ST_ASTEXT(_st_makepoint(10, -20, 5));The return result is as follows:
+--------------------------------------+ | ST_ASTEXT(_st_makepoint(10, -20, 5)) | +--------------------------------------+ | POINT Z (10 -20 5) | +--------------------------------------+ 1 row in set
Considerations
The return value of any geometric parameter in a geometric value function is non-NULL, except in the following cases:
If any geometric parameter is
NULLor the syntax is incorrect, the return value isNULL.The optional
optionsparameter specifies the order in which the latitude and longitude of a geographic coordinate are parsed according to the spatial reference system defined by the geometry parameters.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 an invalid option is specified.If the
SRIDparameter references an undefined Spatial Reference System (SRS), theER_SRS_NOT_FOUNDerror occurs.For the geometric parameters of a geographic SRS, an error occurs if the longitude or latitude of any parameter exceeds its range (in degrees; if the SRS uses other units, the corresponding values in those units are used). The error message is as follows:
If the longitude value is not within the range (-180, 180], the
ER_LONGITUDE_OUT_OF_RANGEerror occurs.If the latitude value is not within the range [−90, 90], the
ER_LATITUDE_OUT_OF_RANGEerror occurs.