[postgis-users] find nearest point on line where distance to point not on line is minimum - or lets say drop a perpendicular on the nearest line through a given point

[Marco Lechner - FOSSGIS e.V.]

Hi Mehmet,

thank you. This seems to be much faster then my experiments combining
line_locate_point() and line_interpolate_point() in subselects. The main
problem is now, that i have a bunch of lines (not just one specific) in
a table and I want the point to be calculated on the nearest line. The
recent request takes about 1.2 seconds because I have to calculate the
distances to all lines first to identify the nearest line. Is there a
better way?

Recent request:
SELECT multiline_locate_point(line, point) AS Ppoint_on_line FROM
(SELECT distance(the_geom, pointfromtext('POINT(3440580 5350000)',
31467)) AS dist, pointfromtext('POINT(3440580 5350000)', 31467) AS
point, the_geom AS line FROM linetable ORDER BY dist LIMIT 1) as foo

Marco


Mehmet Sirin schrieb:
> hi, i think you're searching this:
> http://pgrouting.postlbs.org/wiki/LoadingtheCode4
> bye
> 
> 2009/10/16 Marco Lechner - FOSSGIS e.V. <marco.lechner at fossgis.de
> <mailto:marco.lechner at fossgis.de>>
> 
>     hi,
> 
>     I try to find a way to solve this problem in postgis.
>     I have a table with lines an coorindates of a point that needn't to be
>     on a line. I try to find
>     1. the id of the line wich is nearest to the point
>     2. get the coordinates of the point on the line which is nearest to the
>     point
> 
>     --------0---------
>            |
>            |
>            X (point X not on line)
> 
>     0 is the nearest Point to X on line and therefore the Point i'm
>     searching for.
> 
>     Any hints?
> 
>     Marco
> 
>     --
> 
>     FOSSGIS e.V.
>     die unabhängige Hilfe bei freier GIS-Software und freien Geodaten
>     www.fossgis.de <http://www.fossgis.de>
> 
>     +++++++++++++++++++++++++++++++++
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at postgis.refractions.net
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
++++++++BITTE VORMERKEN++++++++
INTERGEO 2009: 22.09. - 24.09.2009
in Karlsruhe; Halle 1, Stand 1.417
+++++++++++++++++++++++++++++++

FOSSGIS e.V.
die unabhängige Hilfe bei freier GIS-Software und freien Geodaten
www.fossgis.de

+++++++++++++++++++++++++++++++++