How the ST_GEOMETRYN() function works in MariaDB?

The ST_GEOMETRYN() function in MariaDB is used to return the N-th geometry from a collection of geometries. It operates on geometry collections, such as GEOMETRYCOLLECTION or MULTI geometries.

Syntax

The syntax for the MariaDB ST_GEOMETRYN() function is as follows:

ST_GEOMETRYN(geom, n)

• geom: A geometry collection or a MULTI geometry.
• n: An integer representing the index of the geometry to be returned, starting from 1.

The function returns the N-th geometry from the input geometry collection. If the input is not a valid geometry collection or MULTI geometry, or if the index n is out of range, it returns NULL.

Examples

Example 1: Extracting a geometry from a GeometryCollection

This example demonstrates how to extract a specific geometry from a GEOMETRYCOLLECTION.

SELECT ST_GEOMETRYN(
    ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'),
    2
);

The output is as follows:

LINESTRING(2 2,3 3)

This extracts the second geometry (the LineString) from the GEOMETRYCOLLECTION.

Example 2: Extracting a geometry from a MultiPoint

This example demonstrates how to extract a specific Point from a MULTIPOINT.

SELECT ST_GEOMETRYN(
    ST_GeometryFromText('MULTIPOINT(1 1, 2 2, 3 3)'),
    3
);

The output is as follows:

POINT(3 3)

This extracts the third Point from the MULTIPOINT.

Example 3: Extracting a geometry from a MultiLineString

This example demonstrates how to extract a specific LineString from a MULTILINESTRING.

SELECT ST_GEOMETRYN(
    ST_GeometryFromText('MULTILINESTRING((0 0, 1 1), (2 2, 3 3))'),
    2
);

The output is as follows:

LINESTRING(2 2,3 3)

This extracts the second LineString from the MULTILINESTRING.

Example 4: Extracting a geometry from a MultiPolygon

This example demonstrates how to extract a specific Polygon from a MULTIPOLYGON.

SELECT ST_GEOMETRYN(
    ST_GeometryFromText('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2)))'),
    2
);

The output is as follows:

POLYGON((2 2,2 3,3 3,3 2,2 2))

This extracts the second Polygon from the MULTIPOLYGON.

Example 5: Handling invalid input

This example demonstrates how the function handles invalid input.

SELECT ST_GEOMETRYN(ST_GeometryFromText('POINT(1 1)'), 1);

The output is as follows:

NULL

This returns NULL because the input geometry is not a collection or MULTI geometry.

Related Functions

The following are a few functions related to the MariaDB ST_GEOMETRYN() function:

• MariaDB ST_NumGeometries() function is used to return the number of geometries in a geometry collection or MULTI geometry.
• MariaDB ST_GeometryType() function is used to retrieve the type of a geometry value.
• MariaDB ST_GeometryFromText() function is used to construct a geometry value from a Well-Known Text (WKT) representation of a geometry.
• MariaDB ST_GeometryCollectionFromText() function is used to construct a GeometryCollection value from a Well-Known Text (WKT) representation of a geometry collection.

Conclusion

The ST_GEOMETRYN() function in MariaDB is a useful tool for working with geometry collections and MULTI geometries. It allows you to extract individual geometries from these collections based on their index. The provided examples demonstrate various use cases for this function, including extracting geometries from GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON objects. It is an essential function for spatial data manipulation and analysis in MariaDB.