ArcGIS Server Banner

Updating and deleting values in an ST_Geometry spatial column

Updating and deleting values in an ST_Geometry spatial column

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
The SQL UPDATE statement alters the values in a spatial column just as it does any other type of attribute. Typically, spatial attribute data must be retrieved from the table, altered in a client application, then returned to the server. The SQL DELETE statement removes rows of data from a specified table or view.

Fetching data

The following SQL statement illustrates how to fetch the spatial data from one row in the hazardous_sites table and update the same item:

Oracle

UPDATE hazardous_sites 
SET location = sde.st_pointfromtext('point(18 57)', 1) 
WHERE site_id = 102;

PostgreSQL

UPDATE hazardous_sites 
SET location = st_point('point (18 57)', 1) 
WHERE site_id = 102;

Informix

UPDATE hazardous_sites 
SET location = st_pointfromtext('point(18 57)', 1) 
WHERE site_id = 102

DB2

UPDATE hazardous_sites 
SET location = db2gse.st_pointfromtext('point(18 57)', 1) 
WHERE site_id = 102

Deleting data

This example deletes values from a geodatabase in Oracle using the spatial type based on a spatial filter.

DELETE FROM sensitive_areas WHERE names
(SELECT sa.names 
FROM sensitive_areas sa, hazardous_sites hs
WHERE sde.st_overlaps (sa.zone, sde.st_buffer (hs.location,.01)) = 1);

The same example in PostgreSQL is as follows:

DELETE FROM sensitive_areas 
WHERE names EXISTS (SELECT sa.names
FROM sensitive_areas sa, hazardous_sites hs
WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) = 't');

In DB2, use the following:

DELETE FROM sensitive_areas 
WHERE names (SELECT sa.names 
FROM sensitive_areas sa, hazardous_sites hs 
WHERE db2gse.st_overlaps (sa.zone, db2gse.st_buffer (hs.location,.01)) = 1);

Informix does not support modifying a table or view used in a subquery. You can break it up into two parts where you select into a temporary table and delete using that temporary table.

SELECT sa.name FROM
sensitive_areas sa, hazardous_sites hs
WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) 
INTO TEMP tempTable;

DELETE FROM sensitive_areas WHERE name IN (select name from tempTable);