SHARE
TWEET

Untitled

a guest Apr 10th, 2016 10 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. create or replace function slice_linestring (way geometry, slicepoints int[]) returns geometry[]
  2. as
  3. $$
  4.     with
  5.         x as (select (st_dumppoints(way)).geom geom),
  6.         z as (select array_agg(geom) pts_array from x),
  7.         numbers as (select unnest(slicepoints) k),
  8.         slices as (select k, lead(k, 1) over () ll from numbers)
  9.     select array_agg(st_makeline(pts_array[k:ll])) from z, slices
  10.     where ll is not null
  11. $$
  12. language sql;
  13.  
  14. drop table if exists rd_sliced_roads cascade;
  15. create table rd_sliced_roads as
  16. with ways as (
  17.     select b.osm_id, unnest(slice_linestring(way, slices)) way
  18.     from rd_line b, slice_order a
  19.     where a.osm_id = b.osm_id)
  20. select
  21.     row_number() over () id, osm_id, way
  22.     from ways;
  23.  
  24. select st_numpoints(way) from rd_sliced_roads;
  25.  
  26. drop view if exists pg cascade;
  27. drop view if exists nodes cascade;
  28. create view nodes as
  29. select row_number() over () id, st_node(way) way
  30. from rd_sliced_roads;
  31.  
  32.  
  33. drop view if exists pg;
  34. create view pg as
  35. with   
  36.     polygroup as (select ST_Polygonize(way) initial from nodes),
  37.     dumped as (select (st_dump(initial)).geom result from polygroup)
  38. select row_number() over () id, result
  39. from dumped;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top