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

我有一个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"

新表具有检查约束force_geotype_geom =(((geometrytype(geom)='POLYGON':: text)OR(geom IS NULL)))，而旧表则没有，因此我猜有没有数据或非多边形(也许是多面数据) ？)在旧表中。我想将新数据保留为多边形，所以不想插入其他任何内容。

最初，我尝试使用标准的python错误处理包装查询，以期使dud geom行失败，但脚本将继续运行，但是脚本被编写为在最后一行而不是每一行都提交，因此它不起作用。

我认为我需要做的是遍历旧表的geom行，并检查它们是什么类型的几何，以便在我插入新表之前可以确定是否要保留它或将其丢弃

最好的方法是什么？

最佳答案

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;

关于python - Postgis-在插入之前如何检查几何类型，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2023821/