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

[Stefan Keller]

Hi Michi,

2013/1/23  <Michael.Scholz at dlr.de>:
> 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