Access SDO_GEOMETRY properties

SDO_GEOMETRY is a complex data type that has multiple intrinsic properties that can be accessed in PL/SQL.

SDO_GEOMETRY.SDO_GTYPE

SDO_GEOMETRY.SDO_GTYPE specifies the geometry type code of the geometry object.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(10, 50, NULL), NULL, NULL).SDO_GTYPE AS test_sdo_gtype FROM dual;

The result is as follows:

+----------------+
| TEST_SDO_GTYPE |
+----------------+
|           2001 |
+----------------+
1 row in set

SDO_GEOMETRY.SDO_SRID

SDO_GEOMETRY.SDO_SRID specifies the identifier (SRID) of the coordinate system of the geometry object.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL).SDO_SRID AS test_sdo_srid FROM dual;

The result is as follows:

+---------------+
| TEST_SDO_SRID |
+---------------+
|          4326 |
+---------------+
1 row in set

SDO_GEOMETRY.SDO_POINT

SDO_GEOMETRY.SDO_POINT specifies the point coordinates of the geometry object, including X, Y, and optional Z coordinates. You can also access the SDO_POINT subproperty of SDO_GEOMETRY to retrieve the values of these subproperties.

The SDO_POINT subproperties are as follows:

• SDO_GEOMETRY.SDO_POINT.X: specifies the X coordinate of the geometry object.
• SDO_GEOMETRY.SDO_POINT.Y: specifies the Y coordinate of the geometry object.
• SDO_GEOMETRY.SDO_POINT.Z: specifies the optional Z coordinate of the geometry object. The Z coordinate is optional, and not all geometry objects contain Z values.

Here are some examples:

• Access the SDO_POINT property, which contains X, Y, and Z coordinates.

  obclient [SYS]> SELECT SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL).SDO_POINT AS test_sdo_point FROM dual;
  

  The result is as follows:

  +------------------------------+
  | TEST_SDO_POINT               |
  +------------------------------+
  | SDO_POINT_TYPE(12, 14, NULL) |
  +------------------------------+
  1 row in set
  

• Access the X attribute of SDO_POINT.

  obclient [SYS]> SELECT SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL).SDO_POINT.X AS test_sdo_point_x FROM dual;
  

  The result is as follows:

  +------------------+
  | TEST_SDO_POINT_X |
  +------------------+
  |               12 |
  +------------------+
  1 row in set
  

• Access the Y attribute of SDO_POINT.

  obclient [SYS]> SELECT SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL).SDO_POINT.Y AS test_sdo_point_y FROM dual;
  

  The result is as follows:

  +------------------+
  | TEST_SDO_POINT_Y |
  +------------------+
  |               14 |
  +------------------+
  1 row in set
  

• Access the Z attribute of SDO_POINT.

  obclient [SYS]> SELECT SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(12, 14, NULL), NULL, NULL).SDO_POINT.Z AS test_sdo_point_z FROM dual;
  

  The result is as follows:

  +------------------+
  | TEST_SDO_POINT_Z |
  +------------------+
  |             NULL |
  +------------------+
  1 row in set
  

SDO_GEOMETRY.SDO_ELEM_INFO

SDO_GEOMETRY.SDO_ELEM_INFO specifies the element information of the geometry object, such as the start and end point indices of a line segment or polygon.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105)).SDO_ELEM_INFO AS test_sdo_elem_info FROM dual;

The result is as follows:

+---------------------------------+
| TEST_SDO_ELEM_INFO              |
+---------------------------------+
| SDO_ELEM_INFO_ARRAY(1, 1003, 1) |
+---------------------------------+
1 row in set

SDO_GEOMETRY.SDO_ORDINATES

SDO_GEOMETRY.SDO_ORDINATES specifies the array of coordinate values of the geometry object.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105)).SDO_ORDINATES AS test_sdo_ordinates FROM dual;

The result is as follows:

+-----------------------------------------------------------------+
| TEST_SDO_ORDINATES                                              |
+-----------------------------------------------------------------+
| SDO_ORDINATE_ARRAY(10, 105, 15, 105, 20, 110, 10, 110, 10, 105) |
+-----------------------------------------------------------------+
1 row in set