How to get the nearest point on a linestring to a given point?

I've been using PostGIS for a long time now but never had to use the LINESTRING geometry ...! :)

Here's what I would like to do : I have a table of linestrings (representing streets of a given city, SRID 3395) and I would like to find the nearest linestrings to a given point (GPS position, SRID 4326).

The solution I found is to select all the linestrings within my point using the expand() method and determine the distance between each linestring and my point using the ST_Distance() method.

Here's the SQL :

SELECT myLineId, myLineName, ST_Distance(ST_Transform(GeomFromText('POINT(LON LAT)',4326),3395),myLineGeom) AS myLineDistance
FROM myLines
WHERE myLineGeom && expand(ST_Transform(GeomFromText('POINT(LON LAT)',4326),3395), 100)
ORDER BY myLineDistance;

The results I get look OK but I have the feeling something is wrong in my implementation.

1) Do you guys think the expand() can get all the concerned linestrings?

2) Do you guys think the ST_Distance() is the right method to use? I guess I'm doing it wrong since the distance I would like to get is the smallest distance between the point and my line and not the distance between the point and one of the points of the linestring.

Illustration: ad 1) Looking at the documentation for your used functions, I'd say: "Yes, all concerned linestrings will be found."

expand(geometry, float)

This function returns a bounding box expanded in all directions from the bounding box of the input geometry, by an amount specified in the second argument. Very useful for distance() queries, to add an index filter to the query.

A && B

The "&&" operator is the "overlaps" operator. If A's bounding box overlaps B's bounding box the operator returns true.

ad 2) You should be able to achieve what you want via:

line_interpolate_point(linestring, line_locate_point(LineString, Point))

line_interpolate_point(linestring, location)

Interpolates a point along a line. First argument must be a LINESTRING. Second argument is a float8 between 0 and 1 representing fraction of total 2d length the point has to be located.

line_locate_point(LineString, Point)

Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length. You can use the returned location to extract a Point (line_interpolate_point)

• For point 2), i was just wondering if ST_Distance between a POINT geometry and a LINESTRING geometry gives the smallest distance possible between theses (aka the length of the perpendicular line between the POINT and the LINGESTRING); i do want distance from every LINESTRING geometry :) – Vivi Sep 21 '10 at 12:04
• And I guess it's not the distance I am looking for since "the line_locate_point function gives you a value between 0 and 1 representing the location of the closest point on LineString to the given Point" :/ – Vivi Sep 21 '10 at 12:06
• I'm afraid you lost me in your last comment. Now I'm not sure anymore what you want ;) – underdark Sep 21 '10 at 12:15
• Sorry :) I would like this : giving a LINESTRING geometry (representing a path) and a POINT geometry, i want to have the nearest POINT geometry which is on the path (which may not be a point of the LINESTRING geometry definition). Is it clear? Maybe I should update my post with a drawing :D – Vivi Sep 21 '10 at 12:57
• I can't update my post so here's a link to a drawing of what I would like to get : i.imgur.com/UwPxo.jpg – Vivi Sep 21 '10 at 14:34

Hallo

First the question about what ST_Distance returns. ST_Distance returns the shortest distance between the line and the point (or what geometry types are inputed) That means that ST_Distance between point (1 3) and linestring (0 0,0 10) will return 1. The distance will not be measured between the point and (0 0) or the point and (0 10) but from the point (1 3) to (0 3).

So from what I understand ST_Distance gives you the answer you want.

If you want to find the point (0 3) in the example above you can use ST_Closestpoint if you have PostGIS 1.5 For my example you use it like this: ST_Closestpoint('LINESTRING(0 0,0 10)'::geometry, 'POINT(1 3)'::geometry) then you should get the point (0 3) in return, the point on the line that is closest to your point.

HTH Nicklas

I found it :) (Well i guess :P)

Using the ST_Line_Locate_Point() and ST_Line_Interpolate_point() I managed to get a point which IS NOT part of the LINESTRING definition but IS on the said line :) All I have to do is to get the distance from my point to this point and I'm done.

SELECT AsText(ST_Line_Interpolate_Point(myLineGeom,ST_Line_Locate_Point(myLineGeom,ST_Transform(GeomFromText('POINT(LON LAT)',4326),3395))))
FROM myLines
WHERE myGeom && expand(ST_Transform(GeomFromText('POINT(LON LAT)',4326),3395), 100)

The ST_Line_Locate_Point() method find the location of the closest point on the line to the given point, the ST_Line_Interpolate_Point method turn this location into a point.

• St_distance between the point and the line will give you the same answer. Why do you think that you have to do it this way? – Nicklas Avén Nov 5 '13 at 17:07
• I think ST_Distance can be used with any geometry type. postgis.refractions.net/docs/ST_Distance.html : ST_Distance(geometry g1, geometry g2) – Magno C Nov 8 '13 at 15:01

This Postgis archive thread can answer you http://postgis.refractions.net/pipermail/postgis-users/2007-June/016045.html

• refractions.net doesn't work for me: "refractions.net expired on 09/14/2010 and is pending renewal or deletion." – underdark Sep 21 '10 at 15:54
• @underdark it works for me – dassouki Sep 21 '10 at 17:58
• @dassouki: that's weird :| – underdark Sep 21 '10 at 18:04
• @underdark refractions.net work again – ThomasG77 Sep 21 '10 at 23:16