ClickHouse 地理组件自相交，Polygon is not valid: Geometry has invalid self-intersections

Polygon is not valid: Geometry has invalid self-intersections. A self-intersection point was found at ....

Code: 36. DB::Exception: Received from localhost:9000. DB::Exception: Polygon is not valid: Geometry has invalid self-intersections. A self-intersection point was found at ....

很多GEO工具都有对自相交的限制。我的数据用到阿里datav的。

尝试使用shapely去将一个自相交的经纬度数组拆分开来。

 polygon = Polygon([(0, 0), (1, 0), (1, 1), (0.5, 0.5), (0, 1)])  

 fixed_polygon = polygon.buffer(0)

unary_union和buffer 都没有那么简单的把我的问题解决了，新的元组对放进去clickhouse还是报自相交的错误。

然后用PostGIS

WITH json_load AS (
  SELECT $${ 这里json从阿里datav那拿的 https://geo.datav.aliyun.com/areas_v3/bound/geojson?code=100000
    }$$::JSONB j
)
SELECT 
row_number() OVER () AS gid,
 j->>'type' AS type,
   ST_MakeValid(ST_GeomFromGeoJSON(j->'geometry')) geom,
   j->'properties' AS props,
    ST_AsGeoJSON(ST_MakeValid(ST_GeomFromGeoJSON(j->'geometry'))) as json
FROM json_load;

ST_MakeValid 把阿里版本datav的中国边境数据拆分为多个多边形数据的数组了。MultiPolygon里面就可以看到各个地域的数据。外部格式看上去是同样的，但是不知道为什么直接下载下来的不能用。

ST_AsGeoJSON(ST_MakeValid(ST_GeomFromGeoJSON(j->'geometry'))) as json 出来也是GeoJson。

Reference

unmess-poly.glitch.me  Polygon Self-Intersection Removal (quadst.rip)

geo.datav.aliyun.com/areas_v3/bound/geojson?code=100000