# [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

Paragon Corporation lr at pcorp.us
Fri Oct 16 14:31:11 PDT 2009

```Marco,
I think what you want is something like this

http://blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html

The key is you want to use ST_DWithin and come up with a distance that is
longer than any 2 closest lines.  This will weed out a lot of falses with  a
spatial index so should be much faster than what you are doing.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Marco
Lechner - FOSSGIS e.V.
Sent: Friday, October 16, 2009 7:55 AM
To: PostGIS Users Discussion
Subject: Re: [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

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:
> 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

+++++++++++++++++++++++++++++++++
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

```