How the NumGeometries() function works in Mariadb?

The NumGeometries() function is a spatial function in Mariadb that returns the number of geometries in a geometry collection, or 1 if the argument is not a geometry collection. This function can be used to count the number of points, lines, polygons, or other geometries in a spatial data set.

Syntax

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

NumGeometries(g)

The function takes one argument, g, which is a geometry value. The function returns an integer value that represents the number of geometries in g, or 1 if g is not a geometry collection.

Examples

Let’s look at some examples of how to use the NumGeometries() function in Mariadb.

Example 1: Counting the number of geometries in a geometry collection

One common use case of the NumGeometries() function is to count the number of geometries in a geometry collection. For example, suppose we have a table called places that stores the spatial data of some places, as shown below:

If we want to count the number of geometries in each place, we can use the following query:

SELECT name, NumGeometries(location) AS num_geometries
FROM places;

This query will return the following result:

| name    | num_geometries |
| ------- | -------------- |
| Park    | 3              |
| Museum  | 1              |
| Stadium | 1              |

As you can see, the NumGeometries() function returns 3 for the park, which has a point, a line, and a polygon in its geometry collection, and 1 for the museum and the stadium, which are not geometry collections.

Example 2: Filtering the geometry collections by the number of geometries

Another use case of the NumGeometries() function is to filter the geometry collections by the number of geometries. For example, suppose we have a table called shapes that stores the spatial data of some shapes, as shown below:

If we want to find the shapes that have more than one geometry in their geometry collection, we can use the following query:

SELECT shape, geometry
FROM shapes
WHERE NumGeometries(geometry) > 1;

This query will return the following result:

| shape | geometry                                                                     |
| ----- | ---------------------------------------------------------------------------- |
| Star  | GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 1, 1 0), LINESTRING(0 -1, -1 0)) |

As you can see, the NumGeometries() function returns 3 for the star, which has a point and two lines in its geometry collection, and filters out the other shapes that have only one geometry.

Example 3: Using the NumGeometries() function with other spatial functions

The NumGeometries() function can also be used with other spatial functions to perform more complex operations on spatial data. For example, suppose we have a table called regions that stores the spatial data of some regions, as shown below:

If we want to calculate the total area of each region, we can use the following query:

SELECT name, SUM(ST_Area(GeometryN(area, n))) AS total_area
FROM regions
JOIN (SELECT 1 AS n UNION ALL SELECT 2) AS numbers
ON n <= NumGeometries(area)
GROUP BY name;

This query will return the following result:

| name    | total_area |
| ------- | ---------- |
| Region1 | 2          |
| Region2 | 4          |
| Region3 | 16         |

As you can see, the NumGeometries() function is used to join the regions table with a numbers table that has two rows, 1 and 2. This way, we can apply the GeometryN() function to each geometry in the geometry collection, and then use the ST_Area() function to calculate the area of each geometry. Finally, we use the SUM() function to aggregate the area of each region.

Related Functions

There are some other functions in Mariadb that are related to the NumGeometries() function, such as:

• The GeometryN() function, which returns the N-th geometry in a geometry collection, or the argument itself if it is not a geometry collection. For example, GeometryN(GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3)), 2) returns LINESTRING(2 2, 3 3).
• The GeometryType() function, which returns the type of the geometry as a string. For example, GeometryType(GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))) returns 'GEOMETRYCOLLECTION'.
• The ST_NumPoints() function, which returns the number of points in a geometry. For example, ST_NumPoints(LINESTRING(1 1, 2 2, 3 3)) returns 3.

Conclusion

In this article, we have learned how the NumGeometries() function works in Mariadb, and how to use it to count the number of geometries in a geometry collection, or 1 if the argument is not a geometry collection. We have also seen some examples and related functions that can help us work with spatial data in Mariadb.