Updating and deleting values in an ST_Geometry spatial column
Updating and deleting values in an ST_Geometry spatial column
|
| Release 9.3 |
   |
Note: This topic was updated for 9.3.1.
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);
