16

I've imported some data in a Postgis database and some of the geometries are reported invalid (ST_IsValidReason reports self-intersection or ring self-intersection).

The queries I am performing don't seem affected by the invalid aspect of these geometries (i'm only using ST_Distance queries).

What are the things that break when geometries are invalid?

Is fixing these geometries "automatically" (buffer(geom, 0) or ST_SimplifyPreserveTopology(geom, 0.0001)) an option?

Adam Matan
  • 6,838
  • 7
  • 37
  • 50
diciu
  • 2,348
  • 16
  • 15

2 Answers2

20

Keeping malformed data is a bad idea, because you can never predict when and where will the failure occur. Moreover, malformed data can cause Heisenbugs, the most vicious and illusive type of bugs.

I think that it is a bit pointless to discuss the possible outcome of storing invalid geometries. Having that said, The consequences can include:

  • Wrong results (that is, the ST_Distance will return inaccurate or plain wrong figures)
  • Database performance issues: Keeping malformed data can seriously damage the database performance and create huge log file, because every function call will write an error to the log and disrupted the ordinary database work.
  • Database crashes.
  • Application crashes - either caused by receiving malformed data from the database, or by receiving unreasonable outcome (negative distance, for example).
  • Phantom behaviour (see link above). This is the worst consequence of all. You'll have strange things happening. Slowdowns, data loss, crashes, unreasonable results, long pauses, no responsiveness and many other curses. You might not be able to spot them or reproduce them, because they all fall under the "undefined" category in every documentation.

My advice - if small buffers do not significantly harm your data consistency, use them to prevent any of the above from happening. Keep your data valid.

Adam Matan
  • 6,838
  • 7
  • 37
  • 50
  • Can you elaborate a bit on using small buffers? How do I do that? – diciu Aug 11 '10 at 12:57
  • 1
    ST_Buffer(the_geom, 0.0000001) might do the trick for self intersection. Use it only if the consequences of a slightly larger geometry are not severe. – Adam Matan Aug 11 '10 at 13:07
  • 1
    My experience is that correcting malformed data is quite an investigation. But even though it is time-consuming, it normally is worth the effort. The ST_Buffer(the_geom, 0.0000001) trick definitely helps a lot. – Chau Aug 11 '10 at 13:16
  • The thing is ST_Buffer fixes the geometry but the result is not really what I expected - for this invalid polygon here (http://www.openstreetmap.org/browse/way/51954364) ST_Buffer only returns the top left rectangle.

    ST_SimplifyPreserveTopology seems to be closer to what I need (valid geometry but as close as possible to the invalid original).

    Any downsides in using ST_SimplifyPreserveTopology?

     – diciu Aug 11 '10 at 13:26
  • That geometry should be processed as a MULTIPOLYGON of two Polygons, not as a single POLYGON. Try to get the original WKT if possible. – Adam Matan Aug 11 '10 at 13:36
  • ST_MakeValid can also be useful, but examine the results to ensure it succeeded and didn't produce a geometry collection. – jpmc26 Jul 31 '15 at 23:14
16

You can prevent invalid geometries entering your database in the first place. For PostgreSQL/PostGIS users, this is simple to do with check constraints. For example, consider a table public.my_valid_table with a column of polygon geometries geom, use the following SQL/DDL:

ALTER TABLE public.my_valid_table
  ADD CONSTRAINT enforce_valid_geom CHECK (st_isvalid(geom));

Note: this table has to have valid polygons before enforcing the constraint.

If you then try to insert/add an invalid geometry, you will see an error:

ERROR:  new row for relation "my_valid_table" violates check constraint "enforce_valid_geom"
Mike T
  • 42,095
  • 10
  • 126
  • 187