SQL and array data

There are many examples in InfoWorks WS Pro of a network object containing an array of data. The most obvious one is the Bends data field, which stores the intermediate (x,y) pairs that define the bends in a link object.

These arrays can take two forms:

• Value - such as Long Integer or Double contains numbers of the same type, such as the Bends data field. You cannot refer to individual elements in these arrays using SQL
• Structure - contains structured data for a sub-object. Each element in the structure has a name and you can refer to individual elements using SQL. However, an element inside a structure may be a sub-array of type Value. Again you cannot access individual values in such an array

Value arrays

If you carry out a comparison with a value array, InfoWorks WS Pro will make the comparison with all the elements in the array. This is best explained with an example.

In this example:

X = 3

Y (array) = [1, 4, 5], which can also be written like this: Y₁ = 1, Y₂ = 4, Y₃ = 5

Comparison results would be:

X > Y is true because 3 > 1 ≡ X > Y₁

X <= Y is true because 3 <= 4 ≡ X <= Y₂

Therefore in this example X > Y, X >= Y, X < Y and X <= Y are all true. So, for that matter, are NOT(X > Y)and NOT(X > =Y).

Comparisons with value arrays are still useful as long as you understand exactly how they work. For example, if X and Y define the elevation values for customer points and nodes respectively, X<Y can be used to find customer point elevations that are below the ground level.

Structure arrays

You can access the fields in a structure array by name and treat them in the same way as any other object field. The easiest way to do this is to use the list in the SQL Field box on the SQL dialog to choose the data item.

The syntax for fields in a structure array is

<array>.<field>

Note: Be careful not to confuse linked objects with array data as the syntax is the same - <array>.<field> and <linked_object>.<field>