sqlapi

The references below are specific to Informix Spatial DataBlade 8.10. However, IBM DB2 references are very similar but do not have the prefix SE_. Functions prefixed with SE_ are not OGC compliant. Instead, they are additional functions provided to enhance usability.

OGC Compliant Functions

ST_Area
ST_AsBinary
ST_AsText
ST_Boundary
ST_Buffer
ST_Centroid
ST_Contains
ST_ConvexHull
ST_CoordDim
ST_Crosses
ST_Difference
ST_Dimension
ST_Disjoint
ST_Distance
ST_EndPoint
ST_Envelope
ST_EnvelopesIntersect
ST_Equals
ST_ExteriorRing
ST_GeometryN
ST_GeometryType
ST_GeomFromText
ST_GeomFromWKB
ST_InteriorRingN
ST_Intersection
ST_Intersects
ST_IsClosed
ST_IsEmpty
ST_IsRing
ST_IsSimple
ST_IsValid
ST_Length
ST_LineFromText
ST_LineFromWKB
ST_MlineFromText
ST_MlineFromWKB
ST_MpointFromText
ST_MpointFromWKB
ST_MpolyFromText
ST_MpolyFromWKB
ST_NumGeometries
ST_NumInteriorRing
ST_NumPoints
ST_OrderingEquals
ST_Overlaps
ST_Perimeter
ST_Point
ST_PointFromText
ST_PointFromWKB
ST_PointN
ST_PointOnSurface
ST_PolyFromText
ST_PolyFromWKB
ST_Polygon
ST_Relate
ST_SRID
ST_StartPoint
ST_SymDifference
ST_Touches
ST_Transform
ST_Union
ST_Within
ST_WKBToSQL
ST_WKTToSQL

ST_X
ST_Y

Non-OGC compliant Functions (Spatial DataBlade only)

SE_AsShape
SE_GeomFromShape
SE_Is3D
SE_IsMeasured
SE_LineFromShape
SE_LocateAlong
SE_LocateBetween
SE_M
SE_MlineFromShape
SE_MpointFromShape
SE_MpolyFromShape
SE_PointFromShape
SE_PolyFromShape
SE_ShapeToSQL
SE_Z

ST_Area

Syntax

Return type

Example

The city engineer needs a list of building areas. To create the list, a GIS technician selects the building ID and area of each building's footprint.

The building footprints are stored in the buildingfootprints table created with the following CREATE TABLE statement.

create table buildingfootprints (
building_id integer,
lot_id integer,
footprint ST_MultiPolygon);

To satisfy the city engineer's request the technician selects the unique key, the building_id, and the area of each building footprint from the buildingfootprints table.

Four of the building footprints labeled with their building ID numbers are displayed alongside their adjacent street.

The code fragment below converts the footprint multipolygons of the buildingfootprints table into WKB multipolygons with the ST_AsBinary function. The multipolygons are passed to the application's draw_polygon function for display.

/* Create the SQL expression.*/
strcpy(sqlstmt,"select ST_AsBinary (footprint) from buildingfootprints where SE_EnvelopesIntersect(footprint,ST_PolyFromWKB(?,1))");

SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_INFX_UDT_LVARCHAR, query_wkb_len, 0, query_wkb_buf, query_wkb_len, &pcbvalue1);

/* Assign the results of the query (the Zone polygons) to the fetched_binary variable. */
SQLBindCol (hstmt, 1,SQL_C_Binary, fetched_wkb_buf, 100000, &fetch_wkb_len);

/* Fetch each polygon within the display window and display it. */
while(SQL_SUCCESS == (rc = SQLFetch(hstmt)))
draw_polygon(fetched_wkb_buf);

The code fragment below illustrates how the SE_AsShape function converts the zone polygons of the sensitive_areas table into shape polygons. These shape polygons are passed to the application's draw_polygon function for display.

/* Create the SQL expression. */
strcpy(sqlstmt,"select SE_AsShape(zone) from sensitive_areas where SE_EnvelopesIntersect(zone,SE_PolyFromShape(?,1))");

