Spatial accessor functions

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

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

_ST_GeometryType

_ST_GeometryType() returns the type of a geometry object. The function returns the type of the input geometry object in the format of ST_[TYPE] in compliance with the SQL/MM specification. For example, the function returns ST_Point for a point and ST_LineString for a line string.

The syntax is as follows:

_ST_GeometryType(geometry g1);

where

• geometry g1 indicates the input geometry object. It can be a geometry object of any type, such as a Point, LineString, or Polygon object.

Limitations are as follows:

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

Here is an 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, _ST_GeometryType() checks the geometry object created by ST_GeomFromText() and returns the type of the object.

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

_ST_IsCollection() tests whether the type of the input geometry object is a geometry collection type. In a geographic information system (GIS), a geometry collection is a set of geometry objects, such as points, lines, and polygons.

The syntax is as follows:

_ST_IsCollection(geometry g1);

where

• geometry g1 indicates the input geometry object, which must be valid. Geometry collection types mainly include:

  • GEOMETRYCOLLECTION: a collection of geometry objects of any type.
  • MULTIPOINT: a collection of points.
  • MULTIPOLYGON: a collection of polygons.
  • MULTILINESTRING: a collection of line strings.
  • MULTICURVE: a collection of curves (supported in some database systems only).
  • MULTISURFACE: a collection of surfaces (supported in some database systems only).
  • COMPOUNDCURVE: a composite curve that consists of multiple simple curves. This collection type is not supported in OceanBase Database currently.

Here is an example:

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

In this example, the input geometry object is a LINESTRING object, which is a simple geometry type instead of a collection type. Therefore, 0 is returned after the SQL statement is executed.

The return result is as follows:

+-----------------------------------------------------------+
| _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)')) |
+-----------------------------------------------------------+
|                                                         0 |
+-----------------------------------------------------------+
1 row in set

_ST_NumInteriorRings

_ST_NumInteriorRings() returns the number of interior rings of a Polygon object. An interior ring is the boundary of a hole or an island in a polygon. A hole is located inside the outer ring of a polygon. If a polygon has no interior ring, it does not have any hole.

The syntax is as follows:

_ST_NumInteriorRings(geometry a_polygon);

where

• geometry a_polygon indicates the input parameter that represents the polygon to be tested. If the input geometry is not a polygon, the function returns NULL.

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 the well-known text (WKT) format with three-dimensional coordinates (x, y, and z). Although the z coordinate is provided, the specified polygon does not have an interior ring. Therefore, the returned 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