Create a geometric function

You can create a geometric function to create geometric objects in various formats such as well-known text (WKT) and well-known binary (WKB). OceanBase Database supports the following geometric functions in the current version: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), and ST_GeometryCollectionFromWKB().

ST_GeomFromText and ST_GeometryFromText

The ST_GeomFromText() and ST_GeometryFromText() functions take WKT representations and optional spatial reference system identifiers (SRIDs) as arguments and return the corresponding geometric objects. 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 argument. Other functions provide type-specific constructors for constructing geometric values for each geometry type. The syntaxes of the functions are as follows:

ST_GeomFromText(wkt [, srid [, options]]), ST_GeometryFromText(wkt [, srid [, options]])

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_GeometryCollectionFromWKB

The SST_GeomFromWKB() and ST_GeometryCollectionFromWKB() functions take, as arguments, BLOBs that contains WKB representations and optional SRIDs, and return the corresponding geometric objects. For more information about the WKB format, see Spatial data formats.

The syntaxes of the functions are as follows:

ST_GeomFromWKB(wkb [, srid [, options]]), ST_GeometryFromWKB(wkb [, srid [, options]])

Notes

The return value for a geometric argument in a created geometric function is non-NULL, except in the following cases:

• If any geometric argument is NULL or is in an incorrect syntactic format, the return value is NULL.

• The optional options argument enables the latitude and longitude of the geographic coordinates to be resolved in the order specified by the spatial reference system (SRS) of the geometric argument. options consists of a comma-separated list of key=value pairs. key can only be set to axis-order, and value can be lat-long, long-lat, or srid-defined (default). If options is NULL, the return value is NULL. If the options argument is invalid, an error is reported.

• If the SRID argument references an undefined SRS, the ER_SRS_NOT_FOUND error occurs.

• For geographic SRS geometry arguments, if the longitude or latitude of any argument exceeds the valid range in degrees or in other units used by the SRS), an error is reported.

  • If the longitude value is not within the range of (-180, 180], the ER_LONGITUDE_OUT_OF_RANGE error occurs.

  • If the latitude value is not within the range of [−90, 90], the ER_LATITUDE_OUT_OF_RANGE error occurs.