/* Bind the query shape parameter. */
pcbvalue1 = query_shape_len;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_INFX_UDT_LVARCHAR, query_shape_len, 0, query_shape_buf, query_shape_len, amp;pcbvalue1);

/* Assign the results of the query (the Zone polygons) to the fetched_binary variable. */
SQLBindCol (hstmt, 1,SQL_C_Binary, fetched_shape_buf, 100000, &fetch_shape_len);

/* Fetch each polygon within the display window and display it. */
while(SQL_SUCCESS == (rc = SQLFetch(hstmt)))
draw_polygon(fetched_shape_buf);

The ST_AsText function converts thehazardous_sites location point into itstext description.

create tablehazardous_sites (site_id integer,
name varchar(40),
location ST_Point);

insert into hazardous_sites values ( 102,'W. H. KleenareChemical Repository',
ST_PointFromText('point(1020.12 324.02)',1)
);

>SITE_ID Name Location ------- ----------------------------------- ------------------------- 102 W. H. KleenareChemical Repository ST_POINT (1020.12 3240.2)

In this example the boundary_test table is created with two columns: geotype defined as a varchar, and g1 defined as the superclass geometry. The INSERT statements that follow insert each one of the subclass geometries. The ST_Boundary function retrieves the boundary of each subclass stored in the g1 geometry column. Note that the dimension of the resulting geometry is always one less than the input geometry. Points and multipoints always result in a boundary that is an empty geometry, dimension -1. Linestrings and multilinestrings return a multipoint boundary, dimension 0. A polygon or multipolygon always returns a multilinestring boundary, dimension 1.

insert into boundary_test values ( 'Point',
ST_PointFromText ( 'point (10.02 20.01)' ,1));

insert into boundary_test values ( 'Linestring',
ST_LineFromText ( 'linestring(10.02 20.01,10.32 23.98,11.92 25.64)',1));

insert into boundary_test values ( 'Polygon',
ST_PolyFromText ( 'polygon((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01))',1));

insert into boundary_test values ( 'Multipoint',
ST_MpointFromText ('multipoint(10.02 20.01, 10.32 23.98, 11.92 25.64)', 1));

insert into boundary_test values ( 'Multilinestring',
ST_MlineFromText ('multilinestring((10.02 20.01,10.32 23.98, 11.92 25.64), (9.55 23.75,15.36 30.11))', 1));

insert into boundary_test values ( 'Multipolygon',
ST_mpolyFromText('multipolygon(((10.0220.01,11.92 35.64,25.02 34.15, 19.15 33.94, 10.02 20.01)),((51.71 21.73,73.3627.04,71.52 32.87, 52.43 31.90,51.7121.73)))',1));

------------------ ---------------------------------------------------------- Point POINT EMPTY Linestring MULTIPOINT(10.02 20.01, 11.92 25.64) Polygon MULTILINESTRING ((10.02 20.01, 19.15 33.94, 25.02 34.15, 11.92 35.64, 10.02 20.01)) Multipoint POINT EMPTY Multilinestring MULTIPOINT (9.55 23.75, 10.02 20.01, 11.92 25.64, 15.36 30.11) Multipolygon MULTILINESTRING((51.71 21.73, 73.36 27.04, 71.52 32.87, 52.43 31.90, 51.71 21.73), (10.02 20.01, 19.15 33.94, 25.02 34.15, 11.92 35.64, 10.02 20.01))

Takes a geometry object and distance and returns a geometry object that is the buffer surrounding the source object.

The County Supervisor needs a list of hazardous sites whose five-mile radius overlaps sensitive areas such as schools, hospitals, and nursing homes. The sensitive areas are stored in the table sensitive_areas that is created with the CREATE TABLE statement below. The zone column, defined as a ST_Polygon, stores the outline of each of the sensitive areas.

create table sensitive_areas (id integer,
                              name varchar (128),
                              size float,
                              type varchar (10),
                              zone ST_Polygon);

