The Oracle-compatible 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
SDO_AREA()
Note
This function is supported starting from V4.3.5 BP5.
The SDO_AREA() function is used to calculate the planar area of a geometry object. The unit of the return value is consistent with the linear unit of the SRS where the geometry object is located. It does not perform spherical or ellipsoidal conversion. It supports only 2D planar geometry, and does not support 3D input.
The syntax is as follows:
SDO_GEOM.SDO_AREA(geom[, tolerance])
geomspecifies the geometry object.toleranceis an optional parameter with a default value ofNULL. It specifies the tolerance for area calculation, which is used to control the calculation precision.
The return value is of the NUMBER type, indicating the planar area of the geometry object, with the unit consistent with the linear unit of the SRS where the geometry object is located.
Here is an example:
SELECT SDO_GEOM.SDO_AREA(
SDO_GEOMETRY('POLYGON((0 0, 3 0, 3 4, 0 4, 0 0))'),0.005)
) AS area FROM dual;
The result is as follows:
+------+
|| AREA |
+------+
|| 12 |
+------+
1 row in set
Spatial relationship functions
SDO_CONTAINS()
Note
This function is supported starting from V4.3.5 BP5.
This function is used to determine whether one geometry strictly contains another geometry, such as whether geomA strictly contains geomB (boundary points are not considered as contained). It is equivalent to SDO_RELATE(geomA, geomB, 'mask=CONTAINS'), and the logical equivalence is: Contains(A, B) ⇔ Within(B, A).
Syntax:
SDO_CONTAINS(geomA, geomB)
geomAspecifies geometry A.geomBspecifies geometry B.
The return value is a BOOLEAN value: TRUE, FALSE, or NULL (a NULL value is returned if the input geometry is empty). Note that SDO_CONTAINS indicates strict containment, and boundary points return FALSE.
Example:
SELECT SDO_CONTAINS(
SDO_GEOMETRY('POLYGON((0 0,10 0,10 10,0 10,0 0))', 4326),
SDO_GEOMETRY('POINT(5 5)', 4326)
) AS res FROM dual;
The result is as follows:
+------+
|| RES |
+------+
|| TRUE |
+------+
1 row in set
SDO_ANYINTERACT()
Note
This function is supported starting from V4.3.5 BP5.
This function is used to determine whether there is any form of spatial intersection between two geometries, such as intersection, contact, or overlap. Contact at the boundary is considered as interaction. It is equivalent to SDO_RELATE(..., 'mask=ANYINTERACT').
Syntax:
SDO_ANYINTERACT(geomA, geomB)
geomAspecifies geometry A.geomBspecifies geometry B.
The return value is a BOOLEAN value: TRUE, FALSE, or NULL (a NULL value is returned if the input geometry is empty).
Example:
SELECT SDO_ANYINTERACT(
SDO_GEOMETRY('LINESTRING(0 0,10 10)', 4326),
SDO_GEOMETRY('LINESTRING(0 10,10 0)', 4326)
) AS res FROM dual;
The result is as follows:
+------+
|| RES |
+------+
|| TRUE |
+------+
1 row in set
SDO_RELATE()
SDO_RELATE() is used to determine whether an object satisfies a specified spatial relationship.
Considerations:
- 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 a function expression of the formSDO_RELATE(geometry1, geometry2, 'mask = <some_mask_val>') = 'TRUE', andgeometry1must be an indexed column.
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 a spatial index named idx
CREATE INDEX idx ON test_spatial_index(g) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- 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 | |
| ====================================================================
SDO_UTIL package
GETVERTICES()
Note
This function is supported starting from V4.3.5 BP5.
This function is used to retrieve the vertex coordinates of a geometry object, supporting both 2D and 3D geometries:
- For 2D geometries,
ZisNULL. - For 3D geometries, the actual
Zvalue is returned, andWis alwaysNULL.
The syntax is as follows:
TABLE(SDO_UTIL.GETVERTICES(geom))
- When calling the function, use the
TABLE(...)form to expand the nested table. geomrepresents the geometry object.
The function returns a nested table of type VERTEX_SET_TYPE, with each row representing a vertex, containing X, Y, Z, W, and reserved columns:
# X,Y,Z,W represent the vertex coordinates. V1..V11 are reserved columns for future expansion.
# ID represents the vertex ID, starting from 1 and incrementing.
X,Y,Z,W,V1..V11,ID
Here is an example:
SELECT v.id, v.x, v.y, v.z
FROM TABLE(
SDO_UTIL.GETVERTICES(
SDO_GEOMETRY(
3002, -- 3002 = 3D LineString
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
SDO_ORDINATE_ARRAY(
10, 10, 5, -- First point: X=10, Y=10, Z=5
20, 20, 6, -- Second point
30, 10, 7 -- Third point
)
)
)
) v;
The result is as follows:
+------+------+------+------+
|| ID | X | Y | Z |
+------+------+------+------+
|| 1 | 10 | 10 | 5 |
|| 2 | 20 | 20 | 6 |
|| 3 | 30 | 10 | 7 |
+------+------+------+------+
3 rows in set