blob 4153fc3d (1495B) - Raw
1 /* Aggregates rivers by name and proximity. */ 2 drop function if exists aggregate_rivers; 3 create function aggregate_rivers() returns table( 4 id bigint, 5 name text, 6 way geometry 7 ) as $$ 8 declare 9 c record; 10 cc record; 11 changed boolean; 12 begin 13 while (select count(1) from wm_rivers_tmp) > 0 loop 14 select * from wm_rivers_tmp limit 1 into c; 15 delete from wm_rivers_tmp a where a.id = c.id; 16 changed = true; 17 while changed loop 18 changed = false; 19 for cc in ( 20 select * from wm_rivers_tmp a where 21 a.name = c.name and 22 st_dwithin(a.way, c.way, 500) 23 ) loop 24 c.way = st_linemerge(st_union(c.way, cc.way)); 25 delete from wm_rivers_tmp a where a.id = cc.id; 26 changed = true; 27 end loop; 28 end loop; -- while changed 29 return query select c.id, c.name, c.way; 30 end loop; -- count(1) from wm_rivers_tmp > 0 31 return; 32 end 33 $$ language plpgsql; 34 35 drop index if exists wm_rivers_tmp_id; 36 drop index if exists wm_rivers_tmp_gix; 37 drop table if exists wm_rivers_tmp; 38 create temporary table wm_rivers_tmp (id bigint, name text, way geometry); 39 create index wm_rivers_tmp_id on wm_rivers_tmp(id); 40 create index wm_rivers_tmp_gix on wm_rivers_tmp using gist(way) include(name); 41 42 insert into wm_rivers_tmp 43 select p.gid as id, p.vardas as name, p.geom as way from hidro_l p; 44 45 drop table if exists wm_rivers; 46 create table wm_rivers as ( 47 select * from aggregate_rivers() where st_length(way) >= 50000 48 ); 49 drop table wm_rivers_tmp;