SDO_GEOMETRY query functions

OceanBase Database in Oracle mode supports the following member functions of SDO_GEOMETRY that allow you to query the dimension, type, and validity information of SDO_GEOMETRY objects: GET_DIMS(), ST_COORDDIM(), GET_GTYPE(), and ST_ISVALID().

GET_DIMS() and ST_COORDDIM()

GET_DIMS() and ST_COORDDIM() obtain the dimension information of a spatial object, that is, the number of dimensions of a geometry object. For example, for a 2D object, the number of dimensions is 2; for a 3D object, the number of dimensions is 3.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(10,500)).GET_DIMS() AS test_get_dims FROM dual;

The return result is as follows:

+---------------+
| TEST_GET_DIMS |
+---------------+
|             2 |
+---------------+
1 row in set

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(10,500)).ST_COORDDIM() AS test_st_coorddim FROM dual;

The return result is as follows:

+------------------+
| TEST_ST_COORDDIM |
+------------------+
|                2 |
+------------------+
1 row in set

GET_GTYPE()

GET_GTYPE() obtains the geometry type code of a spatial object. A geometry type code is an integer that indicates the type of a geometry object, such as a point, line, or polygon.

Here is an example:

obclient [SYS]> SELECT SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(10,500)).GET_GTYPE() AS test_get_gtype FROM dual;

The return result is as follows:

+----------------+
| TEST_GET_GTYPE |
+----------------+
|              1 |
+----------------+
1 row in set

ST_ISVALID()

ST_ISVALID() tests whether a spatial object is a valid geometry. If the object is a valid geometry, the function returns 1. If not, the function returns 0.

obclient [SYS]> SELECT SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1,1), SDO_ORDINATE_ARRAY(10,500)).ST_ISVALID() AS test_st_isvalid FROM dual;

The return result is as follows:

+-----------------+
| TEST_ST_ISVALID |
+-----------------+
|               1 |
+-----------------+
1 row in set