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

3/7/2012