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])

The parameters are described as follows:

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

• srid: the spatial reference identifier (SRID), which specifies the spatial reference system (SRS) used by 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