7

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?

lambshaanxy
  • 22,552
  • 10
  • 68
  • 92

3 Answers3

9

This query often fixes it for me:

UPDATE place_shapes
  SET geometry=ST_Buffer(geometry, 0.0);
Marcelo
  • 9,387
  • 3
  • 35
  • 40
8

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.

John Powell
  • 12,253
  • 6
  • 59
  • 67
  • 1
    _"might produce a mixture of geometry types, including orphaned points and linestrings"_ - Can't highlight enough, you'll want to watch out for this very closely. – Brad Koch Apr 02 '14 at 18:01
  • Yes, that is the point of the where ST_GeometryType(geom)='ST_Polygon' at the end. kind of deals with the points and linestrings :-) – John Powell Apr 02 '14 at 18:04
0

Turns out that running this postprocessing step does the trick:

UPDATE place_shapes
  SET geometry=ST_SimplifyPreserveTopology(geometry, 0.0001)
  WHERE ST_IsValid(geometry) = false;

The 0.0001 is the tolerance in degrees, you may need to tweak that to your taste, but for street map data that seems about right.

Also, if your shape table enforces validity (which it should), you'll need to use shp2pgsql to create a temporary unvalidated table, fix up your polygons there, and only then copy them to the master table.

lambshaanxy
  • 22,552
  • 10
  • 68
  • 92
  • 2
    This may fix the issue, but not necessarily for the right reasons, seeing as simplify with topology preservation will actually alter the underlying geometries, so what you get back will not necessarily be the original polygon, fixed or not. It would be much better to use ST_MakeValid which is designed for exactly this sort of thing -- removing self-intersections. – John Powell Jan 25 '14 at 19:22