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);

Takes a well-known text representation and a spatial reference ID and returns a geometry object.

The geometry_test table contains the integer gid column, which uniquely identifies each row, and the g1 column, which stores the geometry.

The INSERT statements insert the data into the gid and g1 columns of the geometry_test table. The ST_GeomFromText function converts the text representation of each geometry into its corresponding Spatial DataBlade instantiable subclass.

insert into geometry_test values(
2,
ST_GeomFromText
('linestring (10.01 20.01, 10.01 30.01,10.01 40.01)',1)
)

insert into geometry_test values(
3,
ST_GeomFromText
('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15,
19.15 33.94, 10.02 20.01))',1)
)

insert into geometry_test values(
4,
ST_GeomFromText
('multipoint (10.02 20.01,10.32 23.98,11.92 25.64)',1)
)

insert into geometry_test values(
5,
ST_GeomFromText
('multilinestring((10.02 20.01, 10.32 23.98,
11.92 25.64),(9.55 23.75,15.36 30.11))',1)
)

insert into geometry_test values(
6,
ST_GeomFromText
('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)
)

Takes a well-known binary representation and a spatial reference ID to return a geometry object.

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 ST_GeomFromWKB function converts WKB representations into Spatial DataBlade geometry. The entire INSERT statement is copied into a wkb_sql char string.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(wkb_sql,"insert into lots (lot_id, lot) values(?, ST_GeomFromWKB (?,1))");

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

/* Prepare the SQL statement for execution. */
rc = SQLPrepare(hstmt, (unsigned char *)wkb_sql, SQL_NTS);

/* Bind the integer key 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);

Takes a collection and an integer index and returns the Nth ST_Geometry object in the collection.

ST_GeometryN (mpt1 ST_MultiPoint, index integer)
ST_GeometryN (mln1 ST_MultiLineString, index integer)
ST_GeometryN(mpl1 ST_MultiPolygon, index integer)

The city engineer wants to know which building footprints are all inside the first polygon of the lots ST_MultiPolygon.

The building_id column uniquely identifies each row of the buildingfootprints table. The lot_id column identifies the building's lot. The footprints column stores the buildings' geometries.

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

The query lists the buildingfootprints' building_id and lot_id for all buildingfootprints that area ll within the first lot polygon. The ST_GeometryN function returns a first lot polygon element in the ST_MultiPolygon.

select bf.building_id, bf.lot_id
from buildingfootprints bf, lots
where ST_Within (footprint, ST_GeometryN(lot,1)) = 't'
and bf.lot_id =lots.lot_id;

ST_Point
ST_LineString
ST_Polygon
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon

insert into ST_GeometryType_test values(
ST_GeomFromText('linestring(10.01 20.01, 10.01 30.01, 10.01 40.01)',1)
)

insert into geometrytype_test values(
ST_GeomFromText('polygon((10.02 20.01, 11.92 35.64, 25.02 34.15,
19.15 33.94, 10.02 20.01))',1)
)

insert into geometrytype_test values(
ST_GeomFromText('multipoint(10.02 20.01,10.32 23.98, 11.92 25.64)',1)
)

insert into geometrytype_test values(
ST_GeomFromText('multilinestring((10.02 20.01,10.32 23.98,
11.92 25.64),(9.55 23.75,15.36 30.11))',1)
)

insert into geometrytype_test values(
ST_GeomFromText('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 geometry type of each subclass stored in the g1 geometry column.

Geometry_type
-----------------------
ST_Point
ST_LineString
ST_Polygon
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon

Returns the nth interior ring of a polygon as a 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. If the index exceeds the number of interior rings possessed by a polygon a NULL value is returned.

An ornithologist, wishing to study 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 shoreline of the lakes is inhabited exclusively by another more aggressive species. The ornithologist knows that for each island, if the perimeter of the ponds exceeds a certain threshold, the aggressive species will become so numerous it will threaten the passive seashore species. Therefore, the ornithologist requires the aggregated perimeter of the interior rings of the islands.

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 ID and name columns of the islands table identifies each island, while the land ST_Polygon column stores the island's geometry.

This 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 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"

#define USER "sdetest" /* your user name */
#define PASSWD "acid.rain" /* your user password */
#define DBNAME "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 memoryfor 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 INFORMIX ODBC driver and connect to the data source identified
by the database,user, and password.*/
rc = SQLConnect(handle,
(UCHAR *)DBNAME,
SQL_NTS,
(UCHAR *)USER,
SQL_NTS,
(UCHAR *)PASSWD,
SQL_NTS);
check_sql_err(handle, NULL, rc, "SQLConnect");
/* Allocate memory tothe 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 (interiorrings); */
strcpy (sql_stmt,"select id, ST_NumInteriorRing(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 ofST_NumInteriorRing(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 tothe SQL statement handle lake_cursor . */
rc = SQLAllocStmt(handle, &lake_cursor);
check_sql_err(handle, NULL, rc, "SQLAllocStmt");

/* Prepare the queryto get the length of an interior ring. For
efficiency, we only prepare this query once. */
strcpy (sql_stmt,"select ST_Length(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_idas 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 ofthe ST_Length(ST_InteriorRingN(land, cast
(? as integer)))as 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, getthe island ids and the number of lakes (interiorrings).*/
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 its lakes (interiorrings).*/
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( "\n TestComplete ...\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 ("%sERROR (%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);
}
}

Takes two ST_Geometry objects and returns the intersection set as a ST_Geometry object.

The fire marshal must obtain the areas of the hospitals, schools, and nursing homes intersected by the radius of a possible hazardous waste contamination.

The sensitive areas are stored in the sensitive_areas table that is created with the CREATE TABLE statement that follows. The zone column defined as a 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 with the CREATE TABLE statement that follows. The location column, defined as a point, stores a location that is the geographic center of each hazardous site.

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

The ST_Buffer function generates a five-mile buffer surrounding the hazardous waste site locations. The ST_Intersection function generates polygons from the intersection of the buffered hazardous waste sites and the sensitive areas. The ST_Area function returns the intersection polygons' area, which is summarized for all hazardous sites by the sum function.

