In Oracle-compatible mode of OceanBase Database, you can call four member functions of the SDO_GEOMETRY type to query the dimension, type, and validity information of an SDO_GEOMETRY spatial object. You can also call 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 dimensions of a spatial object, which is the number of coordinates in the geometry object. For example, a 2D object has 2 dimensions, and a 3D object has 3 dimensions.
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 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()
The SDO_AREA() function is used to calculate the planar area of a geometry object. The returned value is in the linear unit of the SRS in which the geometry is located, without spherical or ellipsoidal conversion. Only 2D planar geometry is supported; 3D input is not supported.
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 returned value is of the NUMBER type, representing the planar area of the geometry object, in the linear unit of the SRS in which the geometry 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()
This function determines whether one geometry strictly contains another, such as whether geomA strictly contains geomB (boundary points are not considered as contained). It is equivalent to SDO_RELATE(geomA, geomB, 'mask=CONTAINS'), with the logical equivalence: 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 for an empty input geometry). 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()
This function determines whether one geometry has any form of spatial intersection with another, such as intersection, contact, or overlap. Contact at the boundary is considered an 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 for an empty input geometry).
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()
The SDO_RELATE() function determines whether an object meets 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 in the form ofSDO_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 uses the idx index:
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |TABLE FULL SCAN|TEST_SPATIAL_INDEX(IDX)|1 |135 | |
| ====================================================================
SDO_UTIL package
GETVERTICES()
This function retrieves the vertex coordinates of a geometry, supporting both 2D and 3D geometries:
- For 2D geometries,
Z = NULL. - For 3D geometries, the actual
Zvalue is returned, andWis alwaysNULL.
Syntax:
TABLE(SDO_UTIL.GETVERTICES(geom))
- Use the
TABLE(...)form to expand the nested table when calling the function. geomspecifies the geometry.
The function returns a nested table of type VERTEX_SET_TYPE, with each row representing a vertex and containing X, Y, Z, W, and reserved columns:
# X, Y, Z, W are the coordinates of the vertex, and V1 to 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