[GIS] How to deal with “ERROR: GEOSUnaryUnion: TopologyException: Self-intersection”

postgispostgresql

I'm using Postgres 9.4 and PostGIS 2.1. My table looks like this:

------------------------------------------------------------------
 ogc_fid      | integer                | not null default
 wkb_geometry | geometry(Polygon,4326) |
 county       | character varying      |

I am trying to create the union of several thousand polygon fields:

select ST_UNION(wkb_geometry) from mytable where county='Glos';

But I get this error:

ERROR:  GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: 
Self-intersection at or near point -2.3936967722194602 51.540060494135503 at -2.3936967722194602 51.540060494135503

Why could this be happening? And what can I do about it? Is there a way that I can check whether the input polygons are valid?

I don't mind if the fields overlap: I just need to merge them all down (as it were) to create the overall union.

UPDATE: I'm trying this:

SELECT ogc_fid, ST_isValidReason(wkb_geometry) FROM mytable WHERE (county='Glos') AND (NOT ST_isValid(wkb_geometry));

Results:

   ogc_fid |                      st_isvalidreason
-----------+------------------------------------------------------------
  53270254 | Ring Self-intersection[-2.42234450631667 51.5302222606785]
  20669431 | Ring Self-intersection[-2.42680440846532 51.5321194786993]
  20851050 | Self-intersection[-2.39369677221946 51.5400604941355]

Best Answer

I ended up using the following query:

UPDATE mytable SET wkb_geometry = ST_MakeValid(wkb_geometry);

As stated in the documentation, already-valid geometries are returned without further intervention.

Related Solutions

PostGIS Topology – Dealing with Ring Self-Intersections

I think what is happening is that your self-intersecting polygons becomes MULTIPOLYGONS when buffering.

you have two options:

1 remove the constraint "enforce_geotype_the_geom", you can do that in pgAdmin
2 put the result in a new table instead of updating the old. that is often a good way of doing things because then you don't change anything in your original table. the query can look something like:

CREATE TABLE new_buffered_table as
SELECT ST_Buffer(the_geom,0.0) as the_geom, gid FROM original_table;

of course you might want to bring more fields to your new table.

try the buffer trick first. In the second approach Paul can tell himself what affect the empty linestring has. I don't remember how that magic happens.

[GIS] Intersection postgis TopologyException

I just had a similar problem, and the solution is the following:

You have many queries, so the first question is which one of them fails. I'd suggest bisecting the entire SQL query to find where the problem happens exactly. Comment one half of the query (but make it still work) and see if the problem is still there, if so, bisect this half, if not, the error is in the other, bisect that half instead.

When you see which particular query fails, check which table has invalid geometries. My example:

 with resi as (select st_transform(way, 3857) way from residential),
      road_buff as (select st_buffer(way, 40) way from roads)
 select st_intersection(r.way, b.way) from resi r, road_buff b
 where st_intersects(r.way, b.way)

This query failed, and I just looked for which CTE contains invalid geometry:

 with resi as (select st_transform(way, 3857) way from residential),
      road_buff as (select st_buffer(way, 40) way from roads)
 select r.way, st_isvalid(r.way) val from resi r

Then the same for road_buff CTE. Turned out resi had invalid geometries. Then I filtered by val=false, looked at that table in QGIS, and saw that some polygons were drawn self-intersecting.

The solution was to add st_makevalid(way) on that CTE:

    with resi as (select st_transform(st_makevalid(way), 3857) way from residential

And this solved the issue.

Here's a good tool to fix different invalid geometries.

Related Question