The hazardous sites are stored in the hazardous_sites table created below. The location column, defined as a point, stores the geographic center of each hazardous site.

create table hazardous_sites (site_id integer,
name varchar(128),
location ST_Point);

The sensitive_areas and hazardous_sites tables are joined by the overlap function, which returns t (TRUE) for all sensitive_areas rows whose zone polygons overlap the buffered five-mile radius of the hazardous_sites location point.

select sa.name "Sensitive Areas", hs.name "Hazardous Sites"
from sensitive_areas sa, hazardous_sites hs
where ST_Overlaps (sa.zone, ST_Buffer (hs.location,(5 * 5280))) = 't';

Some of the sensitive areas in this administrative district lie within the five-mile buffer radius of the hazardous site locations. It is clear that both buffers intersect the hospital and one intersects the school. The nursing home lies safely outside both radii.

The city GIS technician wants to display the building footprint multipolygons as single points in a building density graphic.

The building footprints are stored in the buildingfootprints table that was created with the following CREATE TABLE statement.

create table buildingfootprints (building_id integer,
lot_id integer,
footprint ST_MultiPolygon);

The centroid function returns the centroid of each building footprint multipolygon. The SE_AsShape function converts each centroid point into a shape, the external representation recognized by the application.

select building_id,
SE_AsShape(ST_Centroid (footprint)) Centroid
from buildingfootprints;

Takes two geometry objects and returns t (TRUE) if first object completely contains the second; otherwise, it returns f (FALSE).

In the example below two tables are created. One, buildingfootprints, contains a city's building footprints, while the other, lots, contains its lots. The city engineer wants to ensure that all building footprints are completely inside their lots.

In both tables the multipolygon datatype stores the geometry of the buildingfootprints and the lots. The database designer selected multipolygons for both features because she realizes lots can be separated by natural features such as a river, and building footprints can comprise several buildings.

create table buildingfootprints (building_id integer,
lot_id integer,
footprint ST_MultiPolygon);

The city engineer first selects the buildings that are not completely contained within one lot.

select building_id
from buildingfootprints, lots
where ST_Contains (lot,footprint)= 'f';

The city engineer realizes that although the first query will provide her with a list of all building IDs that have footprints outside a lot polygon, it won't tell her if the rest have the correct lot_id assigned to them. This second query performs a data integrity check on the lot_id column of the buildingfootprints table.

select bf.building_id "Building id",
bf.lot_id "buildings lot_id",
lots.lot_id "lots lot_id"
from buildingfootprints bf, lots
where ST_Contains(lot,footprint) = 'f'
and lots.lot_id <> bf.lot_id;

The building footprints labeled with their building IDs lie inside their lot lines. The lot lines are illustrated with dotted lines and although not shown extend to the street centerline to completely encompass the lot lines and the building footprints within them.

The example creates the convexhull_test table that has two columns: geotype and g1. Geotype, a varchar(20), stores the name of the geometry subclass that is stored in g1, a geometry.

The INSERT statements insert several geometry subclasses into the convexhull_test table.

insert into convexhull_test values (
'Point',ST_PointFromText('point (10.02 20.01)',1)
)

insert into convexhull_test values(
'Linestring',
ST_LineFromText('linestring(10.02 20.01,10.32 23.98,11.92 25.64)',1)
)

insert into convexhull_test values(
'Polygon',ST_PolyFromText('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15, 19.15 33.94, 10.02 20.01))',1)
)

insert into convexhull_test values(
'MultiPoint',ST_MpointFromText('multipoint (10.02 20.01,10.32 23.98,
11.92 25.64)',1)
)

insert into convexhull_test values(
'MultiLineString',ST_MlineFromText('multilinestring ((10.02 20.01,
10.32 23.98,11.92 25.64),(9.55 23.75,15.36 30.11))',1)
)

