TVD Connector - SQL Expression Dialog

This dialog is a simplified version of the standard Innovyze SQL dialog and it enables you build an SQL expression for a TVD connector.

The expression can be checked using the Test button in the dialog. If the expression is valid, the results from evaluating the expression are displayed; but if not, a suitable warning will be issued.

A saved expression is automatically evaluated during an appropriate Run.

The SQL Expression dialog is displayed when you click in the Output expression field in the TVD Connector Grid Window of the Polygons Grid or a TVD Connector's Property Sheet.

If you are familiar with SQL syntax, you can type in an expression manually (using the Field drop-down list, if required, to insert the fields you want to include in the expression). The supported SQL operators and functions, which can be used in an expression, are listed below.

Alternatively, click Builder >> . This displays a set of buttons representing the operators that you can include in the expression.

Field	Description
Object Type	A read-only field displaying the type of object to which this query will apply. This is always a TVD connector.
Field Type	A read-only field which shows the fields related to the TVD connector in the Field dropdown list.
Field	A list of database fields applicable to the TVD connector can be selected from the drop-down list. This list includes all the fields that comprise the TVD connector (as displayed in its grid window or property sheet) as well as the following fields: oid (Object ID) - returns the primary key of the object. This can be used to get multi-part IDs for objects such as pipes that include link suffix as part of their ID. otype (Object Type) - used to return the object type. rank (Rank) - used to return the rank, (position), of the object as it appears in an ordered list. See SQL in InfoWorks ICM for further information. run.origin – used to return the run origin as a date/time. Note: Currently, only fields which can return a numerical value are included in the evaluation of an expression. Selecting a field from the list inserts the field into the expression.
Display precision	Select the number of decimal places to be shown when displaying the results of the SQL expression.
Builder	Click the Builder >> button to display a set of buttons representing the operators that can be included in the expression. Click the Finish << button to close the builder.
Test	Tests that the expression contains valid SQL functions and operators. See Testing the expression for further information.
Save	Saves the current expression. The saved expression is displayed in the Output expression field in the TVD Connector's property sheet or grid window.
Close	Closes the SQL Expression dialog without saving the expression.

Supported SQL syntax for TVD expressions

TVD expressions are used to create and manipulate whole time series of numeric (or null) data. All time series exist in a common time domain defined by start and end times (usually based on the run start and end time) and each time series has an interpolation rule (linear or step/extend) that determines how values can be interpolated at times that fall between the individual data points in the series. See the technical paper Theory of time series expressions for more about how time series are represented and manipulated.

Any of the standard set of arithmetic, logical or comparison operators, unary or binary, may be applied to a time series data value or pair of values. All of the standard SQL functions that take one or more numeric arguments and that yield a numeric result can also be used with time series data arguments and will yield a time series data value as a result. See the topic SQL Syntax for more details.

Specialised functions for use with time series data values

The following functions can be used only to create or manipulate time series data values. They cannot be used in "normal" SQL expressions and queries. These functions are described in more detail in the technical paper Theory of time series expressions.

The following functions and operators can be used in TVD expressions:

TSDATA(source, units)

Creates a time series from a data source that is either a single number, or a stream of observed or previously calculated data.

Arguments	Description
source	One of the three input fields (input_a, input_b and input_c) is identified as a source of time series data, where the input fields can be either a numeric value or a string which must be one of: the id of a TVD connector the id of a TSD stream, prefixed by the character # a number
units	Must be specified as a string such as "mm" or "ft", and are used only if the input field is a TSD stream id, in which case the specified units need to be compatible with the units (quantity) of the input, or if the input field is a number. No units should be specified using quotes, e.g. "".

Arguments

Description

source

One of the three input fields (input_a, input_b and input_c) is identified as a source of time series data, where the input fields can be either a numeric value or a string which must be one of:

the id of a TVD connector
the id of a TSD stream, prefixed by the character #
a number

units

Must be specified as a string such as "mm" or "ft", and are used only if the input field is a TSD stream id, in which case the specified units need to be compatible with the units (quantity) of the input, or if the input field is a number. No units should be specified using quotes, e.g. "".

Example

TSDATA(input_a,"mm/hr") + TSDATA(input_b,"mm/hr") + TSDATA(input_c,"mm/hr") Evaluating this expression adds all the inputs.

TSDATA(Input_a, "mm/hr") * 3 Evaluating this expression scales Input A by 3.

NVL(TSDATA(Input_a, "mm/hr"), TSDATA(Input_b, "mm/hr")) Evaluating this expression replaces null or missing values from Input A with those from Input B.

NVL(TSDATA(Input_a, "mm/hr"),4.5) Evaluating this expression replaces null or missing values from Input A with a specified value, in this case, 4.5.

