[postgis-users] Linemerge roads

[Stephen Woodbridge]

On 5/19/2011 7:11 PM, Peter wrote:
> My layer contains a small number of linestrings that have a small Y, ie
> three linestrings two which share one end point of the third. How can i
> query to locate these, so i can manually delete them, or failing that do
> a st_linemerge to LINESTRING and have it basically ignore the two ends
> of the Y, (or better still the shortest end).

Hi Peter,

This is a tough problem I think because presumably it is a little more 
complex than stated. I assume that the real problem looks more like this:

                       A
                       |
                       |
I----------J---K------B--C----N
             \ /          |
              L           |
              |           D
              |
              M

or:


         I         A(This leg might be a problem)
          \        |
           \       |
            J---K--B---------N
             \ /
              L
             /
            /
           M

or some other variants where the triangles are not so proportional.

So some thoughts on locating points the are clustered close together 
like the triangles. In pgRouting there is a stored procedure 
assign_vertex_id() that takes a tolerance for how close together 
endpoints can be to be considered the same.

You could run your data through that and set the tolerance for the 
longest segment length that you want to remove. So in the above pictures 
it would assign the same node number to the cluster of three. It would 
then be very easy to locate all edges where the start and end vertex id 
were the same, ie the little triangle, and the adjoining edges would 
also have that same vertex id.

Some problems might be, B-C link might get squeezed out on top and 
J-K-L,B nodes might get merged on the bottom. And you might have other 
problems depending on your data like:

            B
            |
            |
A----------C--------------D
          G------\
         / \      \
        /   \      H
       J     K

Here two disconnected networks might get join if C and G are merged by 
closeness! So you will need to do some analysis of each merged set and 
determine if it is valid or not based on some set of rules you have for 
what to keep and what to merge. You could then decide if you want remove 
the triangle, and maybe add a new point at say the centroid of the 
triangle, and then extend the connecting edges to the new node, or do 
something else. Anyway the node numbering just gives you a way to detect 
merged nodes, then your analysis will decide what changes if any to make.

You do not need to load all of pgRouting, just copy a few of the stored 
procedures the do the assign_vertex_id().

-Steve