How to calculate the "depth" of a linestring using postgis

Question

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:

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.