Using RECURSIVE in Virtual Layer

Question

Preface

For a while, I was wondering about applying the looping/iterable functions in QGIS's Virtual Layer. There were several cases when the application of a loop would be beneficial and useful, e.g. Intersect multiple layers at once QGIS or Incorrect result while adding points along geometry in QGIS.

---

There is one polyline layer that represents flows (attributes: "ORIGINE", "DEST", "FLUX", and "DIST_KM"). Its attribute table looks as following.

My desire is to obtain some information based on categories and corresponding rules for each category.

By any means, this query will give what I actually need

SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" > 0 AND "DIST_KM" < 1
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 1 AND "DIST_KM" < 2
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 2 AND "DIST_KM" < 3
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 3 AND "DIST_KM" < 4
UNION ALL
SELECT COUNT(*) AS anzahl, SUM(FLUX) AS summe
FROM "flows_workday_GK5"
WHERE "DIST_KM" >= 4

Output

In the above example, I showed only five categories, but what if I will need to work with 10, 100, 1000 categories.

---

Since Virtual Layer supports SQLite library it might be smart to embed a RECURSIVE expression in the afore query. I was inspired by this article: basic recursive query on sqlite3?

My desire is to achieve a table as following

category | anzahl | summe
   1     |   96   | 56996

---

These are several efforts were done, but somehow I am getting errors.

I. When I try with this query

WITH RECURSIVE cat(x) AS (
     SELECT 0
     UNION ALL
     SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
     FROM "flows_workday_GK5", cat
     WHERE "DIST_KM" > x AND "DIST_KM" < x + 1
     AND x < 4
     UNION ALL
     SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
     FROM "flows_workday_GK5", cat
     WHERE "DIST_KM" > x
     AND x = 4
)

SELECT *
FROM cat

I end up with this error

---

II. Even if I simplify the query like

WITH RECURSIVE cat(x) AS (
     SELECT 0
     UNION ALL
     SELECT (x + 1) AS category, COUNT(*) AS anzahl, SUM(FLUX) AS summe
     FROM "flows_workday_GK5", cat
     WHERE "DIST_KM" > x AND "DIST_KM" < x + 1
     AND x < 4
)

SELECT *
FROM cat

In this case, I end up with the following error

---

So, my question is: How shall I adjust the query to be able to get the result by means of RECURSIVE function?

---

The efforts that were made by @M Bain are absolutely brilliant and lead to the desired output. As well as I got to know CAST-expression. Nevertheless, there are somehow several pitfalls that may lead to the deficient result.

• The last 'category' might be incomplete because it won't include all the remained records, see image below. The idea is to aggregate all the rest values in the last 'category'. In my case, the 5th 'category' has to have a '2105282'-value.

  

• ROUND(DIST_KM + 0.5, 0) owns an inevitable 'rounding effect'. 'category' number at certain point will jump over some essential numbers, see below.

  

• Last but not least, I am truly passionate to learn/understand the RECURSIVE technique.

Answer 1

I don't think you need recursive, it looks like you want subtotals for different categories of "DIST_KM"

Does this give something close to want you expect:

SELECT COUNT(*) as anzahl, SUM(FLUX) AS summe,
ROUND(DIST_KM +0.5, 0) AS Dist_group
FROM "flows_workday_GK5"
GROUP BY ROUND(DIST_KM +0.5, 0)

Edit:
There will be gaps in the groups if the data is sparse and not all distance intervals are represented in the data.
If you would prefer bigger intervals as the distances increase you could try this query:

SELECT COUNT(*) as anzahl, SUM(FLUX) AS summe,
CAST(DIST_KM AS INTEGER) || '-' || CAST(DIST_KM + 1 AS INTEGER) AS Dist_group
FROM "flows_workday_GK5"
GROUP BY CAST(DIST_KM AS INTEGER)
WHERE DIST_KM < 10
UNION
SELECT COUNT(*) as anzahl, SUM(FLUX) AS summe,
CAST(CAST(GIS_Length/2  AS INTEGER)*2 AS VARCHAR(4)) || '-' || CAST(CAST(GIS_Length/2 + 1 AS INTEGER)*2 AS VARCHAR(4)) AS Dist_group
FROM "flows_workday_GK5"
GROUP BY CAST(DIST_KM/2 AS INTEGER)
WHERE DIST_KM >=10 AND DIST_KM < 20
UNION
SELECT COUNT(*) as anzahl, SUM(FLUX) AS summe,
CAST(CAST(GIS_Length/10  AS INTEGER)*10 AS VARCHAR(4)) || '-' || CAST(CAST(GIS_Length/10 + 1 AS INTEGER)*10 AS VARCHAR(4)) AS Dist_group
FROM "flows_workday_GK5"
GROUP BY CAST(DIST_KM/10 AS INTEGER)
WHERE DIST_KM >=20

This will give 1km groupings from 0 to 10km, 2km groups from 10 to 20 and 10km intervals for distances greater than or equal to 20.

Answer 2

With RECURSIVE query, you have to do a generate_series (PostgreSQL function not supported by SQLite), which create you a number series from conf.start to conf.stop by conf.step.

Then, retrieve this number and do what you want with, here your flow's summation SELECT.

Here the Virtual Layers / SQLite / GeoPackage working code :

-- number series
WITH RECURSIVE generate_series(category) AS (
SELECT conf.start
FROM conf
UNION ALL
SELECT category + conf.step
FROM generate_series, conf
WHERE category + conf.step <= conf.stop
),

-- configuration
conf AS (
SELECT
1 AS start,
5 AS stop,
1 AS step
)

-- query
SELECT gs.category, COUNT(*) AS anzahl, SUM(f.FLUX) AS summe
FROM flows AS f, generate_series gs, conf
WHERE f.DIST_KM >= category
AND DIST_KM < category + conf.step
GROUP BY gs.category

EDIT

You can make the couple queries generate_series and conf more independent :

-- number series
WITH RECURSIVE generate_series(category, upper_category) AS (
SELECT conf.start,
       conf.start + conf.step
FROM conf
UNION ALL
SELECT category + conf.step,
       upper_category + conf.step
FROM generate_series, conf
WHERE category + conf.step <= conf.stop
),

-- configuration
conf AS (
SELECT
1 AS start,
5 AS stop,
1 AS step
)

-- query
SELECT gs.category, COUNT(*) AS anzahl, SUM(f.FLUX) AS summe
FROM flows AS f, generate_series gs
WHERE f.DIST_KM >= category
AND DIST_KM < upper_category
GROUP BY gs.category