TSPOINTS(interval_unit, interval_multiplier, date_origin, time_of_day_origin, daylight_saving)

Generate a new time-series with specified intervals.

Arguments	Description
interval_unit	Must be one of the following: "s" or "second" "m" or "minute" "h" or "hour" "d" or "day" "w" or "week" "mon" or "month" "y" or "year"
interval_multiplier	The repeating time interval. For example, this would be 15 if the repeating time interval was every 15 minutes.
date_origin	A string representing the date in the format yyyy-mm-dd
time_of_day_origin	A string representing the time in the format hh:mm, hh:mm:ss or hh:mm:ss.1
daylight_saving	Should be either1 (true) or 0 (false).

Example

TSPOINTS("m", 15, "", "", 0) This generates a 15 minutes interval time series.

TSPOINTS("y", 1, "2000-07-01", "09:00", 1) This generates a time series at annual intervals, at 09:00 local clock time on 1 July each year.

TSRESAMPLE(value_series, time_stamp_series, statistic, window_option, left_secs, right_secs, statistic_factor, output_interpolation)

Apply smoothing and statistical resampling.

Resamples the values in value_series using the time stamps of the non-null, non-zero data points in time_stamp_series, calculating the value for a window around each time stamp using the specified statistic and window_option.

A resampling buffer (set on a tvd connector) can be defined to extend the time domain backwards in chained simulations.

Arguments	Description
value_series	Time series data generated by `TSDATA`
time_stamp_series	A time series generated by `TSPOINTS`
statistic	One of the following: "MEAN" "TMEAN" "INTEGRAL" "SDEV" "FIRST" "LAST" "DUR" "DVDT" "MAX" "MIN" "MAXTIME" "MINTIME" "COUNT" "COUNTNONNULL" "TOTAL" "COVERAGE" "PERCENTIL" "BWDPERCENTILE"
window_option	1 – window is the interval between this time stamp and the next time stamp 0 – window is from left_secs seconds before this time stamp to right_secs seconds after it -1 – window is the interval between the preceding time stamp and this time stamp
left_secs	Used to define the width of the left part of the time window in seconds
right_secs	Used to define the width of the right part of the time window in seconds
statistic_factor	A multiplier applied after calculating the statistic
Output_interpolation	Linear, step or extend

Example

Take raw data from input_a and resample it to a 15-minute time step using a moving average window with a width of 1 hour:

SET $values = TSDATA(input_a, "");

SET $points = TSPOINTS("m", 15, "", "", 0);

TSRESAMPLE($values, $points, "MEAN", 0, 1800, 1800, 1, "linear");

TSAPPLYPROFILE(input_series, normalize, divide_by_duration, multiplier, output_interpolation, number_list)

Apply a repeating or individual (event) profile constructed from a list of numbers.

Arguments	Description
input_series	A time series generated by `TSPOINTS`
normalize	1 (true) or 0 (false)
divide_by_duration	1 (true) or 0 (false). If this is true then the initial values are divided by the duration of the interval.
multiplier	Each value can be multiplied by the specified multiplier. This can be used for unit conversion.
output_interpolation	Linear, step or extend
number_list	A list of numerical values

Example

Create a profile that repeats daily using 24 hourly values with linear interpolation. The multiplier is taken from the User_number_1 field of the TVD connector.

LIST $profile = 8, 3, 1, 1, 1, 2, 5, 22, 51, 42, 35, 33, 38, 31, 32, 31, 31, 40, 47, 52, 39, 21, 19, 13, 10;

SET $daypoints = TSPOINTS("d", 1, "", "", 0);

TSAPPLYPROFILE($daypoints, 1, 0, user_number_1, "linear", $profile);

TSBEFORE(date_time)

Constructs a series that is true (1.0) before the specified date/time and false (0.0) thereafter, with interpolation="extend"

Arguments	Description
date_time	A numeric representation of the date/time in days

Example

Use input_a up to 1 hour before the run origin, then input_b

SET $a = TSDATA(input_a, "");

SET $b = TSDATA(input_b, "");

SET $before = TSBEFORE(run.origin – 1/24);

IIF($before, $a, $b);

TSDATETIME(data_series, daylight_saving, output_interpolation)

Constructs a series with time stamps that match the points in the input data series and values that are the numeric equivalents of these time stamps.

Arguments	Description
data_series	Input time series
daylight_saving	1 (true) or 0 (false)
output_interpolation	Extend or linear

Example

Use input_a on weekends, input_b on weekdays, local clock time:

SET $points = TSPOINTS("d", 1, "", "", 1);

SET $datetime = TSDATETIME($points, 1, "extend");

SET $dayofweek = DAYOFWEEK($datetime);

SET $a = TSDATA(input_a, "");

SET $b = TSDATA(input_b, "");

IIF($dayofweek=6 OR $dayofweek=7, $a, $b);

