SDO_GEOMETRY constructor functions

OceanBase Database in Oracle mode supports the creation of SDO_GEOMETRY objects by using the default constructor (SDO_GEOMETRY()), Well-Known-Binary (WKB) data format, or Well-Known-Text (WKT) data format.

Syntax of SDO_GEOMETRY constructor functions

SDO_GEOMETRY({to_blob('wkb_value') | 'wkt_value'} [, srid])

Parameters:

• wkb_value: the geometry object in Well-Known-Binary (WKB) data format. It is a binary-encoded string that describes the type and coordinate data of a spatial object. For example, 0101000000000000000000F03F000000000000F03F is the WKB value of a point object.

• wkt_value: the geometry object in Well-Known-Text (WKT) data format. It is a text string that describes the type and coordinate data of a spatial object. For example:

  • POINT(1.0 1.0) is the WKT value of a point object.
  • LINESTRING(0 0, 1 1, 2 2) is the WKT value of a line segment composed of three points.
  • POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) is the WKT value of a closed rectangle.
  • MULTILINESTRING((0 0, 1 1, 2 2), (3 3, 4 4, 5 5)) is the WKT value of a multilinestring object composed of two line segments.
  • MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2))) is the WKT value of a multipolygon object composed of two polygons.
  • GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 1, 2 2)) is the WKT value of a geometry collection that contains a point object and a line segment object.

• srid: optional. The default value is NULL. It specifies the spatial reference identifier (Spatial Reference Identifier), which indicates the spatial reference system used by the geometry object. The srid parameter is an integer.

Create an SDO_GEOMETRY object by using the default constructor

Create an SDO_GEOMETRY object by using the WKB data format

Create an SDO_GEOMETRY object by using the SDO_GEOMETRY() constructor and the WKB data format.

Here is an example:

The following query statement converts a binary string to a spatial object of the SDO_GEOMETRY type and names the object TEST_SDO_GEOMETRY in the query result. In this query statement, the TO_BLOB() function converts the binary string 01010000000000000000000000000000000000F03F to a BLOB value, and the SDO_GEOMETRY() constructor creates a spatial object of the SDO_GEOMETRY type.

obclient [SYS]> SELECT SDO_GEOMETRY(to_blob('01010000000000000000000000000000000000F03F'), null) AS TEST_SDO_GEOMETRY FROM dual;

The query result is as follows:

+------------------------------------------------------------------+
| TEST_SDO_GEOMETRY                                                |
+------------------------------------------------------------------+
| SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(0, 1, NULL), NULL, NULL) |
+------------------------------------------------------------------+
1 row in set

Create an SDO_GEOMETRY object by using the WKT data format

Create an SDO_GEOMETRY object by using the SDO_GEOMETRY() constructor and the WKT data format.

Here is an example:

The following query statement creates a spatial object of the SDO_GEOMETRY type named TEST_SDO_GEOMETRY and sets its value to a two-dimensional point object. The SDO_GEOMETRY() constructor creates a spatial object of the SDO_GEOMETRY type. The POINT(-1e5 1e-3) string specifies the WKT value of a two-dimensional point object. In this string, -1e5 indicates that the X coordinate of the point is -100000, and 1e-3 indicates that the Y coordinate of the point is 0.001.

obclient [SYS]> SELECT SDO_GEOMETRY('POINT(-1e5 1e-3)', null) AS TEST_SDO_GEOMETRY FROM dual;

The query result is as follows:

+----------------------------------------------------------------------------+
| TEST_SDO_GEOMETRY                                                          |
+----------------------------------------------------------------------------+
| SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-100000, 0.001, NULL), NULL, NULL) |
+----------------------------------------------------------------------------+
1 row in set