[postgis-users] Nearest linestring given a point, a tolerance and a set of linestrings?

[=?utf-8?Q?Nicklas_Av=E9n?=]

Hallo


Your problem is:
ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom))


there is two things with this, on warning and one error.


The error is that it should be 
ST_Distance( mypt.geom,ST_ClosestPoint(osm_line.way, mypt.geom))
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.
The notice or warning is that you should instead write 
ST_Distance( osm_line.way, mypt.geom)


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.


ST_ClosestPoint is just one of the points that ST_Distance uses to get its result. To get both points use ST_ShortestLine.


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)


Then you can use ST_DWithin to speed up since ST_Dwithin can use spatial indexes to do a first bounding box search.


Second and very important
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.


Your query rewritten would be something like:

SELECT
    ST_AsText(osm_line.way) AS geom,
    ST_Distance(osm_line.way, mypt.geom) AS label
  FROM osm_line,
    (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',
4326), 900913) AS geom) AS mypt
  WHERE tags @> hstore('railway', 'rail') AND ST_DWithin(osm_line.way, mypt.geom,100)
  ORDER BY ST_Distance(osm_line.way, mypt.geom)
  LIMIT 1;


A slightly other approach I wrote about long ago here:
http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/


HTH


Nicklas Avén






2013-01-23 Stefan Keller  wrote:

Hi Michi,
>
>2013/1/23:
>> Hey! What about ST_ClosestPoint (http://postgis.refractions.net/documentation/manual-2.0/ST_ClosestPoint.html)?
>>
>> Greets, Michi
>
>That's it. but I got unexpected malfunctioning of the query which uses
>ST_Distance:
>
>  -- Solution with KNN order index => OK:
>  SELECT
>    ST_AsText(osm_line.way) AS geom,
>    ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom)) AS label
>  FROM osm_line,
>    (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',
>4326), 900913) AS geom) AS mypt
>  WHERE tags @> hstore('railway', 'rail')
>  ORDER BY ST_ClosestPoint(osm_line.way, mypt.geom) <-> mypt.geom
>  LIMIT 1
>
>  -- Solution with ST_Distance => wrong result. linestring is far away :-<
>  SELECT
>    ST_AsText(osm_line.way) AS geom,
>    ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom)) AS label
>  FROM osm_line,
>    (SELECT ST_Transform(ST_GeomFromText('POINT(8.8211 47.2221)',
>4326), 900913) AS geom) AS mypt
>  WHERE tags @> hstore('railway', 'rail')
>  ORDER BY ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom))
>  LIMIT 1
>
>You can reproduce that in http://labs.geometa.info/postgisterminal .
>Simply paste it into the Query textarea.
>Here's 'mypoint'
>
>  SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(8.8211
>47.2221)', 4326), 900913)) as geom, 'XXX' AS label
>  LIMIT 1
>
>-S.
>
>
>>> -----Ursprüngliche Nachricht-----
>>> Von: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
>>> bounces at lists.osgeo.org] Im Auftrag von Stefan Keller
>>> Gesendet: Mittwoch, 23. Januar 2013 03:25
>>> An: PostGIS Users Discussion
>>> Betreff: [postgis-users] Nearest linestring given a point, a tolerance and a set
>>> of linestrings?
>>>
>>> What is the nearest interpolated point on a linestring given a single point, a
>>> tolerance (say 100m) and a set of linestrings (i.e. a table called myways with
>>> linestring geometry column); return way/linestring id and distance?
>>>
>>> It's a use case similar to snapping or to a navigation system where you have a
>>> point and a set of lines.
>>>
>>> I found hints to ST_Line_Interpolate_Point and ST_Line_Locate_Point but
>>> theses posts/blogs are rather outdated and I'm not sure if KNN index would
>>> do the job better?
>>>
>>> - S.
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130123/a464e8b0/attachment.html>