TSLAG (input_series, lag_seconds)

Constructs a series containing the same data points as the input series, but with time stamps shifted forward (lagged) by the specified number of seconds.

Arguments

Description

input_series

A time series generated by TSPOINTS

lag seconds

The number of seconds by which the timesteps are to lag.

A negative lag will shift the time stamps backwards.

The resampling buffer for the TVD connector (in minutes) must be set to be at least as large as the lag time (in seconds) so that data from before the start of the simulation can be shifted into the simulation period. If this is not the case, the first lag_seconds of the simulation period will contain null data.

Example

TSLAG(TSDATA(input_a, ""), 900) Creates a 15 minute lag for input A.

TIDE("constituent name", amplitude, phase lag, timestep)

Constructs a time series for a specified tidal constituent, amplitude and phase lag, using linear interpolation.

Arguments	Description
constituent name	The unit code for a tidal constituent. 28 constituents, derived from the Admiralty Tide Tables (time zone referenced by Greenwich Mean Time (GMT)), are supported and are described in the Tidal constituents table. The unit code for the constituent must be enclosed in quotation marks, i.e. "".
amplitude	The mean amplitude, in units, such as meters
phase lag	The phase lag for each tidal constituent, in degrees
timestep	The timestep to be used, in minutes. A timestep must not be less than 0.1 minutes.

Example

TIDE("S2", 12.35, 26.7, 5.0) This is an example of a basic expression for calculating tidal harmonic boundaries.

TIDE("M2", 0.42, 8, 5) + TIDE("S2", 0.15, 58, 5) + TIDE(“K1”, 0.24, 155, 5) + 0.55 Evaluating this expression combines multiple constituents and includes the mean sea level (0.55) which results in a time series with terms, for each time step, corresponding to the sum of the separate values.

TIDE("M8", 0.006, 323.035, 3)+TIDE("Q1", 0.006, 201.703, 2) Evaluating this expression results in a time series with times corresponding to a combination of the different time steps and tide values; in general, a combination of calculated and interpolated values.

Tidal constituents

Semi-diurnal constituents

InfoWorks ICM Unit Code	Name	Description	Hourly Speed (°)
M2	M₂	Principal lunar constituent	28.98
S2	S₂	Principal solar constituent	30.00
N2	N₂	Allow for the changes in the Moon's distance due to its elliptic orbit round the Earth	28.44
L2	L₂		29.53
K2	K₂	Allow for the effect of the declination of the Sun and Moon and of changes in the Sun's distance	30.08
T2	T₂		29.96

Diurnal constituents

InfoWorks ICM Unit Code	Name	Description	Hourly Speed (°)
K1	K₁	Allow for the effect of the Moon's declination	15.04
O1	O₁	Allow for the effect of the Moon's declination	13.94
	K₁	Allow for the effect of the Sun's declination
P1	P₁	Allow for the effect of the Sun's declination	14.96
Q1	Q₁	Allow for the effect of changes in the Moon's distance on K1 and O1	13.40
M1	M₁		14.49
J1	J₁		15.59

Quarter-diurnal constituents

InfoWorks ICM Unit Code	Name	Description	Hourly Speed (°)
M4	M₄	First shallow water harmonic of M2 with a speed twice that of M2	57.98
MS4	MS₄	Shallow water constituent produced by the interaction of M2 and S2, with speed equal to sum of speeds of M2 and S2	58.98

Other constituents

InfoWorks ICM Unit Code	Name	Hourly Speed (°)
SA	Sa	0.041
SSA	SSa	0.082
MM	Mm	0.544
MSF	MSf	1.015
MF	Mf	1.098
PI1	π₁	14.92
2N2	2N₂	27.90
MU2	µ₂	27.97
NU2	υ₂	28.51
M3	M₃	43.48
M6	M₆	86.95
2MS6	2MS₆	87.97
2SM6	2SM₆	88.98
M8	M₈	115.94

TVD Connector - SQL Expression Dialog

Supported SQL syntax for TVD expressions

Specialised functions for use with time series data values

TSDATA(source, units)

TSPOINTS(interval_unit, interval_multiplier, date_origin, time_of_day_origin, daylight_saving)

TSRESAMPLE(value_series, time_stamp_series, statistic, window_option, left_secs, right_secs, statistic_factor, output_interpolation)

TSAPPLYPROFILE(input_series, normalize, divide_by_duration, multiplier, output_interpolation, number_list)

TSBEFORE(date_time)

TSDATETIME(data_series, daylight_saving, output_interpolation)

TSLAG (input_series, lag_seconds)

TIDE("constituent name", amplitude, phase lag, timestep)

Tidal constituents

Semi-diurnal constituents

Diurnal constituents

Quarter-diurnal constituents

Other constituents

Was this information helpful?