Spatial accessor functions

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

seekdb 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 format ST_[TYPE]. For example, if the input geometry object is a point, the function returns ST_Point; if it is a linestring, it returns ST_LineString.

Syntax:

_ST_GeometryType(geometry g1);

Parameter description:

• geometry g1: The input geometry object. It can be any type of geometry object, such as a point (Point), a line (LineString), or a 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:

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 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 (0.001 sec)

_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, which can be points, lines, or polygons.

Syntax:

_ST_IsCollection(geometry g1);

Parameter description:

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

  • GEOMETRYCOLLECTION: A collection of geometry objects, which can be of any type.
  • MULTIPOINT: A collection of multiple point objects.
  • MULTIPOLYGON: A collection of multiple polygon objects.
  • MULTILINESTRING: A collection of multiple 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 composed of multiple simple curves. However, this type may not be supported in seekdb.

Example:

 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 and 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 (0.001 sec)

_ST_NumInteriorRings

The _ST_NumInteriorRings function returns the number of interior rings in 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);

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 returns NULL.

Example:

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 (x, y, z). Although z-coordinates are provided, the specified polygon does not have any interior rings, so the returned 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 (0.001 sec)