作者

digoal

日期

2021-07-20

标签

PostgreSQL , PostGIS , invalid polygons and multipolygons , ST_MakeValid , structure

背景

详见: https://blog.crunchydata.com/blog/waiting-for-postgis-3.2-st_makevalid

polygons and multipolygons 必须符合以下规则

The rules are things like: - Polygon rings should not cross themselves - Polygon rings should not cross other rings - Multipolygon components should neither touch nor overlap

不符合这些规则的polygons and multipolygons是invalid的, 例如 存在空间重叠描述, 线段相交描述. invalid的空间对象有什么问题呢?st_area可能返回错误的结果:

This invalid "figure" polygon will return an ST_Area() of 0.0, because the area of one lobe cancels out the area of the other.

以前的版本支持linework算法, 修复效果不太好.

PostGIS 3.2 支持 ST_MakeValid 新算法: structure , 修复invalid polygons and multipolygons.

Algorithm Differences: The original algorithm is called "linework" because it endeavors to preserve every edge and vertex in the original input. The new algorithm is called "structure" because it tries to reason from the structure of the input (exterior rings bound area, interior holes exclude area) to find the "correct" repair.

``` -- Old 'linework' algorithm SELECT ST_AsText(ST_MakeValid( 'POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))', 'method=linework'));

-- New 'structure' algorithm SELECT ST_AsText(ST_MakeValid( 'POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0))', 'method=structure')); ```

    Geometry validity is worth keeping track of, using ST_IsValid(), to avoid algorithm failures. Geometry can be repaired using ST_MakeValid() The new "method=structure" algorithm offers a higher performance and maybe "more correct" (depending on who you ask) take on geometry repair. Performance of both validity testing and validity repair has improved substantially in PostGIS 3.2 via GEOS 3.10.

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.