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

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:

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:

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.