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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
6
  • Can you elaborate a bit on using small buffers? How do I do that?
    – diciu
    Aug 11, 2010 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, 2010 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, 2010 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 (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, 2010 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, 2010 at 13:36
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"
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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