SDO_GEOMETRY constructor functions

OceanBase Database in Oracle mode allows you to create spatial objects of the SDO_GEOMETRY type by using the default function SDO_GEOMETRY() from well-known text (WKT) or well-known binary (WKB) data.

Syntax of SDO_GEOMETRY()

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

Parameters

• wkb_value: a geometry object in the WKB format. The value is a binary string that describes the type and coordinate data of the geospatial object. For example, 0101000000000000000000F03F000000000000F03F is the WKB value of a Point object.

• wkt_value: a geometry object in the WKT format. The value is a text string that describes the type and coordinate data of the geospatial object. Here are some examples:

  • POINT(1.0 1.0) is a Point object.
  • LINESTRING(0 0, 1 1, 2 2) is a LineString object that consists of three points.
  • POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) is a Polygon object that represents a closed rectangle.
  • MULTILINESTRING((0 0, 1 1, 2 2), (3 3, 4 4, 5 5)) is a MultiLineString object that consists 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 a MultiPolygon object that consists of two polygons.
  • GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 1, 2 2)) is a Collection object that consists of a Point object and a LineString object.

• srid: the spatial reference identifier (SRID) of the geometry object. This parameter is optional. The default value is NULL. The value of srid is an integer.

Create a SDO_GEOMETRY object by using the default constructor function

Create a SDO_GEOMETRY object from WKB data

Create a SDO_GEOMETRY object by using the SDO_GEOMETRY() function from WKB data.

Here is an example:

Execute the following query statement to convert a binary string into a SDO_GEOMETRY object and name the object as TEST_SDO_GEOMETRY in the query result. In the query statement, TO_BLOB() converts the binary string 01010000000000000000000000000000000000F03F into BLOB data and SDO_GEOMETRY() creates a SDO_GEOMETRY object from the data.

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

The return result is as follows:

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

Create a SDO_GEOMETRY object from WKT data

Create a SDO_GEOMETRY object by using the SDO_GEOMETRY() function from WKT data.

Here is an example:

Execute the following query statement to create a SDO_GEOMETRY object named TEST_SDO_GEOMETRY and set it as a 2D Point object. The SDO_GEOMETRY() function creates a spatial object of the SDO_GEOMETRY type. POINT(-1e5 1e-3) is a WKT string that represents a 2D Point object, where -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 return result is as follows:

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