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: [email protected] [mailto:[email protected]] 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: > http://pgrouting.postlbs.org/wiki/LoadingtheCode4 > bye > > 2009/10/16 Marco Lechner - FOSSGIS e.V. <[email protected] > <mailto:[email protected]>> > > 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 > [email protected] > <mailto:[email protected]> > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > ---------------------------------------------------------------------- > -- > > _______________________________________________ > postgis-users mailing list > [email protected] > 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
