« 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:
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):
Once the function is stored and available for use, you can make the query like:
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.
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users
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;
-- 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
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
« Return to Thread: Select Point near Polyline Postgis
| Free embeddable forum powered by Nabble | Forum Help |
