$B%@%K%(%k$5$s!"%j%9%H$N3'MM!"(B<div><br></div><div>$B@hF|!"%@%K%(%k$5$s$+$i;XE&$N$"$C$?%]!<%k$5$s$N%V%m%0$+$i;I7c$r<u$1$F%@%`$+$i2O8}$^$G$N5wN%$rB,$k$?$a$K!"(BPostgreSQL$B$N(BWITH RECURSIVE$BJ8$,$I$N$h$&$K;H$($k$N$+$r$$$m$$$m;n$7$F$_$^$7$?!#%]!<%k$5$s$N%]%9%F%#%s%0$K$"$k(BSQL$BJ8$@$H!"2O@n$,J,N.$7$?$H$-$KLdBj$,H/@8$9$k$N$G!"J,N.E@$KMh$?$H$-$K2?$+$7$i$N4p=`$G?e$,?J$`F;$rA*$VI,MW$,$"$j$^$9!#$=$3$G!"(BWITH RECURSIVE$BJ8$N(BUNION ALL$B0J2<$NItJ,$G!"@~J,$ND9$5$r;H$C$F$I$A$i$+D9$$$[$&!J$^$?$OC;$$$[$&!K$N@~J,$rA*Br$9$k$h$&$K$7$F$_$k$3$H$O=PMh$J$$$+$H9M$($?$o$1$G$9!#$=$N$?$a$K$O(BORDER BY$B$H(BLIMIT$B$r;H$&$3$H$,=PMh$k$@$m$&$H?dB,$7$^$7$?!#(B</div>
<div>$B7k2L$O8+;v$K<:GT$G!"C1=c$K(BUNION ALL$B$NJ8$K(BORDER BY$B$H(BLIMIT$B$O;H$($J$$$h$&$G$9!J(B<a href="http://wiki.postgresql.org/wiki/CTEReadme">http://wiki.postgresql.org/wiki/CTEReadme</a>$B!K!#$=$l$G$b$7$D$3$/$$$m$$$mD4$Y$?$j!J(B<a href="http://explainextended.com/2009/11/23/recursive-ctes-postgresql/">http://explainextended.com/2009/11/23/recursive-ctes-postgresql/</a>$B!K;n$7$?$j$7$F$$$?$i!"$J$s$H!"$&$^$/$$$-$^$7$?$N$G!"$=$N(BSQL$BJ8$r$3$3$KE:IU$7$^$9!#$?$@!"$*$O$:$+$7$J$,$i$J$<$3$NJ8$G(BORDER BY $B$H(BLIMIT$B$,;H$($?$N$+$o$+$i$J$$$N$G!"$4B8CN$NJ}$,$$$?$i65$($F$/$@$5$$!#:#=q$$$?%W%m%;%9$O!";d$N%5%$%H$G$h$j>\$7$/Js9p$7$F$$$^$9!#6=L#$"$kJ}$OGA$$$F$_$F$/$@$5$$!#(B</div>
<div><a href="http://www.geopacific.org/opensourcegis/postgis/with-recursive">http://www.geopacific.org/opensourcegis/postgis/with-recursive</a></div><div><br></div><div><span class="Apple-style-span" style="font-family: 'Lucida Grande', Verdana, Lucida, Helvetica, Arial, sans-serif; font-size: 12px; border-collapse: collapse; color: rgb(72, 75, 82); "><pre style="font-family: Monaco, 'Courier New', Courier, monospace; font-size: 12px; padding-top: 1em; padding-right: 1em; padding-bottom: 1em; padding-left: 1em; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(140, 172, 187); border-right-color: rgb(140, 172, 187); border-bottom-color: rgb(140, 172, 187); border-left-color: rgb(140, 172, 187); color: rgb(72, 75, 82); background-color: rgb(105, 133, 173); overflow-x: auto; overflow-y: auto; ">
WITH RECURSIVE walk_network AS (
SELECT * FROM (
SELECT * FROM network WHERE id = 1
order by length desc
limit 1) q
UNION ALL
SELECT * FROM (
SELECT n.*
FROM walk_network w JOIN network n
ON ST_DWithin(ST_EndPoint(w.segment),ST_StartPoint(n.segment),0.01)
order by n.length desc
limit 1 ) q2
)
SELECT *
FROM walk_network;</pre></span></div><div><br><br><div class="gmail_quote">2010/7/23 Hiroo Imaki <span dir="ltr"><<a href="mailto:hiroo@angeli.org">hiroo@angeli.org</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
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" target="_blank">daniel.kastl@georepublic.de</a>></span><div>
<div></div><div class="h5"><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>$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" 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></div></div><div><div></div><div class="h5"><br><br clear="all"><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></div></div>
</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>