Returns the nth interior ring of a polygon as a linestring. The rings are not organized by geometric orientation. They are organized according to the rules defined by the internal geometry verification routines. So, the order of the rings cannot be predefined.
Syntax
ST_InteriorRingN(p ST_Polygon, n Integer)
Return type
db2gse.ST_LineString
Examples
An ornithologist who is studying the bird population on several south sea islands knows that the feeding zone of a particular passive species is restricted to the seashore. Some of the islands have several lakes on them. The shorelines of the lakes are inhabited exclusively by another more aggressive species. The ornithologist knows that for each island, if the perimeter of the lakes 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.
In Figure 34, the exterior rings of the islands represent the ecological interface that each island shares with the sea. Some of the islands have lakes, which are represented by the interior rings of the polygons.
Figure 34. Using ST_InteriorRingN to determine the length of the lakeshores within each island
|
The ID and name columns of the ISLANDS table identify each island, while the land polygon column stores the island's geometry.
CREATE TABLE ISLANDS (id integer,
name varchar(32),
land db2gse.ST_Polygon);
The following ODBC program uses the ST_InteriorRingN function to extract the interior ring (lake) from each island polygon as a linestring. The perimeter of the linestring that is returned by the length function is totaled and displayed along with the island's ID.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include "sg.h"
#include "sgerr.h"
#include "sqlcli1.h"
/*** ***
*** Change these constants ***
*** ***/
#define USER_NAME "sdetest" /* your user name */
#define USER_PASS "acid.rain" /* your user password */
#define DB_NAME "mydb" /* database to connect to */
static void check_sql_err (SQLHDBC handle,
SQLHSTMT hstmt,
LONG rc,
CHAR *str);
void main( argc, argv )
int argc;
char *argv[];
{
SQLHDBC handle;
SQLHENV henv;
CHAR sql_stmt[256];
LONG rc,
total_perimeter,
num_lakes,
lake_number,
island_id,
lake_perimeter;
SQLHSTMT island_cursor,
lake_cursor;
SDWORD pcbvalue,
id_ind,
lake_ind,
length_ind;
/* Allocate memory for the ODBC environment handle henv and initialize the application. */
rc = SQLAllocEnv (&henv);
if (rc != SQL_SUCCESS)
{
printf ("SQLAllocEnv failed with %d\n", rc);
exit(0);
}
/* Allocate memory for a connection handle within the henv environment. */
rc = SQLAllocConnect (henv, &handle);
if (rc != SQL_SUCCESS)
{
printf ("SQLAllocConnect failed with %d\n", rc);
exit(0);
}
/* Load the ODBC driver and connect to the data source identified by the database,
user, and password.*/
rc = SQLConnect (handle,
(UCHAR *)DB_NAME,
SQL_NTS,
(UCHAR *)USER_NAME,
SQL_NTS,
(UCHAR *)USER_PASS,
SQL_NTS);
check_sql_err (handle, NULL, rc, "SQLConnect");
/* Allocate memory to the SQL statement handle island_cursor. */
rc = SQLAllocStmt (handle, &island_cursor);
check_sql_err (handle, NULL, rc, "SQLAllocStmt");
/* Prepare and execute the query to get the island IDs and number of
lakes (interior rings) */
strcpy (sql_stmt, "select id, db2gse.ST_NumInteriorRings(land) from ISLANDS");
rc = SQLExecDirect (island_cursor, (UCHAR *)sql_stmt, SQL_NTS);
check_sql_err (NULL, island_cursor, rc, "SQLExecDirect");
/* Bind the island table's ID column to the variable island_id */
rc = SQLBindCol (island_cursor, 1, SQL_C_SLONG, &island_id, 0, &id_ind);
check_sql_err (NULL, island_cursor, rc, "SQLBindCol");
/* Bind the result of numinteriorrings(land) to the num_lakes variable. */
rc = SQLBindCol (island_cursor, 2, SQL_C_SLONG, &num_lakes, 0, &lake_ind);
check_sql_err (NULL, island_cursor, rc, "SQLBindCol");
/* Allocate memory to the SQL statement handle lake_cursor. */
rc = SQLAllocStmt (handle, &lake_cursor);
check_sql_err (handle, NULL, rc, "SQLAllocStmt");
/* Prepare the query to get the length of an interior ring. */
strcpy (sql_stmt,
"select Length(db2gse.ST_InteriorRingN(land, cast (? as
integer))) from ISLANDS where id = ?");
rc = SQLPrepare (lake_cursor, (UCHAR *)sql_stmt, SQL_NTS);
check_sql_err (NULL, lake_cursor, rc, "SQLPrepare");
/* Bind the lake_number variable as the first input parameter */
pcbvalue = 0;
rc = SQLBindParameter (lake_cursor, 1, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &lake_number, 0, &pcbvalue);
check_sql_err (NULL, lake_cursor, rc, "SQLBindParameter");
/* Bind the island_id as the second input parameter */
pcbvalue = 0;
rc = SQLBindParameter (lake_cursor, 2, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &island_id, 0, &pcbvalue);
check_sql_err (NULL, lake_cursor, rc, "SQLBindParameter");
/* Bind the result of the Length(db2gse.ST_InteriorRingN(land, cast (? as integer)))
to the variable lake_perimeter */
rc = SQLBindCol (lake_cursor, 1, SQL_C_SLONG, &lake_perimeter, 0,
&length_ind);
check_sql_err (NULL, island_cursor, rc, "SQLBindCol");
/* Outer loop, get the island ids and the number of lakes (interior rings) */
while (SQL_SUCCESS == rc)
{
/* Fetch an island */
rc = SQLFetch (island_cursor);
if (rc != SQL_NO_DATA)
{
check_sql_err (NULL, island_cursor, rc, "SQLFetch");
/* Inner loop, for this island, get the perimeter of all of
its lakes (interior rings) */
for (total_perimeter = 0,lake_number = 1;
lake_number <= num_lakes;
lake_number++)
{
rc = SQLExecute (lake_cursor);
check_sql_err (NULL, lake_cursor, rc, "SQLExecute");
rc = SQLFetch (lake_cursor);
check_sql_err (NULL, lake_cursor, rc, "SQLFetch");
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);
SQLDisconnect (handle);
SQLFreeConnect (handle);
SQLFreeEnv (henv);
printf( "\nTest Complete ...\n" );
}
static void check_sql_err (SQLHDBC handle, SQLHSTMT hstmt, LONG rc,
CHAR *str)
{
SDWORD dbms_err = 0;
SWORD length;
UCHAR err_msg[SQL_MAX_MESSAGE_LENGTH], state[6];
if (rc != SQL_SUCCESS)
{
SQLError (SQL_NULL_HENV, handle, hstmt, state, &dbms_err,
err_msg, SQL_MAX_MESSAGE_LENGTH - 1, &length);
printf ("%s ERROR (%d): DBMS code:%d, SQL state: %s, message: \n %s\n",
str, rc, dbms_err, state, err_msg);
if (handle)
{
SQLDisconnect (handle);
SQLFreeConnect (handle);
}
exit(1);
}
}