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:
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:
|
| units | Must be specified as a string such as "mm" or "ft", and are used only if the input field is a TSDB 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. "". |
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:
|
| 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). |
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:
|
| 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 |
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 |
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 |
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 |
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. |
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. |
Tidal constituents
Semi-diurnal constituents
|
InfoWorks ICM Unit Code |
Name |
Description |
Hourly Speed (°) |
|---|---|---|---|
|
M2 |
M2 |
Principal lunar constituent |
28.98 |
|
S2 |
S2 |
Principal solar constituent |
30.00 |
|
N2 |
N2 |
Allow for the changes in the Moon's distance due to its elliptic orbit round the Earth |
28.44 |
|
L2 |
L2 |
29.53 |
|
|
K2 |
K2 |
Allow for the effect of the declination of the Sun and Moon and of changes in the Sun's distance |
30.08 |
|
T2 |
T2 |
29.96 |
Diurnal constituents
| InfoWorks ICM Unit Code |
Name |
Description |
Hourly Speed (°) |
|---|---|---|---|
|
K1 |
K1 |
Allow for the effect of the Moon's declination |
15.04 |
|
O1 |
O1 |
13.94 |
|
|
|
K1 |
Allow for the effect of the Sun's declination |
|
|
P1 |
P1 |
14.96 |
|
|
Q1 |
Q1 |
Allow for the effect of changes in the Moon's distance on K1 and O1 |
13.40 |
|
M1 |
M1 |
14.49 |
|
|
J1 |
J1 |
15.59 |
Quarter-diurnal constituents
| InfoWorks ICM Unit Code |
Name |
Description |
Hourly Speed (°) |
|---|---|---|---|
|
M4 |
M4 |
First shallow water harmonic of M2 with a speed twice that of M2 |
57.98 |
|
MS4 |
MS4 |
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 |
Description |
Hourly Speed (°) |
|---|---|---|---|
|
SA |
Sa |
|
0.041 |
|
SSA |
SSa |
|
0.082 |
|
MM |
Mm |
|
0.544 |
|
MSF |
MSf |
|
1.015 |
|
MF |
Mf |
|
1.098 |
|
PI1 |
p1 |
|
14.92 |
|
2N2 |
2N2 |
|
27.90 |
|
MU2 |
µ2 |
|
27.97 |
|
NU2 |
u2 |
|
28.51 |
|
M3 |
M3 |
|
43.48 |
|
M6 |
M6 |
|
86.95 |
|
2MS6 |
2MS6 |
|
87.97 |
|
2SM6 |
2SM6 |
|
88.98 |
|
M8 |
M8 |
|
115.94 |