The group by clause directs the query to aggregate the intersection areas by hazardous waste site ID.

select hs.name,sum(ST_Area(ST_Intersection(sa.zone, ST_Buffer(hs.location,(5* 5280)))::st_multipolygon))
from sensitive_areas sa, hazardous_sites hs
group by hs.site_id;

The circles represent the five-mile buffer polygons surrounding the hazardous waste sites. The intersection of these buffer polygons with the sensitive area polygons produces three polygons. The hospital in the upper left-hand corner is intersected twice, while the school in the lower right-hand corner is intersected only once.

Returns t (TRUE) if the intersection of two geometries doesn't result in an empty set; otherwise, returns f (FALSE).

The fire marshal wants a list of sensitive areas within a five-mile radius of a hazardous waste site.

The sensitive areas are stored in the sensitive_areas table created below. The zone column is defined as a polygon and stores the outline 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 with the CREATE TABLE statement that follows. 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 query returns a list of sensitive area and hazardous site names for sensitive areas that intersect the five-mile buffer radius of the hazardous sites.

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

Returns t (TRUE) if theST_Geometry object has three-dimensional coordinates; otherwise, returns f (FALSE).

The threed_test table is created with integer gid and g1 ST_Geometry columns.

The INSERT statements insert two points into the threed_test table. The first point does not contain Z coordinates, while the second does.

insert into threed_test values(
2,
ST_PointFromText('pointz (10.92 10.12 5)',1)
)

The query lists the contents of the gid column with the results of the SE_Is3d function. The function returns a f for the first row, which doesn't have a Z coordinate, and a t for the second row, which does.

Takes a ST_Linestring or ST_MultiLineString and returns t (TRUE) if it is closed; otherwise, it returns f (FALSE).

The INSERT statements insert two records into the closed_linestring table. The first record is not a closed linestring, while the second is.

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

insert into closed_linestring values(
ST_LineFromText('linestring(10.02 20.01, 11.92 35.64, 25.02 34.15, 19.15 33.94, 10.02 20.01)',1)
)

The query returns the results of the isclosed function. The first row returns a 0 because the linestring is not closed, while the second row returns a 1 because the linestring is closed.

The closed_mlinestring table is created with a single ST_MultiLineString column.

The INSERT statements insert a ST_MultiLineString record that is not closed and another that is.

