从 ST_Geometry 空间列中删除值

SQL DELETE 语句可从指定的表或视图中删除数行数据。

步骤:

1. 以下查询示例用于删除每个受支持数据库的数据：
   • 此例在使用空间类型的 Oracle 中根据空间过滤器从地理数据库中删除值：

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

   • 此例在使用空间类型的 PostgreSQL 中根据空间过滤器从地理数据库中删除值：

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

   • 在 IBM DB2 中，使用以下语句：

     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 不支持修改在子查询中使用的表或视图。因此，将查询分为两部分：选择一个临时表，然后用该临时表执行删除操作。

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