从 ST_Geometry 空间列中删除值

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

步骤:
  1. 打开 SQL 编辑器并连接到数据库。

    使用具有在表中执行删除操作所需权限的帐户连接。

  2. 以下查询示例用于删除每个受支持数据库的数据:
    • 本示例中,将在 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');
      
    • 本示例中,将在 SQLite 数据库中基于空间过滤器从表中删除值:
      DELETE FROM sensitive_areas WHERE names
      (SELECT sa.names 
       FROM sensitive_areas sa, hazardous_sites hs
       WHERE st_overlaps (sa.zone, st_buffer (hs.location,.01)) = 1);
      
    • 在 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);
      
5/25/2014