Spatial accessor functions provide access to the components of spatial data, such as the vertices of a polygon or the starting point of a line.
The spatial accessor functions currently supported by OceanBase AI Database include _ST_GeometryType, _ST_IsCollection, and _ST_NumInteriorRings.
_ST_GeometryType
The _ST_GeometryType function returns the geometry type of a geometric object. The function returns the type of the input geometric object, which is a geometry type name that follows the SQL/MM (SQL Multimedia) standard, in the format ST_[TYPE]. For example, if the input geometric object is a point, the function returns ST_Point; if it is a line string, it returns ST_LINEString.
The syntax is as follows:
_ST_GeometryType(geometry g1);
Parameter description:
geometry g1: The input geometry object for the function. It can be any type of geometry object, such as a point (Point), line (LineString), polygon (Polygon), etc.
Limitations:
- The input value must be a valid geometric object. If the input is
NULLor a non-geometric data type, the function may return an error orNULL.
An example is as follows:
obclient> SELECT _ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
In this example, the _ST_GeometryType function checks the geometry object converted by the ST_GeomFromText function and returns the object's type.
The return result is as follows:
+--------------------------------------------------------------------------------------------------+
| _ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)')) |
+--------------------------------------------------------------------------------------------------+
| ST_LineString |
+--------------------------------------------------------------------------------------------------+
1 row in set
_ST_IsCollection
The _ST_IsCollection function determines whether the input geometry object is a geometric collection type. In Geographic Information Systems (GIS), a geometric collection type is a set composed of multiple geometric objects, which can be points, lines, polygons, and so on.
The syntax is as follows:
_ST_IsCollection(geometry g1);
Parameter description:
geometry g1: the geometry object to be checked, which must be a valid geometry object. The main types of geometry collections are:GEOMETRYCOLLECTION: a collection of geometric objects, where the objects in the collection can be of any type.MULTIPOINT: a collection of multiple point objects.MULTIPOLYGON: a collection of multiple polygon objects.MULTILINESTRING: a collection of multiple multi-line string objects.MULTICURVE: a collection of multiple curve objects (supported by some database systems).MULTISURFACE: a collection of multiple surface objects (supported by some database systems).COMPOUNDCURVE: a composite curve formed by combining multiple simple curves. However, this type may not be supported in OceanBase AI Database.
For example:
obclient> SELECT _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)'));
In this example, the input is an object of the LINESTRING type, which is a simple geometric type, not a collection composed of multiple geometric objects. After executing this SQL statement, the return result is 0.
The return result is as follows:
+-----------------------------------------------------------+
| _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)')) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
1 row in set
_ST_NumInteriorRings
The _ST_NumInteriorRings function returns the number of interior rings of a polygonal geometry object. An "interior ring" refers to the boundary line surrounding a "hole" or "island" within the polygon, where these holes are located inside the polygon's outer ring. If a polygon has no interior rings, it means it contains no holes.
The syntax is as follows:
_ST_NumInteriorRings(geometry a_polygon);
Parameter description:
geometry a_polygon: The input parameter, representing the polygon geometry object to be checked. If the input geometry object is not of the polygon type, the function returnsNULL.
Here is an example:
obclient> select _ST_NumInteriorRings(ST_GeomFromText('POLYGON((2 2 1,2 8 5,8 8 7,8 2 4,2 2 1))'));
In this example, the input polygon is defined in Well-Known Text (WKT) format and its coordinates are three-dimensional (including x, y, and z coordinates). Although a z-coordinate is provided, the specified polygon does not define an inner ring, so the return value is 0.
The return result is as follows:
+-----------------------------------------------------------------------------------+
| _ST_NumInteriorRings(ST_GeomFromText('POLYGON((2 2 1,2 8 5,8 8 7,8 2 4,2 2 1))')) |
+-----------------------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------------------+
1 row in set