Computing a point on a Linestring

[Jim]

I'm will be demonstrating my lack ofspatialite knowledge  with the following questions:

Assume:

1. a Linestring A-B of multiple Line segments.

2. a Point P close to but not on  Linestring A-B.  In reality, Point P is within an ST_Buffer surrounding Linestring A-B.

3. the distance result should be in meters, however, the spatilalite data in 4326.

Questions:

1. how can I determine the Point  X on Linestring A-B that is closest to P, maybe perpendicular to Linestring A-B

2. given the Linestring A-X, does St_Length compute a straight line distance between Point A and Point X, or does it follow the "contour" of the Linestring, A-X,  i.e., compute the sum of the length individual Line segments that make up  Linestring A-X

3. can this somehow be done on one SQL statement or are multiple required.

Any help would be appreciated.

Regards,

Jim

[a.fu...@lqt.it]

On Mon, 26 Jun 2023 06:59:40 -0700 (PDT), Jim wrote:
> I'm will be demonstrating my lack ofspatialite knowledge  with the
> following questions:
>
> Assume:
> 1. a Linestring A-B of multiple Line segments.
>
> 2. a Point P close to but not on  Linestring A-B.  In reality, Point
> P is within an ST_Buffer surrounding Linestring A-B.
>
> 3. the distance result should be in meters, however, the spatilalite
> data in 4326.
>
> Questions:
>

Hi Jim,

please read the documentation for futher details:

https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

> 1. how can I determine the Point  X on Linestring A-B that is closest
> to P, maybe perpendicular to Linestring A-B
>

you can directly call ST_ShortestLine( geom1 , geom2 )

it will return the shortest line between two geometries, which always
is a simple segment connecting two points.
In other words, it will certainly be a straight line.

> 2. given the Linestring A-X, does St_Length compute a straight line
> distance between Point A and Point X, or does it follow the "contour"
> of the Linestring, A-X,  i.e., compute the sum of the length
> individual Line segments that make up  Linestring A-X
>

ST_Length() will always follow the "contour" of the Linestring.
if instead you wish to get the straight line distance you should
call ST_Distance();

in this case both will return the same result because it's
a straight line.

note: if your geometries are 4326 and you wish to get
lengths measured in metres then you should call the
"special" flavor of ST_Length() asking for geodesic
computations on the surface of the ellipsoid, that is:

ST_Length( line, 1);

> 3. can this somehow be done on one SQL statement or are multiple
> required.
>
> Any help would be appreciated.
>

you simply have to define a chain of function calls
nested one inside the other, something like:

SELECT ST_Length(
ST_ShortestLine( geom1 , geom2 ), 1)
FROM .....

bye Sandro

[Jim]

Sandro,

Thank you for the insight.

And to clarify my question about ST_LENGTH following the "contour" of the Linestring, I may have used the word "contour" incorrectly.  What I meant to ask is, given a Linestring  A-B-C-D, which represents a road, does ST_LENGTH compute the sum of the lengths A-B ,  B-C,  and C-D, or does it return the  straight line distance between A and D?

Using my original assumptions,  since my objective is to compute the length of the Linestring from A-X-P then I might use conceptually:

1. ST_ClosestPoint(Linestring(A-B), POINT(P) giving me Point X

2. Create a new Linestring A-X using what combination of ST_????  giving me Linestring(A-X)

3. Compute ST_Length(Linestring(A-X) + ST_LENGTH(Line(X-P)

Does that sound reasonable?

Now comes the hard part for me.  I am nowhere nearly as proficient with SQL as your are. Could I ask you to outline  the above the above in SQL?

Regards,

Jim

[a.fu...@lqt.it]

On Mon, 26 Jun 2023 11:44:47 -0700 (PDT), Jim wrote:
> Sandro,
>
> Thank you for the insight.
>
> And to clarify my question about ST_LENGTH following the "contour" of
> the Linestring, I may have used the word "contour" incorrectly.  What
> I meant to ask is, given a Linestring  A-B-C-D, which represents a
> road, does ST_LENGTH compute the sum of the lengths A-B ,  B-C,  and
> C-D
>

certainly yes, the LENGTH algorithm works exactly this way

> or does it return the  straight line distance between A and D?
>

absolutely not: if you need to know the distance between
Start and End points of the linestring you must explicitly
call ST_Distance()

> Using my original assumptions,  since my objective is to compute the
> length of the Linestring from A-X-P then I might use conceptually:
>
> 1. ST_ClosestPoint(Linestring(A-B), POINT(P) giving me Point X
> 2. Create a new Linestring A-X using what combination of ST_???? 
> giving me Linestring(A-X)
>
> 3. Compute ST_Length(Linestring(A-X) + ST_LENGTH(Line(X-P)
>
> Does that sound reasonable?
>

please see the attached figure:

1. first of all you must determine the X point position, that
is you must identify the point on the linestring closest
to the external point P
the appropriate Spatial SQL function is ST_ClosestPoint()

2. then you must insert X into the linestring by calling
ST_Snap()

3. now you'll be ready for cutting the linestring at point X;
in other words you'll extract A-B-C-X starting from
A-B-C-D using X as a blade. see ST_Split() and friends.

4. final steps: you simply have to add the lengths of A-B-C-X
and of X-P

all together now in a single SQL expression:

SELECT ST_Length(
ST_SplitLeft(
ST_Snap(x.l, ST_ClosestPoint(x.l, x.p), 0.001),
ST_ClosestPoint(x.l, x.p)))
+ ST_Length(
ST_ShortestLine(x.l, x.p))
FROM
(SELECT ST_GeomFromText('LINESTRING(0 0, 100 0)') AS l,
MakePoint(50, 10) AS p) AS x;


I'm happy to leave all the work of refining and fine-tuning
the above SQL expression to you ;-)

bye Sandro