# 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.

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:

id | name | location |
---|---|---|

1 | Park | GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3), POLYGON((4 4, 5 5))) |

2 | Museum | POINT(6 6) |

3 | Stadium | POLYGON((7 7, 8 8, 9 9, 7 7)) |

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:

id | shape | geometry |
---|---|---|

1 | Square | POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) |

2 | Circle | POINT(0 0) |

3 | Star | GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 1, 1 0), LINESTRING(0 -1, -1 0)) |

4 | Triangle | POLYGON((0 0, 0 1, 1 0, 0 0)) |

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:

id | name | area |
---|---|---|

1 | Region1 | GEOMETRYCOLLECTION(POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)), POLYGON((2 2, 2 3, 3 3, 3 2, 2 2))) |

2 | Region2 | GEOMETRYCOLLECTION(POLYGON((0 0, 0 2, 2 2, 2 0, 0 0)), POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))) |

3 | Region3 | POLYGON((0 0, 0 4, 4 4, 4 0, 0 0)) |

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.