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 V4.3.3 supports the following functions that create geometry values: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), ST_GeometryFromWKB(), and _ST_MakePoint.

• ST_GeomFromText(): creates a geometry object from a WKT string.
• ST_GeometryFromText(): creates a geometry object from a WKT string, which is similar to ST_GeomFromText(). Functions with similar capabilities may be named differently in different database systems.
• ST_GeomFromWKB(): creates a geometry object from binary data in the WKB format.
• ST_GeometryFromWKB(): creates a geometry object from binary data in the WKB format, which is similar to ST_GeomFromWKB().
• _ST_MakePoint: creates a Point object.

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() functions take, as arguments, binary large objects (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]])

_ST_MakePoint

The _ST_MakePoint function creates a Point object. It is usually used to process map data or in applications where geographic locations need to be accurately represented.

The syntax is as follows:

_ST_MakePoint(x, y, [z])

The arguments are described as follows:

• x: the X coordinate.

• y: the Y coordinate.

• z: The Z coordinate. This argument is optional.

  • If z is not specified, the output Point object is a two-dimensional point represented as (x, y).
  • If z is specified, the output Point object is a three-dimensional point represented as (x, y, z).

Here are some examples:

• Create a two-dimensional Point object.

  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 set
  

• Create a three-dimensional Point object.

  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 for arguments

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. The options argument 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. srid-defined is the default value. If the options argument 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.