# How the NumInteriorRings() function works in Mariadb?

The `NumInteriorRings()`

function is a spatial function in Mariadb that returns the number of interior rings in a polygon, or 0 if the argument is not a polygon.

The `NumInteriorRings()`

function is a spatial function in Mariadb that returns the number of interior rings in a polygon, or 0 if the argument is not a polygon. This function can be used to count the number of holes or islands in a polygon.

## Syntax

The syntax of the `NumInteriorRings()`

function is as follows:

```
NumInteriorRings(p)
```

The function takes one argument, `p`

, which is a polygon value. The function returns an integer value that represents the number of interior rings in `p`

, or 0 if `p`

is not a polygon.

## Examples

Let’s look at some examples of how to use the `NumInteriorRings()`

function in Mariadb.

### Example 1: Counting the number of interior rings in a polygon

One common use case of the `NumInteriorRings()`

function is to count the number of interior rings in a polygon. For example, suppose we have a table called `countries`

that stores the spatial data of some countries, as shown below:

id | name | boundary |
---|---|---|

1 | China | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0)) |

2 | Japan | POLYGON((0 0, 0 5, 5 5, 5 0, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) |

3 | Canada | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1), (6 6, 6 9, 9 9, 9 6, 6 6)) |

If we want to count the number of interior rings in each country, we can use the following query:

```
SELECT name, NumInteriorRings(boundary) AS num_interior_rings
FROM countries;
```

This query will return the following result:

```
| name | num_interior_rings |
| ------ | ------------------ |
| China | 0 |
| Japan | 1 |
| Canada | 2 |
```

As you can see, the `NumInteriorRings()`

function returns 0 for China, which has no interior rings in its polygon, 1 for Japan, which has one interior ring that represents an island, and 2 for Canada, which has two interior rings that represent two islands.

### Example 2: Filtering the polygons by the number of interior rings

Another use case of the `NumInteriorRings()`

function is to filter the polygons by the number of interior rings. For example, suppose we have a table called `lakes`

that stores the spatial data of some lakes, as shown below:

id | name | shape |
---|---|---|

1 | Lake1 | POLYGON((0 0, 0 5, 5 5, 5 0, 0 0)) |

2 | Lake2 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1)) |

3 | Lake3 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 9, 9 9, 9 1, 1 1)) |

4 | Lake4 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1), (6 6, 6 9, 9 9, 9 6, 6 6)) |

If we want to find the lakes that have more than one interior ring in their shape, we can use the following query:

```
SELECT name, shape
FROM lakes
WHERE NumInteriorRings(shape) > 1;
```

This query will return the following result:

```
| name | shape |
| ----- | -------------------------------------------------------------------------------------------- |
| Lake4 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1), (6 6, 6 9, 9 9, 9 6, 6 6)) |
```

As you can see, the `NumInteriorRings()`

function returns 2 for Lake4, which has two interior rings that represent two islands, and filters out the other lakes that have only one or zero interior rings.

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

The `NumInteriorRings()`

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 `parks`

that stores the spatial data of some parks, as shown below:

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

1 | Park1 | POLYGON((0 0, 0 5, 5 5, 5 0, 0 0)) |

2 | Park2 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1)) |

3 | Park3 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 9, 9 9, 9 1, 1 1)) |

4 | Park4 | POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1), (6 6, 6 9, 9 9, 9 6, 6 6)) |

If we want to calculate the average area of the interior rings for each park, we can use the following query:

```
SELECT name,
CASE WHEN NumInteriorRings(area) = 0 THEN 0
ELSE SUM(ST_Area(InteriorRingN(area, n))) / NumInteriorRings(area)
END AS avg_interior_area
FROM parks
JOIN (SELECT 1 AS n UNION ALL SELECT 2) AS numbers
ON n <= NumInteriorRings(area)
GROUP BY name;
```

This query will return the following result:

```
| name | avg_interior_area |
| ----- | ----------------- |
| Park1 | 0 |
| Park2 | 9 |
| Park3 | 64 |
| Park4 | 18 |
```

As you can see, the `NumInteriorRings()`

function is used to join the parks table with a numbers table that has two rows, 1 and 2. This way, we can apply the `InteriorRingN()`

function to each interior ring in the polygon, and then use the `ST_Area()`

function to calculate the area of each interior ring. Finally, we use the `SUM()`

and `CASE`

functions to aggregate and average the area of the interior rings for each park.

## Related Functions

There are some other functions in Mariadb that are related to the `NumInteriorRings()`

function, such as:

- The
`InteriorRingN()`

function, which returns the N-th interior ring of a polygon, or NULL if the argument is not a polygon or the N-th interior ring does not exist. For example,`InteriorRingN(POLYGON((0 0, 0 10, 10 10, 10 0, 0 0), (1 1, 1 4, 4 4, 4 1, 1 1)), 1)`

returns`POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))`

. - The
`ExteriorRing()`

function, which returns the exterior ring of a polygon, or NULL if the argument is not a polygon.

## Conclusion

In this article, we have learned how the `NumInteriorRings()`

function works in Mariadb, and how to use it to count the number of interior rings in a polygon, or 0 if the argument is not a polygon. We have also seen some examples and related functions that can help us work with spatial data in Mariadb.