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

Question

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:

Answer 1

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:

st_line_interpolate_point(linestring, st_line_locate_point(LineString, Point))

st_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.

st_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)

Answer 2

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

Answer 3

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.

Answer 4

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