1

I have imported Open Street Map lines into PostGIS 2.1 and I need to clean up the self intersecting ways. I have done some manual edits in the past but there are many more ST_IsSimple(way) = false than I have time to do by hand. Is there a quick way to find rings and split off their dangles? I would like to write a ST_MakeSimple(the_geom) function for handling these in the future, it would be equivalent to ST_MakeValid(the_geom) that makes polygons valid.

An example is shown selected in green in the image. What will make it simple is to cut the tail off of the ring.

Looped linestring with a tail

For the linestring pictured above ST_IsSimple(ST_Node(way)) is false. Adding nodes is not able to self intersection. However the linestring below is fixed, it just doubles back on itself, so it can be made simple.
A linestring that doubles back on itself

  • 1
    Might ST_Node be what you're looking for? – Michal Zimmermann Mar 23 '16 at 18:01
  • Thanks for the idea it works for about 10% of the non-simple lines, the number went from 78 to 72 after ST_Node. – thayer Mar 23 '16 at 18:31
  • Could you post a screenshot? – Michal Zimmermann Mar 23 '16 at 18:35
  • I posted some images, one where ST_Node does not work, and one where it does. – thayer Mar 23 '16 at 19:43
0

The following query converts 85% of of the linestrings in my sample of not simple linestrings to being ST_IsSimple equal to true. I realized after I posted my question that my situation is similar to this previously answered post. In my case the majority of the lines are made simple by splitting the tails off of rings by using the end point of the ring: ST_Split(way, ST_EndPoint(way)). Interestingly, in my sample the rings never begin at the start point of the line so splitting using the start point did not have any effect. At the end I union in the linestrings that are made simple using ST_Node per Michal Zimmermann's suggestion in the comments to my original question.

NOTE that calling ST_Split on linestrings that have already been ST_Node'd will create non-simple features, so they need to be excluded and unioned back on at the end.

Also NOTE that I have added a unique id 'gid' to planet_osm_line to account for the non-unique nature of osm_id after osm2pgsql import.

Also NOTE that it would probably be good to create new gid's for the split features.

WITH A AS
( SELECT gid, ST_IsSimple(way) AS simple, 
    ST_IsSimple(ST_Node(way)) AS simple_afternode, 
    way AS geom--ST_IsSimple() AS simple--, ST_IsSimple(St_Node(way)) AS noded
  FROM planet_osm_line
  WHERE ST_IsSimple(way) = false)

( SELECT S.gid, S.simple, S.simple_afternode, 
    ST_IsSimple(S.geom) AS simple_aftersplitend, S.geom
  FROM 
  ( SELECT A.gid, simple, simple_afternode, 
     (ST_Dump(ST_Split(A.geom,ST_EndPoint(A.geom)))).geom AS geom
    FROM A
    WHERE simple_afternode <> true
  ) AS S
  ORDER BY simple, simple_afternode, simple_aftersplitend, gid
)

UNION ALL

( SELECT N.gid, N.simple, N.simple_afternode, false AS simple_aftersplitend, N.geom
  FROM 
  ( SELECT A.gid, simple, simple_afternode, geom
    FROM A
    WHERE simple_afternode = true
  ) AS N
  ORDER BY simple, simple_afternode, simple_aftersplitend, gid
)

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.