How the ST_NUMGEOMETRIES() function works in Mariadb?

The ST_NUMGEOMETRIES() function in MariaDB is used to retrieve the number of geometries in a geometry collection. A geometry collection is a type of geometry that can contain multiple geometries of different types, such as points, linestrings, and polygons.

Syntax

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

ST_NUMGEOMETRIES(g)

• g: The geometry collection for which the number of geometries will be counted.

The function returns an integer value representing the number of geometries in the provided geometry collection.

Examples

Example 1: Counting geometries in a geometry collection

This example demonstrates how to count the number of geometries in a geometry collection.

SELECT ST_NUMGEOMETRIES(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'));

The following is the output:

2

The geometry collection contains two geometries: a point and a linestring. Therefore, the function returns 2.

Example 2: Counting geometries in an empty geometry collection

This example shows how to count the number of geometries in an empty geometry collection.

SELECT ST_NUMGEOMETRIES(ST_GeomFromText('GEOMETRYCOLLECTION()'));

The following is the output:

0

An empty geometry collection contains no geometries, so the function returns 0.

Example 3: Counting geometries in a table

This example demonstrates how to count the number of geometries in a geometry collection stored in a table.

DROP TABLE IF EXISTS geom_table;
CREATE TABLE geom_table (id INT PRIMARY KEY, geom GEOMETRY);
INSERT INTO geom_table (id, geom) VALUES
    (1, ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))')),
    (2, ST_GeomFromText('GEOMETRYCOLLECTION(POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'));

SELECT id, ST_NUMGEOMETRIES(geom) FROM geom_table;

The following is the output:

1, 2
2, 1

The function counts the number of geometries in each geometry collection stored in the geom column of the geom_table.

Example 4: Counting geometries in a geometry

This example shows how the function handles a non-geometry collection input.

SELECT ST_NUMGEOMETRIES(ST_GeomFromText('POINT(1 1)'));

The following is the output:

NULL

When a non-geometry collection is provided as input, the function returns NULL because the number of geometries is not applicable for non-collection geometries.

Example 5: Counting geometries in a nested geometry collection

This example demonstrates how to count the number of geometries in a nested geometry collection.

SELECT ST_NUMGEOMETRIES(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1), GEOMETRYCOLLECTION(LINESTRING(2 2, 3 3), POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))))'));

The following is the output:

2

The outer geometry collection contains two geometries: a point and another nested geometry collection. The function returns 2, counting the top-level geometries in the outer collection.

Related Functions

The following are some functions related to the MariaDB ST_NUMGEOMETRIES() function:

• MariaDB ST_GeometryType() function is used to get the geometry type of a given geometry.
• MariaDB ST_SRID() function is used to get the spatial reference system identifier (SRID) of a given geometry.
• MariaDB ST_GeometryN() function is used to retrieve a specific geometry from a geometry collection.
• MariaDB ST_NumInteriorRings() function is used to get the number of interior rings in a polygon geometry.
• MariaDB ST_NumPoints() function is used to get the number of points in a linestring or polygon geometry.

Conclusion

The ST_NUMGEOMETRIES() function in MariaDB is a valuable tool for working with geometry collections. By allowing you to count the number of geometries within a collection, this function enables you to perform various operations and analyses on the individual geometries within the collection. The examples provided in this article demonstrate how to use the ST_NUMGEOMETRIES() function effectively in different scenarios, helping you manage and work with geometry collections in your spatial applications and databases.