I Have a massive Dataset from OpenStreetMap and imported it into a Postgres-Database using ogr2ogr. I got several warnings during this process because of multipolygons with too little vertices. Now I can’t transform the data in the Database using PostGIS, because of this invalid features. It is no Problem to losse the invalid features but I don't know how to delete them or correct them. I tried:
ALTER TABLE multipolygons ALTER COLUMN wkb_geometry TYPE geometry USING ST_Transform(ST_MakeValid(ST_SetSRID(wkb_geometry,4326)),3857);
But I got the error Message: Polygon must have at least four points in each ring
Any recommendations? Thx!
UPDATE: I tried: Alter table multipolygons ADD COLUMN wkb_geom_valid geometry; Update multipolygons SET wkb_geom_valid = ST_MakeValid(wkb_geometry);
delete from multipolygons where ST_NPoints(wkb_geometry)<4 ;
select * from multipolygons where ST_isvalid(wkb_geometry)=TRUE ;
All without success...
Answer
OK, the problem started with the way I was reading the file using ogr2ogr. I left out the -skipfailures
-command. That caused that no entry in the geometry_colums table was set for the multipolygons table. It looks like this is necessary to run ST_MakeValid. After reading the data in the database again I was able to run ST_MakeValid()
. This returned geometry-collections for the features witch contain too little points. I filtered these geometries by type:
ALTER TABLE multipolygons ALTER COLUMN wkb_geometry TYPE geometry(multipolygon,3857) USING ST_CollectionExtract(ST_MakeValid(wkb_geometry),3);
I think this is the best way to gain valid data with defined geometry-type, loosing as little as possible data.
No comments:
Post a Comment