$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">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">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">ln.id</a> AS ln_id,</div>
<div> <a href="http://pt.id">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">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">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">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">hiroo@angeli.org</a><br><a href="http://www.geopacific.org">http://www.geopacific.org</a><br>
</div>