| SQL API Functional Reference |
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.
Returns the area of a polygon or multipolygon.
ST_Area (pl1 ST_Polygon)
ST_Area (mpl1 ST_MultiPolygon)
Double precision
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.
select
building_id, ST_Area(footprint) "Area"
from buildingfootprints;
building_id
Area
----------- ----------------------
506 +1.40768000000000E+003
1208 +2.08679000000000E+003
178 +1.80786000000000E+003
543 +2.15751000000000E+003
.
.
.
Four of the building footprints labeled with their building ID numbers are displayed alongside their adjacent street.
Takes a geometry object and returns its well-known binary representation.
Syntax
ST_AsBinary (g1 ST_Geometry)
Return type
ST_Geometry
Example
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))");
/*
Prepare the SQL statement. */
SQLPrepare(hstmt,(UCHAR*) sqlstmt, SQL_NTS);
/*
Bind the query geometry parameter. */
pcbvalue1 = query_wkb_len;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_INFX_UDT_LVARCHAR, query_wkb_len, 0, query_wkb_buf, query_wkb_len, &pcbvalue1);
/*
Execute the query.*/
rc =SQLExecute(hstmt);
/*
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);
Takes a geometry object and returns it as an ESRI binary shape.
Syntax
SE_AsShape (g1 ST_Geometry)
Return type
ST_Geometry
Example
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))");
/*
Prepare the SQLstatement. */
SQLPrepare(hstmt,(UCHAR*)sqlstmt, SQL_NTS);
/*
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);
/*
Execute the query.*/
rc = SQLExecute(hstmt);
/*
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);
Takes a ST_Geometryobject and returns its well-known text representation.
Syntax
ST_AsText (g1 ST_Geometry)
Return type
varchar (256)
Example
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)
);
select
site_id, name,ST_AsText(location)
Location
from hazardous_sites;
>SITE_ID
Name
Location
------- ----------------------------------- -------------------------
102 W. H. KleenareChemical Repository ST_POINT
(1020.12 3240.2)
Takes a geometry object and returns its combined boundary as a geometry object.
Syntax
ST_Boundary (g1 ST_Geometry)
Return type
ST_Geometry
Example
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.
create table boundary_test (geotype varchar(20), g1 ST_Geometry);
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));
select
geotype, ST_Boundary (g1) "The
boundary"
from boundary_test
>GEOTYPE The boundary
------------------ ----------------------------------------------------------
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))
6 record(s) selected.
Takes a geometry object and distance and returns a geometry object that is the buffer surrounding the source object.
Syntax
ST_Buffer (g1 ST_Geometry, distance double_precision)
Return type
ST_Geometry
Example
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.
Takes a polygon or multipolygon and returns its geometric center as a point.
Syntax
ST_Centroid
(pl1 ST_Polygon)
ST_Centroid (mpl1 ST_MultiPolygon)
Return type
ST_Point
Example
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).
Syntax
ST_Contains (g1 geometry, g2 geometry)
Return type
Boolean
Example
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);
create
table lots(lot_id integer,
lot 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.
Returns the convex hull of a geometry object.
Syntax
ST_ConvexHull (g1 ST_ Geometry)
Return type
ST_Geometry
Example
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.
create table convexhull_test (geotype varchar(20), g1 ST_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.
select
geotype, ST_ConvexHull(g1)convexhull
from convexhull_test
Returns the coordinate dimensions of the ST_Geometry value.
Syntax
ST_CoordDim (g ST_Geometry)
Return Type
Integer
Example
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.
CREATE TABLE coorddim_test (geotype varchar(20), g1 geometry);
The INSERT statements insert a sample subclass into the coorddim_test table.
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.
SELECT geotype,coorddim(g1), coordinate_dimension FROM coorddim_test;
GEOTYPE
coordinate_dimension
----------------- --------------------
Point
2
LineString
2
Polygon
2
MultiPoint
2
MultiLineString
2
MultiPolygon
2
6 record(s) selected.
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).
Syntax
ST_Crosses (g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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.
Syntax
ST_Difference (g1 ST_Geometry,g2 ST_Geometry)
Return type
ST_Geometry
Example
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);
create table lots(lot_id integer, lot 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;
Returns the dimension of a geometry object.
Syntax
ST_Dimension(g1 ST_Geometry)
Returns
Integer
Example
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.
create table dimension_test (geotype varchar(20), g1 ST_Geometry)
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.
Select
geotype, ST_Dimension(g1)Dimension
from dimension_test
GEOTYPE
Dimension
----------------------- ------------
Point
0
Linestring
1
Polygon
2
Multipoint
0
Multilinestring
1
Multipolygon
2
6 record(s) selected.
ST_Disjoint takes two geometries and returns t (TRUE) if the intersection of two geometries produces an empty set; otherwise, it returns f (FALSE).
Syntax
ST_Disjoint (g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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.
Returns the closest distance separating two geometries.
Syntax
ST_Distance(g1 ST_Geometry, g2 ST_Geometry)
Return type
Double precision
Example
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.
create
table lots (lot_id integer,
lot ST_Multipolygon);
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;
Returns the last point of a linestring.
Syntax
ST_EndPoint (ln1 ST_LineString)
Return type
ST_Point
Example
The endpoint_test table stores the gid integer column, which uniquely identifies each row and the ln1 ST_LineString column that stores linestrings.
create table endpoint_test (gid integer, ln1 ST_LineString);
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.
select
gid,(ST_EndPoint(ln1) Endpoint
from endpoint_test
GID
Endpoint
---------- -------------------------------------------------------------
1 ST_POINT (30.10 40.23)
2 ST_POINT ZM (30.10 40.23
6.9 7.2)
2 record(s) selected.
Returns the bounding box of a geometry object as a polygon.
Syntax
ST_Envelope(g1 ST_Geometry)
Return type
ST_Geometry
Example
The envelope_test table's geotype column stores the name of the geometry subclass stored in the g1 ST_Geometry column.
create table envelope_test (geotype varchar(20), g1 ST_Geometry)
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))
8 record(s) selected.
Returns t (TRUE) if the envelopes of two geometries intersect; otherwise, it returns f (FALSE).
Syntax
ST_EnvelopesIntersect(g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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;
/*
Prepare the SQLstatement. */
SQLPrepare(hstmt,(UCHAR *)sqlstmt, SQL_NTS);
/*
Set the pcbvalue1 to the window shape.*/
pcbvalue1 = blob_len;
/*
Bind the shape parameter.*/
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BLOB, blob_len,
0, window, blob_len, &pcbvalue1);
/*
Execute the query.*/
rc =SQLExecute(hstmt);
/*
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).
Syntax
ST_Equals (g1 ST_Geometry, g2ST_Geometry)
Return type
Boolean
Example
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;
Returns the exterior ring of apolygon as a linestring.
Syntax
ST_ExteriorRing(pl1 ST_Polygon)
Return type
ST_LineString
Example
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.
create
table islands(id integer,
name varchar(32),
land ST_Polygon);
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.
select sum(ST_length(ST_ExteriorRing(land)))
from 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.
Takes a shape and a spatial reference ID to return a geometry object.
Syntax
SE_GeomFromShape(s1 blob(1m),SRID integer)
Return type
ST_Geometry
Example
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.
create
table lots(lot_id integer,
lot ST_Multipolygon);
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);
/*
Execute the insert statement. */
rc = SQLExecute(hstmt);
Takes a well-known text representation and a spatial reference ID and returns a geometry object.
Syntax
ST_GeomFromText (wkt varchar(255),SRID integer)
Return type
ST_Geometry
Example
The geometry_test table contains the integer gid column, which uniquely identifies each row, and the g1 column, which stores the geometry.
create table geometry_test (gid smallint, g1 ST_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(
1, ST_GeomFromText('point(10.02
20.01)',1)
)
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.
Syntax
ST_GeomFromWKB (WKB blob(1m), SRID integer)
Return type
ST_Geometry
Example
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.
create table
lots(lot_id integer,
lot ST_MultiPolygon);
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Takes a collection and an integer index and returns the Nth ST_Geometry object in the collection.
Syntax
ST_GeometryN
(mpt1 ST_MultiPoint, index integer)
ST_GeometryN (mln1 ST_MultiLineString, index integer)
ST_GeometryN(mpl1 ST_MultiPolygon, index integer)
Return type
ST_Geometry
Example
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);
create table
lots(lot_id integer,
lot 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;
Takes a ST_Geometry object and returns its geometry type as a string.
Syntax
ST_GeometryType (g1 ST_Geometry)
Return type
Varchar(32) containing one of
ST_Point
ST_LineString
ST_Polygon
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon
Example
The geometrytype_test table contains the g1 geometry column.
create table geometrytype_test(g1 st_geometry)
The INSERT statements insert each geometry subclass into the g1 column.
insert
into geometrytype_test values(
ST_GeomFromText('point(10.02 20.01)',1)
)
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.
select
ST_GeometryType(g1)Geometry_type
from geometrytype_test
Geometry_type
-----------------------
ST_Point
ST_LineString
ST_Polygon
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon
6 record(s) selected.
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.
Syntax
ST_InteriorRingN(pl1 ST_Polygon, index integer)
Return type
ST_LineString
Example
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.
create
table islands (id integer,
name varchar(32),
land ST_Polygon);
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"
/* Change these constants! */
#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.
Syntax
ST_Intersection(g1 ST_Geometry,g2 ST_Geometry)
Return type
ST_Geometry
Example
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).
Syntax
ST_Intersects(g1 ST_Geometry, g2ST_Geometry)
Return type
Boolean
Example
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).
Syntax
SE_Is3D(g1 ST_Geometry)
Return type
Boolean
Example
The threed_test table is created with integer gid and g1 ST_Geometry columns.
create table threed_test (gid smallint, g1 ST_Geometry)
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(
1,
ST_PointFromText('point(10 10)',1)
)
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.
select
gid,SE_Is3D(g1)
Is_it_3d
from threed_test
gid
Is_it_3d
--------- --------
1 f
2 t
Takes a ST_Linestring or ST_MultiLineString and returns t (TRUE) if it is closed; otherwise, it returns f (FALSE).
Syntax
ST_IsClosed(ln1 ST_LineString)
ST_IsClosed(mln1 ST_MultiLineString)
Return type
Boolean
Example
The closed_linestring table is created with a single linestring column.
create table closed_linestring (ln1 ST_LineString)
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.
select
ST_IsClosed(ln1)Is_it_closed
from closed_linestring
Is_it_closed
------------
f
t
2 record(s) selected.
The closed_mlinestring table is created with a single ST_MultiLineString column.
create table closed_mlinestring (mln1 ST_MultiLineString)
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.
select
ST_IsClosed(mln1) Is_it_closed
from closed_mlinestring
Is_it_closed
------------
f
t
2 record(s) selected.
Returns t (TRUE) if the geometry is empty; otherwise, returns f (FALSE).
Syntax
ST_IsEmpty(g1 ST_Geometry)
Return type
Boolean
Example
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.
create table empty_test (geotype varchar(20), g1 ST_Geometry)
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(
'Point', ST_PointFromText('point empty',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)
)
insert
into empty_test values(
'Polygon', ST_PolyFromText('polygon empty',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
6 record(s) selected.
Returns t (TRUE) if the ST_Geometry object has measures; otherwise, returns f (FALSE).
Syntax
SE_IsMeasured(g1 ST_Geometry)
Return type
Boolean
Example
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.
create table measure_test (gid smallint, g1 ST_Geometry)
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(
1,
ST_PointFromText('point(10 10)',1)
)
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.
select
gid,SE_IsMeasured(g1)Has_measures
from measure_test
gid Has_measures
----------- ----------
1 f
2 t
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).
Syntax
ST_IsRing(ln1 ST_LineString)
Return type
Boolean
Example
The ring_linestring table is created with a single ST_LineString column called ln1.
create table ring_linestring (ln1 ST_LineString)
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.
select
ST_IsRing(ln1)Is_it_a_ring
from ring_linestring
Is_it_a_ring
------------
f
t
f
3 record(s) selected.
Returns t (TRUE) if the geometry object is simple; otherwise, it returns f (FALSE).
Syntax
ST_IsSimple(g1 ST_Geometry)
Return type
Boolean
Examples
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.
select
ST_IsSimple(g1) Is_it_simple
from issimple_test
Is_it_simple
------------
t
f
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.
Syntax
ST_IsValid(g ST_Geometry)
Return Type
Boolean
Example
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.
CREATE TABLE valid_test (geotype varchar(20), g1 ST_Geometry)
The INSERT statements insert a sample subclass into the valid_test table.
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.
Returns the length of a ST_LineStringor ST_MultiLinestring.
Syntax
length (ln1 ST_LineString)
length (mln1 ST_MultiLineString)
Return type
Double precision
Example
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.
create table waterways (id integer, name varchar(128), water
ST_MultiLineString);
The query returns the name of each system along with the length of the system generated by the length function.
select
name, ST_Length(water) "Length"
from waterways;
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.
Syntax
SE_LineFromShape (s1 blob(1m),SRID integer)
Return type
ST_LineString
Example
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);
/*
Execute the insertstatement. */
rc = SQLExecute(hstmt);
Takes a well-known text representation of type ST_LineString and a spatial reference ID and returns a ST_LineString.
Syntax
ST_LineFromText (WKT varchar(256), SRID integer)
Return type
ST_LineString
Example
The linestring_test table is created with a single ln1 ST_LineString column.
create table linestring_test (ln1 ST_LineString)
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.
Syntax
ST_LineFromWKB(wkb blob(1m), SRID integer)
Return type
ST_LineString
Example
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
table sewerlines (sewer_id integer,
class integer,
sewer ST_LineString);
/*
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Takes a geometry object and a measure to return as a ST_MultiPoint the set of points found at the measure.
Syntax
SE_LocateAlong(g1 ST_Geometry, m1 double precision)
Return type
ST_Geometry
Example
The locatealong_test table is created with two columns: the gid column uniquely identifies
each row, and the g1 ST_Geometry column stores sample geometry.
create table locatealong_test (gid integer, g1 ST_Geometry)
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.
select
gid, SE_locatealong(g1,6.5)Geometry
from locatealong_test
GID
Geometry
--------------------------------------------------------------------------
1 ST_MULTIPOINT M(27.01 19.38 6.5, 27.4 22.14 6.5)
2 ST_POINT EMPTY
2 record(s) selected.
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.
select
gid,SE_LocateAlong(g1,7) Geometry
from locatealong_test
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)
2 record(s) selected.
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.
Syntax
SE_LocateBetween (g1 ST_Geometry, fm double precision, tm double precision)
Return type
ST_Geometry
Example
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.
create table locatebetween_test (gid integer, g1 ST_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.
select
gid, SE_LocateBetween(g1,6.5,7.5)Geometry
from locatebetween_test
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)
2 record(s) selected.
Returns the measure value of a point.
Syntax
SE_M(p1 ST_Point)
Return type
Double precision
Example
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.
create table m_test(gid integer, pt1 ST_Point)
The INSERT statements insert a point with measures and a point without measures.
insert
into m_test values(
1,
ST_PointFromText('point(10.02 20.01)', 1)
)
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.
select
gid, (pt1) The_measure
from m_test
GID
The_measure
---------- ----------------------
1 -
2 +7.0E+000
2 record(s) selected.
SE_MlineFromShape creates a ST_MultiLineString from a multilinestring shape and a spatial reference ID.
Syntax
SE_MlineFromShape(s1 blob(1m), SRID integer)
Return type
ST_MultiLineString
Example
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);
/* Execute
the insertstatement. */
rc = SQLExecute(hstmt);
Takes a well-known text representation of type ST_MultiLineString and a spatial reference ID and returns a ST_MultiLineString.
Syntax
ST_MlineFromText(wktvarchar(255), SRID integer)
Return type
ST_MultiLineString
Example
The mlinestring_test is created with the gid smallint column that uniquely identifies the row and the ml1 ST_MultiLineString column.
create table mlinestring_test (gid smallint, ml1 ST_MultiLineString)
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.
Syntax
ST_MlineFromWKB(WKB blob(1m),SRID integer)
Return type
ST_MultiLineString
Example
This code fragment populates the waterways table with a unique ID, a name, and a water ST_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
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);
/* Execute
the insertstatement. */
rc = SQLExecute(hstmt);
Takes a shape of type ST_MultiPoint and a spatial reference ID to return a ST_MultiPoint.
Syntax
SE_MpointFromShape (s1 blob(1m), SRID integer)
Return type
ST_MultiPoint
Example
This code fragment populates a biologist's species_sitings table.
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Creates a ST_Multipoint from a well-known text representation of type ST_MultiPoint and a spatial reference ID.
Syntax
ST_MpointFromText(WKT varchar(255), SRID integer)
Return type
ST_MultiPoint
Example
The multipoint_test table is created with the single ST_MultiPoint mpt1 column.
create table multipoint_test (mpt1 ST_MultiPoint)
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.
Syntax
ST_MpointFromWKB (WKB blob(1m), SRID integer)
Return type
ST_MultiPoint
Example
This code fragment populates a biologist's species_sitings table.
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Takes a shape of type multipolygon and a spatial reference ID to return a ST_MultiPolygon.
Syntax
SE_MpolyFromShape (s1 blob(1m), SRID integer)
Return type
ST_MultiPolygon
Example
This code fragment populates the lots table.
The lots table stores the lot_id, which uniquely identifies each lot, and the lot multipolygon that contains the lot line geometry.
create
table lots (lot_id integer,
lot ST_MultiPolygon);
/*
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Takes a well-known text representation of type ST_MultiPolygon and a spatial reference ID and returns a ST_MultiPolygon.
Syntax
ST_MpolyFromText (WKTvarchar(255), SRID integer)
Return type
ST_MultiPolygon
Example
The multipolygon_test table is created with the single ST_MultiPolygon mpl1 column.
create table multipolygon_test (mpl1 ST_MultiPolygon)
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.
Syntax
ST_MpolyFromWKB(WKB blob(1m), SRID integer)
Return type
ST_MultiPolygon
Example
This code fragment populates the lots table.
The lots table stores the lot_id, which uniquely identifies each lot, and the lot multipolygon that contains the lot line geometry.
create
table lots (
lot_id integer,
lot ST_MultiPolygon);
/*
Create the SQL insert 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(wkb_sql,"insert into lots (lot_id,lot) values(?, ST_MpolyFromWKB
(?,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 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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Takes a collection and returns the number of geometries in the collection.
Syntax
ST_NumGeometries(mpt1
ST_MultiPoint)
ST_NumGeometries(mln1 ST_MultiLineString)
ST_NumGeometries(mpl1 ST_MultiPolygon)
Return type
Integer
Example
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;
Takes a ST_Polygon and returns the number of its interior rings.
Syntax
ST_NumInteriorRing(pl1 ST_Polygon)
Return type
Integer
Example
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.
create
table islands(
id integer,
name varchar(32),
land ST_Polygon);
Because interior rings represent the lakes, the ST_NumInteriorRing function will list only those islands that have at least one interior ring.
select name
from islands
where ST_NumInteriorRing(land)> 0;
Returns the number of points in a ST_Geometry.
Syntax
ST_NumPoints(g1 ST_Geometry)
Return type
Integer
Example
The numpoints_test table is created with the geotype column, which contains the ST_Geometry type stored in the g1 geometry column.
create table numpoints_test (geotype varchar(12), g1 ST_Geometry)
The INSERT statements insert a point, a linestring, and a polygon.
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)
)
The query lists the geometry type and the number of points in each.
select
geotype, ST_NumPoints(g1)
Number_of_points
from numpoints_test
GEOTYPE
Number_of_points
----------- -----------------
point
1
linestring 2
polygon
5
3 record(s) selected.
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).
Syntax
ST_OrderingEquals(g1 ST_Geometry, g2 ST_Geometry)
Return Type
Integer
Examples
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).
Syntax
ST_Overlaps(g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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);
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 points.
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.
ST_Perimeter returns the perimeter of an ST_Surface or ST_MultiSurface.
Syntax
ST_Perimeter(s ST_Surface)
ST_Perimeter(ms ST_MultiSurface)
Return type
Double Precision
Examples
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.
CREATE TABLE WATERBODIES (wbid integer, waterbody ST_MultiPolygon);
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.
SELECT SUM(ST_Perimeter(waterbody)) FROM waterbodies;
ST_Point returns an ST_Point, given an x-coordinate, y-coordinate, and spatial reference ID.
Syntax
ST_Point(X double Precision, Y Double Precision, SRID)
Return type
ST_Point
Examples
The following CREATE TABLE statement creates the POINT_TEST table, which has a single point column, PT1.
CREATE TABLE POINT_TEST (pt1 ST_Point)
The ST_Point function converts the point coordinates into a ST_Point geometry before the INSERT statement insert sit into the PT1 column.
INSERT INTO
point_test VALUES (
ST_Point(10.01,20.03,1)
)
SE_PointFromShape creates a ST_Point from a shape of type point and a spatial reference ID.
Syntax
SE_PointFromShape(s1 blob(1m),SRID integer)
Return type
ST_Point
Example
The program fragment populates the hazardous_sites table.
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);
/*
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);
/* Execute
the insertstatement. */
rc = SQLExecute(hstmt);
ST_PointFromText takes a well-known text representation of type point and a spatial reference ID and returns a point.
Syntax
ST_PointFromText(WKTvarchar(255), SRID integer)
Return type
ST_Point
Example
The point_test table is created with the single ST_Point column pt1.
create table point_test (pt1 ST_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.
Syntax
ST_PointFromWKB(WKB blob(1m), SRID integer)
Return type
ST_Point
Example
The program fragment populates the hazardous_sites table.
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);
/*
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
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.
Syntax
ST_PointN(ln1 ST_LineString,index integer)
Return type
ST_Point
Example
The pointn_test table is created with the gid column, which uniquely identifies each row, and the ln1 ST_LineString column.
create table pointn_test (gid integer, ln1 ST_LineString)
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.
select
gid, pointn(ln1,2)The_2ndvertex
from pointn_test
GID
The_2ndvertex
--- -------------------------------------------------------------------
1 ST_POINT (23.73 21.92)
2 ST_POINT ZM (23.73 21.92 7 7.1)
2 record(s) selected.
ST_PointOnSurface takes a ST_Polygon or ST_MultiPolygon and returns a ST_Point guaranteed to lie on its surface.
Syntax
ST_PointOnSurface(pl1
ST_Polygon)
ST_PointOnSurface(mpl1 ST_Multipolygon)
Return type
ST_Point
Example
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.
select SE_AsShape(ST_PointOnSurface(footprint))
from buildingfootprints;
SE_PolyFromShape returns a ST_Polygon from a shape of type polygon and a spatial reference ID.
Syntax
SE_PolyFromShape(s1 blob(1m), SRID integer)
Return type
ST_Polygon
Example
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
ST_PolyFromText takes a well-known text representation of type ST_Polygon and a spatial reference ID and returns a ST_Polygon.
Syntax
ST_PolyFromText(wkt varchar(255), SRID integer)
Return type
ST_Polygon
Example
The polygon_test table is created with the single polygon column.
create table polygon_test (pl1 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.
Syntax
ST_PolyFromWKB(wkb blob(1m), SRID integer)
Return type
ST_Polygon
Example
The program fragment populates the sensitive_areas table.
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);
/*
Execute the insertstatement. */
rc = SQLExecute(hstmt);
ST_Polygon generates a ST_Polygon from a ring (a ST_linestring that is both simple and closed).
Syntax
ST_Polygon(ln ST_LineString)
Return Type
ST_Polygon
Examples
The following CREATE TABLE statement creates the POLYGON_TEST tables, which has a single column, P1.
CREATE TABLE POLYGON_TEST (p1 ST_polygon)
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.
Syntax
ST_Relate(g1 ST_Geometry, g2 ST_Geometry, patternMatrix String)
Return type
Boolean
Examples
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*").
|
|
|
|
b |
|
|
|
|
|
Interior |
Boundary |
Exterior |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
SELECT
ST_relate(g1,g2) equals, st_relate(g1,g3) not_equals
FROM relate_test;
equals
not_equals
---------- -----------
t t
1 record(s) selected.
Constructs an ST_Geometry value given it's well-known binary representation and an SRID value of 0.
Syntax
SE_ShapeToSQL(ShapeGeometryString)
Return Type
ST_Geometry
Examples
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.
CREATE
TABLE lots(lot_id integer,
lot ST_Multipolygon);
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);
/* Execute
the insertstatement. */
rc = SQLExecute(hstmt);
Takes a geometry object and returns its spatial reference ID.
Syntax
ST_SRID(g1 ST_Geometry)
Return type
Integer
Example
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'
)
The following table is created:
create table srid_test(g1 geometry)
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.
insert
into srid_testvalues(
ST_PointFromText('point(10.01 50.76)',1)
)
The ST_SRID function returns the spatial reference ID of the geometry just entered.
select ST_SRID(g1) from srid_test
g1
--------------
1
Returns the first point of a linestring.
Syntax
ST_StartPoint(ln1 ST_LineString)
Return type
ST_Point
Example
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.
create table startpoint_test (gid integer, ln1 ST_LineString)
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.
select
gid, ST_StartPoint(ln1)Startpoint
from startpoint_test
GID
Startpoint
--- ------------------------------------------------------------------
1 ST_POINT(10.02 20.01)
2 ST_POINT ZM(10.02 20.01 5 7)
2 record(s) selected.
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.
Syntax
ST_SymDifference(g1 ST_Geometry,g2 ST_Geometry)
Return type
ST_Geometry
Example
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.
Syntax
ST_Touches(g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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.
create
table sewerlines (sewer_id integer,
class integer,
sewer ST_LineString);
The query returns an ordered list of sewer_ids that touch one another.
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.
Syntax
ST_Transform(g ST_Geometry, SRID integer)
Return type
ST_Geometry
Example
The following CREATE TABLE statement creates the TRANSFORM_TEST table, which has two ST_LineString columns, ln1 and ln2.
CREATE TABLE TRANSFORM_TEST (ln1 ST_LineString, ln2 ST_LineString);
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.
UPDATE TRANSFORM_TEST SET ln2 = ST_Transform(ln1,105);
Returns a ST_Geometry object that is the union of two source objects.
Syntax
ST_Union(g1 ST_Geometry, g2 ST_Geometry)
Return type
ST_Geometry
Example
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).
Syntax
ST_Within(g1 ST_Geometry, g2 ST_Geometry)
Return type
Boolean
Example
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);
create
table lots(lot_id integer,
lot 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.
Syntax
ST_WKBToSQL(WKBGeometry Binary)
Return type
ST_Geometry
Examples
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.
CREATE TABLE lots(lot_id integer, lot ST_MultiPolygon);
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);
/* Execute
the insert statement. */
rc = SQLExecute(hstmt);
Constructs a ST_Geometry value given its well-known text representation and an SRID value of 0.
Syntax
ST_WKTToSQL(WKT Varchar(255))
Return type
ST_Geometry
Examples
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.
CREATE TABLE GEOMETRY_TEST (gid integer, g1 ST_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(
1, ST_WKTToSQL('point(10.02 20.01)')
);
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)))')
);
Returns the X coordinate of a point.
Syntax
ST_X(pt1 ST_Point)
Return type
Double precision
Example
The x_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.
create table x_test(gid integer, pt1 ST_Point)
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(
1,
ST_PointFromText('point(10.02 20.01)', 1)
)
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.
select
gid, ST_X(pt1)"The
X coordinate"
from x_test
GID
The X coordinate
--- --------------------------------
1 +1.00200000000000E+001
2 +1.00200000000000E+001
2 record(s) selected.
Returns the Y coordinate of a point.
Syntax
ST_Y(p1 ST_Point)
Return type
Double precision
Example
The y_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.
create table y_test(gid integer, pt1 ST_Point)
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.
select
gid, ST_Y(pt1)"The Y coordinate"
from y_test
GID
The Y coordinate
--- --------------------------------
1 +2.00100000000000E+001
2 +2.00100000000000E+001
2 record(s) selected.
Returns the Z coordinate of a point.
Syntax
SE_Z(p1 ST_Point)
Return type
Double precision
Example
The z_test table is created with two columns: the gid column, which uniquely identifies the row, and the pt1 point column.
create table z_test(gid integer, pt1 ST_Point)
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(
1,
ST_PointFromText('point(10.02 20.01)', 1)
)
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
2 record(s) selected.