« Return to Thread: Select Point near Polyline Postgis
Re: Select Point near Polyline Postgis
by Rodrigo Martín LÓPEZ GREGORIO-3
::
Rate this Message:
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.
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
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@...> 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@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
« Return to Thread: Select Point near Polyline Postgis
| Free embeddable forum powered by Nabble | Forum Help |
