[postgis-users] Select Point near Polyline Postgis

[Rodrigo Martín LÓPEZ GREGORIO]

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 at gmail.com> 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 at postgis.refractions.net
> > 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 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/4fd9b6a5/attachment.html>