The ST_InteriorRingN() function

The ST_InteriorRingN() function returns the nth interior ring of a polygon as an ST_LineString.

The order of the rings cannot be predefined since the rings are organized according to the rules defined by the internal geometry verification routines and not by geometric orientation.

Syntax

ST_InteriorRingN(pl1 ST_Polygon, index integer)

Return type

ST_LineString

Example

An ornithologist studying the bird population on several South Sea islands knows that the feeding zone of this passive species is restricted to the seashore. Some of the islands are so large they have several lakes on them. The shorelines of the lakes are inhabited exclusively by another more aggressive species. The ornithologist knows that if the perimeter of the ponds on each island exceeds a certain threshold, the aggressive species will become so numerous that it will threaten the passive seashore species. Therefore, the ornithologist requires the aggregated perimeter of the interior rings of the islands.

The following figure shows the exterior rings of the islands that represent the ecological interface each island shares with the sea. Some of the islands have lakes, which are represented by the interior rings of the polygons.
Figure 1: Islands and lakes.

This graphic is described in the surrounding text.
The ID and name columns of the islands table identifies each island, while the land ST_Polygon column stores the island geometry: 
CREATE TABLE islands (id    integer,
                      name  varchar(32),
                      land  ST_Polygon);
This ODBC code fragment uses the ST_InteriorRingN() function to extract the interior ring (lake) from each island polygon as a linestring. The perimeter of the linestring returned by the ST_Length() function is totaled and displayed along with the island ID: 
/* Prepare and execute the query to get the island IDs and number
       of lakes (interior rings); */
    sprintf(sql_stmt,
            "SELECT id, ST_NumInteriorRing(land) FROM islands");

    /* Allocate memory for the island cursor */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &island_cursor);

    rc = SQLExecDirect (island_cursor, (UCHAR *)sql_stmt, SQL_NTS);

    /* Bind the island table's id column to island_id. */
    rc = SQLBindCol (island_cursor, 1, SQL_C_SLONG,
                     &island_id, 0, &id_ind);

    /* Bind the result of ST_NumInteriorRing(land) to num_lakes. */
    rc = SQLBindCol (island_cursor, 2, SQL_C_SLONG,
                     &num_lakes, 0, &lake_ind);

    /* Allocate memory to the SQL statement handle lake_cursor. */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &lake_cursor);

    /* Prepare the query to get the length of an interior ring. For
     * efficiency, we only prepare this query once. */
    sprintf (sql_stmt,
             "SELECT ST_Length(ST_InteriorRingN(land, ?))"
             "FROM islands WHERE id = ?");
    rc = SQLPrepare (lake_cursor, (UCHAR *)sql_stmt, SQL_NTS);

    /* Bind the lake_number to the first parameter. */
    pcbvalue1 = 0;
    rc = SQLBindParameter (lake_cursor, 1, SQL_PARAM_INPUT, SQL_C_LONG,
                           SQL_INTEGER, 0, 0,
                           &lake_number, 0, &pcbvalue1);

    /* Bind the island_id to the second parameter. */
    pcbvalue2 = 0;
    rc = SQLBindParameter (lake_cursor, 2, SQL_PARAM_INPUT, SQL_C_LONG,
                           SQL_INTEGER, 0, 0,
                           &island_id, 0, &pcbvalue2);

    /* Bind the result of the ST_Length function to lake_perimeter. */
    rc = SQLBindCol (lake_cursor, 1, SQL_C_SLONG,
                     &lake_perimeter, 0, &length_ind);

    /* Outer loop:
     * get the island ids and the number of lakes (interior rings).*/
    while (1)
    {
        /* Fetch an island.*/
        rc = SQLFetch (island_cursor);
        if (rc == SQL_NO_DATA)
            break;
        else 
            returncode_check(NULL, hstmt, rc, "SQLFetch");

        /* Inner loop: for this island,
         * get the perimeter of all its lakes (interior rings). */
        for (total_perimeter = 0,lake_number = 1;
             lake_number <= num_lakes;
             lake_number++)
        {
            rc = SQLExecute (lake_cursor);
            rc = SQLFetch (lake_cursor);
            total_perimeter += lake_perimeter;
            SQLFreeStmt (lake_cursor, SQL_CLOSE);
        }

        /* Display the island ID and the total perimeter of its lakes.*/
        printf ("Island ID = %d, Total lake perimeter = %d\n",
                island_id,total_perimeter);
    }

    SQLFreeStmt (lake_cursor, SQL_DROP);
    SQLFreeStmt (island_cursor, SQL_DROP);