Functions that create geometry values

You can use functions in this topic to create a geometry object from a well-known text (WKT) representation or a well-known binary (WKB) representation of an input geometry. OceanBase Database supports the following functions that create geometry values in the current version: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), and ST_GeometryFromWKB().

ST_GeomFromText and ST_GeometryFromText

The ST_GeomFromText() and ST_GeometryFromText() functions take the WKT representation and optional spatial reference system identifiers (SRIDs) as arguments and return the corresponding geometry 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 geometry values for each geometry type. The syntaxes of the functions are as follows:

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

Here is an 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() take, as arguments, BLOBs that contain WKB representations and optional SRIDs, and return the corresponding geometry 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]])

Considerations for parameters

The return value for a geometry argument in a function that creates geometry values is not NULL, except in the following cases:

• If any geometry 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 geometry 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.