Ok, here is the function. It takes two parameters. The first is a LINESTRING or a MULTILINESTRING and the second is the location, similar as line_interpolate_point function. I think it works ok. Obviously if the MULTILINESTRING have gaps between constituent LINESTRINGs and the location is right in a "discontinuity" point it will return just one of the point (a kind of mathematical "left limit" I think
<span onclick="dr4sdgryt2(event)" style="cursor: pointer;">although</span><span onclick="dr4sdgryt2(event)" style="cursor: pointer;"></span> I didn't make several test about that).<br>I hope it will be useful for someone. Here goes the code:
<br><br><div style="margin-left: 40px;"><span style="font-style: italic;">-- Function: multiline_interpolate_point(amultils geometry,location float8)</span><br style="font-style: italic;"><br style="font-style: italic;"><span style="font-style: italic;">
-- DROP FUNCTION multiline_interpolate_point(amultils geometry,location float8);</span><br style="font-style: italic;"><br style="font-style: italic;"><span style="font-style: italic;">CREATE OR REPLACE FUNCTION multiline_interpolate_point(amultils geometry,location float8)
</span><br style="font-style: italic;"><span style="font-style: italic;"> RETURNS geometry AS</span><br style="font-style: italic;"><span style="font-style: italic;">$BODY$</span><br style="font-style: italic;"><span style="font-style: italic;">
DECLARE</span><br style="font-style: italic;"><span style="font-style: italic;"> accumlength float8;</span><br style="font-style: italic;"><span style="font-style: italic;"> totallength float8;</span><br style="font-style: italic;">
<span style="font-style: italic;"> nearestpoint geometry;</span><br style="font-style: italic;"><span style="font-style: italic;"> i integer;</span><br style="font-style: italic;"><br style="font-style: italic;"><span style="font-style: italic;">
BEGIN</span><br style="font-style: italic;"><span style="font-style: italic;"> IF ((location < 0) or (location > 1)) THEN</span><br style="font-style: italic;"><span style="font-style: italic;"> RAISE EXCEPTION 'location must be between 0 and 1 --> %', location;
</span><br style="font-style: italic;"><span style="font-style: italic;"> END IF;</span><br style="font-style: italic;"><span style="font-style: italic;"> IF (GeometryType(amultils) != 'LINESTRING') and (GeometryType(amultils) != 'MULTILINESTRING') THEN
</span><br style="font-style: italic;"><span style="font-style: italic;"> RAISE EXCEPTION 'the geometry must be a LINESTRING or a MULTILINESTRING';</span><br style="font-style: italic;"><span style="font-style: italic;">
END IF;</span><br style="font-style: italic;"><br style="font-style: italic;"><span style="font-style: italic;"> IF (GeometryType(amultils) = 'LINESTRING') THEN</span><br style="font-style: italic;"><span style="font-style: italic;">
nearestpoint:=line_interpolate_point(amultils,location);</span><br style="font-style: italic;"><span style="font-style: italic;"> RETURN nearestpoint;</span><br style="font-style: italic;"><span style="font-style: italic;">
END IF;</span><br style="font-style: italic;"><span style="font-style: italic;"> accumlength := 0;</span><br style="font-style: italic;"><span style="font-style: italic;"> totallength := Length(amultils);</span>
<br style="font-style: italic;"><span style="font-style: italic;"> FOR i IN 1 .. NumGeometries(amultils) LOOP</span><br style="font-style: italic;"><span style="font-style: italic;"> IF ((accumlength + Length(GeometryN(amultils,i))/totallength) >= location) THEN
</span><br style="font-style: italic;"><span style="font-style: italic;"> IF ((location-accumlength)/(Length(GeometryN(amultils,i))/totallength) > 1) THEN</span><br style="font-style: italic;"><span style="font-style: italic;">
nearestpoint:=line_interpolate_point(GeometryN(amultils,i),1);</span><br style="font-style: italic;"><span style="font-style: italic;"> ELSE</span><br style="font-style: italic;"><span style="font-style: italic;">
nearestpoint:=line_interpolate_point(GeometryN(amultils,i),(location-accumlength)/(Length(GeometryN(amultils,i))/totallength));</span><br style="font-style: italic;"><span style="font-style: italic;"> END IF;
</span><br style="font-style: italic;"><span style="font-style: italic;"> EXIT;</span><br style="font-style: italic;"><span style="font-style: italic;"> ELSE</span><br style="font-style: italic;"><span style="font-style: italic;">
accumlength:=accumlength+Length(GeometryN(amultils,i))/totallength;</span><br style="font-style: italic;"><span style="font-style: italic;"> END IF;</span><br style="font-style: italic;"><span style="font-style: italic;">
END LOOP;</span><br style="font-style: italic;"><span style="font-style: italic;"> RETURN nearestpoint;</span><br style="font-style: italic;"><span style="font-style: italic;">END;</span><br style="font-style: italic;">
<span style="font-style: italic;">$BODY$</span><br style="font-style: italic;"><span style="font-style: italic;"> LANGUAGE 'plpgsql' IMMUTABLE STRICT;</span><br style="font-style: italic;"><span style="font-style: italic;">
ALTER FUNCTION multiline_interpolate_point(amultils geometry,location float8) OWNER TO postgres;</span><br></div><br>Rodrigo.<br><br><div><span class="gmail_quote">On 6/22/07, <b class="gmail_sendername">Rodrigo Martín LÓPEZ GREGORIO
</b> <<a href="mailto:rodrigomartin@lopezgregorio.com.ar">rodrigomartin@lopezgregorio.com.ar</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
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.
<br><span class="sg"><br>Rodrigo.<br><br></span><div><span class="e" id="q_11351a6a946b3163_2"><div><span class="gmail_quote">On 6/21/07, <b class="gmail_sendername">Rodrigo Martín LÓPEZ GREGORIO</b> <<a href="mailto:rodrigomartin@lopezgregorio.com.ar" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">
rodrigomartin@lopezgregorio.com.ar</a>
> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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
<br><br><div style="margin-left: 40px;"><span style="font-style: italic;"><span style="font-style: italic;">[...] </span>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):
</span><br style="font-style: italic;"><br style="font-style: italic;"></div><div style="margin-left: 80px; font-style: italic;">-- Function: multiline_locate_point(amultils geometry,apoint geometry)<br><br>-- DROP FUNCTION multiline_locate_point(amultils geometry,apoint geometry);
<br><br>CREATE OR REPLACE FUNCTION multiline_locate_point(amultils geometry,apoint geometry)
<br> RETURNS geometry AS<br>$BODY$<br>DECLARE<br> mindistance float8;<br> nearestlinestring geometry;<br> nearestpoint geometry;<br> i integer;<br><br>BEGIN<br> mindistance := (distance(apoint,amultils)+100);
<br> FOR i IN 1 .. NumGeometries(amultils) LOOP<br> if distance(apoint,GeometryN(amultils,i)) < mindistance THEN<br> mindistance:=distance(apoint,GeometryN(amultils,i));<br> nearestlinestring:=GeometryN(amultils,i);
<br> END IF;<br> END LOOP;<br> nearestpoint:=line_interpolate_point(nearestlinestring,line_locate_point(nearestlinestring,apoint));<br> RETURN nearestpoint;<br>END;<br>$BODY$<br> LANGUAGE 'plpgsql' IMMUTABLE STRICT;
<br>ALTER FUNCTION multiline_locate_point(amultils geometry,apoint geometry) OWNER TO postgres;<br></div><div style="margin-left: 40px;"><br style="font-style: italic;"><span style="font-style: italic;">Once the function is stored and available for use, you can make the query like:
</span><br style="font-style: italic;"><br style="font-style: italic;"></div><div style="margin-left: 80px; font-style: italic;">
SELECT *, multiline_locate_point(the_geom,PointFromText('POINT(517651 2121421)', 42102)) <br>FROM mainroad ORDER BY Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1 <br></div><div style="margin-left: 40px;">
<br style="font-style: italic;"><span style="font-style: italic;">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:
</span><br style="font-style: italic;"><br style="font-style: italic;"><span style="font-style: italic;">SELECT *, multiline_locate_point(the</span></div><div style="font-style: italic; margin-left: 40px;">_geom,PointFromText('POINT(517651 2121421)', 42102)) FROM
<br>(SELECT *, Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) as dist <br>FROM mainroad ORDER BY Distance(the_geom,PointFromText('POINT(517651 2121421)', 42102)) LIMIT 1 ) as foo
<br><br>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 ;)</div><span><br>Rodrigo<br><br></span><div><span><span class="gmail_quote">
On 6/21/07, <b class="gmail_sendername">
Pradeep B V</b> <<a href="mailto:pradeepbv@gmail.com" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">pradeepbv@gmail.com</a>> wrote:</span></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><span>
<span>On 6/21/07, <b class="gmail_sendername">Rodrigo Martín LÓPEZ GREGORIO</b> <<a href="mailto:rodrigomartin@lopezgregorio.com.ar" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">rodrigomartin@lopezgregorio.com.ar
</a>> wrote:</span><div><span><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
As Nicolas says you should use:<br><br>select<span> line_interpolate_point(LineMerge(the_geom),0.5).<br><br>you can try this and see if the point is the one you want:</span></blockquote></span><div><br>It works fine.<br>
<br>and here is the proof.
<br><br>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;<br>
<br> stdist | enddist<br> ----------------------+----------------------<br> 0.000737003466840363 | 0.000737003466840363<br><br> Thanks Rodrigo/Nicolas.<br><br> - Pradeep B V<br>
</div><a href="http://www.btis.in" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">www.btis.in</a><br></div></span></div><span>
<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">postgis-users@postgis.refractions.net
</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br>
</span></blockquote></div><br>
</blockquote></div><br>
</span></div></blockquote></div><br>