insert into convexhull_test values(
'Multipolygon',
ST_MpolyFromText('multipolygon(((10.02 20.01, 11.92 35.64, 25.02 34.15,
19.15 33.94,10.02 20.01)),((51.71 21.73,73.36 27.04,71.52 32.87,
52.43 31.90,51.71 21.73)))',1)
)

The select statement lists the subclass name stored in the geotype column and the convex hull.

The coorddim_test table is created with the columns, geotype and g1. The geotype column stores the name of the geometry subclass stored in the g1 ST_Geometry column.

INSERT INTO coorddim_test VALUES(
'Point',ST_PointFromText('point (10.02 20.01)',1)
);

INSERT INTO coorddim_test VALUES(
'LineString',
ST_LineFromText('linestring(10.02 20.01,10.32 23.98,11.92 25.64)',1)
);

INSERT INTO coorddim_test VALUES(
'Polygon',
ST_PolyFromText('polygon((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01))',1)
);

INSERT INTO coorddim_test VALUES(
'MultiPoint',
ST_MPointFromText('multipoint(10.02 20.01,10.32 23.98,11.92 25.64)',1)
);

INSERT INTO coorddim_test VALUES(
'MultiLineString',
ST_MLineFromText('multilinestring((10.02 20.01,10.32 23.98,11.92 25.64),(9.55 23.75,15.36 30.11))',1)
);

INSERT INTO coorddim_test VALUES(
'MultiPolygon',
ST_MPolyFromText('multipolygon(((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01)),((51.7121.73,73.36 27.04,71.52 32.87,52.43 31.90,51.7121.73))',1)
);

The SELECT statement lists the subclass name stored in the geotype column with the dimension of that geotype.

GEOTYPE           coordinate_dimension
----------------- --------------------
Point                                2
LineString                           2
Polygon                              2
MultiPoint                           2
MultiLineString                      2
MultiPolygon                         2

ST_Crosses takes two geometry objects and returns t (TRUE) if their intersection results in a geometry object whose dimension is one less than the maximum dimension of the source objects. The intersection object must contain points that are interior to both source geometries and it is not equal to either of the source objects. Otherwise, it returns f (FALSE).

The county government is considering a new regulation stating that all hazardous waste storage facilities within the county may not be within five miles of any waterway. The county GIS manager has an accurate representation of rivers and streams stored as multilinestrings in the waterways table but he only has a single point location for each of the hazardous waste storage facilities.

create table waterways (id integer,
name varchar(128),
water ST_MultiLineString);

create table hazardous_sites (site_id integer,
name varchar(128),
location ST_Point);

To determine if he must alert the county supervisor to any existing facilities that would violate the proposed regulation, the GIS manager will have to buffer the hazardous_sites locations to see if any rivers or streams cross the buffer polygons. The cross predicate compares the buffered hazardous_sites with waterways, returning only those records where the waterway crosses over the county's proposed regulated radius.

select ww.name "River or stream", hs.name "Hazardous Sites"
from waterways ww, hazardous_sites hs
where ST_Crosses(ST_Buffer(hs.location,(5 * 5280)),ww.water) = 't';

The five-mile buffered radius of the hazardous waste sites crosses the stream network that runs through the county's administrative district. Because the stream network is defined as a multilinestring, all linestring segments that are part of those segments that cross the radius are included in the result set.

ST_Difference takes two geometry objects and returns a geometry object that is the difference of the source objects.

The city engineer needs to know the total area of the city's lot area not covered by buildings. In fact, she wants the sum of the lot area after the building area has been removed.

create table buildingfootprints (building_id integer, lot_id integer, footprint ST_Multipolygon);

The city engineer equijoins the buildingfootprints and lots table on the lot_id and takes the sum of the area of the difference of the lots less the buildingfootprints.

select sum(ST_Area(ST_Difference(lot,footprint)::st_multipolygon))
from buildingfootprints bf, lots
where bf.lot_id = lots.lot_id;

The dimension_test table is created with the columns geotype and g1. The geotype column stores the name of the subclass stored in the g1 geometry column.

The INSERT statements insert a sample subclass into the dimension_test table.

