[postgis-users] Nearest point on line

[Derek C. Figg]

Hello all,
 
Please excuse this question if it is very basic or has been answered
recently.  I have searched online for hours to no avail looking for an
answer.
 
I have a database full of linestrings (StreetLine).  I am searching this
database to find the line string that is closest to a specified point.
I am successfully able to do this with this code:
 
CREATE FUNCTION rg (decimal (9,6), decimal (9,6)) RETURNS SET OF
tblImportData AS $$
SELECT *
 FROM tblImportData
 WHERE "StreetLine" && Expand(GeomFromText('POINT(' || $1 || ' ' || $2
|| ')',-1),.0004)
  AND Distance(GeomFromText('POINT(' || $1 || ' ' || $2 ||
')',-1),"StreetLine") < .0004
 ORDER BY Distance(GeomFromText('POINT(' || $1 || ' ' || $2 ||
')',-1),"StreetLine")
 LIMIT 1;
$$ LANGUAGE SQL;

I want to go a step further though, and determine what exact point on
the linestring is closest to the point I have specified.  Basically, I
want to know the intersecting point on the linestring where the distance
from the specified point to the line is smallest.  Thanks in advance for
any info that you can provide me.
 
Derek Figg
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050411/46c5a605/attachment.html>