Postgresql PostGis, 自相交数据修复

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- 交集 create table test_0803_1 as SELECT t1.fid,t1.gradecode,t1.checkid,t1.the_geom, -- st_intersection(t1.the_geom, t2.the_geom) geom FROM zsex_sjjzq_27693 t1,zsex_bjxzq_27693 t2  where  ST_Intersects(t1.the_geom,t2.the_geom) order by fid -- 合并 SELECT * from test_0803_1 create table test_0803_2 as SELECT max(fid) gid,gradecode,max(checkid) checkid, st_union(the_geom) the_geom from test_0803_1  GROUP BY gradecode -- 合并报错 GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Ring Self-intersection at or near point 118.589044663 37.956121001000099 at 118.589044663 37.956121001000099  -- 查看自相交 SELECT * from test_0803_1 where public.ST_IsEmpty(the_geom)='t' or public.ST_IsValid(the_geom)='f' -- 取除自相交数据 create table test_0803_zxj as SELECT * FROM test_0803_1  where  ST_IsValid(the_geom) = 'f' SELECT * from test_0803_zxj; SELECT * from test_0803_zxj where public.ST_IsEmpty(the_geom)='t' or public.ST_IsValid(the_geom)='f'     --修复自相交 select st_geometryType(ST_Multi(ST_Buffer(the_geom, 0.0))) type1 ,st_geometryType(the_geom) type2 from test_0803_zxj; update test_0803_zxj set the_geom =ST_Multi(ST_Buffer(the_geom, 0.0)) -- 不丢失顶点使无效变有效 update test_0803_zxj set geom=ST_MakeValid(geom)