Do you have a set of lines that you need to determine if there are any “dangle” nodes? A dangle is a line segment that overhangs another line segment. Now, some dangles are valid, like a pipe that terminates in a cul-de-sac.
A few people have posted about this already, but I figured I would give it a shot as well, as I think my SQL is a little more terse. Anyway, here is the query, and we’ll talk about it line by line:
SELECT DISTINCT g1 AS g
INTO dangles
FROM plines,
(SELECT g AS g1 FROM
(SELECT g, count(*) AS cnt
FROM
(SELECT ST_StartPoint(g) AS g FROM plines
UNION ALL SELECT ST_EndPoint(g) AS g FROM plines ) AS T1
GROUP BY g) AS T2
WHERE cnt = 1) AS T3
WHERE ST_Distance(g1, g) BETWEEN 0.01 AND 2;The first thing to notice is the most inner select statement. We are using ST_StartPoint and ST_EndPoint to grab the endpoints of the lines – these we’ll call nodes.
The next line to notice is where we are getting the count of the nodes. We are grabbing all the nodes, but using the GROUP BY function to return the number of nodes that occupy a place in space. Now, an intersection of two lines will have 2 nodes (from the first line and the second line). But, a “dangle” will only have one node occupying a space. This is where the next section of SQL comes into play.
What we want to do is only select those nodes where the count (cnt) is equal to 1. That means the node is just sitting there in space. It is a “dangle”. But, not all dangles are created equally, as I said above. That final WHERE clause lets me specify how far I want a dangle node to be from another node. In the example above, we are choosing under 2m apart. The last bit of SQL we have to consider is the DISTINCT clause. Nodes can be near one or more lines. We don’t want to double count them, so using DISTINCT eliminates the duplicates.
That’s it! Pretty easy. Think of the ST_Distance function as a variant of the basic SQL to find dangles. There are other variants we could add to this if we’d like, such as the length of the line the dangle touches has to be less than 5m, or something like that. That would be just a matter of adding another WHERE clause.