<div dir="ltr">(I spammed this thread a bit with image attachment....)<div><br></div><div><div>Hi Steve,</div><div><br></div><div>We the given dataset, my approach is indeed slow compared to st_union approach (though precision for the st_dwithin clause must be adapted to the current dataset. I took the following precision: 0.000001)</div>
<div><br></div><div>The st_union method generates 18322 segments in 7318 ms, though the final association between original lines and new segment is not done here.</div><div><br></div><div>With the query I gave, the st_dwithin part takes 11.7 sec on a recent laptop machine (1.8 Ghz Intel Core I7, 1024 mb of ram for shared_buffer, 512 for work_mem)...</div>
<div><br></div><div>The complete query returns 17292 segments in 17956 ms.</div><div><br></div><div>As the lines are almost already noded, it generates a lot of intersection points coincident with one line ends.</div><div>
<br></div><div>As you noted, intermediate temp tables may help here:</div><div><br></div><div>I decomposed the query into intermediate steps and the performance is about the same as with st_union :</div><div><br></div><div>
-- First creates temp table with intersection points</div><div>drop table if exists intergeom;</div><div>create temp table intergeom as </div><div>select <a href="http://l1.id">l1.id</a> as l1id, <a href="http://l2.id">l2.id</a> as l2id, st_intersection(l1.geom, l2.geom) as geom </div>
<div>from bdaways l1 join bdaways l2 on (st_dwithin(l1.geom, l2.geom, 0.000001))</div><div>where <a href="http://l1.id">l1.id</a> <> <a href="http://l2.id">l2.id</a>;</div><div><br></div><div>-- keeps only true intersection points</div>
<div>-- must handle the case where lines intersects at a linestring...</div><div>delete from intergeom where geometryType(geom) <> 'POINT';</div><div><br></div><div>-- second temp table with locus (index of intersection point on the line)</div>
<div>-- to avoid updating the previous table</div><div>-- we keep only intersection points occuring onto the line, not at one of its ends</div><div>drop table if exists inter_loc;</div><div>create temp table inter_loc as (</div>
<div><span class="" style="white-space:pre"> </span>select l1id, l2id, st_line_locate_point(l.geom, i.geom) as locus</div><div><span class="" style="white-space:pre"> </span>from intergeom i left join bdaways l on (<a href="http://l.id">l.id</a> = i.l1id)</div>
<div><span class="" style="white-space:pre"> </span>where st_line_locate_point(l.geom, i.geom) <> 0 and st_line_locate_point(l.geom, i.geom) <> 1</div><div>);</div><div><br></div><div>-- index on l1id</div><div>
create index inter_loc_id_idx on inter_loc(l1id);</div><div><br></div><div>-- Then computes the intersection on the lines subset, which is much smaller than full set </div><div>-- as there are very few intersection points</div>
<div>drop table if exists res;</div><div>create table res as </div><div>with cut_locations as (</div><div><span class="" style="white-space:pre"> </span>select l1id as lid, locus </div><div><span class="" style="white-space:pre"> </span>from inter_loc</div>
<div><span class="" style="white-space:pre"> </span></div><div><span class="" style="white-space:pre"> </span>-- then generates start and end locus for each line that have to be cut buy a location point</div><div><span class="" style="white-space:pre"> </span>UNION ALL</div>
<div><span class="" style="white-space:pre"> </span>select i.l1id as lid, 0 as locus</div><div><span class="" style="white-space:pre"> </span>from inter_loc i left join bdaways b on (i.l1id = <a href="http://b.id">b.id</a>)</div>
<div><span class="" style="white-space:pre"> </span>UNION ALL</div><div><span class="" style="white-space:pre"> </span>select i.l1id as lid, 1 as locus</div><div><span class="" style="white-space:pre"> </span>from inter_loc i left join bdaways b on (i.l1id = <a href="http://b.id">b.id</a>)</div>
<div><span class="" style="white-space:pre"> </span>order by lid, locus</div><div>), </div><div>-- we generate a row_number index column for each input line </div><div>-- to be able to self-join the table to cut a line between two consecutive locations </div>
<div>loc_with_idx as (</div><div><span class="" style="white-space:pre"> </span>select lid, locus, row_number() over (partition by lid order by locus) as idx</div><div><span class="" style="white-space:pre"> </span>from cut_locations</div>
<div>) </div><div>-- finally, each original line is cut with consecutive locations using linear referencing functions</div><div>select <a href="http://l.id">l.id</a>, loc1.idx as sub_id, st_line_substring(l.geom, loc1.locus, loc2.locus) as geom ,</div>
<div><span class="" style="white-space:pre"> </span>st_geometryType(st_line_substring(l.geom, loc1.locus, loc2.locus)) as type</div><div>from loc_with_idx loc1 join loc_with_idx loc2 using (lid) join bdaways l on (<a href="http://l.id">l.id</a> = loc1.lid)</div>
<div>where loc2.idx = loc1.idx+1</div><div>-- keeps only linestring geometries</div><div>and geometryType(st_line_substring(l.geom, loc1.locus, loc2.locus)) = 'LINESTRING';</div><div><br></div><div>The total time is 7727 ms and it generates 1865 new segments.</div>
<div><br></div><div>I will see if some filtering clauses used here can be ported efficiently in the big query.</div><div><br></div><div>Nicolas </div></div></div>