# How to calculate the “depth” of a linestring using postgis

I have linestring features, which look like bent linestrings, hemicycles or convex or concave linestrings. In the moment I can select the distance between the startpoint and endpoint of each feature. Now I want to calculate the vertical (orthogonal) distance between

a) the imaginary line between startpoint and endpoint

b) and the most distant point of the linestring

Like the maximum orthogonal distance between the deepest point in a hole referring to a straight surface. How can I calculate this with postgis?

Here is a picture: • I don't know PostGIS at all, but would like to remark that there is a simple formula which might help: given a start point S=(x0,y0) and end point P=(x1,y1), you wish to find points U (among the vertices of the linestring) attaining extreme values of |n.(U-S)|. Here, n is the vector (y0-y1,x1-x0), "-" is a vector difference, "." represents the dot product, and "||" is absolute value. – whuber Mar 15 '13 at 17:45
• @whuber you have said it before that you don't know PostGIS. Isn't it time to download and give it a try;-) With your knowledge of algorithms open source must be superior over "black boxes". And if you see need of improvement, just get on board :-) – Nicklas Avén Mar 16 '13 at 19:32
• Let's look at the point on the curve where the red line hits the curve. Let's assume the point spans a triangle with the endpoint and startpoint of the curve. Is it right, that the area of this triangle has it's maximum at this point? Maybe you can check when the triangle hits this maximum, looping over the points on the curve. After that you have to calculate the height of the triangle. An Idea =) – Stefan Mar 18 '13 at 0:45
• No, it is the key_word geom that should be used. – Nicklas Avén Mar 18 '13 at 13:10

Consider some test data similar top the thick line in the question's figure:

``````SELECT 'LINESTRING (114 374, 200 380, 250 350, 259 343, 350 280, 380 180, 383 169, 360 80)'::geometry AS geom
INTO TEMP data;
``````

the straight line (dashed) can be constructed from the start and end points:

``````SELECT ST_AsText(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom)))
FROM data;

st_astext
----------------------------
LINESTRING(114 374,360 80)
(1 row)
``````

Distances from each vertex (coordinate) can then be found to the straight line:

``````SELECT ST_Distance((ST_DumpPoints(geom)).geom, ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom)))
FROM data
GROUP BY geom;

st_distance
------------------
0
69.806901017674
88.9020622825982
91.3124350980067
120.67515848571
79.5109993654224
74.7528608207199
0
(8 rows)
``````

All of this logic can be put into a nested query to get the maximum depth from the geometry:

``````SELECT geom, max(depth)
FROM (
SELECT geom, ST_Distance((ST_DumpPoints(geom)).geom, ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS depth
FROM data
GROUP BY geom
) AS f
GROUP BY geom;
``````

which is 120.67515848571 for this example.

• This is really a smart way to do this. Many thanks!! – Stefan Mar 18 '13 at 13:31

Something like this would do for your part a):

``````SELECT ST_Distance(startpt.geom, endpt.geom) AS "Start to End Distance"
FROM
(SELECT ST_StartPoint(geom) FROM line_feature) AS startpt,
(SELECT ST_EndPoint(geom) FROM line_feature) AS endpt;
``````

I'm not quite clear what you're asking in b). But I would guess that it requires exploding the line into a geometry collection of points, then looping thru all points to find the max distance from the start.

• Thanks. Your first answer is the part I've already done. Your second answer is an interesting approach to calculate this. But I have no experience in looping thru data with postgis. Do you have an idea? – Stefan Mar 15 '13 at 14:32

Take a look at ST_LongestLine.

By taking the start or end point from longestline between your imaginary line and the curved line you should get what you want.

EDIT:

Ok, the brute force way is the way to go I guess. Iterate every point in curve and take the one with the longest mindistance to the imaginary line. Something like this:

``````WITH g AS
(SELECT geom FROm curve_table)
SELECT ST_Distance(f.points_from_curve, im_l.imaginary_line) dist,
ST_ShortestLine(f.points_from_curve, im_l.imaginary_line) the_geom
FROM
(SELECT (ST_DumpPoints(geom )).geom points_from_curve from g) f,
(SELECT ST_MakeLine(ST_StartPoint(g.geom),ST_EndPoint(g.geom)) imaginary_line FROM g) im_l
ORDER BY dist desc
LIMIT 1;
``````

ST_Shortestline visualizes the red line in your example.

• I've posted my attempt in my question above. It produces not the correct result. – Stefan Mar 15 '13 at 23:47
• @user15459 try using the midpoint of the imaginary line instead of the whole line. That should work if the "deepest" point is on the middle of the curve. – Nicklas Avén Mar 16 '13 at 17:33
• The points are not in the middle of the curves and not in the middle of the imaginary lines. So it is more complex. – Stefan Mar 16 '13 at 18:32
• @user15459 Can you visualize the curves in some way? – Nicklas Avén Mar 16 '13 at 19:28
• I've uploaded a picture. I'm interested in the length of the red linestring. – Stefan Mar 16 '13 at 20:08

Here is a simple algorithm to calculate this 'width' or 'depth'. It assumes that the lines bend on only one side.

1. First find the angle defined by the start and end point of the line and the horizontal using ST_Azimuth.

2. Rotate the polyline by negative of the angle using ST_Rotate.

3. Get the Bounding box of the rotated Line. This bounding box is basically a rectangle with length equal to the distance between the Start Point & End Point, and width equal to your 'depth'

4. Get the area of your bounding box using ST_Area.

5. Divide the Area by the distance between the start point & End point, using ST-Distance. http://postgis.net/docs/ST_Distance.html

Note that you can get the start and end point of the line using ST_StartPoint & ST_EndPoint

You can create a function for this, and use it get the results from your table.