Sum Grouped by Query of Values from Field of Variable Length

Question

I am trying to find the sum of watershed areas for about 30 small streams in BC. Each stream has a code that is the same as the code for its watershed. The code for each stream is derived by adding several digits to the code of it's parent stream. For example; stream [c] 100-123-456-789-000-000-000 is a tributary of stream [b] 100-123-456-000-000-000-000, which is a tributary of stream [a] 100-123-000-000-000-000-000.

So, if streams [b] and [c] are the only tributaries of stream [a], then the total area of watershed [a] is the sum of the area of all three streams. diagram

I think that the best way to perform this query would be using SQL in the Field Calculator (QGIS). I am trying to write the sql query myself, but I am having trouble grouping the streams because of the varying length of the code using:

I think that I would need to use something like replace to deal with the varying length of code. So I create a virtual field (code.trunc') with a truncated version ofcode' using : 'Replace("code", ’-000’,’’).

Then I am trying to sum the watershed area of the tributaries of each stream using:

sum ("Sum.Val", "code.trunc" LIKE concatenate("code.trunc",'%')

This code crashes QGIS. I am guessing that it is because the LIKE function is not very efficient, and this is a large data set. Note, I am using a subset (n=6144 features) of the data set which I will link to below).

Is there a better way to do this?

This table shows what the results of the query might look like: example

The data that I am using is available here. When I figure out the query that I need to use, I plan to perform it only on the 30 streams which will be selected prior to the query.

• Kazuhito: Dropped my "answer". I do agree with you, and I think difficult part of this question is how you define the group after removing tailing '-000-'s. Thanks for interesting question!

Answer 1

If I correctly understood the translation and because the original data is not available for me, I performed my experiment, as a result, the geodata are abstract tables, see figure 1. Fragment of the river network.

enter image description here

My way of solving your problem is the following:

1) Create a line type - a point type from the river network, for this, run the code in pgAdmin (or in QGIS - the tool to extract the nodes) according to your desire, for example:

create table hydrography_poi_exper as SELECT (ST_Dumppoints (geom)). * FROM hydrography_arc_exper;

Result, Figure 2

enter image description here

2) In QGIS, run the "Voronoi Polygons" tool on the table (vector layer) table hydrography_poi_exper, save as hydrography_poi_exper_voronoi and add it to the DBMS

The result is shown in Figure 3.

enter image description here

3) in pgAdmin run the code, and in your tables there should be the names of rivers, streams, etc., in my example it's rivers cacha, belbek, chernaya (three times)

create table hydrography_poi_exper_cacha as SELECT * FROM hydrography_poi_exper_voronoi, hydrography_arc_exper WHERE st_intersects (hydrography_poi_exper_voronoi.geometry, hydrography_arc_exper.geom) and hydrography_arc_exper.objname = 'Kacha'; Result of Figure 4

enter image description here

4) Combine the shreds in one polygon for each river by the following query (for each river separately):

create table hydrography_poi_exper_cacha_union SELECT ST_union (geometry) FROM hydrography_poi_exper_cacha; Result of Figure 5

enter image description here

5) Combine your rivers into one table by the following query:

create table hydrography_exper_area_union as SELECT * FROM hydrography_poi_exper_cacha_union UNION ALL SELECT * FROM hydrography_poi_exper_chernaya_union UNION ALL SELECT * FROM hydrography_poi_exper_belbek_union

Now you can choose your rivers and determine the area of one or several watershed zones by the following query:

SELECT Sum (ST_Area (geography (geom))) / 1000000 AS area_km2 FROM hydrography_exper_area_union WHERE objname = 'Belbek' or objname = 'Black' or objname = 'Kacha' I hope that these examples will not be in vain,

Answer 2

You can indeed use SQL to perform that task.

Load both layers (stream and watershed) in QGIS.

Go to databasemanager> virtual layers> QGIS layers and open the SQL-window

The code that will do the thing for you is:

select substr(s.code, 5, 3) subcode, sum(st_area(w.geometry)) sum_area
from stream s
join watershed w on s.code = w.code
group by substr(s.code, 5, 3)

This code joins the watershed to the streams based on their common field 'code'. Then it substracts the wished 'level' to sum up the watershedarea.

If you want to change the level, just change the second and third parameter of substr(). More info about that function: https://www.sqlite.org/lang_corefunc.html#substr.