The Oracle-compatible mode of OceanBase Database provides some functions for the construction, query, calculation, and format conversion of spatial objects.
Function list
The following table describes the member functions of the SDO_GEOMETRY type.
| Function | Return type | Description |
|---|---|---|
| GET_DIMS() | NUMBER | Obtains the dimension information of a geometry object. It returns an integer that represents the number of dimensions of the geometry object, as defined in the SDO_GTYPE attribute. The value returned is the same as that returned by ST_COORDDIM(). |
| ST_COORDDIM() | NUMBER | Same as GET_DIMS(). |
| GET_GTYPE() | NUMBER | Obtains the type code of a geometry object. It returns an integer that represents the type of the geometry object. For example, 2001 represents a point, 2002 represents a line, and 2003 represents a polygon. |
| ST_ISVALID() | NUMBER | Tests the validity of a geometry object. It returns a Boolean value that indicates whether the geometry object is valid. If the geometry object is valid, TRUE is returned. If not, FALSE is returned. |
| GET_WKB() | BLOB | Obtains the well-known binary (WKB) representation of a geometry object. It returns a binary string that represents the geometry object in the WKB format, without the spatial reference identifier (SRID). |
| GET_WKT() | CLOB | Obtains the well-known text (WKT) representation of a geometry object. It returns a string that represents the geometry object in the WKT format, without the SRID. |
| GET_GEOJSON() | CLOB | Obtains the JSON representation of a geometry object. It returns a string that represents the geometry object in the JSON format. |
The following table describes the member functions of SDO_GEOMETRY.
| Function | Return type | Description |
|---|---|---|
| SDO_DISTANCE() | NUMBER | Calculates the distance between two geometry objects. It returns an integer that represents the distance between the geometry objects. |
| SDO_AREA() | NUMBER | Calculates the planar area of a geometry object. The returned value is expressed in the linear units of the SRS of the geometry. Spherical and ellipsoidal conversions are not performed. Only 2D planar geometries are supported. 3D inputs are not supported. |
The following table describes spatial relationship functions.
| Name | Returns | Description |
|---|---|---|
| SDO_CONTAINS() | BOOLEAN | Returns TRUE if one geometry object strictly contains another geometry object (boundaries are not considered as containing). This is equivalent to SDO_RELATE(geomA, geomB, 'mask=CONTAINS'). |
| SDO_ANYINTERACT() | BOOLEAN | Returns TRUE if two geometry objects have any spatial intersection, such as overlap, contact, or adjacency (edges are considered as intersecting). This is equivalent to SDO_RELATE(..., 'mask=ANYINTERACT'). |
| SDO_RELATE() | VARCHAR2 | Returns TRUE or FALSE to indicate whether two geometry objects satisfy the specified spatial relationship. |
The following table describes the functions in the SDO_UTIL package.
| Name | Returns | Description |
|---|---|---|
| GETVERTICES() | TABLE | Returns the vertex coordinates of a geometry object. Supports 2D and 3D. Returns a nested table of VERTEX_SET_TYPE, each row containing the X, Y, Z, W, and reserved columns of a vertex. |