« Return to Thread: Select Point near Polyline Postgis

Re: Select Point near Polyline Postgis

by anhtin :: Rate this Message:

hi Rodrigo
Thanks very much reply to me.
With the first script it good.
However when i run the second script:

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

Suddenly have a exception error :
ERROR: line_locate_point: 1st arg isnt a line
SQL state: XX000

Note: the number 42102 is my SRIDs.

if i run this script:
Select line_locate_point(the_geom,PointFromText('POINT(517651 2121421)', 42102)) from mainroad

it error too

this is structure my table mainroad:
CREATE TABLE mainroad
(
gid serial NOT NULL,
name character varying(40),
id smallint,
the_geom geometry,
CONSTRAINT mainroad_pkey PRIMARY KEY (gid),
CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL),
CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 42102)
)
WITHOUT OIDS;
ALTER TABLE mainroad OWNER TO postgres;

Can u show me ???

Rodrigo Martín LÓPEZ GREGORIO-3 wrote:

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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

« Return to Thread: Select Point near Polyline Postgis