insert into closed_mlinestring values(
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 intoclosed_mlinestring values(
ST_MlineFromText('multilinestring((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 results of the ST_IsClosed function. The first row returns 0 because the multilinestring is not closed. The second row returns 1 because the multilinestring stored in them ln1 column is closed. A multilinestring is closed if all of its linestring elements are closed.

The CREATE TABLE statement below creates the empty_test table with geotype, which stores the data type of the subclasses that are stored in the g1 ST_Geometry column.

The INSERT statements insert two records for the geometry subclasses point, linestring, and polygon: one that is empty and one that is not.

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

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

insert into empty_test values(
'Linestring', ST_LineFromText('linestring empty',1)
)

insert into empty_testvalues(
'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)
)

The query returns the geometry type from the geotype column and the results of the ST_IsEmpty function.

select geotype, ST_IsEmpty(g1) Is_it_empty
from empty_test
GEOTYPE Is_it_empty
-------------------------------
Point f
Point t
Linestring f
Linestring t
Polygon f
Polygon t

Returns t (TRUE) if the ST_Geometry object has measures; otherwise, returns f (FALSE).

The measure_test table is created with two columns: a smallint column, gid, uniquely identifies the rows while g1, a ST_Geometry column, stores the ST_Point geometries.

The INSERT statements insert two records into the measure_test table. The first record stores a point that doesn't have a measure, while the second record does have a measure value.

insert into measure_test values(
2,
ST_PointFromText('pointm (10.92 10.12 5)',1)
)

The query lists the gid column and the results of the SE_IsMeasured function. The SE_IsMeasured function returns a 0 for the first row because the point does not have a measure, and it returns a1 for the second row because the point does have measures.

Takes a ST_LineString and returns t (TRUE) if it is a ring (i.e., the ST_LineString is closed and simple); otherwise, it returns f (FALSE).

The ring_linestring table is created with a single ST_LineString column called ln1.

The INSERT statements insert three linestrings into the ln1 column. The first row contains a linestring that's not closed and isn't a ring. The second row contains a closed and simple linestring that is a ring. The third row contains a linestring that is closed but not simple because it intersects its own interior. It's also not a ring.

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

insert into ring_linestring values(
ST_LineFromText ('linestring(10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.94,10.02 20.01)', 1)
)

insert into ring_linestring values(
ST_LineFromText('linestring(15.47 30.12,20.73 22.12,10.83 14.13,
16.45 17.24,21.56 13.37,11.23 22.56,19.11 26.78,15.47 30.12)', 1)
)

The query returns the results of the isring function. The first and third rows return 0 because the linestrings aren't rings while the second row returns1 because it is a ring.

Returns t (TRUE) if the geometry object is simple; otherwise, it returns f (FALSE).

The table issimple_test is created with two columns. The pid column is a smallint containing the unique identifier for each row. The g1 ST_Geometry column stores the simple and nonsimple geometry samples.

The INSERT statements insert two records into the issimple_test table. The first is a simple

linestring because it doesn't intersect its interior. The second is nonsimple because it does intersect its interior.

insert into issimple_test values(
1, ST_LineFromText('linestring (10 10, 20 20, 30 30)',1)
)

insert into issimple_test values(
2,ST_LineFromText('linestring (10 10,20 20,20 30,10 30,10 20,
20 10)',1)
)

The query returns the results of the issimple function. The first record returns t because the linestring is simple, while the second record returns f because the linestring is not simple.

Takes an ST_Geometry and returns t (TRUE) if it is valid, otherwise it returns f (FALSE). A geometry inserted into an Informix database with the Informix Spatial DataBlade registered, will always be valid because the Spatial DataBlade always validates spatial data before accepting it. Other DBMS vendors may not validate the input, but require the application to do so.

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

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

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

INSERT INTO valid_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 valid_test VALUES (
'MultiPoint',ST_MPointFromText('multipoint (10.02 20.01,10.32 23.98,11.9225.64)',1)
)

INSERT INTO valid_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 valid_test VALUES (
'MultiPolygon', ST_MPolyFromText ('multipolygon (((10.02 20.01,11.92 35.64,25.02 34.15,19.15 33.64,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 with the dimension of that geotype.

GEOTYPE Valid
------------------- -------------
Point t
Linestring t
Polygon t
MultiPoint t
MultiLineString t
MultiPolygon t

6 record(s) selected.

A local ecologist studying the migratory patterns of the salmon population in the county's waterways wants the length of all stream and river systems within the county.

The waterways table is created with the ID and name columns which identify each stream and river system stored in the table. The water column is a multilinestring because the river and stream systems are often an aggregate of several linestrings.

The query returns the name of each system along with the length of the system generated by the length function.

The figure displays the river and stream systems that lie within the county boundary.

Takes a shape of type point and a spatial reference ID to return a ST_Linestring.

This code fragment populates the sewerlines table with the unique ID, class,and geometry of each sewer line.

The sewer lines table is created with three columns. The first column, sewer_id,uniquely identifies each sewer line. The integer class column identifies the type of sewer line, generally associated with the line's capacity. The sewer ST_LineString column stores the sewer line geometry.

create table sewerlines ( sewer_id integer,
class integer,
sewer ST_LineString);

/* Create the SQL insert statement to populate the sewer_id, class, and the sewer ST_LineString. The question marks are parameter markers that indicate the sewer_id, class, and sewer geometry values that will be retrieved at runtime. */

strcpy(shp_sql,"insert into sewerlines (sewer_id,class,sewer) values(?,?,SE_LineFromShape(?,1))");

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

/* Prepare the SQL statement 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,&sewer_id, 0, &pcbvalue1);

/* Bind the integer class value to the second parameter. */
pcbvalue2 = 0;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0,&sewer_class, 0, &pcbvalue2);

/* Bind the shape tothe third parameter. */
pcbvalue3 = blob_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, blob_len,0, sewer_shape, blob_len, &pcbvalue3);

Takes a well-known text representation of type ST_LineString and a spatial reference ID and returns a ST_LineString.

The linestring_test table is created with a single ln1 ST_LineString column.

The INSERT statement inserts a ST_LineString into the ln1 column using the ST_LineFromText function.

insert into linestring_test values(
ST_LineFromText('linestring(10.01 20.03,20.94 21.34,35.93 19.04)',1)
)

Takes a well-known binary representation of type ST_LineString and a spatial reference ID, returning a ST_LineString.

This code fragment populates the sewerlines table with the unique ID, class, and geometry of each sewer line.

The sewer lines table is created with three columns. The first column, sewer_id, uniquely identifies each sewer line. The integer class column identifies the type of sewer line, generally associated with the line's capacity. The sewer line string column stores the sewer lines' geometries.

/* Create the SQLinsert statement to populate the sewer_id, size class and
sewer linestring. The question marks are parameter markers that
indicate the sewer_id, class, and sewer geometry values that will be
retrieved at runtime. */

strcpy(wkb_sql,"insert into sewerlines (sewer_id,class,sewer) values(?,?,ST_LineFromWKB(?,1))");

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

/* Prepare the SQL statement for execution. */
rc = SQLPrepare(hstmt, (unsigned char *)wkb_sql, SQL_NTS);

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

/* Bind the integer class value to the second parameter. */
pcbvalue2 = 0;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0,&sewer_class, 0, &pcbvalue2);

/* Bind the shape to the third parameter. */
pcbvalue3 = blob_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, blob_len,0, sewer_wkb, blob_len, &pcbvalue3);

Takes a geometry object and a measure to return as a ST_MultiPoint the set of points found at the measure.

The locatealong_test table is created with two columns: the gid column uniquely identifies

The INSERT statements insert two rows. The first is a multilinestring, while the second is a multipoint.

insert into locatealong_test values(
1,
ST_MlineFromText('multilinestring m ((10.29 19.23 5,23.82 20.29 6,30.19 18.47 7,45.98 20.74 8),(23.82 20.29 6,30.98 23.98 7,42.92 25.98 8))', 1)
)

insert intolocatealong_test values(
2,
ST_MpointFromText('multipoint m (10.29 19.23 5,23.82 20.29 6,30.19 18.47 7,45.98 20.74 8,23.82 20.29 6,30.98 23.98 7,42.92 25.98 8)', 1)
)

In this query the SE_LocateAlong function finds points whose measure is 6.5. The first row returns a ST_MultiPoint containing two points. However, the second row returns an empty point. For linear features (geometry with a dimension greater than 0), SE_LocateAlong can interpolate the point, but for multipoints the target measure must match exactly.

GID Geometry
--------------------------------------------------------------------------
1 ST_MULTIPOINT M(27.01 19.38 6.5, 27.4 22.14 6.5)
2 ST_POINT EMPTY

In this query the locatealong function returns a multipoint for both rows. The target measure of 7 matches measures in the multilinestring and multipoint source data.

GID Geometry
-------------------------------------------------------------------------
1 ST_MULTIPOINT M (30.19 18.47 7, 30.98 23.98 7)
2 ST_MULTIPOINT M (30.19 18.47 7, 30.98 23.98 7)

SE_LocateBetween takes a ST_Geometry object and two measure locations and returns a ST_LineString that represents the set of disconnected paths between the two measure locations.

SE_LocateBetween (g1 ST_Geometry, fm double precision, tm double precision)

The locatebetween_test table is created with two columns: the gid integer column uniquely identifies each row, while the g1 ST_MultiLineString stores the sample geometry.

The INSERT statements insert two rows into the locatebetween_test table. The first row is a ST_MultiLineString and the second is a ST_MultiPoint.

insert into locatebetween_test values(
1,
ST_MlineFromText('multilinestringm ((10.29 19.23 5,23.82 20.29 6, 30.19 18.47 7,45.98 20.74 8),(23.82 20.29 6,30.98 23.98 7,42.92 25.98 8))',1)
)

insert into locatebetween_test values(
2,
MpointFromText('multipointm (10.29 19.23 5,23.82 20.29 6,30.19 18.47 7,45.98 20.74 8,23.82 20.29 6,30.98 23.98 7,42.92 25.98 8)', 1)
)

The SE_LocateBetween function below locates measures lying between measures 6.5 and 7.5, inclusively. The first row returns a ST_Multilinestring containing several LineStrings. The second row returns a ST_MultiPoint because the source data was ST_MultiPoint. When the source data has a dimension of 0 (point or multipoint) an exact match is required.

GID Geometry
---- ------------------------------------------------------------------------
1 ST_ MULTILINESTRING M (27.01 19.38 6.5, 31.19 18.47 7, 38.09 19.61 7.5),
(27.4 22.14 6.5, 30.98 23.98 7, 36.95 24.98 7.5)
2 ST_MULTIPOINT M(30.19 18.47 7, 30.98 23.98 7)

The m_test table is created with the gid integer column, which uniquely identifies the row, and the pt1 point column that stores the sample geometry.

The INSERT statements insert a point with measures and a point without measures.

insert into m_test values(
2,
ST_PointFromText('pointz m (10.02 20.01 5.0 7.0)', 1)
)

In this query the m function lists the measure values of the points. Because the first point doesn't have measures, the m function returns NULL.

SE_MlineFromShape creates a ST_MultiLineString from a multilinestring shape and a spatial reference ID.

This code fragment populates the waterways table with a unique ID, a name, and a water multilinestring.

The waterways table is created with the ID and name columns that identify each stream and river system stored in the table. The water column is a ST_MultiLineString because the river and stream systems are often an aggregate of several linestrings.

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

/* Create the SQLinsert statement to populate the id, name, and water. The question marks are parameter markers that
indicate the ID, name, and water values that will be retrieved at run time.*/

strcpy(shp_sql,"insert into waterways (id,name,water) values(?,?, ST_MlineFromShape(?,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 integer ID value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0,&id, 0, &pcbvalue1);

/* Bind the varcharname value to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, name_len,0, name, name_len, &pcbvalue2);

/* Bind the shape tothe third parameter. */
pcbvalue3 = blob_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, blob_len,0, water_shape, blob_len, &pcbvalue3);

Takes a well-known text representation of type ST_MultiLineString and a spatial reference ID and returns a ST_MultiLineString.

The mlinestring_test is created with the gid smallint column that uniquely identifies the row and the ml1 ST_MultiLineString column.

The INSERT statement inserts the ST_MultiLineString with the ST_MlineFromText function.

insert intomlinestring_test values(
1,
ST_MlineFromText
('multilinestring((10.01 20.03,10.52 40.11,30.29 41.56,
31.78 10.74),(20.93 20.81, 21.52 40.10))', 1)
)

