Automatically fixing ring self-intersections in shp2pgsql

Question

We're importing a whole bunch of ArcGIS shapefiles into PostGIS, converted on the fly with shp2pgsql. Problem is, if the shapefiles have any ring self-intersections, the import chokes:

NOTICE:  Ring Self-intersection at or near point -80.1338 25.8102
ERROR:  new row for relation "place_shapes" violates
  check constraint "shape_is_valid"

How can we fix this?

Answer 1

This query often fixes it for me:

UPDATE place_shapes
  SET geometry=ST_Buffer(geometry, 0.0);

Answer 2

While buffering a feature by zero is a known fix for self-intersecting polygons -- which are all too common in shp files -- as suggested by Marcelo, there is also the ST_MakeValid function for this purpose. There is also an associated function, ST_IsValidReason which will inform as to where the issue lies, rather than just blindly attempting to fix it.

In practice, using either ST_MakeValid(geom) or ST_Buffer(geom, 0) might produce a mixture of geometry types, including orphaned points and linestrings. A further refinement, therefore, might be to check the geometry type returned and only include, for example, the polygons that result from ST_MakeValid.

create table valid_geoms as
with make_valid (id, geom) as 
   (select 
      row_number() over() as id, 
     (ST_Dump(ST_MakeValid(geom))).geom as geom from invalid_table
  )
select id, geom from make_valid where ST_GeometryType(geom)='ST_Polygon';

where invalid_table is the table resulting from the original shp2pgsql import.

I include a generated id here, as ST_MakeValid will produce potentially more than one polygon from the input geometries. The query could be rewritten to include the original id field, but it is no longer guaranteed to be unique.