Snippets

Henry Huang WITH RECURSIVE SQL examples

Updated by Henry Huang
View revision

File with_recursive.sql Modified

  • Ignore whitespace
  • Hide word diff 
-/* simple loop from 1 to 999999 */
-WITH RECURSIVE
-  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
-SELECT x FROM cnt;
-
 /* for each geometry in the dh_lue table insert its shapepoints into dh_lup table! */
 with recursive
      points_per_element(id, numpoints, points) as
Updated by Henry Huang
View revision

File with_recursive.sql Modified

  • Ignore whitespace
  • Hide word diff 
+/* simple loop from 1 to 999999 */
 WITH RECURSIVE
   cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
 SELECT x FROM cnt;
 
+/* 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
+     (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 n < p.numpoints)
-/*insert into clip.dh_lup (element, x, y, heightlevel)*/
-select element, x, y, 0
+      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;
Updated by Henry Huang
View revision

File with_recursive.sql Modified

  • Ignore whitespace
  • Hide word diff 
 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, heightlevel) as
-     (select 1, e.id, x(geometryn(p.points,1)), y(geometryn(p.points,1)), 0  from clip.dh_lue e, points_per_element p where e.id = p.id
+     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 n+1, p.id, x(geometryn(p.points,n+1)), y(geometryn(p.points,n+1)), 0 from rows_dh_lup r, points_per_element p where r.element = p.id and n < p.numpoints)
+      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 n < p.numpoints)
 /*insert into clip.dh_lup (element, x, y, heightlevel)*/
-select element, x, y, heightlevel
+select element, x, y, 0
   from rows_dh_lup;
Updated by Henry Huang
View revision

File with_recursive.sql Modified

  • Ignore whitespace
  • Hide word diff 
      rows_dh_lup(n, element, x, y, heightlevel) as
      (select 1, e.id, x(geometryn(p.points,1)), y(geometryn(p.points,1)), 0  from clip.dh_lue e, points_per_element p where e.id = p.id
       union all
-      select n+1, p.id, x(geometryn(p.points,n+1)), y(geometryn(p.points,n+1)), 0 from rows_dh_lup r, points_per_element p where r.element = p.id limit p.numpoints)
+      select n+1, p.id, x(geometryn(p.points,n+1)), y(geometryn(p.points,n+1)), 0 from rows_dh_lup r, points_per_element p where r.element = p.id and n < p.numpoints)
 /*insert into clip.dh_lup (element, x, y, heightlevel)*/
 select element, x, y, heightlevel
   from rows_dh_lup;
Updated by Henry Huang
View revision

File with_recursive.sql Modified

  • Ignore whitespace
  • Hide word diff 
 SELECT x FROM cnt;
 
 with recursive
-     row(id, numpoints, points) as
-     (select id, NumGeometries(dissolvepoints(GeomWGS84)), dissolvepoints(GeomWGS84) from dh_lue
+     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, heightlevel) as
+     (select 1, e.id, x(geometryn(p.points,1)), y(geometryn(p.points,1)), 0  from clip.dh_lue e, points_per_element p where e.id = p.id
       union all
-      select 
-      );
+      select n+1, p.id, x(geometryn(p.points,n+1)), y(geometryn(p.points,n+1)), 0 from rows_dh_lup r, points_per_element p where r.element = p.id limit p.numpoints)
+/*insert into clip.dh_lup (element, x, y, heightlevel)*/
+select element, x, y, heightlevel
+  from rows_dh_lup;
  2. 1
  3. 2
HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.