The Oracle mode of OceanBase Database allows you to use the four member functions of SDO_GEOMETRY to query the dimension, type, and validity information of SDO_GEOMETRY objects, and use the SDO_DISTANCE() function in the SDO_GEOM package in combination with the SDO_RELATE() function to calculate the distance and relationship between spatial objects.
SDO_GEOMETRY type
GET_DIMS() and ST_COORDDIM()
GET_DIMS() and ST_COORDDIM() obtain the dimension information of a spatial object, namely, 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:
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
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:
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.
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
SDO_GEOM package
SDO_DISTANCE()
SDO_DISTANCE() calculates the shortest distance between two geometry objects. For example, you can call the function to calculate the shortest distance between two 2D objects named geom1 and geom2.
SELECT SDO_GEOM.SDO_DISTANCE(SDO_GEOMETRY('POINT(0 0)', 4326), SDO_GEOMETRY('POINT(0.01 0)', 4326)) FROM dual;
The return 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()
SDO_RELATE() tests whether two geometry objects are of the specified spatial relationship.
Take note of the following considerations:
- At present, only the
ANYINTERACTspatial relationship operation is supported. - If
geom1andgeom2are based on different coordinate systems, an error is returned forgeom2. - To use the
SDO_RELATE()function for an index-based query, you must use theSDO_RELATE(geometry1, geometry2, 'mask = <some_mask_val>') = 'TRUE'function expression in theWHEREclause andgeometry1must be an indexed column.
Here is an example:
-- 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 several test data records into the table.
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 a spatial index named `idx`.
CREATE INDEX idx ON test_spatial_index(g) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Perform an index-based 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 return 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, indicating that the idx index is used for data access.
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |TABLE FULL SCAN|TEST_SPATIAL_INDEX(IDX)|1 |135 | |
| ====================================================================