10

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.

input

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

result

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

error_1


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

error_2


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.

    issue1

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

    issue2

  • Last but not least, I am truly passionate to learn/understand the RECURSIVE technique.
2
  • When you get J. Monticolo's wonderful query working would you check those gaps in the sequence you highlighted above?
    – M Bain
    Sep 27, 2019 at 9:54
  • There are some gaps, but they apparently were caused by the missing data for that particular category. I double-checked it with a simple expression in the Attribute table "DIST_KM" > 243 AND "DIST_KM" < 244 => means that there is no category 243.
    – Taras
    Sep 27, 2019 at 10:03

2 Answers 2

6

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.

1
  • 1
    Thank you for your efforts and new information
    – Taras
    Sep 27, 2019 at 9:04
5

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
3
  • Thank you! Now it is clear to me
    – Taras
    Sep 27, 2019 at 8:47
  • Excellent example! I hadn't seen a recursive query with the separate conf table table before - makes it easier to read and makes the intent clearer. Thank you.
    – M Bain
    Sep 27, 2019 at 9:19
  • Thanks for the edit, I did wonder if it was necessary to reference conf in the final query.
    – M Bain
    Sep 27, 2019 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.