3

I have two tables: [see image beloW]

  1. HAK where the Column "Volumenstrom_h" has the input value ( x ) needed for the linear interpolation formula displayed which can be seen surrounded by the black border
  2. RohrdimensionierungsTabelle ( table with no geometry ) hosting the rest of needed variables for the formula.

HowTo

Example of formula at work: For the first value "3573" from the table "HAK" we need the next smallest and next biggest value from the RohrdimensionierungsTabelle. For this step I managed to write an expression and get the X1 (3240) and X2 (3600) values.

/* X1 aka Xmin = 3240 */

    with_variable(
     'Vstrom',
     "Volumenstrom_h",
    array_max(array_filter(aggregate(
                            layer:='RohrdimensionierungsTabelle', 
                            aggregate:='array_agg', 
                            expression:="Volumenstrom [h]" ),
    @element < @Vstrom))
       )

and

/* X2 aka. Xmax = 3600 */

    with_variable(
     'Vstrom',
     "Volumenstrom_h",
    array_max(array_filter(aggregate(
                            layer:='RohrdimensionierungsTabelle', 
                            aggregate:='array_agg', 
                            expression:="Volumenstrom [h]" ),
    @element < @Vstrom))
       )

Next for finding the Y2 and Y2 I need to look in the RohrdimensionierungsTabelle at the index (@row_number) of X1 and X2 and then shift to the next column untill the values are ≤ 250. In the example above: Y1 (111,4) and Y2 (134,9). Here is the part where I need help.

Breaking it down:

1.First I need to get the index for the X2 and X1 from the RohrdimensionierungsTabelle.

2.Then get the values Y2 and Y1 at the index positon from the step 1 for the first Column ( 25 x 2,3 [Pa/m] ) and wrap it in a if statement checking if the values are smaller/equal to 250. If not check for the next Column and so on.

3.I think it would be more elegant if I would have another index for calling the columns by their position in the array an not by their name.

In Summary: I am looking for a expression in the HAK table where i can get all the values ( X1,X2,Y1,Y2) from the RohrdimensionierungsTabelle for each value x in the column "Volumenstrom_h"

Improve this question

2 Answers 2

Reset to default
1

  • Disclaimer #1: the Field Calculator solution I've given below is particularly convoluted because I've aimed to use one expression that works for all four values (x1, x2, y1, y2).

    It would be simpler with separate expressions for x and y, but I chose to combine them, because I feel the likelihood and consequences of pasting the wrong expression in the wrong column is high enough (at least in my experience!) to warrant writing a universal expression at the expense of readability.

  • Disclaimer #2 : because of the numerous variables and operations involved in parsing/sorting attribute names (see below), storing features etc, a custom Python function would be far more appropriate and elegant as a long-term solution, especially in handling errors.

Expression

To use the expression below with the data you've provided, simply change the value of the variable var_eval at the top of the expression (case-insensitive)

with_variable('var_eval',    --specify variable from equation: x1, x2, y1, y2
'x1',                        --CHANGE VARIABLE VALUE HERE

with_variable('table_name',  --other table name
'RohrdimensionierungsTabelle',

with_variable('v_fld',       --volume flow field in other table
'Volumenstrom [h]',

with_variable('pam_array',   --all pa/m field names from other table, sorted ascending
array_foreach(
    array_sort(
        array_foreach(
            array_filter(map_akeys(attributes(get_feature_by_id(@table_name,1))),
                         @element ilike '%[Pa/m]'),
                    regexp_replace('0'||@element,'^(0)([0-9]{3}|[^0-9])','\\2')
                )
            ),
            regexp_replace(@element,'^0','') --added leading 0 earlier to sort names, now remove 0
        ),

with_variable('idx_ops',     --assign operators for next expr based on equation var (1=prev, 2=next)
map_get(map('1',array('<',-1),'2',array('>',0)),right(@var_eval,1)),

with_variable('idx_ftr',     --look up index (prev/next) feature id in other table, get feature
get_feature_by_id(@table_name,
    eval(format('aggregate(''%1'',
                           ''array_agg'', 
                           $id,
                           "%2" %3 attribute(@parent,''Volumenstrom_h''))[%4]',
                @table_name,@v_fld,@idx_ops[0],@idx_ops[1] --configure lookup expr based on operators
            )
        )
    ),

case                         --this section calculates output value using above variables
when lower(left(@var_eval,1)) = 'x' 
then 
attribute(@idx_ftr,          --if x, get volume flow field value for index feature
          @v_fld)
when lower(left(@var_eval,1)) = 'y'
then 
array_filter(
    array_foreach(@pam_array,
                  attribute(@idx_ftr,@element)
                  ),         --if y, get all pa/m field values for index feature
            @element < 250 and @element != '', 1 
            )[0]                --then filter out empty vals and vals < 250, and keep first value left
else
-1                           --error output
end
))))))