insert into dimension_test values(
'Point',ST_PointFromText('point (10.02 20.01)',1)
)

insert into dimension_test values(
'Linestring',
ST_LineFromText('linestring(10.02 20.01, 10.32 23.98, 11.92 25.64)',1)
)

insert into dimension_test values(
'Polygon',ST_PolyFromText('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15,19.15 33.94,10.02 20.01))',1)
)

insert into dimension_test values(
'Multipoint',ST_MpointFromText('multipoint (10.02 20.01,10.32 23.98,
11.92 25.64)',1)
)

insert into dimension_test values(
'Multilinestring',MlineFromText('multilinestring ((10.02 20.01,
10.32 23.98,11.92 25.64),(9.55 23.75,15.36 30.11))',1)
)

insert into dimension_test values(
'Multipolygon',
MpolyFromText ('multipolygon(((10.02 20.01,11.92 35.64,25.02 34.15,
19.15 33.94,10.02 20.01)),((51.71 21.73,73.36 27.04,71.52 32.87,
52.43 31.90,51.7121.73)))',1)
)

The select statement lists the subclass name stored in the geotype column with the dimension of that geotype.

GEOTYPE                 Dimension
----------------------- ------------
Point                       0
Linestring                  1
Polygon                     2
Multipoint                 0
Multilinestring             1
Multipolygon                2

ST_Disjoint takes two geometries and returns t (TRUE) if the intersection of two geometries produces an empty set; otherwise, it returns f (FALSE).

An insurance company wants to assess the insurance coverage for the town's hospital, nursing homes, and schools. Part of this process includes determining the threat that hazardous waste sites pose to each institution. At this time the insurance company wants to consider only those institutions that are not at risk of contamination. The GIS consultant, hired by the insurance company, has been commissioned to locate all institutions that are outside a five-mile radius of a hazardous waste storage facility.

The sensitive_areas table contains several columns that describe the threatened institutions in addition to the zone column, which stores the institutions' polygon geometries.

create table sensitive_areas (id integer,
                              name varchar(128),
                              size float,
                              type varchar(10),
                              zone ST_Polygon);

The hazardous_sites table stores the identity of the sites in the site_id and name columns, while the actual geographic location of each site is stored in the location point column.

create table hazardous_sites (site_id integer,
name varchar(128),
location ST_Point);

The select statement lists the names of all sensitive areas that are outside the five-mile radius of a hazardous waste site. You could use the intersects function instead in this query by equating the result of the function to 'f',because intersects and disjoint return the opposite results.

select sa.name
from sensitive_areas sa, hazardous_sites hs
where ST_Disjoint (ST_Buffer(hs.location,(5*5280)),sa.zone) = 't';

The nursing home is the only sensitive area for which the disjoint function will return t (TRUE) when comparing sensitive sites to the five-mile radius of the hazardous waste sites. The ST_Disjoint function returns 't' whenever two geometries do not intersect in any way.

The city engineer needs a list of all buildings within one foot of any lot line.

The building_id column of the buildingfootprints table uniquely identifies each building. The lot_id column identifies the lot each building belongs to. The footprints multipolygon stores the geometry of each building's footprint.

create table buildingfootprints (building_id integer,
lot_id integer,
footprint ST_Multipolygon);

The lots table stores the lot_id, which uniquely identifies each lot, and the lot ST_MultiPolygon that contains the lot geometry.

The query returns a list of building IDs that are within one foot of their lot lines. The distance function performs a spatial join on the footprints and lot ST_MultiPolygons. However, the equijoin between bf.lot_id and lots.lot_id ensures that only the ST_MultiPolygons belonging to the same lot are compared by the ST_Distance function.

select bf.building_id
from buildingfootprints bf, lots
where bf.lot_id = lots.lot_id
and ST_Distance(bf.footprint,ST_Boundary(lots.lot)) <= 1.0;

The endpoint_test table stores the gid integer column, which uniquely identifies each row and the ln1 ST_LineString column that stores linestrings.

