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