Creates a ST_MultiLineString from a well-known binary representation of type ST_MultiLineString and a spatial reference ID.

This code fragment populates the waterways table with a unique ID, a name, and a water ST_MultiLineString.

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

/* Create the SQLinsert statement to populate the integer, varchar, and
ST_Multilinestring. The question marks are parameter markers that
indicate the ID, name, and water values that will be retrieved at
run time. */
strcpy(shp_sql,"insert into waterways (id,name,water) values(?,?,ST_MlineFromWKB(?,1))");

/* Allocate memoryfor 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 integerid value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0,&id, 0, &pcbvalue1);

/* Bind the varchar name value to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, name_len,0, name, name_len, &pcbvalue2);

/* Bind the shape to the third parameter. */
pcbvalue3 = blob_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, blob_len,0, water_shape, blob_len, &pcbvalue3);

Takes a shape of type ST_MultiPoint and a spatial reference ID to return a ST_MultiPoint.

The species_sitings table is created with three columns. The species and genus columns uniquely identify each row, while the sitings ST_MultiPoint stores the locations of the species sitings.

create table species_sitings (
species varchar(32),
genus varchar(32),
sitingsST_MultiPoint);

/* Create the SQLinsert statement to populate the species, genus, and
sitings. The question marks are parameter markers that indicate the ID,
name, and water values that will be retrieved at run time. */
strcpy(shp_sql,"insert into species_sitings (species,genus,sitings) values(?,?,SE_MpointFromShape(?,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 varchar species value to the first parameter. */
pcbvalue1 = species_len;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,species_len, 0, species, species_len, &pcbvalue1);

/* Bind the varchar genus value to the second parameter. */
pcbvalue2 =genus_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, genus_len,0, name, genus_len, &pcbvalue2);

/* Bind the shape to the third parameter. */
pcbvalue3 = blob_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB,sitings_len, 0, sitings_shape, sitings_len, &pcbvalue3);

Creates a ST_Multipoint from a well-known text representation of type ST_MultiPoint and a spatial reference ID.

The multipoint_test table is created with the single ST_MultiPoint mpt1 column.

The INSERT statement inserts a multipoint into the mpt1 column using the ST_MpointFromText function.

insert into multipoint_test values(
1,
ST_MpointFromText('multipoint(10.01 20.03,10.52 40.11,30.29 41.56,
31.78 10.74)',1)
)

Creates a ST_MultiPoint from a well-known binary representation of type ST_MultiPoint and a spatial reference ID.

The species_sitings table is created with three columns. The species and genus columns uniquely identify each row, while the sitings ST_MultiPoint stores the locations of the species sitings.

create table species_sitings (
species varchar(32),
genus varchar(32),
sitings ST_MultiPoint);

/* Create the SQLinsert statement to populate the species, genus, and
sitings. The question marks are parameter markers that
indicate the species,genus and sitings values that will be retrieved at
run time. */
strcpy(wkb_sql,"insert into species_sitings (species, genus, sitings) values(?,?,ST_MpointFromWKB(?,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 varchar species value to the first parameter. */
pcbvalue1 =species_len;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR,species_len, 0, species, species_len, &pcbvalue1);

