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.


alt text


4 Answers 4


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)

  • 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, 2010 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, 2010 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, 2010 at 12:15
  • 1
    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, 2010 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, 2010 at 14:34


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.

  • 1
    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? Nov 5, 2013 at 17:07
  • 1
    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, 2013 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, 2010 at 15:54
  • @underdark it works for me
    – dassouki
    Sep 21, 2010 at 17:58
  • @dassouki: that's weird :|
    – underdark
    Sep 21, 2010 at 18:04
  • @underdark refractions.net work again
    – ThomasG77
    Sep 21, 2010 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.