Intersection postgis TopologyException

Question

I'm trying to make a quite simple analysis in Postgis, but I'm finding some problems. I want to in the first place create buffers for then, intersects themselves but one, this will be differenced. With this product I want to intersects again with another layer. But, here is the problem Postgis complains about a non-existent error topology. I say non-existent because there is no feature in that coordinate.

> Error performing intersection: TopologyException: Input geom 1 is
> invalid: Self-intersection at or near point 723120.23091093975
> 4379700.195452339 at 723120.23091093975 4379700.195452339

Here is the SQL sentence that I'm using:

     select 1 as id, st_intersection(diff_1.st_difference, mapa_ruido_dia.geom)
    from
    (select 1 as id, st_difference(intersectionEMT_valenbisi_carrilBici.st_intersection, buf_ocio.st_union)
    from
    (select 1 as id, st_intersection(buf_carril_bici.st_union,inter_emt_valenbisi.st_intersection)
    from
    (select 1 as id, st_intersection(buf_emt.st_union,buf_valenbisi.st_union)
    from
    (SELECT 1 as id, ST_union(st_buffer(emt_paradas.geom, 200)) FROM emt_paradas )as buf_emt,
    (SELECT 1 as id, ST_union(st_buffer(valenbisi.geom, 200)) FROM valenbisi )as buf_valenbisi) as inter_emt_valenbisi,
    (SELECT 1 as id, ST_union(st_buffer(carril_bici.geom, 200)) FROM carril_bici )as buf_carril_bici) as intersectionEMT_valenbisi_carrilBici,
    (SELECT 1 as id, ST_union(st_buffer(ocio.geom, 250)) FROM ocio )as buf_ocio)as diff_1, mapa_ruido_dia 
     where mapa_ruido_dia.gridcode='1' or mapa_ruido_dia.gridcode='2'

Here is the database

This is the place where postgis says there's a problem,and the two layers I want to intersect

---

EDIT:

What I try to do is the next:

1. First I want to create a buffer for emt_paradas, and the layer valenbisi, and then intersect them, the result I will call "intersect_1".
2. Second, I want to create a buffer for carril_bici, and intersect with "intersect_1". The result I will call "intersect_2".
3. Third, I want to create a buffer for layer ocio and make a difference with "intersect_2". The result I will call: "diff_1".
4. Finnaly I want to intersect "diff_1" with layer mapa_ruido_dia, ideally where the field gridcode equals one and two.

Answer 1

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

1. 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.

2. 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.