[postgis-users] Select Point near Polyline Postgis

[Rodrigo Martín LÓPEZ GREGORIO]

Hi anhtin

To find the nearest polyline from a table to a point you can do this:

SELECT * FROM mainroad ORDER BY
Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1

To get the coordinates of the nearest point of that line to your point then
you must do:

SELECT *,
line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
2121421)'))) FROM mainroad ORDER BY
Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1

So with the last query you can get all the data (*) of the nearest
linestring and the point of the that linestring that is nearest to the given
Point.

The way it works is, first the line_locate_point function get the linstring
and the point and gives you a value between 0 and 1 representing the
location of the closest point on LineString to the given Point, as a
fraction of total 2d line length. Then the line_interpolate_point function
will take the linestring and the value between 0 and 1 and return a Point
geometry with the location of the nearest point on the linestring. If you
want to get the X and Y coordinates of that point you can do also
X(geometry), Y(geometry):

SELECT *,
X(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
2121421)')))),
Y(line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
2121421)')))) FROM mainroad ORDER BY
Distance(the_geom,PointFromText('POINT(517651 2121421)')) LIMIT 1

Rodrigo.

On 6/16/07, anhtin <anhtin at gmail.com> wrote:
>
>
> hi all
> I am developing a web application. i am using Mapserver and Postgis
> I have a question below
> if i want to click one point on map to select the nearest point on the
> nearest polyline from the point i clicked. How can i do?
> Notes: The point I clicked is converted to X,Y coordinate (295149 2315499)
>
> could you show me the sql command in postgis.
> 1. How can i select the nearest  polyline on the click point.
> 2. How can  i select the the nearest point on the selected nearest
> polyline.
> :computer-user:
> :rules:
>
> Somebody suggest that I should search the polyline nearest on the click
> point by the script below
>
> SELECT * FROM mainroad WHERE GeomFromText('POINT(517651 2121421)', 42102)
> &&
> the_geom
> AND distance(the_geom,
> GeomFromText('POINT(517651 2121421)', 42102)) < 200000
>
> however,  sometime it has no result because the distance from point to
> polyline is larger than 200000.
> --
> View this message in context:
> http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11150683
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070616/5451ca96/attachment.html>