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;" id="mb_5">_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><br>Rodrigo<br><br><div><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><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<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>
<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>
</blockquote></div><br>