2

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...

2

You can't easily correct them because you can't know how such A-B-A polygons should be like, but delete is simple

DELETE from multipolygons where ST_IsValid(wkb_geometry)=false;
  • it is a bit funny, but i got the same error if i try your command: FEHLER: Polygon must have at least four points in each ring. Is it maybe possible to delete them just by counting the points? – Julian May 22 '14 at 11:49
  • Yes, it is funny. Let's see if I can test it somehow. Which PostgreSQL and PostGIS versions do you have? You can check those with SELECT version(); and SELECT PostGIS_Version(); – user30184 May 22 '14 at 11:58
  • It's Postgres 9.3.4 and PostGIS 2.1 running on a Windows 7 64bit maschine. THX! – Julian May 22 '14 at 12:10
  • I tested with PostgreSQL 9.0.4 and PostGIS 1.5 with these commands: select * from osm_polygon where IsValid(wkb_geometry)=false; Result OK. delete from osm_polygon where IsValid(wkb_geometry)=false and ogc_fid=79645; I get message but the row is deleted anyhow: NOTICE: Self-intersection at or near point 268002.71398115356 6809126.963438808 Query returned successfully: one row affected, 172 ms execution time. Perhaps you experienced the same and rows were actually deleted despite the error messages. – user30184 May 22 '14 at 16:35
  • If I try to execute your select command i got several Hints because of self intersecting Polygons, but it breaks with an error message at the first entry with less than 4 Point with an error: [...] HINT: Self-intersection at or near point 10.663791700000001 49.288200500000002 ERROR: Polygon must have at least four points in each ring ********** ERROR ********** ERROR: Polygon must have at least four points in each ring SQL Status:XX000 – Julian May 23 '14 at 8:46
0

Sometimes you can fix broken polygons with with minimal st_buffer - maybe you could try:

ST_Transform(ST_Buffer(ST_MakeValid(ST_SetSRID(wkb_geometry,4326)),0.000001,3857)
  • I tried but with no success, same error as before. – Julian May 23 '14 at 8:56
  • ST_Buffer can often correct self-intersections but it does not help in this case. A valid polygon must have at least three vertices and in WKT it is presented with four points because the first and last must be same for closing the area. This is such polygon:POLYGON (( 0 0, 1 0, 0 1, 0 0 )) The errors in this case are typical for OSM data and osm2pgsql. An object called polygon is made from two vertices A and B so it is just a linestring. Points in WKT run from A to B and back to A like here: POLYGON (( 0 0, 1 0, 0 0)) – user30184 May 23 '14 at 9:29
  • Is there maybe a better way to read the data into the Postgres-Database? – Julian May 23 '14 at 12:57
0

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.

0

I create is_valid column :

Alter table multipolygons ADD COLUMN is_valid bool;

and set it by:

CREATE OR REPLACE FUNCTION setIsValid () RETURNS INT AS $body$
  DECLARE
    err_context text;
    curid bigint :=(select min(ogc_fid) from multipolygons);
    maxId   bigint := (select max(ogc_fid) from multipolygons);
  BEGIN
      LOOP
        EXIT       WHEN curid > maxId;
         BEGIN
        update multipolygons
        set is_valid = true
        WHERE  st_isvalid(wkb_geometry)
               AND    ogc_fid = curid;

        curid:=curid +1;


        IF MOD(curid, 100) = 0 THEN
            RAISE info 'ID:%',curid;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
              RAISE info 'Error Id:%',curid;
        update multipolygons
        set is_valid = false
        WHERE ogc_fid = curid;

        curid:=curid +1;
        END;
      END LOOP;
      RETURN curid;
  END;
  $body$ LANGUAGE PLPGSQL;


SELECT *
FROM setIsValid()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.