The INSERT statements insert linestrings into the endpoint_test table. The first linestring doesn't have Z coordinates or measures, while the second one does.

insert into endpoint_test values(
1,
ST_LineFromText('linestring(10.02 20.01,23.73 21.92,30.10 40.23)',1)
)

insert into endpoint_test values(
2,
ST_LineFromText('linestring zm(10.02 20.01 5.0 7.0,23.73 21.92 6.5 7.1,30.10 40.23 6.9 7.2)',1)
)

The query lists the gid column with the output of the ST_EndPoint function. The ST_EndPoint function generates a ST_Point geometry.

GID           Endpoint
---------- -------------------------------------------------------------
         1 ST_POINT (30.10 40.23)
         2 ST_POINT ZM (30.10 40.23 6.9 7.2)

The envelope_test table's geotype column stores the name of the geometry subclass stored in the g1 ST_Geometry column.

The INSERT statements insert each geometry subclass into the envelope_test table.

insert into envelope_test values(
'Point',
ST_PointFromText('point(10.02 20.01)',1)
)

insert into envelope_test values(
'Linestring',
ST_LineFromText('linestring(10.01 20.01, 10.01 30.01, 10.01 40.01)', 1)
)

insert into envelope_test values(
'Linestring',
ST_LineFromText('linestring(10.02 20.01,10.32 23.98,11.92 25.64)', 1)
)

insert into envelope_test values(
'Polygon',
ST_PolyFromText('polygon((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01))',1)
)

insert into envelope_test values(
'Multipoint',
ST_MpointFromText('multipoint(10.02 20.01,10.32 23.98,11.92 25.64)', 1)
)

insert into envelope_test values(
'Multilinestring',
ST_MlineFromText('multilinestring((10.01 20.01,20.01 20.01,30.01 20.01),(30.01 20.01,40.01 20.01,50.01 20.01))',1)
)

insert into envelope_test values(
'Multilinestring',
ST_MlineFromText('multilinestring((10.02 20.01,10.32 23.98,11.92 25.64),(9.55 23.75,15.36 30.11))',1)
)

insert into envelope_test values(
'Multipolygon',
ST_MpolyFromText('multipolygon(((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01)),((51.71 21.73,73.36 27.04,71.52 32.87,52.43 31.90,51.71 21.73)))',1)
)

The query lists the subclass name and its envelope. Because the ST_Envelope function returns a point, linestring, or polygon.

select geotype, ST_Envelope(g1)Envelope
from envelope_test
GEOTYPE      Envelope
--------------- --------------------------------------------------------
Point           ST_POINT(10.02 20.01)
Linestring      ST_LINESTRING (10.01 20.01,10.01 40.01)
Linestring      ST_POLYGON((10.02 20.01, 11.92 20.01,11.92 25.64, 10.02 25.64, 10.02 20.01))
Polygon         ST_POLYGON((10.02 20.01, 25.02 20.01, 25.02 35.64, 10.02 35.64,10.02 20.01))
Multipoint      ST_POLYGON((10.02 20.01,11.92 20.01,11.92 25.64,10.02 25.64,10.02 20.01))
Multilinestring ST_LINESTRING (10.01 20.01, 50.01 20.01)
Multilinestring ST_POLYGON ((9.55 20.01, 15.36 20.01, 15.36 30.11, 9.55 30.11, 9.55 20.01))
Multipolygon    ST_POLYGON ((10.02 20.01,73.36 20.01,73.36 35.64,10.02 35.64,10.02 20.01))

Returns t (TRUE) if the envelopes of two geometries intersect; otherwise, it returns f (FALSE).

The get_window function retrieves the display window's coordinates from the application. The window parameter is actually a polygon shape structure containing a string of coordinates that represent the display polygon. The SE_PolygonFromShape function converts the display window shape into a Spatial DataBlade polygon that the ST_EnvelopesIntersect function uses as its intersection envelope. All sensitive_areas zone polygons that intersect the interior or boundary of the display window are returned. Each polygon is fetched from the result set and passed to the draw_polygon function.

