<div dir="ltr"><div class="gmail_default" style="font-family:monospace,monospace">Hey,<br></div><div class="gmail_default" style="font-family:monospace,monospace">overall seems like you should use postgis topology <br></div><div class="gmail_default" style="font-family:monospace,monospace">(or GRASS GIS).<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Secondly,<br></div><div class="gmail_default" style="font-family:monospace,monospace">I'm afraid you don't use the function correctly.<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">You should<br></div><div class="gmail_default" style="font-family:monospace,monospace">- for each line<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - find points that may cut the line<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - group those points into a blade<br></div><div class="gmail_default" style="font-family:monospace,monospace"> - cut the line with the blade<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Which is not at all what you do .<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">Your querry should look like this (provided you don't use postgis topology, which has been designed exactly for your use case)<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">WITH points_intersecting_lines AS( --for each line, which point shall intersect it<br></div><div class="gmail_default" style="font-family:monospace,monospace"> SELECT <a href="http://lines.id">lines.id</a> AS lines_id, lines.geom AS line_geom, ST_Collect(points.geom) AS blade<br></div><div class="gmail_default" style="font-family:monospace,monospace"> FROM lines, points<br></div><div class="gmail_default" style="font-family:monospace,monospace"> WHERE st_dwithin(lines.geom, points.geom, your_tolerance) = true<br></div><div class="gmail_default" style="font-family:monospace,monospace"> GROUP BY <a href="http://lines.id">lines.id</a>, lines.geom<br></div><div class="gmail_default" style="font-family:monospace,monospace">)<br></div><div class="gmail_default" style="font-family:monospace,monospace">SELECT lines_id, rc_split(line_geom, blade, your_tolerancy)<br></div><div class="gmail_default" style="font-family:monospace,monospace">FROM points_intersecting_lines<br></div><div class="gmail_default" style="font-family:monospace,monospace">--note : lines.geom and points.geom should have GIST index<br><br></div><div class="gmail_default" style="font-family:monospace,monospace">it should take less than a minute (obviously not tested here! )<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">If perf is a big issue, I'd recommand using st_snap to grid, and st_node.<br></div><div class="gmail_default" style="font-family:monospace,monospace"><br></div><div class="gmail_default" style="font-family:monospace,monospace">Cheers,<br></div><div class="gmail_default" style="font-family:monospace,monospace">Rémi-C<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">2015-07-16 20:06 GMT+02:00 Vladimir Ezequiel Bellini <span dir="ltr"><<a href="mailto:vlasvlasvlas@gmail.com" target="_blank">vlasvlasvlas@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">nicely done..<div><br></div><div>the thing is:</div><div><br></div><div>st_union table with lines (Crossing lines, like street lines) : 1200 rows (lines)</div><div>st_union table with points (intersection points of this lines): 1700 rows (points)</div><div><br></div><div>the test with rc_split_line_by_points (replace lines & points with my tables and kept tolerance 4) is really slow.. like 2 hours ago and still going......... is this normal?</div><div><br></div><div>i already did the st_dump(st_split way and this also is a huge delay.. and the result is bringing me lots of garbage that i must replace after.</div><div><br></div><div><br></div><div>------------------------------</div><div>## 1 First attempt (really long, without discint / clean table is a whole mess like millions of rows). And this takes really forever..really slow..like 6hrs : 1200lines, 1700 points</div><div><br></div><div><div>--first</div><div>CREATE TABLE lines_with_mess AS (</div><div>SELECT</div><div>((ST_DUMP(ST_SPLIT(a.geom,b.ix))).geom) as geom</div><div>FROM st_union_lines a</div><div>INNER JOIN lots_of_points b</div><div>ON ST_INTERSECTS(a.geom, b.ix)</div><div>);</div><div>--then</div><div>create table lines_clean_segments as (<br></div><div>SELECT DISTINCT ON (ST_AsBinary(geom)) </div><div>geom </div><div>FROM </div><div>lines_with_mess<br></div><div>);</div></div><div><br></div><div>source of this way/approach : <a href="http://stackoverflow.com/questions/25753348/how-do-i-divide-city-streets-by-intersection-using-postgis" target="_blank">http://stackoverflow.com/questions/25753348/how-do-i-divide-city-streets-by-intersection-using-postgis</a></div><div><br></div><div>--------------</div><div><br></div><div>---------------------------------</div><div>## 2nd attempt: cleaner but still takes really a lot of time......</div><div><br></div><div>--first, really gets also messy, lots LOTS of unwanted rows in result</div><div><div>Create table lines_segments as </div><div> Select </div><div> row_number() over() as id,</div><div> (st_dump(st_split(input.geom, blade.ix))).geom as geom</div><div> from st_union_lines input</div><div> inner join lots_of_points blade on st_intersects(input.geom, blade.ix);</div><div> </div><div>--then cleaning the table</div><div>delete from lines_segments a</div><div>where exists </div><div> (</div><div> select 1 from lines_segments b where <a href="http://a.id" target="_blank">a.id</a> != <a href="http://b.id" target="_blank">b.id</a> and st_coveredby(b.geom,a.geom)</div><div> );</div><div><br></div><div>source of this way / approach : <a href="http://gis.stackexchange.com/questions/115973/split-lines-at-intersection-points/115982#115982" target="_blank">http://gis.stackexchange.com/questions/115973/split-lines-at-intersection-points/115982#115982</a></div><div> </div></div><div>--------------------- </div><div><br></div><div><br></div><div>Now i'm testing your function but i still got really delayed for result and dunno if i got messy rows..</div><div><br></div><div><br></div><div>...</div><div><br></div><div>:\</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Vladimir.</div></font></span><div><div class="h5"><div><br></div><div><br>El martes, 4 de marzo de 2014, 12:54:33 (UTC-3), Rémi Cura escribió:<blockquote class="gmail_quote" style="margin:0;margin-left:0.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div>Hey List,<br></div>here is a link <a href="https://github.com/Remi-C/PPPP_utilities/blob/master/postgis/rc_Split_Line_By_Points.sql" rel="nofollow" target="_blank">a link</a> to a working implementation of ST_Split((multi)line,(multi)point).<br>
</div>The current ST_Split is not working correctly with points due to precision issues (and it is not working with multi ofc).<br><br></div>The proposed function hacks the Curvilinear api (<a href="http://postgis.refractions.net/docs/ST_Line_Locate_Point.html" rel="nofollow" target="_blank">http://postgis.refractions.net/docs/ST_Line_Locate_Point.html</a>, etc), in order to escape the precision issue that plagues all point related stuff in postgis.<br>
<br></div>Tolerance is fully supported (no segment too small can be generated with a filtering on split point, only split if close enough).<br><br></div><div>I didn't benchmarked it, but I would expect it to be way slower than the C (non-working) function. This is totally non-optimal but is temporary until the necessary changes are made on ST_Split.<br>
<br></div><div>Function is tested for 2.0 <br></div><div><br></div>Cheers,<br></div>Rémi-C<br></div>
</blockquote></div></div></div></div></blockquote></div><br></div>