Postgis - 如何在插入前检查几何类型

Question

我有一个Postgres数据库，里面有几百万行数据，其中有一列叫做geom，存储的是一个地产的边界信息。

我用一个Python脚本从这个表中提取信息，然后把这些信息重新插入到一个新表里。

但是，当我把数据插入新表时，脚本出现了问题，错误信息如下：

Traceback (most recent call last):
  File "build_parcels.py", line 258, in <module>
    main()
  File "build_parcels.py", line 166, in main
    update_cursor.executemany("insert into parcels (par_id, street_add, title_no, proprietors, au_name, ua_name, geom) VALUES (%s, %s, %s, %s, %s, %s, %s)", inserts)
psycopg2.IntegrityError: new row for relation "parcels" violates check constraint "enforce_geotype_geom"

新表有一个检查约束，叫做enforce_geotype_geom，要求geom的类型必须是'POLYGON'，或者geom可以是空值。而旧表没有这个约束，所以我猜旧表里可能有一些不合格的数据，或者是非多边形的数据（可能是多多边形数据？）。我想保持新数据为多边形，所以不想插入其他类型的数据。

最开始我尝试用标准的Python错误处理来包裹查询，希望那些不合格的geom行会出错，但脚本能继续运行。不过，脚本是设计成在最后一次性提交，而不是每插入一行就提交，所以这个方法不奏效。

我觉得我需要做的是遍历旧表中的geom行，检查它们是什么类型的几何形状，这样我才能决定是保留还是丢弃这些数据，然后再插入到新表中。

那么，最好的做法是什么呢？

Answer 1

我觉得你可以使用 ST_CollectionExtract 这个功能。它的作用是，从一个（多重）几何图形中提取出指定类型的元素，返回一个新的（多重）几何图形。

我在插入 ST_Intersection 的结果时会用到这个功能，因为 ST_Dump 会把任何多边形集合拆分成单独的几何图形。然后使用 ST_CollectionExtract (theGeom, 3) 就可以只保留多边形：

ST_CollectionExtract((st_dump(ST_Intersection(data.polygon, grid.polygon))).geom, )::geometry(polygon, 4326)

上面提到的第二个参数 3 代表的是：1 == 点, 2 == 线, 3 == 多边形

Answer 2

选项一是在每次插入数据之前创建一个保存点，如果插入失败，就回滚到这个保存点。

选项二是在原始查询中添加一个检查条件，作为WHERE条件，这样就可以避免选择到错误的数据。

哪个方法最好，取决于表的大小、出错行的数量，以及这个操作需要多快和多频繁地执行。

Answer 3

这段非常有用的PostGIS SQL代码可以帮助你理解……里面有很多关于几何类型的测试：

-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-- 
-- $Id: cleanGeometry.sql 2008-04-24 10:30Z Dr. Horst Duester $
--
-- cleanGeometry - remove self- and ring-selfintersections from 
--                 input Polygon geometries 
-- http://www.sogis.ch
-- Copyright 2008 SO!GIS Koordination, Kanton Solothurn, Switzerland
-- Version 1.0
-- contact: horst dot duester at bd dot so dot ch
--
-- This is free software; you can redistribute and/or modify it under
-- the terms of the GNU General Public Licence. See the COPYING file.
-- This software is without any warrenty and you use it at your own risk
--  
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


CREATE OR REPLACE FUNCTION cleanGeometry(geometry)
  RETURNS geometry AS
$BODY$DECLARE
  inGeom ALIAS for $1;
  outGeom geometry;
  tmpLinestring geometry;

Begin

  outGeom := NULL;

-- Clean Process for Polygon 
  IF (GeometryType(inGeom) = 'POLYGON' OR GeometryType(inGeom) = 'MULTIPOLYGON') THEN

-- Only process if geometry is not valid, 
-- otherwise put out without change
    if not isValid(inGeom) THEN

-- create nodes at all self-intersecting lines by union the polygon boundaries
-- with the startingpoint of the boundary.  
      tmpLinestring := st_union(st_multi(st_boundary(inGeom)),st_pointn(boundary(inGeom),1));
      outGeom = buildarea(tmpLinestring);      
      IF (GeometryType(inGeom) = 'MULTIPOLYGON') THEN      
        RETURN st_multi(outGeom);
      ELSE
        RETURN outGeom;
      END IF;
    else    
      RETURN inGeom;
    END IF;


------------------------------------------------------------------------------
-- Clean Process for LINESTRINGS, self-intersecting parts of linestrings 
-- will be divided into multiparts of the mentioned linestring 
------------------------------------------------------------------------------
  ELSIF (GeometryType(inGeom) = 'LINESTRING') THEN

-- create nodes at all self-intersecting lines by union the linestrings
-- with the startingpoint of the linestring.  
    outGeom := st_union(st_multi(inGeom),st_pointn(inGeom,1));
    RETURN outGeom;
  ELSIF (GeometryType(inGeom) = 'MULTILINESTRING') THEN 
    outGeom := multi(st_union(st_multi(inGeom),st_pointn(inGeom,1)));
    RETURN outGeom;
  ELSIF (GeometryType(inGeom) = '<NULL>' OR GeometryType(inGeom) = 'GEOMETRYCOLLECTION') THEN 
    RETURN NULL;
  ELSE 
    RAISE NOTICE 'The input type % is not supported %',GeometryType(inGeom),st_summary(inGeom);
    RETURN inGeom;
  END IF;     
End;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;