/* Get the display window coordinates as a polygon shape. */
get_window(&window)

/* Create the SQL expression. The envelopesintersect function limits
the result set to only those zone polygons that intersect the envelope
of the display window. */

strcpy(sqlstmt,"select SE_AsShape(zone) from sensitive_areas
where ST_EnvelopesIntersect(zone,SE_PolyFromShape(?,1))");

/* Set blob_len to the byte length of a 5-point shape polygon. */
blob_len = 128;

/* Bind the shape parameter.*/
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BLOB, blob_len, 0, window, blob_len, &pcbvalue1);

/* Assign the results of the query (the Zone polygons) to the fetched_binary variable. */
SQLBindCol (hstmt, 1,SQL_C_Binary, fetched_binary, 100000, &ind_blob);

/* Fetch each polygon within the display window and display it.*/
while (SQL_SUCCESS ==(rc = SQLFetch(hstmt)))
draw_polygon(fetched_binary);

Compares two geometries and returns t (TRUE) if the geometries are identical; otherwise, it returns f (FALSE).

The city GIS technician suspects that some of the data in the buildingfootprints table was somehow duplicated. To alleviate his concern, he queries the table to determine if any of the footprints multipolygons are equal.

The buildingfootprints table is created with the following statement. The building_id column uniquely identifies the buildings, the lot_id identifies the building's lot, and the footprint multipolygon stores the building's geometry.

create table buildingfootprints (building_id integer,
lot_id integer,
footprint ST_MultiPolygon);

The buildingfootprints table is spatially joined to itself by the equal predicate, which returns 1 whenever it finds two multipolygons that are equal. The bf1.building_id<>bf2.building_id condition eliminates the comparison of a geometry to itself.

select bf1.building_id, bf2.building_id
from buildingfootprints bf1, buildingfootprints bf2
where ST_Equals(bf1.footprint,bf2.footprint)= 't'
and bf1.building_id <> bf2.building_id;

An ornithologist, wishing to study the bird population on several south sea islands, knows that the feeding zone of the bird species she is interested in is restricted to the shoreline. As part of her calculation of the island's carrying capacity the ornithologist requires the islands' perimeters. Some of the islands are so large they have several ponds on them. However, the shoreline of the ponds are inhabited exclusively by another more aggressive bird species. Therefore, the ornithologist requires the perimeter of the exteriorring only of the islands.

The ID and name columns of the islands table identifies each island, while the land polygon column stores the island's geometry.

The ST_ExteriorRing function extracts the exterior ring from each island polygon as a linestring. The length of the linestring is calculated by the length function.The linestring lengths are summarized by the sum function.

The exterior rings of the islands 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.

The following C code fragment contains ODBC functions embedded with Spatial DataBlade SQL functions that insert data into the lots table.

The lots table was created with two columns: the lot_id, which uniquely identifies each lot, and the lot polygon column, which contains the geometry of each lot.

The SE_GeomFromShape function converts shapes into Spatial DataBlade geometry. The entire INSERT statement is copied into shp_sql. The INSERT statement contains parameter markers to accept the lot_id and lot data, dynamically.

/* Create the SQL insert statement to populate the lot ID and the
lot ST_MultiPolygon. The question marks are parameter markers that
indicate the lot_id and lot values that will be retrieved at
run time. */
strcpy (shp_sql,"insert into lots (lot_id, lot) values(?, SE_GeomFromShape(?,1))");

/* Allocate memory for the SQL statement handle and associate the statement handle with the connection handle. */
rc = SQLAllocStmt(handle, &hstmt);

/* Prepare the SQLstatement for execution. */
rc = SQLPrepare(hstmt, (unsigned char *)shp_sql, SQL_NTS);

/* Bind the integerkey value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0,&lot_id, 0, &pcbvalue1);

/* Bind the shape tothe second parameter. */
pcbvalue2 = blob_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, blob_len,0, shape_blob, blob_len, &pcbvalue2);