Spatial accessor functions

Spatial accessor functions provide methods to access components of spatial data, such as accessing the vertices of a polygon or the start points of a line.

OceanBase Database currently supports the following spatial accessor functions: _ST_GeometryType, _ST_IsCollection, and _ST_NumInteriorRings.

_ST_GeometryType

The _ST_GeometryType function returns the geometry type of a geometry object. The returned type is a geometry type name that follows the SQL-MM (SQL Multimedia) standard, in the ST_[TYPE] format. For example, if the input geometry object is a point, the function returns ST_Point; if it is a line string, the function returns ST_LineString.

Syntax:

_ST_GeometryType(geometry g1);

Parameters:

• geometry g1: The input geometry object. It can be any type of geometry object, such as a point (Point), line (LineString), or polygon (Polygon).

Limitations:

• The input value must be a valid geometry object. If the input is NULL or a non-geometry type, the function may return an error or NULL.

Example:

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 type of the object.

The 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 geometry collection type. In a geographic information system (GIS), a geometry collection type is a set of geometry objects, such as points, lines, and polygons.

Syntax:

_ST_IsCollection(geometry g1);

Parameters:

• geometry g1: The geometry object to be checked, which must be a valid geometry object. Geometry collection types include:

  • GEOMETRYCOLLECTION: A collection of geometry objects, which can be of any type.
  • MULTIPOINT: A collection of point objects.
  • MULTIPOLYGON: A collection of polygon objects.
  • MULTILINESTRING: A collection of line string objects.
  • MULTICURVE: A collection of curve objects (supported by some database systems).
  • MULTISURFACE: A collection of surface objects (supported by some database systems).
  • COMPOUNDCURVE: A composite curve composed of multiple simple curves. However, this type may not be supported in OceanBase Database.

Example:

obclient>  SELECT _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)'));

In this example, the input is a LINESTRING object, which is a simple geometry type, not a collection of multiple geometry objects. After executing this SQL statement, the result is 0.

The 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 polygon (Polygon) geometry object. An interior ring refers to the boundary lines that enclose "holes" or "islands" within the polygon. These holes are located inside the exterior ring of the polygon. If a polygon has no interior rings, it means it has no holes.

Syntax:

_ST_NumInteriorRings(geometry a_polygon);

Parameters:

• geometry a_polygon: The input parameter representing the polygon geometry object to be checked. If the input geometry object is not a polygon type, the function returns NULL.

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 has three-dimensional coordinates (x, y, z). Although z-coordinates are provided, the specified polygon does not have any interior rings, so the return value is 0.

The 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