/* Bind the varchargenus value to the second parameter. */
pcbvalue2 =genus_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, genus_len,0, name, genus_len, &pcbvalue2);

/* Bind the shape tothe third parameter. */
pcbvalue3 =sitings_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB,sitings_len, 0, sitings_wkb, sitings_len, &pcbvalue3);

Takes a shape of type multipolygon and a spatial reference ID to return a ST_MultiPolygon.

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

/* Create the SQLinsert statement to populate the lot_id and lot. 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_MpolyFromShape (?,1))");
/* Allocate memory forthe 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 lot_id integer value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTEGER,
SQL_INTEGER, 0, 0,&lot_id, 0, &pcbvalue1);

/* Bind the lot shape to the second parameter. */
pcbvalue2 = lot_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, lot_len, 0,lot_shape, lot_len, &pcbvalue2);

Takes a well-known text representation of type ST_MultiPolygon and a spatial reference ID and returns a ST_MultiPolygon.

The multipolygon_test table is created with the single ST_MultiPolygon mpl1 column.

The INSERT statement inserts a ST_MultiPolygon into the mp11 column using the ST_MpolyFromText function.

insert into multipolygon_test values(
ST_MpolyFromText('multipolygon(((10.01 20.03,10.52 40.11,30.29 41.56,
31.78 10.74,10.01 20.03),(21.23 15.74,21.34 35.21,28.94 35.35,
29.02 16.83,21.23 15.74)),((40.91 10.92,40.56 20.19,50.01 21.12,
51.34 9.81,40.91 10.92)))',1)
)

Takes a well-known binary representation of type ST_MultiPolygon and a spatial reference ID to return a ST_MultiPolygon.

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

/* 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 *)wkb_sql, SQL_NTS);

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

/* Bind the lot shape to the second parameter. */
pcbvalue2 = lot_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, lot_len, 0,lot_wkb, lot_len, &pcbvalue2);

Takes a collection and returns the number of geometries in the collection.

ST_NumGeometries(mpt1 ST_MultiPoint)
ST_NumGeometries(mln1 ST_MultiLineString)
ST_NumGeometries(mpl1 ST_MultiPolygon)

The city engineer needs to know the number of distinct buildings associated with each building footprint.

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 query lists the building_id that uniquely identifies each building and the number of buildings in each footprint with the ST_NumGeometries function.

select building_id, ST_NumGeometries(footprint)"Number of buildings"
from buildingfootprints;

An ornithologist wishes to study a bird population on several south sea islands. She wants to identify which islands contain one or more lakes because the bird species she is interested in feeds only in freshwater lakes.

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

Because interior rings represent the lakes, the ST_NumInteriorRing function will list only those islands that have at least one interior ring.

The numpoints_test table is created with the geotype column, which contains the ST_Geometry type stored in the g1 geometry column.

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

insert into numpoints_test values(
'linestring',
ST_LineFromText('linestring(10.02 20.01, 23.73 21.92)',1)
)

insert into numpoints_test values(
'polygon',
ST_PolyFromText('polygon((10.02 20.01,23.73 21.92,24.51 12.98,
11.64 13.42,10.02 20.01))',1)
)

GEOTYPE Number_of_points
----------- -----------------
point 1
linestring 2
polygon 5

ST_OrderingEquals compares two geometries and t (TRUE) if the geometries are equal and the coordinates are in the same order; otherwise it returns f (FALSE).

The following CREATE TABLE statement creates the LINESTRING_TEST table, which has two ST_LineString columns L1 and L2.

CREATE TABLE LINESTRING_TEST (
lid integer,
ln1 ST_LineString,
ln2 ST_LineString);

The following INSERT statement inserts two ST_LineString into ln1 and ln2 that are equal and have the same coordinate ordering.

INSERT INTO LINESTRING_TEST VALUES (
1,
ST_LineFromText ('linestring(10.01 20.02, 21.50 12.10)',1),
ST_LineFromText ('linestring(10.01 20.02, 21.50 12.10)',1)
);

The following INSERT statement inserts two ST_LineStrings into ln1 and ln2 that are equal but do not have the same coordinate ordering.

INSERT INTO LINESTRING_TEST VALUES (
2,
ST_LineFromText ('linestring (10.01 20.02, 21.50 12.10)',1),
ST_LineFromText ('linestring (21.50 12.10, 10.01 20.02)',1)
);

The following SELECT statement and corresponding result set shows how the ST_Equals function returns t (TRUE) regardless of the order of the coordinates. The ST_OrderEquals function returns f (FALSE) if the geometries are not both equal and have the same coordinate ordering.

SELECT lid, ST_Equals(ln1, ln2) Equals, ST_OrderEquals(ln1,ln2) OrderEquals
FROM linestring_test;
lid Equals OrderEquals
--- ---------- -----------
1 t t
2 t f

ST_Overlaps takes two ST_Geometry objects and returns t (TRUE) if the intersection of the objects results in a ST_Geometry object of the same dimension but not equal to either source object; otherwise,it returns f (FALSE).

The county supervisor needs a list of hazardous waste sites whose five-mile radius overlaps sensitive areas.

The sensitive_areas table contains several columns that describe the threatened institutions in addition to the zone column, which stores the institutions' ST_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);

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

The hospital and the school overlap the five-mile radius of the county's two hazardous waste sites, while the nursing home does not.

An ecologist studying shoreline birds needs to determine the shoreline for the lakes within a particular area. The lakes as stored as ST_Multipolygon in the WATERBODIES table was created with the following CREATE TABLE statement.

In the following SELECT statement, the ST_Perimeter function returns the perimeter surrounding each body of water, while the SUM function aggregates the perimeters to return their total.

ST_Point returns an ST_Point, given an x-coordinate, y-coordinate, and spatial reference ID.

The following CREATE TABLE statement creates the POINT_TEST table, which has a single point column, PT1.

The ST_Point function converts the point coordinates into a ST_Point geometry before the INSERT statement insert sit into the PT1 column.

SE_PointFromShape creates a ST_Point from a shape of type point and a spatial reference ID.

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

