How the GeometryN() function works in Mariadb?

The GeometryN() function is a spatial function that returns the N-th geometry in a geometry collection.

Posted on

The GeometryN() function is a spatial function that returns the N-th geometry in a geometry collection. It can be used to access the individual elements of a geometry collection.

Syntax

The syntax of the GeometryN() function is as follows:

GeometryN(geomcollection, n)

The geomcollection parameter is a geometry collection object that contains one or more geometries of any type. The function returns the geometry object that is the N-th element in the geometry collection.

The n parameter is an integer that specifies the position of the geometry to be returned. The function uses a one-based index, which means the first geometry is at position 1, the second geometry is at position 2, and so on. The function returns NULL if the position is out of range.

Examples

Example 1: Basic usage

The following example shows how to use the GeometryN() function to return the first geometry in a geometry collection.

SELECT AsText(GeometryN(GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))'), 1)) AS geom;

The output is:

+------------+
| geom       |
+------------+
| POINT(1 1) |
+------------+

The function returns a point geometry object that has the coordinates (1, 1). The object is stored as a binary string in the internal format of Mariadb.

Example 2: Out of range position

The following example shows how to use the GeometryN() function with an out of range position.

SELECT AsText(GeometryN(GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 4, 5 5, 4 5, 4 4)))'), 4)) AS geom;

The output is:

+------+
| geom |
+------+
| NULL |
+------+

The function returns NULL because the position 4 is out of range. The geometry collection only has three geometries.

Some of the functions that are related to the GeometryN() function are:

  • NumGeometries(): This function returns the number of geometries in a geometry collection. It can be used to determine the range of valid positions for the GeometryN() function.
  • GeometryType(): This function returns the type of a geometry object as a string. It can be used to identify the type of the geometry returned by the GeometryN() function.
  • AsText(): This function converts a geometry object to a well-known text representation. It can be used to display the geometry returned by the GeometryN() function in a human-readable format.

Conclusion

The GeometryN() function is a useful function for returning the N-th geometry in a geometry collection. It can be used to access the individual elements of a geometry collection. It can also be combined with other spatial functions to achieve various spatial operations.