Tuesday 19 March 2019

postgresql - Dismiss invalid Polygons in PostGIS


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...