/* Create the SQLinsert statement to populate the site_id, name, and
location. The question marks are parameter markers that indicate the
site_id, name, and location values that will be retrieved at run time. */
strcpy (shp_sql,"insertinto hazardous_sites (site_id, name, location) values(?,?, SE_PointFromShape(?,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 site_idinteger value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTEGER,
SQL_INTEGER, 0, 0,&site_id, 0, &pcbvalue1);

/* Bind the namevarchar value to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0, name,0, &pcbvalue2);

/* Bind the location shapeto the third parameter. */
pcbvalue3 =location_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB,location_len, 0, location_shape, location_len, &pcbvalue3);

ST_PointFromText takes a well-known text representation of type point and a spatial reference ID and returns a point.

The ST_PointFromText function converts the point text coordinates to the point format before the INSERT statement inserts the point into the pt1 column.

insert into point_test values(
ST_PointFromText('point(10.0120.03)', 1)
)

ST_PointFromWKB takes a well-known binary representation of type ST_Point and a spatial reference ID to return a ST_Point.

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

/* Create the SQLinsert statement to populate the site_id, name, and
location. The question marks are parameter markers that indicate the
site_id, name and location values that will be retrieved at run time. */
strcpy(wkb_sql,"insert into hazardous_sites (site_id, name, location)values(?,?,ST_PointFromWKB(?, 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 *)wkb_sql, SQL_NTS);

/* Bind the site_idinteger value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTEGER,
SQL_INTEGER, 0, 0,&site_id, 0, &pcbvalue1);

/* Bind the name varcharvalue to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0, name,0, &pcbvalue2);

/* Bind the locationshape to the third parameter. */
pcbvalue3 =location_len;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB,location_len, 0, location_wkb, location_len, &pcbvalue3);

ST_PointN takes a ST_LineString and an integer index and returns a point that is the nth vertex in the ST_LineString's path.

The pointn_test table is created with the gid column, which uniquely identifies each row, and the ln1 ST_LineString column.

The INSERT statements insert two linestring values. The first linestring doesn't have Z coordinates or measures, while the second linestring has both.

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

insert intopointn_test values(
2,
ST_LineFromText('linestringzm (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 and the second vertex of each linestring. The first row results in a ST_Point without a Z coordinate or measure, while the second row results in a ST_Point with a Z coordinate and a measure. The ST_PointN function will also include a Z coordinate or measure value if they exist in the source linestring.

GID The_2ndvertex
--- -------------------------------------------------------------------
1 ST_POINT (23.73 21.92)
2 ST_POINT ZM (23.73 21.92 7 7.1)

ST_PointOnSurface takes a ST_Polygon or ST_MultiPolygon and returns a ST_Point guaranteed to lie on its surface.

The city engineer wishes to create a label point for each building's footprint.

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 ST_PointOnSurface function generates a point that is guaranteed to be on the surface of the buildingfootprints. The ST_PointOnSurface function returns a point that the SE_AsShape function converts to a shape for use by the application.

SE_PolyFromShape returns a ST_Polygon from a shape of type polygon and a spatial reference ID.

The program fragment populates the sensitive_areas table. The question marks represent parameter markers for the ID, name, size, type, and zone values that will be retrieved at run time.

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 tablesensitive_areas (id integer,
name varchar(128),
size float,
type varchar(10),
zone ST_Polygon);

/* Create the SQLinsert statement to populate the ID, name, size, type, and
zone. The question marks are parameter markers that indicate the
ID, name, size, type, and zone values that will be retrieved at run
time.*/

strcpy(shp_sql,"insert into sensitive_areas (id, name, size, type, zone)values(?,?,?,?,SE_PolyFromShape(?, 1))");

/* Allocate memory for the SQL statement handle and associate
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 idinteger value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTEGER,
SQL_INTEGER, 0, 0,&site_id, 0, &pcbvalue1);

/* Bind the name varchar value to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0, name,0, &pcbvalue2);

/* Bind the size float to the third parameter. */
pcbvalue3 = 0;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT,
SQL_REAL, 0, 0,&size, 0, &pcbvalue3);

/* Bind the type varchar to the fourth parameter. */
pcbvalue4 = type_len;
rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR,type_len, 0, type, type_len, &pcbvalue4);

/* Bind the zone polygon to the fifth parameter. */
pcbvalue5 = zone_len;
rc = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, zone_len,0, zone_shp, zone_len, &pcbvalue5);

ST_PolyFromText takes a well-known text representation of type ST_Polygon and a spatial reference ID and returns a ST_Polygon.

The INSERT statement inserts a polygon into the polygon column using the polyfromtext function.

insert into polygon_test values(
1,ST_PolyFromText('polygon((10.01 20.03,10.52 40.11,30.29 41.56,
31.78 10.74,10.01 20.03))',1)
)

ST_PolyFromWKB takes a well-known binary representation of type ST_Polygon and a spatial reference ID to return a ST_Polygon.

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

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

/* Create the SQLinsert statement to populate the ID, name, size, type, and
zone. The question marks are parameter markers that indicate the id, name,
size, type, and zone values that will be retrieved at run time. */
strcpy(shp_wkb,"insert into sensitive_areas (id, name, size, type, zone)values(?,?,?,?,ST_PolyFromWKB(?, 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 *)wkb_sql, SQL_NTS);

/* Bind the id integer value to the first parameter. */
pcbvalue1 = 0;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_INTEGER,
SQL_INTEGER, 0, 0,&id, 0, &pcbvalue1);

/* Bind the name varchar value to the second parameter. */
pcbvalue2 = name_len;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, 0, 0, name,0, &pcbvalue2);

/* Bind the size float to the third parameter. */
pcbvalue3 = 0;
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT,
SQL_REAL, 0, 0,&size, 0, &pcbvalue3);

/* Bind the type varchar to the fourth parameter. */
pcbvalue4 = type_len;
rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR,type_len, 0, type, type_len, &pcbvalue4);

/* Bind the zone polygon to the fifth parameter. */
pcbvalue5 = zone_len;
rc = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, zone_len,0, zone_wkb, zone_len, &pcbvalue5);

