How the ST_InteriorRingN() function works in MariaDB?

The ST_InteriorRingN() function in MariaDB is used to retrieve the N-th interior ring of a Polygon geometry. Interior rings are the rings that define holes or inner boundaries within a polygon.

Syntax

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

ST_InteriorRingN(poly, n)

• poly: A Polygon geometry.
• n: An integer representing the index of the interior ring to be returned, starting from 1.

The function returns the N-th interior ring of the input Polygon geometry as a LineString. If the input is not a Polygon geometry, or if the index n is out of range, it returns NULL.

Examples

Example 1: Retrieving the first interior ring of a Polygon

This example demonstrates how to retrieve the first interior ring of a Polygon.

SELECT ST_InteriorRingN(
    ST_GeometryFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2))'),
    1
);

The output is as follows:

LINESTRING(2 2,2 8,8 8,8 2,2 2)

This retrieves the first (and only) interior ring of the Polygon, which defines a hole within the Polygon.

Example 2: Retrieving the second interior ring of a Polygon

This example demonstrates how to retrieve the second interior ring of a Polygon with multiple interior rings.

SELECT ST_InteriorRingN(
    ST_GeometryFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2), (4 4, 4 6, 6 6, 6 4, 4 4))'),
    2
);

The output is as follows:

LINESTRING(4 4,4 6,6 6,6 4,4 4)

This retrieves the second interior ring of the Polygon, which defines another hole within the Polygon.

Example 3: Handling invalid input

This example demonstrates how the function handles invalid input.

SELECT ST_InteriorRingN(ST_GeometryFromText('POINT(1 1)'), 1);

The output is as follows:

NULL

This returns NULL because the input geometry is not a Polygon.

Example 4: Retrieving interior rings from a table

This example demonstrates how to retrieve interior rings from a table containing Polygon data.

DROP TABLE IF EXISTS polygons;
CREATE TABLE polygons (id INT, geom GEOMETRY);
INSERT INTO polygons VALUES
    (1, ST_GeometryFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2))')),
    (2, ST_GeometryFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2), (4 4, 4 6, 6 6, 6 4, 4 4))'));

SELECT id, ST_AsText(ST_InteriorRingN(geom, 1)) AS ring1, ST_AsText(ST_InteriorRingN(geom, 2)) AS ring2
FROM polygons;

The output is as follows:

+----+------------------------+-------+
| id | ring1                  | ring2 |
+----+------------------------+-------+
|  1 | LINESTRING(2 2,2 8,8 8,8 2,2 2) | NULL  |
|  2 | LINESTRING(2 2,2 8,8 8,8 2,2 2) | LINESTRING(4 4,4 6,6 6,6 4,4 4) |
+----+------------------------+-------+

This retrieves the first and second interior rings (if present) for each Polygon in the polygons table.

Example 5: Retrieving a non-existent interior ring

This example demonstrates how the function handles requests for non-existent interior rings.

SELECT ST_InteriorRingN(
    ST_GeometryFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 2 8, 8 8, 8 2, 2 2))'),
    2
);

The output is as follows:

NULL

This returns NULL because the Polygon has only one interior ring, and the requested index (2) is out of range.

Related Functions

The following are a few functions related to the MariaDB ST_InteriorRingN() function:

• MariaDB ST_NumInteriorRings() function is used to retrieve the number of interior rings in a Polygon geometry.
• MariaDB ST_ExteriorRing() function is used to retrieve the exterior ring of a Polygon geometry.
• MariaDB ST_GeometryFromText() function is used to construct a geometry value from a Well-Known Text (WKT) representation of a geometry.
• MariaDB ST_AsText() function is used to convert a geometry value to its Well-Known Text (WKT) representation.

Conclusion

The ST_InteriorRingN() function in MariaDB is a valuable tool for working with Polygon geometries that have interior rings or holes. It allows you to retrieve individual interior rings based on their index, which can be useful in various scenarios, such as spatial analysis, data visualization, or geometry processing. The provided examples demonstrate how to use this function with different types of Polygon geometries, including those with single and multiple interior rings. Additionally, the examples cover handling invalid input and working with data stored in tables. Overall, ST_InteriorRingN() is an important function for spatial data manipulation and analysis in MariaDB.