Create a geometry value function

You can use the geometry value creation functions to create geometry values in various formats, such as Well-Known Text (WKT) or Well-Known Binary (WKB). The current OceanBase Database version supports the following geometry value creation functions: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), ST_GeometryFromWKB(), and _ST_MakePoint.

• ST_GeomFromText(): creates a geometry value from a string in WKT format.
• ST_GeometryFromText(): creates a geometry value from a string in WKT format. This function is functionally similar to ST_GeomFromText(). Different database systems may provide functions with different names, but they perform similar functions.
• ST_GeomFromWKB(): creates a geometry value from binary data in WKB format.
• ST_GeometryFromWKB(): creates a geometry value from binary data in WKB format. This function is functionally similar to ST_GeomFromWKB().
• _ST_MakePoint: creates a 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 system identifier (SRID) as parameters and return the corresponding geometry value. For more information about the WKT format, see Spatial data format.

The ST_GeomFromText() function accepts WKT values of any geometry type as its first parameter. Other functions provide specific constructors for each geometry type. The syntax of the functions is as follows:

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

Here are some examples:

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 containing a Well-Known Binary (WKB) representation and an optional spatial reference system identifier (SRID) as parameters and return the corresponding geometry value. For more information about the WKB format, see Spatial data format.

The syntax of the functions is as follows:

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

_ST_MakePoint

The _ST_MakePoint function creates a point object. This function is commonly used when handling map data or accurately representing geographic locations.

The syntax is as follows:

_ST_MakePoint(x, y, [z])

The parameters are described as follows:

• x: the X coordinate value.

• y: the Y coordinate value.

• z: the Z coordinate value, which is optional.

  • If you do not specify z, the output is a two-dimensional (2D) point with coordinates (x, y).
  • If you specify z, the output is a three-dimensional (3D) point with coordinates (x, y, z).

Here are some examples:

• Create a 2D point.

  obclient [test]> SELECT ST_ASTEXT(_st_makepoint(10, 20));
  

  The output is as follows:

  +----------------------------------+
  | ST_ASTEXT(_st_makepoint(10, 20)) |
  +----------------------------------+
  | POINT(10 20)                     |
  +----------------------------------+
  1 row in set
  

• Create a 3D point.

  obclient [test]> SELECT ST_ASTEXT(_st_makepoint(10, -20, 5));
  

  The output is as follows:

  +--------------------------------------+
  | ST_ASTEXT(_st_makepoint(10, -20, 5)) |
  +--------------------------------------+
  | POINT Z (10 -20 5)                   |
  +--------------------------------------+
  1 row in set
  

Considerations

The return value of a geometry parameter in the geometry value creation functions is not NULL in the following cases:

• If any geometry parameter is NULL or has an incorrect syntax, the return value is NULL.

• The optional options parameter specifies the order in which latitude and longitude values of geographic coordinates are parsed based on the spatial reference system of the geometry parameter. The options parameter is a comma-separated list of key=value pairs. The only valid key value is axis-order, which can be lat-long, long-lat, or srid-defined (default). If the options parameter is NULL, the return value is NULL. If the options parameter is invalid, an error is returned.

• If the SRID parameter refers to an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error is returned.

• For geometry parameters in a geographic SRS, if the longitude or latitude value of any parameter is out of range (in degrees, or in the corresponding unit of the SRS if the SRS uses a different unit), an error is returned:

  • If the longitude value is not in the range (-180, 180], an ER_LONGITUDE_OUT_OF_RANGE error is returned.

  • If the latitude value is not in the range [−90, 90], an ER_LATITUDE_OUT_OF_RANGE error is returned.