ST_Polygon generates a ST_Polygon from a ring (a ST_linestring that is both simple and closed).

The following CREATE TABLE statement creates the POLYGON_TEST tables, which has a single column, P1.

The INSERT statement converts a ring(a ST_LineString that is both closed and simple) into a ST_Polygon and inserts it into the P1 column using the ST_LineFromText function within the ST_Polygon function.

INSERT INTO POLYGON_TEST VALUES (
ST_Polygon(ST_LineFromText('linestring(10.01 20.03, 20.94 21.34, 35.93 10.04,10.01 20.03)'))
)

ST_Relate compares two geometries and returns t (TRUE) if the geometries meet the conditions specified by the DE-9IM pattern matrix string; otherwise, f(FALSE) is returned.

A DE-9IM pattern matrix is a device for comparing geometries. These are serveral types of such matrices. There are several types of such matrices. For example, the equals pattern matrix will tell you if any two geometries are equal.

In this example, an equals pattern matrix, shown below is read left to right, and top to bottom into the string("T*F**FFF*").

Next, the table RELATE_TEST is created with the following CREATE TABLE statement.

CREATE TABLE RELATE_TEST (g1 ST_Geometry, g2 ST_Geometry, g3 ST_Geometry);

The following INSERT statements insert a sample subclass into the RELATE_TEST table.

INSERT INTO RELATE_TEST VALUES (
ST_PointFromText('point(10.02 20.01)',1),
ST_PointFromText('point(10.02 20.01)',1),
ST_PointFromText('point(30.01 20.01)',1)
)

The following SELECT statement and the cooresponding result set lists the subclass name stored in the GEOTYPE column with the dimension of that geotype.

Constructs an ST_Geometry value given it's well-known binary representation and an SRID value of 0.

The following C code fragment contains ODBC functions embedded with Informix 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 ShapeToSQL function converts shapes into Informix SpatialBlade geometry. The entire INSERT statement is copied into shp_sql. The INSERT statement contains parameter markers to accept the LOT_ID and the lot data, dynamically.

Note: When the Informix Spatial DataBlade is installed, the default 0 SRID is inserted into the sde.spatial_references table. The DBA can edit the values of the 0 SRID if he/she wishes to do so.

/* Create the SQLinsert statement to populate the lot id and the lot 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(?, ShapeToSQL(?))");

/* 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. */
pcbvalue = 0;
rc = SQLBindParameter(hstmt, 1 SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,0, 0, &lot_id, 0,&pcbvalue1);

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

During the installation of the Spatial DataBlade module the following spatial_references table is created by this CREATE TABLE statement.

create table sde.spatial_references(
srid integer not null,
description varchar(64),
auth_name varchar(255),
auth_srid integer,
falsex float not null,
falsey float not null,
xyunits float not null,
falsez float not null,
zunits float not null,
falsem float not null,
munits float not null,
srtext char(2048) not null,
primary key (srid)constraint sde.sp_ref_pk
);

Before you can create geometry and insert it into a table you must enter the SRID of that geometry into the spatial_references table. This is a sample insert of a spatial reference system. The spatial reference system has an SRID value of 1, a false X,Y of(0,0), and its system units are 100. The Z coordinate and measure offsets are 0, while the Z coordinate and measure units are 1.

insert into spatial_references values(
1,
NULL,
NULL,
NULL,
0,
0,
100,
0,
1,
0,
1,
'UNKNOWN'
)

In the next statement a ST_Point geometry located at coordinate (10.01,50.76) is inserted into the geometry column g1. When the ST_Point geometry was created by the ST_PointFromText function it was assigned the SRID value of 1.

The ST_SRID function returns the spatial reference ID of the geometry just entered.

The startpoint_test table is created with the gid integer column, which uniquely identifies the rows of the table, and the ln1 ST_LineString column.

The INSERT statements insert the ST_LineStrings into the ln1 column. The first ST_LineString does not have Z coordinates or measures, while the second ST_LineString has both.

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

insert into startpoint_test values(
2,
ST_LineFromText('linestring zm (10.02 20.01 5 7,23.73 21.92 6.5 7.1,30.10 40.23 6.9 7.2)', 1)
)

The ST_StartPoint function extracts the first point of each ST_LineString. The first point in the list doesn't have a Z coordinate or a measure, while the second point has both because the source linestring does.

GID Startpoint
--- ------------------------------------------------------------------
1 ST_POINT(10.02 20.01)
2 ST_POINT ZM(10.02 20.01 5 7)

ST_SymDifference takes two geometry objects and returns a geometry object that is composed of the parts of the source objects that aren't common to both.

For a special report, the county supervisor must determine the area of sensitive areas and five-mile hazardous site radii that aren't intersected.

The sensitive_areas table contains several columns that describe the threatened institutions in addition to the zone column, which stores the institutions' ST_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 ST_Buffer function generates a five-mile buffer surrounding the hazardous waste site locations. The ST_SymDifference function returns the polygons of the buffered hazardous waste site polygons and the sensitive areas that don't intersect.

select sa.name,hs.name,
ST_Area(ST_SymmetricDiff(ST_buffer(hs.location,(5* 5280)), sa.zone)::st_multipolygon)
from hazardous_sites hs, sensitive_areas sa

The symmetric difference of the hazardous waste sites and the sensitive areas results in the subtraction of the intersected areas.

Returns t (TRUE) if none of the points common to both geometries intersect the interiors of both geometries; otherwise, it returns f (FALSE). At least one geometry must be a ST_Linestring, ST_Polygon, ST_Multilinestring, or ST_Multipolygon.

The GIS technician has been asked by his boss to provide a list of all sewer lines whose endpoints intersect another sewer line.

The sewer lines table is created with three columns. The first column, sewer_id, uniquely identifies each sewer line. The integer class column identifies the type of sewer line, generally associated with the line's capacity. The sewer ST_Linestring column stores the sewerline's geometry.

