I don't know why I mixed my ideas. The function that I send in my previous
mail find the nearest point of a multilinestring to a given point so it has
nothing to do with the middle point in the multilinestring. Tomorrow I will
modify that function and I'll send it again. Sorry about that.
Rodrigo.
On 6/21/07, Rodrigo Martín LÓPEZ GREGORIO <
[EMAIL PROTECTED]> wrote:
I was testing the LineMerge() solution with different MULTILINESTRINGs and
it will only return a LINESTRING geometry if there is no gap between
constituent linestrings. I remember then that a few days ago I answered a
similar question but that time the linestrings had gaps and I tryed with
LineMerge with no luck, so that time I created a function that work with
those MULTILINESTRINGs. I know that the LineMerge solution works fine in
your case but I send again the code of the function I created. I know maybe
there could be a better solution but at least it works :P
[...] So I wrote 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/21/07, Pradeep B V <[EMAIL PROTECTED]> wrote:
>
> On 6/21/07, Rodrigo Martín LÓPEZ GREGORIO <[EMAIL PROTECTED]
> > wrote:
> >
> > As Nicolas says you should use:
> >
> > select line_interpolate_point(LineMerge(the_geom),0.5).
> >
> > you can try this and see if the point is the one you want:
>
>
> It works fine.
>
> and here is the proof.
>
> select distance(startpoint(the_geom),
> line_interpolate_point(LineMerge(the_geom),0.5)) as stdist,
> distance(endpoint(the_geom), line_interpolate_point(LineMerge(the_geom),
> 0.5)) as endist from testdataset limit 1;
>
> stdist | enddist
> ----------------------+----------------------
> 0.000737003466840363 | 0.000737003466840363
>
> Thanks Rodrigo/Nicolas.
>
> - Pradeep B V
> www.btis.in
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