Result

Click to enlarge:

Click to enlarge

A note on data structures

Your post mentions referencing columns (aka fields) in another table by their "position in the array" rather than by their name. I felt the need to clarify that although this is the right concept, in GIS you have to always reference a column by its name.

This means it is not trivial to specify "columns 2 to 12 in that order" ($A:$L in Excel). Here you have to look at the actual names and specify the logic that gets you the result. In this instance it's:

  • all column names containing [Pa/m]
  • ordered by the numeric first portion

The second point bears mentioning; even though map_akeys(attributes()) returns column names in alphabetical order by default, that's not based on numeric portions, so 110 x 10 [Pa/m] comes before 25 x 2,3 [Pa/m]. This required padding an 0 on the required columns to sort the columns appropriately, then taking the 0 out again so the columns return to their original names for further use.

As you can see, although the concept is in the right direction, the actual execution is very different, so keep column names and not positions in mind when structuring your data and finding ways to problem solve. (It was good that your pressure columns had a common suffix! But consider zero-padding the column names or using letters to sort next time. Also, column names starting with numbers are best avoided for technical reasons)

Improve this answer
6
  • this is exactly what i was looking for, although it seems very advanced for me at the moment, I am very happy and looking forward to suck all the information out of the expression. Meanwhile I found an answer all by myself for getting X1,X2,Y1,Y2 and will document it and post it here. The difference is that I came up with a different expression for each variable. What you did is more than genius for generalizing it and making it possible to just swap at the beginnig the neede variable. I trully appreciate your time putting into this masterpiece :) Commented Feb 1, 2024 at 9:46
  • I understand everything except the expression where you eval the aggregate. First of all why do we need eval at this point, what exactly is he doing, why notcalling just as usual the aggregate function. Second i understand the use of escaping characters but what is the explenation for '%1' being the name of the layer and for the filter part: "%2" %3 attribute(@parent,''Volumenstrom_h''))[%4] .It would be nice if you coul provide some more information about this expresion and the meaning of % placeholders and what are they referencing. Thx in advance Commented Feb 1, 2024 at 10:07
  • 1
    I had to skip on the usual explanations for this post because it was already very long. Your second question first: the %1 etc are part of format(). The first parameter, the string input, is not an actual aggregate() expression but one entirely formatted as a string. Why? Because previous and next index use almost the same expression except one is "field" > attribute(@parent... and the other is "field" < attribute(@parent... That is editing the actual syntax (operator) of the expression. Can't use a variable to represent > or < so need to edit the raw string using format(). Commented Feb 1, 2024 at 11:07
  • 1
    Once you convert the entire aggregate() expression to a raw string, much more elegant to use format() than concat() or ||, so I used all the other variables as well even though if it wasn't for < and > I could've used them directly. Once everything has been replaced we now have a raw string representation of the expression we want to run. But that alone is useless. For the field calc to recognise it as an expression and actually evaluate it, we need eval(). It's like the difference between '1 + 1' - result is '1 + 1' - and eval('1 + 1') - result is 2. Hope that helps Commented Feb 1, 2024 at 11:15
  • My bad, i missed to read the documentation of the format() function because I was a bit overwhelmed with the new mix between format and eval . It all makes sense now and how to use it and for what the arguments (placeholders) are. I owe you big time. Commented Feb 1, 2024 at 12:18
1

Before getting the formidable solution from @she_weeds I managed to come up with a solution on my own:

X1[min]:

--Expression I

with_variable(
 'Vstrom',
 "Volumenstrom_h",
      array_max(array_filter(aggregate(
               layer:='RohrdimensionierungsTabelle', 
               aggregate:='array_agg', 
               expression:="Volumenstrom [h]" ),@element < @Vstrom)))

-- Result:3240

X2[max]:

--Expression I

with_variable(
 'Vstrom',
 "Volumenstrom_h",
      array_min(array_filter(aggregate(
               layer:='RohrdimensionierungsTabelle', 
               aggregate:='array_agg', 
               expression:="Volumenstrom [h]" ),@element > @Vstrom)))

-- Result: 3600

For getting the values of Y1[min] and Y2[max] we need the index from the X1 and X2 values that we just have found out. The Y values that we are looking for are situated on the same row as the X but shifted because they belong to another column. We must shift so often untill a value is beeing found that is smaller then 250.

Expression to find the index of a hardcored value for example:

--Expression II

array_find(aggregate(
           layer:='RohrdimensionierungsTabelle', 
           aggregate:='array_agg', 
           expression:="Volumenstrom [h]") ,3240)+2  

-- Result: 27

To find the index of Y1[min] we need to replace the hardcored value "3240" with the expression from X1[min] that is returning the exact same value back:

--Expression I + II

array_find(aggregate(
           layer:='RohrdimensionierungsTabelle', 
           aggregate:='array_agg', 
           expression:="Volumenstrom [h]") , 

with_variable(
 'Vstrom',
 "Volumenstrom_h",
array_max(array_filter(aggregate(
layer:='RohrdimensionierungsTabelle', 
aggregate:='array_agg', 
expression:="Volumenstrom [h]" ),@element < @Vstrom)))

)+2 

-- Result: 27

enter image description here

The last step is to get the value of Y from the existing columns at the index we just find out "27". As an example, give me the value from column "50 x 4,6 [Pa/m]" at the index 27 ==> 111,4

--Expression III

attribute(get_feature_by_id('RohrdimensionierungsTabelle',27),'50 x 4,6 [Pa/m]')

At this point we need to replace the in index (27) in Expression III with the Expressin I+II.The Problem is still that we have to shift the name of column untill a value smaller then 250 is beeing reached. For this reason I have decided for a "quick & dirty" CASE-WHEN... It is not an elegant solution but is working.... The expression shown below is for the Y1[min]. The same for Y2[min] but with array_min and the aggregate filter : @element < @Vstrom

--Expression I + II + III

 -- CASE: 25 x 2,3 [Pa/m]
case when 
attribute(get_feature_by_id('RohrdimensionierungsTabelle',
array_find(aggregate(
layer:='RohrdimensionierungsTabelle', 
aggregate:='array_agg', 
expression:="Volumenstrom [h]") , 
with_variable(
 'Vstrom',
 "Volumenstrom_h",
array_max(array_filter(aggregate(
layer:='RohrdimensionierungsTabelle', 
aggregate:='array_agg', 
expression:="Volumenstrom [h]" ),@element < @Vstrom)))
)+2 
),'25 x 2,3 [Pa/m]')  <=  250

then 

attribute(get_feature_by_id('RohrdimensionierungsTabelle',
array_find(aggregate(
layer:='RohrdimensionierungsTabelle', 
aggregate:='array_agg', 
expression:="Volumenstrom [h]") , 
with_variable(
 'Vstrom',
 "Volumenstrom_h",
array_max(array_filter(aggregate(
layer:='RohrdimensionierungsTabelle', 
aggregate:='array_agg', 
expression:="Volumenstrom [h]" ),@element < @Vstrom)))
)+2 
),'25 x 2,3 [Pa/m]') ... END -- AND SO ON FOR THE REST OF COLUMNS
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.