Select Point near Polyline Postgis

[anhtin]

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.



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.

[Rodrigo Martín LÓPEZ GREGORIO-3]

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@...> 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

[anhtin]

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
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Rodrigo Martín LÓPEZ GREGORIO-3]

I think the problem obviously is that some geometries in your table are not linestrings or multilinestrings. You can check it doing:

SELECT GeometryType(the_geom) FROM mainroad WHERE GeometryType(the_geom) != 'LINESTRING'

Then you will find wich Geometries are not linestring and those are the ones that are giving you some problems. You must take a look at that geometries and choose what to do with them.

As a temporary sollution I think you can add a condition to your query that only takes into account for the result the geometries that are LINESTRING. So your query will look something like this:

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

Maybe you can also consider the 'MULTILINESTRING' geometries; the line_interpolate_point should work also with that type of geometry. So the condition will be:

WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) = 'MULTILINESTRING')

I never used any SRID in my querys but I don't think that can be a problem at all.

Rodrigo.

On 6/16/07, anhtin <anhtin@...> wrote:

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

--
View this message in context: http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11151024
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

[anhtin]

hi Rodrigo
this  my column the_geom is type "MULTILINESTRING"
and all geometry data on this table  is type = "MULTILINESTRING"
when i run this script:
SELECT * , line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651 2121421)', 42102)))
FROM mainroad WHERE (GeometryType(the_geom) = 'LINESTRING')
 ORDER BY Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1

it not error, however it has not result because geometry data is type = "MULTILINESTRING"

but when i add a condition is:
WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) = 'MULTILINESTRING')
 it have exception error:

ERROR: line_locate_point: 1st arg isnt a line
SQL state: XX000

I think this script error because the condition (GeometryType(the_geom) = 'MULTILINESTRING')

How could i do? Because my all geometry data is MULTILINESTRING  type.
Could i convert my geometry data to orther data type ("LINESTRING").
But i think it not good if i convered to orther type.

this is my Data MainRoad. Can u try to use it. And u could show me the good a way :).
data+type+MainRoad.rar

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

I think the problem obviously is that some geometries in your table are not
linestrings or multilinestrings. You can check it doing:

SELECT GeometryType(the_geom) FROM mainroad WHERE GeometryType(the_geom) !=
'LINESTRING'

Then you will find wich Geometries are not linestring and those are the ones
that are giving you some problems. You must take a look at that geometries
and choose what to do with them.

As a temporary sollution I think you can add a condition to your query that
only takes into account for the result the geometries that are LINESTRING.
So your query will look something like this:

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

Maybe you can also consider the 'MULTILINESTRING' geometries; the
line_interpolate_point should work also with that type of geometry. So the
condition will be:

WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) =
'MULTILINESTRING')

I never used any SRID in my querys but I don't think that can be a problem
at all.

Rodrigo.

_______________________________________________
postgis-users mailing list
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Rodrigo Martín LÓPEZ GREGORIO-3]

Well. Sorry for the delay. Here I'm with a posible solution.

I downloaded your data file and take a look at it. When I take a look I figure out that there is no way line_locate_point and line_interpolate_point work with MULTILINESTRING so the first solution I though was join your MULTILINESTRINGS in just one LINESTRING but looking at the complexity of your data I though that this was not a good idea, speccialy cause the diferents linestring in a MULTILINESTRING were not geographically continuous so joining all in one linestring was not an alternative. So I write a stored function (preety simple by the way) that get what you need. The function get two parameters... the first is a multilinestring, the second, your point. The function loops over all linestrings in the multilinestring, get the nearest one and then with that linestring calculates the location of the nearest point of the linestring to your point. To get this function working run this script and it will create your function (take a look at the end of the function and chanche the user name if necesary; mine was postgres):

-- Function: multiline_locate_point(amultils geometry,apoint geometry)

-- DROP FUNCTION multiline_locate_point(amultils geometry,apoint geometry);

