SDO_GEOMETRY query functions

OceanBase Database in Oracle mode supports the GET_DIMS(), ST_COORDDIM(), GET_GTYPE(), and ST_ISVALID() member functions of the SDO_GEOMETRY type to query the dimension, type, and validity of an SDO_GEOMETRY spatial object.

GET_DIMS() and ST_COORDDIM()

The GET_DIMS() and ST_COORDDIM() functions are used to obtain the dimension of a spatial object, which is the number of coordinates of the geometric object. For example, the dimension of a two-dimensional object is 2, and the dimension of a three-dimensional object 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 result is as follows:

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

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 result is as follows:

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

GET_GTYPE()

The GET_GTYPE() function is used to obtain the geometric type code of a spatial object. The geometric type code is an integer value that represents a specific type of geometric 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 result is as follows:

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

ST_ISVALID()

The ST_ISVALID() function is used to check whether a spatial object is a valid geometric object. If the object is valid, the function returns 1; otherwise, it 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 result is as follows:

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