Hi Daniel-san,<div><br></div><div>Nice to talk to you over internet! Thanks for pointing out the posting. It was a straight forward task for me to use ESRI products but I am always trying to use PostGIS and other open source GIS product for my work. I love to try Paul's recursive method. I think it should work for my task since the "cost" for me is just a total length of selected segments. I even did not know the WITH RECURSIVE function with PostgreSQL. I definitely will learn about this and summarize in my site.</div>
<div><br></div><div>I just recently watched "Karate-kid" not the newest one but old one with my kids. My five years old daughter sometimes imitates Mr. Miyagi and said "Daniel-san!". It just so cute to hear. Sorry for my silly comment.</div>
<div><br></div><div>Thanks!</div><div><br></div><div>Hiroo<br><br><div class="gmail_quote">2010/7/23 Daniel Kastl <span dir="ltr"><<a href="mailto:daniel.kastl@georepublic.de">daniel.kastl@georepublic.de</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Imaki-san,<div><br></div><div>Even if I'm very glad about every pgRouting user and happy to see nice tutorials in Japanese, I had to think about your water flow task when I read this blog post of Paul Ramsey:</div>
<div>
<br></div><div><a href="http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html" target="_blank">http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html</a></div><div><br></div><div>Paul's example looks very much like a simple river network, and my first thought was how could pgRouting eventually make use of this new feature of PostgreSQL 8.4.</div>
<div><br></div><div>Well, maybe you want to try it with your river network.</div><div>Not sure how to apply "costs" but maybe they are not important in your case. Would be interesting to see if this is possible with only PostgreSQL and PostGIS.</div>
<div><br></div><div>Daniel<span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse"><div><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse"><br>
</span></div><div><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse"><br></span></div><div><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse"><br>
</span></div><div><span style="font-family:arial, sans-serif;font-size:13px;border-collapse:collapse"><br></span></div>-- <br>Georepublic UG & Georepublic Japan<br>eMail: <a href="mailto:daniel.kastl@georepublic.de" style="color:rgb(66, 99, 171)" target="_blank">daniel.kastl@georepublic.de</a><br>
Web: <a href="http://georepublic.de" style="color:rgb(66, 99, 171)" target="_blank">http://georepublic.de</a><br><br></span>
</div><div><br></div><div><a href="http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html" target="_blank"></a><br><br><div class="gmail_quote">2010/7/22 Hiroo Imaki <span dir="ltr"><<a href="mailto:hiroo@angeli.org" target="_blank">hiroo@angeli.org</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">$B%j%9%H$N3'MM!"(B<div><br></div><div>$B@hF|!"(BpgRouting$B$r;H$C$?%@%`$+$i2O8}$^$G$N5wN%7W;;$K$D$$$F<ALd$7$?:#LZ$G$9!#$7$P$i$/$3$NLdBj$H3JF.$7$F$$$k4V$K!"$$$/$D$+JXMx$J%U%!%s%/%7%g%s$r=q$$$?$N$G!J$H$$$C$F$bC/$+$N=q$$$?$b$N$r<h$j9~$s$@$j!"2~A1$7$?$j$7$?$b$N$G$9$,!K3'$5$s$N$*Lr$KN)$F$P$H;W$$%]%9%H$7$^$9!#(B</div>
<div><br></div><div>$B0l$DL\$O!"J#?t$NE@!J(BPOINT)$B$r@~!J(BLINESTRING$B!K$K%9%J%C%W$9$k%U%!%s%/%7%g%s$G$9!#$3$l$O!"(BPaul Ramsey$B$N%V%m%0$+$i%a%$%s$N%3!<%I$rGR<Z$7$F%U%!%s%/%7%g%s$K$7$?$b$N$G$9!#;H$$J}$O!"(B</div>
<div><div>SELECT * FROM points_snap2lines</div><div> ('SELECT gid AS id, the_geom AS geom FROM point_table',</div><div> 'SELECT gid AS id, the_geom AS geom FROM line_table',</div><div> 0.0001);</div>
<div>$B$N$h$&$K$7$F!":G8e$KCO?^$NC10L$G%9%J%C%W$N5vMFEY$rF~NO$7$^$9!#$3$N%U%!%s%/%7%g%s$r<B9T$9$k$H%]%$%s%H!"%i%$%s$N$=$l$>$l$N(BID$B$H%9%J%C%W8e$N%8%*%a%H%j!<$,5"$C$F$-$^$9!#(B</div><div><br></div><div>2$B$DL\$O!"J#?t$NE@$r;H$C$FJ#?t$N@~$r%/%j%C%W$9$k%U%!%s%/%7%g%s$G$9!#$3$l$O!"(B<a href="http://postgis.refractions.net/pipermail/postgis-users/2007-September/017159.html" target="_blank">http://postgis.refractions.net/pipermail/postgis-users/2007-September/017159.html</a>$B!!$NJb%9%F%#%s%0$K$"$C$?%U%!%s%/%7%g%s$r=q$-49$($?$b$N$G$9!#$3$N%]%9%F%#%s%0$N$^$^$G$O;d$N(Bpostgresql8.3.3$B$G$O$&$^$/F0$+$J$+$C$?$N$G!"<jD>$7$7$?$N$H!"(BST_DWithin()$B$r;H$C$FB?>/!"E@$,@~$+$i$:$l$F$$$F$b%/%j%C%W$G$-$k$h$&$K$7$?$N$H!"4pK\E*$J%k!<%W>e$N%(%i!<$rD>$7$?$b$N$G$9!#;H$$J}$O!"(B</div>
<div><div>SELECT * FROM split_lines2('SELECT gid AS id, the_geom AS geom FROM line_table',</div><div> $B!!!!!!!!(B 'SELECT the_geom AS geom FROM point_table', 0.0001);</div></div><div>$B$N$h$&$K$7$F!"!!(BST_DWithin$B$N5wN%%Q%i%a%?!<$r:G8e$KF~NO$7$^$9!#(B</div>
<div><br></div><div>$B;d$N4D6-!J(BWindowsXP, PostgreSQL8.3.3)$B$G$O$&$^$/F0$-$^$7$?!#2~NI!"%P%0$J$I$"$j$^$7$?$iO"Mm$/$@$5$$!#;H$C$?46A[$J$I$b$*J9$+$;$/$@$5$$!#(B</div><div><br></div><div>$B$$$^$-(B</div><div><div><br></div><div>CREATE OR REPLACE FUNCTION points_snap2lines (in pt_q text, in ln_q text, in torelance float4, out p_id int, out l_id int, out p_geom geometry)</div>
<div>RETURNS SETOF RECORD AS</div><div>$$</div><div>DECLARE</div><div> snap_q text;</div><div> pointrec record;</div><div>BEGIN</div><div> EXECUTE 'CREATE TEMP TABLE line_tmp as '|| ln_q;</div><div> EXECUTE 'CREATE TEMP TABLE point_tmp as '|| pt_q;</div>
<div>snap_q :='</div><div>SELECT</div><div> pt_id,</div><div> ln_id,</div><div> ST_line_interpolate_point(</div><div> ln_geom,</div><div> ST_line_locate_point(ln_geom, pt_geom)</div><div> ) AS the_geom</div>
<div>FROM</div><div> (</div><div> SELECT DISTINCT ON (<a href="http://pt.id" target="_blank">pt.id</a>)</div><div> ln.the_geom AS ln_geom,</div><div> pt.the_geom AS pt_geom,</div><div> <a href="http://ln.id" target="_blank">ln.id</a> AS ln_id,</div>
<div> <a href="http://pt.id" target="_blank">pt.id</a> AS pt_id</div><div> FROM</div><div> point_tmp pt INNER JOIN</div><div> line_tmp ln</div><div> ON</div><div> ST_DWithin(pt.the_geom, ln.the_geom, '||cast(torelance as text) ||')</div>
<div> ORDER BY</div><div> <a href="http://pt.id" target="_blank">pt.id</a>,ST_Distance(ln.the_geom, pt.the_geom) </div><div> ) as foo' ;</div><div> FOR pointrec in EXECUTE snap_q LOOP</div><div> p_id := pointrec.pt_id;</div>
<div> l_id := pointrec.ln_id;</div><div> p_geom := pointrec.the_geom;</div><div> RETURN NEXT;</div><div> END LOOP;</div><div> DROP TABLE line_tmp;</div><div> DROP TABLE point_tmp;</div><div> RETURN;</div><div>END;</div>
<div>$$</div><div>LANGUAGE plpgsql;</div><div><br></div></div><div><br></div><div><br></div><div><div>CREATE OR REPLACE FUNCTION split_lines2(in lineq text, in pointq text, in torelance float4, out lineid int, out the_geom geometry)</div>
<div>RETURNS SETOF RECORD AS</div><div>$$</div><div>DECLARE</div><div> linerec record;</div><div> pointrec record;</div><div> linepos float;</div><div> start_ float;</div><div> end_ float;</div><div> loopqry text;</div>
<div>BEGIN</div><div> EXECUTE 'CREATE TEMP TABLE line_tmp as '|| lineq;</div><div> EXECUTE 'CREATE TEMP TABLE point_tmp as '|| pointq;</div><div> FOR linerec in EXECUTE 'SELECT * FROM line_tmp ORDER BY id' LOOP</div>
<div> start_ := 0;</div><div> end_ := 0;</div><div> loopqry := '</div><div> SELECT</div><div> *, ST_line_locate_point('''||cast(linerec.geom as text)||''',geom) AS frac </div><div>
FROM point_tmp </div><div> WHERE ST_DWithin(geom,'''||cast(linerec.geom as text)||''', '||torelance||')</div><div> ORDER BY ST_line_locate_point('''||cast(linerec.geom as text)||''',geom)';</div>
<div> FOR pointrec in EXECUTE loopqry LOOP</div><div> end_ := pointrec.frac;</div><div> lineid := <a href="http://linerec.id" target="_blank">linerec.id</a>;</div><div> the_geom := ST_line_substring(linerec.geom, start_, end_);</div>
<div> start_ := end_;</div><div> RETURN NEXT;</div><div> END LOOP;</div><div> lineid := <a href="http://linerec.id" target="_blank">linerec.id</a>;</div><div> the_geom:= ST_line_substring(linerec.geom, end_,1.0);</div>
<div> RETURN NEXT;</div><div> END LOOP;</div><div> DROP TABLE line_tmp;</div><div> DROP TABLE point_tmp;</div><div> RETURN;</div><div>END;</div><div>$$</div><div>LANGUAGE plpgsql;</div><div><br></div><div><br></div>
</div><br>-- <br>Hiroo Imaki<br><a href="mailto:hiroo@angeli.org" target="_blank">hiroo@angeli.org</a><br><a href="http://www.geopacific.org" target="_blank">http://www.geopacific.org</a><br>
</div>
<br></div></div>_______________________________________________<br>
OSGeoJapan-discuss mailing list<br>
<a href="mailto:OSGeoJapan-discuss@lists.osgeo.org" target="_blank">OSGeoJapan-discuss@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss" target="_blank">http://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss</a><br>
<br></blockquote></div><br></div>
<br>_______________________________________________<br>
OSGeoJapan-discuss mailing list<br>
<a href="mailto:OSGeoJapan-discuss@lists.osgeo.org">OSGeoJapan-discuss@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss" target="_blank">http://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br>Hiroo Imaki<br><a href="mailto:hiroo@angeli.org">hiroo@angeli.org</a><br><a href="http://www.geopacific.org">http://www.geopacific.org</a><br>
</div>