Create a geometry function

Create a geometry function to create a geometry in various formats, such as Well-Known Text (WKT) or Well-Known Binary (WKB). The current version of seekdb supports the following functions for creating geometry values: ST_GeomFromText(), ST_GeometryFromText(), ST_GeomFromWKB(), ST_GeometryFromWKB(), and _ST_MakePoint.

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

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

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

Here is an example:

SET @geo = "MULTILINESTRING((10 10, 11 11), (9 9, 10 10))";
Query OK, 0 rows affected (0.000 sec)

SELECT ST_AsText(ST_GeomFromText(@geo));
+--------------------------------------------+
| ST_AsText(ST_GeomFromText(@geo))           |
+--------------------------------------------+
| MULTILINESTRING((10 10,11 11),(9 9,10 10)) |
+--------------------------------------------+
1 row in set (0.001 sec)

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. For more information about the WKB format, see Spatial data format.

The syntax of the function is as follows:

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

_ST_MakePoint

The _ST_MakePoint function is used to create a point object. This function is typically used when handling map data or when accurately representing geographical locations is required.

The syntax is as follows:

_ST_MakePoint(x, y, [z])

Here is the parameter description:

• x: The X coordinate value.

• y: The Y coordinate value.

• z: Optional. The Z coordinate value.

  • If z is not provided, the output is a 2D point with coordinates (x, y).
  • If z is provided, the output is a 3D point with coordinates (x, y, z).

info

• _ST_MakePoint does not support creating 4D points.

• The _ST_MakePoint function accepts integer (int), floating-point (double), and string inputs, and converts them to double-precision floating-point numbers (double) to create a point object.

Here are some examples:

• Create a 2D point.

  SELECT ST_ASTEXT(_st_makepoint(10, 20));

  The result is as follows:

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

• Create a 3D point.

  SELECT ST_ASTEXT(_st_makepoint(10, -20, 5));

  The result is as follows:

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

Considerations for parameters

The return value of a geometry parameter in a geometry value creation function is not NULL unless the following conditions are met:

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

• The optional options parameter can specify the order of latitude and longitude for geographic coordinates 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 is axis-order, which can take the values 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 occurs.

• If the SRID parameter references an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.

• For geographic SRS geometry parameters, if the longitude or latitude of any parameter is out of range (in degrees, or in the corresponding units if the SRS uses other units), an error occurs:

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

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