CREATE OR REPLACE FUNCTION multiline_locate_point(amultils geometry,apoint geometry)
  RETURNS geometry AS
$BODY$
DECLARE
    mindistance float8;
    nearestlinestring geometry;
    nearestpoint geometry;
    i integer;

BEGIN
    mindistance := (distance(apoint,amultils)+100);
    FOR i IN 1 .. NumGeometries(amultils) LOOP
        if distance(apoint,GeometryN(amultils,i)) < mindistance THEN
            mindistance:=distance(apoint,GeometryN(amultils,i));
            nearestlinestring:=GeometryN(amultils,i);
        END IF;
    END LOOP;
    nearestpoint:=line_interpolate_point(nearestlinestring,line_locate_point(nearestlinestring,apoint));
    RETURN nearestpoint;
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE STRICT;
ALTER FUNCTION multiline_locate_point(amultils geometry,apoint geometry) OWNER TO postgres;

Once the function is stored and available for use, you can make the query like:

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

However, the query speed is a little bit slow if you do the query in that way. You can use the next query instead wich first gets the nearest multilinestring to your point and then call my function with that geometry instead of calling the function for all geometries:

SELECT *, multiline_locate_point(the_geom,PointFromText('POINT(517651 2121421)', 42102)) FROM
(SELECT *, Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) as dist
FROM mainroad ORDER BY Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1 ) as foo

On my PC the second query takes 1/6 of the time that tooks the first one so the speed is much better at least here ;)

Rodrigo.

On 6/16/07, anhtin <anhtin@...> wrote:

hi Rodrigo
this  my column the_geom is type "MULTILINESTRING"
and all geometry data on this table  is type = "MULTILINESTRING"
when i run this script:
SELECT * ,
line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(517651
2121421)', 42102)))
FROM mainroad WHERE (GeometryType(the_geom) = 'LINESTRING')
ORDER BY Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102))
LIMIT 1

it not error, however it has not result because geometry data is type =
"MULTILINESTRING"

but when i add a condition is:
WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) =
'MULTILINESTRING')
it have exception error:

ERROR: line_locate_point: 1st arg isnt a line
SQL state: XX000

I think this script error because the condition (GeometryType(the_geom) =
'MULTILINESTRING')

How could i do? Because my all geometry data is MULTILINESTRING  type.
Could i convert my geometry data to orther data type ("LINESTRING").
But i think it not good if i convered to orther type.

this is my Data MainRoad. Can u try to use it. And u could show me the good
a way :).
http://www.nabble.com/file/p11151446/data%2Btype%2BMainRoad.rar
data+type+MainRoad.rar







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

>
> I think the problem obviously is that some geometries in your table are
> not
> linestrings or multilinestrings. You can check it doing:
>
> SELECT GeometryType(the_geom) FROM mainroad WHERE GeometryType(the_geom)
> !=
> 'LINESTRING'
>
> Then you will find wich Geometries are not linestring and those are the
> ones
> that are giving you some problems. You must take a look at that geometries
> and choose what to do with them.
>
> As a temporary sollution I think you can add a condition to your query
> that
> only takes into account for the result the geometries that are LINESTRING.
> So your query will look something like this:
>
> SELECT * ,
> line_interpolate_point(the_geom,line_locate_point(the_geom,PointFromText('POINT(5176512121421)',
> 42102))) FROM
> mainroad WHERE GeometryType(the_geom) = 'LINESTRING' ORDER BY
> Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1
>
> Maybe you can also consider the 'MULTILINESTRING' geometries; the
> line_interpolate_point should work also with that type of geometry. So the
> condition will be:
>
> WHERE (GeometryType(the_geom) = 'LINESTRING') or (GeometryType(the_geom) =
> 'MULTILINESTRING')
>
> I never used any SRID in my querys but I don't think that can be a problem
> at all.
>
> Rodrigo.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

--
View this message in context: http://www.nabble.com/Select-Point-near-Polyline-Postgis-tf3931432.html#a11151446
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