10

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

The Yellow star is where I have the topology exception.


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.
8
  • Valencia bike lines, cool, I have used them. Is there any way you could reduce your problem down a bit. It would be useful to know which of the unions, differences or buffers, was causing the problem. In my experience, this kind of error, comes when Linestrings get included as part of some result, so that you end up trying to union or difference a geometry collection with something else -- bad. You can fix this, by using ST_GeometryType and ST_Dump to remove all but (Multi) Polygons. – John Powell Nov 28 '14 at 10:33
  • Also, what does something like buf_ocio.st_union refer to. It seems like you are using a function name for a non-existent field name. – John Powell Dec 1 '14 at 10:29
  • John, thank you for your comment. The problem is in the last one. That appears the first, "select 1 as id, st_intersection(diff_1.st_difference, mapa_ruido_dia.geom)" The others are working fine.Linestrings are just used for create the buffer, so I'm working only with polygons. I've started again but the result is the same. Topology exception. I'm going to explicit better what I want to do. – Luismi Dec 1 '14 at 19:07
  • Sorry, I think the layer mapa_ruido_dia it's a completly mess. Thank you John. :) – Luismi Dec 2 '14 at 14:21
  • 1
    :-). Have you tried running ST_IsValid and/or ST_MakeValid? It is possible that you have an invalid geometry to start with. It is also possible that your queries are creating a Linestring, which could also be the cause of such errors. – John Powell Dec 2 '14 at 14:31
10

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.

Self-intersecting polygons

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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