select s1.sewer_id,s2.sewer_id
from sewerlines s1,sewerlines s2
where ST_Touches(s1.sewer,s2.sewer) = 't',
order by 1,2;

Transforms the ST_Geometry into the spatial reference specified by the Spatial Reference ID.

The following CREATE TABLE statement creates the TRANSFORM_TEST table, which has two ST_LineString columns, ln1 and ln2.

The following INSERT statement inserts a ST_LineString into ln1 with an SRID of 102.

INSERT INTO TRANSFORM_TEST VALUES (ST_LineFromText('linestring(10.01 40.03,92.32 29.39)',102),NULL);

The ST_Transform function converts the ST_LineString of ln1 from the coordinate reference assigned to SRID 102 to the coordinate reference assigned to SRID 105. The following UPDATE statement stores the transformed ST_LineString in column ln2.

The sensitive_areas table contains several columns that describe the threatened institutions in addition to the zone column, which stores the institutions' ST_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 ST_Buffer function generates a five-mile buffer surrounding the hazardous waste site locations. The ST_Union function generates polygons from the union of the buffered hazardous waste site polygons and the sensitive areas. The ST_Area function returns the unioned polygon's area.

select sa.name,hs.name,
ST_Area(ST_Union(ST_Buffer(hs.location,(5* 5280)), sa.zone)::st_multipolygon)
from hazardous_sites hs, sensitive_areas sa;

Returns t (TRUE) if the first object is completely within 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 make sure that all the building footprints are completely inside their lots.

In both tables the multipolygon datatype stores the ST_Geometry of the buildingfootprints and the lots. The database designer selected ST_Multipolygons for both features because she realizes a lot can be separated by a natural feature such as a river, and a building footprint can be made up of 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 within a lot.

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

The city engineer realizes that although the first query will provide her with a list of all building_id that have footprints outside of 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_Within(footprint,lot)= 't'
and lots.lot_id<>bf.lot_id;

Constructs a ST_Geometry value given its well-known binary representation and an SRID value of 0.

The following CREATE TABLE statement creates the LOTS table, which has two columns: the LOT_ID column which uniquely identifies each lot, and the LOT polygon column which contains the geometry of each lot.

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

The ST_WKBToSQL function converts WKB representations into Informix Spatial DataBlade geometry using the 0 SRID. The entire INSERT statement is copied into the wkb_sql CHAR string. The INSERT statement contains parameter markers to accept the LOT_ID data and the LOT data, dynamically.

Note: When the Informix Spatial DataBlade is installed the default 0 SRID is inserted into the sde.spatial_references table. The DBA can edit the values of the 0 SRID if he/she wishes to do so.

/* Create the SQLinsert statement to populate the lot id and the lot multipolygon. The questionmarks (?) are parameter markers that indicate the lot_id and lot values that will retrieved at run time. */
strcpy (wkb_sql,"insert into lots (lot_id, lot)
values (?,ST_WKBToSQL(?))");

/* 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 *)wkb_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 to the second parameter. */
pcbvalue2 = bloblen;
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BLOB,blob_len,0, shape_blob,blob_len, &pcbvalue2);

Constructs a ST_Geometry value given its well-known text representation and an SRID value of 0.

The following CREATE TABLE statement creates the GEOMETRY_TEST table, which contains two columns: the GID column of type integer, which uniquely identifies each row, and the G1 column, which stores the geometry.

The following INSERT statements insert the data into the GID and G1 columns of the GEOMETRY_TEST table. The ST_WKTToSQL function converts the text representation of each geometry into its corresponding Informix Spatial DataBlade instantiable subclass.

Note: When the Informix Spatial DataBlade is installed the default 0 SRID is inserted into the sde.spatial_references table. The DBA can edit the values of the 0 SRID if he/she wishes to do so.

INSERT INTO GEOMETRY_TEST VALUES(
2,ST_WKTToSQL('linestring (10.02 20.01, 10.01 30.01, 10.01 40.01)')
);

INSERT INTO GEOMETRY_TESTVALUES(
3,ST_WKTToSQL('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15, 19.15 33.94, 10.02 20.01))')
);

INSERT INTO GEOMETRY_TEST VALUES(
4,ST_WKTToSQL('multipoint (10.02 20.01, 10.32 23.98, 11.92 35.64)')
);

INSERT INTO GEOMETRY_TEST VALUES(
5, ST_WKTToSQL('multilinesting((10.02 20.01, 10.32 23.98, 11.92 25.64),(9.55 23.75, 15.36 30.11))')
);

INSERT INTO GEOMETRY_TEST VALUES(
6,ST_WKTToSQL('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)))')
);

The x_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.

The INSERT statements insert two rows. One is a point without a Z coordinate or a measure. The other column has both a Z coordinate and a measure.

insert into x_test values(
2,
ST_PointFromText('pointzm (10.02 20.01 5 7)', 1)
)

The query lists the gid column and the double-precision X coordinate of the points.

GID The X coordinate
--- --------------------------------
1 +1.00200000000000E+001
2 +1.00200000000000E+001

The y_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.

The INSERT statements insert two rows. One is a point without a Z coordinate or a measure. The other has both a Z coordinate and a measure.

insert into y_test values(
1,
ST_PointFromText('point(10.02 20.01)', 1)
)
insert into y_testvalues(
2,
ST_PointFromText('pointzm (10.02 20.01 5.0 7.0)',1)
)

The query lists the gid column and the double-precision Y coordinate of the points.

GID The Y coordinate
--- --------------------------------
1 +2.00100000000000E+001
2 +2.00100000000000E+001

The z_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.

The INSERT statements insert two rows. One is a point without a Z coordinate or a measure. The other has both a Z coordinate and a measure.

insert into z_test values(
2,
ST_PointFromText('pointzm (10.02 20.01 5.0 7.0)', 1)
)

The query lists the gid column and the double-precision Z coordinate of the points. The first row is NULL because the point does not have a Z coordinate.

select gid, SE_Z(pt1)"The Z coordinate"
from z_test

GID The Z coordinate
---- -------------------------------
1 -
2 +5.00000000000000E+000

	b
Interior	Boundary	Exterior