Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

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 looks pretty OK but I have the feeling something is wrong in my implementation.

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

2) Do you guys thinks 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 point of the linestring.

Thanks for any tips!

Illustration:

alt text

share|improve this question
    
Oh thanks for the info :) –  Vivi Sep 21 '10 at 9:31
add comment

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:

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)

Source: http://main.merlin.com.ua/doc/postgis/docs/ch06.html

share|improve this answer
    
Thanks underdark. –  Vivi Sep 21 '10 at 12:02
    
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
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 '10 at 12:57
show 3 more comments
up vote 4 down vote accepted

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.

share|improve this answer
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? –  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
add comment

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

share|improve this answer
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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