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

[Stefan Keller]

Hi Nicklas

2013/1/23 Nicklas Avén <nicklas.aven at jordogskog.no>:
> Hallo
>
> Your problem is:
> ST_Distance(osm_line.way,ST_ClosestPoint(osm_line.way, mypt.geom))
(...)
> 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)

Exactly.

(...)
> 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.

Yes, but I think one could use <#> instead of <-> (if newest release
is available):

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

> Your query rewritten would be something like:

Many thanks. That seems to be the final solution

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) -- tolerance=100m
ORDER BY 2
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/

Nice blog.
But I think that asks either for the closest point itself, whereas I
ultimately only want the closest line (or id) and the distance.
Thus, consequently it leads to the same solution, as above?

-S.

2013/1/23 Nicklas Avén <nicklas.aven at jordogskog.no>:
> 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
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>