Detect and measure loop at end of linestring

Question

In PostGIS, is there a good way of finding out whether a linestring loops back on itself and, in that case, how long the loop is?

I am rendering 1:50000 topographic maps from OpenStreetMap data, trying to emulate the style of the maps produced by the Swedish geodetic survey, Lantmäteriverket. On these, a turning circle, as often found at the end of forestry roads, is represented by a small white disc. In OSM data, however, editors simply often draw a loop at the end of the road (because that is often what it looks like). So, I want to detect a small loop at the end of a linestring, so that I can render it properly.

Since I am working with OSM data, I have topological information as well as geometric, so the "whether" bit is easy. Just see if the end node of the way is also an interior node. But how do I "chop off" the loop to determine its length (or diameter, or any other dimension)? There is ST_Line_Substring, but that requires proportional distance along the way, which I do not have. I do have node indices, so I could do ST_LineLocatePoint(ST_PointN(...)) but since I will always have two coincident points (start and end of loop) I doubt this will work.

Answer 1

Have you looked at ST_Split? Assuming that the loop is at the end of the linestring, your query would look like this:

SELECT ST_GeometryN(ST_Split(geom, ST_EndPoint(geom)),1) FROM lines

ST_Split breaks the line into two at the endpoint.

ST_GeometryN gets the first linestring from the MultiLineString that is created with ST_Split.