ArcGIS Server Banner

Querying tables with an ST_Geometry column

Querying tables with an ST_Geometry column
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note: This topic was updated for 9.3.1.

Spatial queries utilize indexes to speed the query process. The type of database management system you use determines the type of spatial index used.

R-tree

Informix uses an R-tree index to perform spatial queries. A search using an R-tree index descends the tree to find objects in the general area of interest and perform tests on the objects themselves. An R-tree index eliminates the need to examine objects outside the area of interest. Without an R-tree index, a query would need to evaluate every object to find those that match the query criteria.

For more information about the Informix R-tree index, consult the IBM Informix Spatial DataBlade Module User's Guide.

PostgreSQL uses a Generalized Search Tree (GiST) R-tree index to perform spatial queries. The GiST provides a template to implement different indexing schemes.

For more information about the PostgreSQL GiST index, consult the PostgreSQL documentation on GiST indexes.

Grid

When the Oracle or DB2 query optimizer chooses a spatial index, the query execution uses the following multiple-step filter process:

  1. Determine the grid cells that intersect the query window. The query window is the envelope of the filter geometry that you specify as the second parameter in a spatial function. (See examples below.)

  2. Scan the index for all entries that have matching grid cell identifiers.

  3. Compare the geometry envelope of the geometries from the first parameter corresponding to the index entries found in step 2 with the query window and discard any candidates that do not intersect.

  4. Perform further analysis as appropriate. The candidate set of geometries from the previous steps might undergo further analysis to determine if it satisfies the spatial function such as ST_Contains or ST_Intersects. The spatial function ST_EnvIntersects omits this step and, therefore, typically has the best performance.

Queries on an ST_Geometry column using a spatial index

The following is an example of a spatial query for geodatabases for each database that uses a spatial type (ST_Geometry).


DB2 
db2 => SELECT sa.name "Sensitive Areas", hs.name "Hazardous Sites" 

FROM sensitive_areas sa, hazardous_sites hs 

WHERE db2gse.st_overlaps (sa.zone, db2gse.st_buffer (hs.location,.01)) = 1
Informix 
SELECT sa.name sensitive_area, hs.name hazardous_site 

FROM sensitive_areas sa, hazardous_sites hs 

WHERE st_overlaps(sa.zone, st_buffer(hs.location, .01)) = 't';
Oracle 
SELECT sa.name "Sensitive Areas", hs.name "Hazardous Sites" 

FROM sensitive_areas sa, hazardous_sites hs 

WHERE sde.st_overlaps (sa.zone, sde.st_buffer(hs.location,.01)) = 1;
PostgreSQL 
SELECT sa.name AS sensitive_area, hs.name AS hazardous_sites

FROM sensitive_areas sa, hazardous_sites hs 

WHERE st_overlaps(sa.zone, st_buffer(hs.location,.01) = 't');

See Also

  • Creating spatial indexes on tables with an ST_Geometry column