POSTGIS修复错误数据

SELECT * from river as R LIMIT 10;

-- 先备份垃圾到表temp1,然后删除垃圾数据
CREATE TABLE temp1 as select * from river where ST_IsValid(geom) = false;
delete from river where ST_IsValid( geom) = false;

-- 更改原有库表列为三维
ALTER TABLE temp1 
  ALTER COLUMN geom TYPE geometry(MultiPolygonZ)
    USING ST_Force3D(geom);

-- 更改原有库表列为三维
ALTER TABLE river 
  ALTER COLUMN geom TYPE geometry(MultiPolygonZ)
    USING ST_Force3D(geom);

-- 查询表里面有没有垃圾数据
select * from temp1 where ST_IsValid(geom) = false;
--修复垃圾数据 
update temp1 as R set geom =ST_MakeValid(ST_Force3D(R.geom));

-- 把弄好的数据插入到原表
insert into river select * from temp1;