# Nearest point of MultiPoint to another Point, over a LineString

I need to calculate the distance between some points on a line (LineString), using PostGIS, to find the nearest over a set of MultiPoint.

I know there is a function ST_Distance(), but this one returns a distance as the crow flies, with no possibility to take a path into account.

The problem is : "which of A or B is the nearest of S ?"

Here is a little drawing, for a better explanation :

Is there an alternative to ST_Distance() ?

I have already read Getting distance between two points over linestring using PostGIS?, but unfortunately, it doesn't give a complete reply : I have a MultiPoint feature.

The solution given wasn't succesfull, but I guess it may just need an adaptation ..?

• Possible duplicate of PostGIS get the distance between two points over a linestring Dec 4, 2016 at 20:11
• Well, not really : the example given uses "Point" features, I unfortunately need "MultiPoint". Dec 4, 2016 at 20:24
• What did you try when it "wasn't successful"?
– PolyGeo
Dec 4, 2016 at 21:04
• You could first split the `MultiPoint` into a set of `Point`s, find the distance to each, order the result and take the first. Dec 4, 2016 at 21:49

Here's a quick adaptation of the answer for this question. The additional part that you need is ST_Dump

``````SELECT
ST_Length(ST_LineSubstring(
line,
ST_LineLocatePoint(line, s),
ST_LineLocatePoint(line, (ST_Dump(p)).geom)))
,(ST_Dump(p)).path[1] ind
FROM (
SELECT
'LINESTRING(0 0, 10 0, 10 5, 0 5)'::geometry line,
'POINT(1 0)'::geometry s,
'MULTIPOINT(1 5, 10 3, 8 5)'::geometry p
) data
ORDER BY 1;
``````