I have a massive Dataset from OpenStreetMap and imported it into a PostgreSQL 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?

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.


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

I create is_valid column :

Alter table multipolygons ADD COLUMN is_valid bool;

and set it by:

    err_context text;
    curid bigint :=(select min(ogc_fid) from multipolygons);
    maxId   bigint := (select max(ogc_fid) from multipolygons);
        EXIT       WHEN curid > maxId;
        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;
              RAISE info 'Error Id:%',curid;
        update multipolygons
        set is_valid = false
        WHERE ogc_fid = curid;

        curid:=curid +1;
      END LOOP;
      RETURN curid;

FROM setIsValid()

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

  • 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

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.

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.