<html>
<head>
</head>
<body>Hallo
<div><br />
</div>
<div>Your problem is:</div>
<div>ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom))</div>
<div><br />
</div>
<div>there is two things with this, on warning and one error.</div>
<div><br />
</div>
<div>The error is that it should be </div>
<div>ST_Distance( mypt.geom,ST_ClosestPoint(osm_line.way, mypt.geom))</div>
<div>to get what you want. The way you wrote it you ask for thedistance from the interpolated point on the line to the line which will be 0 in all cases.</div>
<div>The notice or warning is that you should instead write </div>
<div>ST_Distance( osm_line.way, mypt.geom)</div>
<div><br />
</div>
<div>It is the same thing and will be twice as fast since it is only doing the calculation once instead of doing the exactly same thing twice.</div>
<div><br />
</div>
<div>ST_ClosestPoint is just one of the points that ST_Distance uses to get its result. To get both points use ST_ShortestLine.</div>
<div><br />
</div>
<div>So, from your query it doesn't look like you want any interpolated opint at all, just the distance (wich is calculated from a interpolated point behind the scenes)</div>
<div><br />
</div>
<div>Then you can use ST_DWithin to speed up since ST_Dwithin can use spatial indexes to do a first bounding box search.</div>
<div><br />
</div>
<div>Second and very important</div>
<div>Using KNN distance as you do will not always give you the right answer since the <-> operator only works on the center of the bounding box, not the geoemetry itself. So another geometry with the bbox-center much more far away can come much closer than the one you have found. In this case it happened to be the same and give correct result.</div>
<div><br />
</div>
<div>Your query rewritten would be something like:</div>
<div>
<div>SELECT</div>
<div> ST_AsText(osm_line.way) AS geom,</div>
<div> ST_Distance(osm_line.way, mypt.geom) AS label</div>
<div> FROM osm_line,</div>
<div> (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',</div>
<div>4326), 900913) AS geom) AS mypt</div>
<div> WHERE tags @> hstore('railway', 'rail') AND ST_DWithin(osm_line.way, mypt.geom,100)</div>
<div> ORDER BY ST_Distance(osm_line.way, mypt.geom)</div>
<div> LIMIT 1;</div>
<div><br />
</div>
<div>A slightly other approach I wrote about long ago here:</div>
<div>http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/</div>
<div><br />
</div>
<div>HTH</div>
<div><br />
</div>
<div>Nicklas Avén</div>
<div><br />
</div>
<div><br />
</div>
<div><br />
</div><br />
2013-01-23 Stefan Keller wrote:<br />
<br />
Hi Michi,<br />
><br />
>2013/1/23
<michael.scholz@dlr.de>:<br />
>> Hey! What about ST_ClosestPoint (http://postgis.refractions.net/documentation/manual-2.0/ST_ClosestPoint.html)?<br />
>><br />
>> Greets, Michi<br />
><br />
>That's it. but I got unexpected malfunctioning of the query which uses<br />
>ST_Distance:<br />
><br />
> -- Solution with KNN order index => OK:<br />
> SELECT<br />
> ST_AsText(osm_line.way) AS geom,<br />
> ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom)) AS label<br />
> FROM osm_line,<br />
> (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',<br />
>4326), 900913) AS geom) AS mypt<br />
> WHERE tags @> hstore('railway', 'rail')<br />
> ORDER BY ST_ClosestPoint(osm_line.way, mypt.geom) <-> mypt.geom<br />
> LIMIT 1<br />
><br />
> -- Solution with ST_Distance => wrong result. linestring is far away :-<<br />
> SELECT<br />
> ST_AsText(osm_line.way) AS geom,<br />
> ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom)) AS label<br />
> FROM osm_line,<br />
> (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',<br />
>4326), 900913) AS geom) AS mypt<br />
> WHERE tags @> hstore('railway', 'rail')<br />
> ORDER BY ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom))<br />
> LIMIT 1<br />
><br />
>You can reproduce that in http://labs.geometa.info/postgisterminal .<br />
>Simply paste it into the Query textarea.<br />
>Here's 'mypoint'<br />
><br />
> SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(8.8211<br />
>47.2221)', 4326), 900913)) as geom, 'XXX' AS label<br />
> LIMIT 1<br />
><br />
>-S.<br />
><br />
><br />
>>> -----Ursprüngliche Nachricht-----<br />
>>> Von: postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-<br />
>>> bounces@lists.osgeo.org] Im Auftrag von Stefan Keller<br />
>>> Gesendet: Mittwoch, 23. Januar 2013 03:25<br />
>>> An: PostGIS Users Discussion<br />
>>> Betreff: [postgis-users] Nearest linestring given a point, a tolerance and a set<br />
>>> of linestrings?<br />
>>><br />
>>> What is the nearest interpolated point on a linestring given a single point, a<br />
>>> tolerance (say 100m) and a set of linestrings (i.e. a table called myways with<br />
>>> linestring geometry column); return way/linestring id and distance?<br />
>>><br />
>>> It's a use case similar to snapping or to a navigation system where you have a<br />
>>> point and a set of lines.<br />
>>><br />
>>> I found hints to ST_Line_Interpolate_Point and ST_Line_Locate_Point but<br />
>>> theses posts/blogs are rather outdated and I'm not sure if KNN index would<br />
>>> do the job better?<br />
>>><br />
>>> - S.<br />
>>> _______________________________________________<br />
>>> postgis-users mailing list<br />
>>> postgis-users@lists.osgeo.org<br />
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br />
>> _______________________________________________<br />
>> postgis-users mailing list<br />
>> postgis-users@lists.osgeo.org<br />
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@lists.osgeo.org<br />
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users<br />
><br />
>
</michael.scholz@dlr.de></div>
</body>
</html>