You can execute SQL queries on tables that contain ST_Geometry columns to return information about the table itself, to spatially compare the features in one table with features in another table, or to generate new geometry from input tables.
Queries on an ST_Geometry column using a spatial index
The following table contains example spatial queries using a spatial index for geodatabases that use the ST_Geometry type.
|
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');
|
SQL spatial query6/19/2015