Complex column formulas (discontinuities, if statements, etc)

Calculated column formulas are a powerful feature of Cartographica, allowing you to create a column that changes with other data in the layer. However, it's not obvious that these formulas contain a lot of additional flexibility. It is possible, for example, to create discontinuous functions by using the "if" operator, which is mentioned in the documentation.

The general format for an if block is a bit odd for some, as it is read as if it is a sentence. For example, let's say you wanted to create a column who's value is pinned to the last 500 greater than zero. Thus, if the original value was -5, the value should be 0; if it was 400, it should be 0; if it was 501, it should be 500; etc. Here is an example of this formula in Cartographica:

trunc(value/500)*500 if(value>0) else 0

Breaking this down, we'll use 599 as an example value first:

trunc( value/500) takes the value, divides it by 500 and then removes everything after the decimal point. 599/500 is 1.198 and thus trunc( 599/500) = 1
*500 multiplies this by 500. 1*500 = 500.
if (value>0) is where the fun begins. Right now, the calculated amount is 500, but this test will determine if that calculation is used or not. The test is value>0, thus 599>0, which in this case is true. Thus, we'll be using the value before now in the formula.
else 0 is not going to be used here because the if(value>0) test was successful. However, if it wasn't, then we'd be moving to the next expression, which is 0.

So, with value=599, the column calculated by this formula would be 500.

Let's work through the example using -4200 as the value now:

trunc( value/500) takes the value, divides it by 500 and then removes everything after the decimal point. -4200/500 is -8.4 and thus trunc( -4200/500) = -8
*500 multiplies this by 500. -8*500 = -4000.
if (value>0) is where the fun begins. This time, the calculated amount is -4000, but this test will determine if that calculation is used or not. The test is value>0, thus -4000>0, which in this case is false. Thus, we'll be throwing out the calculation we've done so far.
else 0 is now going to be used, because we failed the previous test.

So, with the value=-4200, the column calculated by this formula would be 0.

Of course, there's no reason why you should limit yourself to a single source column, so instead of testing the column in the calculation, you could test a value from another column. For example, let's say you have a polygon layer with a "Crop" column that describes what is planted in an area. You want to calculate the potential yield for your polygon layer, knowing that when Crop is "Corn", it's going to be 10 per acre, when it's "Wheat" it's 8 per acre, and when it's "Fallow" it's zero. How do we represent this?

geometry.area()*10 if (Crop=="Corn") else (geometry.area()*8) if (Crop=="Wheat") else 0)

A few things to note about this:

It is comprised of 2 different clauses:
geometry.area()*10 if (Crop=="Corn") else ...
geomerty.area()*8 if (Crop=="Wheat") else 0
With the second clause being in parenthesis to indicate that it is to be taken as a unit and only evaluated if the first clause is inappropriate.
The equality comparison is made using a == and not an =. This is part of the syntax and must be yielded. If you use just a single =, you will get an error while typing in the formula.

We hope you find this useful in making your work easier with Cartographica!

Comments