OceanBase Database in Oracle mode provides four member functions of the SDO_GEOMETRY type to query the dimension, type, and validity information of SDO_GEOMETRY spatial objects. It also provides the SDO_DISTANCE() function in the SDO_GEOM package and the SDO_RELATE() function 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 get the dimension of a spatial object, which is the number of coordinates of the geometry object. For example, the dimension of a 2D object is 2, and the dimension of a 3D 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 get the geometry type code of a spatial object. The geometry type code is an integer value that represents a specific type of 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 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 geometry 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 geometry objects. For example, the minimum distance between the 2D parameter geom1 and the 2D parameter 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
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 in which the geometry object is located. It does not perform spherical or ellipsoidal conversion. Only 2D planar geometry is supported, and 3D input is not supported.
The syntax is as follows:
SDO_GEOM.SDO_AREA(geom[, tolerance])
geomspecifies the geometry object.toleranceis an optional parameter. The default value isNULL. It specifies the tolerance for area calculation, which is used to control the calculation precision.
The return value is of the NUMBER type, representing the planar area of the geometry object, with the unit consistent with the linear unit of the SRS in which 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 judgment functions
SDO_CONTAINS()
Note
This function is supported starting from V4.3.5 BP5.
This function determines whether one geometry strictly contains another geometry, for example, 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 (if the input geometry is empty). Note that SDO_CONTAINS indicates strict containment, and boundary points return FALSE.
Here is an 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 return 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 determines whether one geometry has any form of spatial intersection with another geometry, 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 (if the input geometry is empty).
Here is an 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 return result is as follows:
+------+
|| RES |
+------+
|| TRUE |
+------+
1 row in set
SDO_RELATE()
The SDO_RELATE() function determines whether the objects meet the 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 in the form ofSDO_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 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, 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 obtain the vertex coordinates of a geometric object, supporting 2D/3D:
- For 2D geometry,
ZisNULL. - For 3D geometry, the actual
Zvalue is returned, andWis alwaysNULL.
Syntax:
TABLE(SDO_UTIL.GETVERTICES(geom))
- When calling, use the
TABLE(...)form to expand the nested table. geomrepresents the geometric object.
It returns a nested table VERTEX_SET_TYPE, with each row representing a vertex, containing X/Y/Z/W and reserved columns:
# X,Y,Z,W are the vertex coordinates, V1..V11 are reserved columns for future expansion
# ID is the vertex ID, starting from 1 and incrementing
X,Y,Z,W,V1..V11,ID
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