Snippets

Henry Huang WITH RECURSIVE SQL examples

Created by Henry Huang last modified
/* for each geometry in the dh_lue table insert its shapepoints into dh_lup table! */
with recursive
     points_per_element(id, numpoints, points) as
     (select id, numgeometries(dissolvepoints(GeomWGS84)), dissolvepoints(GeomWGS84) from clip.dh_lue),
     rows_dh_lup(n, element, x, y) as
     (select 1, id, x(geometryn(points,1)), y(geometryn(points,1))
        from points_per_element
      union all
      select r.n+1, p.id, x(geometryn(p.points,r.n+1)), y(geometryn(p.points,r.n+1))
        from rows_dh_lup r,
             points_per_element p
       where r.element = p.id
         and r.n < p.numpoints)
insert into clip.dh_lup (element, x, y, heightlevel)
select element, cast(x as integer), cast(y as integer), 0
  from rows_dh_lup;

Comments (0)