OceanBase Database in Oracle mode supports four member functions of the SDO_GEOMETRY type to query the dimension, type, and validity information of SDO_GEOMETRY spatial objects. It also supports the SDO_DISTANCE() function and other functions in the SDO_GEOM package to calculate the distance and relationship information of spatial objects.
SDO_GEOMETRY type
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:
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
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 that represents a specific type of geometric object, such as a point, line, or polygon.
Here is an example:
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.
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
SDO_GEOM package
SDO_DISTANCE()
The SDO_DISTANCE() function is used to calculate the minimum distance between two spatial geometric objects. For example, the minimum distance between the two-dimensional parameters geom1 and geom2.
SELECT SDO_GEOM.SDO_DISTANCE(SDO_GEOMETRY('POINT(0 0)', 4326), SDO_GEOMETRY('POINT(0.01 0)', 4326)) FROM dual;
The result is as follows:
+-----------------------------------------------------------------------------------------+
| SDO_GEOM.SDO_DISTANCE(SDO_GEOMETRY('POINT(00)', 4326),SDO_GEOMETRY('POINT(0.010)',4326)) |
+-----------------------------------------------------------------------------------------+
| 1113.1949077920626 |
+-----------------------------------------------------------------------------------------+
1 row in set
Other spatial functions
SDO_RELATE()
The SDO_RELATE() function is used to determine whether an object meets the specified spatial relationship.
Consider the following notes:
- Spatial relationship operations currently only support
ANYINTERACT. - If the parameters
geom1andgeom2are based on different coordinate systems, an error will be returned forgeom2. - When used for index queries,
SDO_RELATE()must be used in theWHEREclause with the function expressionSDO_RELATE(geometry1, geometry2, 'mask = <some_mask_val>') = 'TRUE', andgeometry1must be an indexed column.
Here is an example of an index query:
-- Create a test table named test_spatial_index.
CREATE TABLE test_spatial_index (fid INTEGER NOT NULL PRIMARY KEY, g SDO_GEOMETRY NOT NULL SRID 4326 );
-- Insert some test data.
INSERT INTO test_spatial_index VALUES(1, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10 50, null), null, null));
INSERT INTO test_spatial_index VALUES(2, SDO_GEOMETRY(2001, 4326, null, SDO_ELEM_INFO_ARRAY (1,1,1), SDO_ORDINATE_ARRAY (10,50)));
INSERT INTO test_spatial_index VALUES(3, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234,50.567,null), null, null));
INSERT INTO test_spatial_index VALUES(4, SDO_GEOMETRY(2001, 4326, null, SDO_ELEM_INFO_ARRAY (1,1,1), SDO_ORDINATE_ARRAY (10.1234,50.567)));
INSERT INTO test_spatial_index VALUES(5, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234,50.567,null), null, null));
-- Create an index named idx.
CREATE INDEX idx ON test_spatial_index(g) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Perform an index query.
SELECT * FROM test_spatial_index WHERE SDO_RELATE(g, SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234,50.567,null), null, null), 'querytype=WINDOW mask=anyinteract') = 'TRUE';
The result is as follows:
+------+-----------------------------------------------------------------------------+
| FID | G |
+------+-----------------------------------------------------------------------------+
| 3 | SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234, 50.567, NULL), NULL, NULL) |
| 4 | SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234, 50.567, NULL), NULL, NULL) |
| 5 | SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(10.1234, 50.567, NULL), NULL, NULL) |
+------+-----------------------------------------------------------------------------+
3 rows in set
The execution plan is as follows, using the idx index for access:
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |TABLE FULL SCAN|TEST_SPATIAL_INDEX(IDX)|1 |135 | |
| ====================================================================