As well as using single properties in Schema and Filter objects, several properties can be combined into a mathematical expression. SIS will calculate the value for the expression in the same way as many computer programming languages, such as Visual Basic.
SIS allows you to cascade expressions where multiple operators are used in a single expression. A string expression for example can be used in combination with a comparison operator like Iif.
iif(Left(test$,3)="abc","String begins with ABC","String does not begin with ABC")
If the test$ property is “ABCD” the true statement will be returned from the expression.
If the test$ string property is “BCDA” the false statement will be returned from the expression.
The expressions can use mathematical operators, functions, and spatial searches.
Note: A number of functions are available with or without a W Suffix. In practical use on words and numbers in languages such as English there will be no difference between text functions with or without the W Suffix, other than performance, where the W version is faster. See explanation below.
If using double byte characters such as Kanji in places such as Japan, the difference will be seen. In these instances it is appropriate to use the expression without the W suffix.
Text functions with the W suffix use UTF-16 encoded strings (http://en.wikipedia.org/wiki/Utf-16) and all offsets/indices as parameters/return values are interpreted as word-indices of UTF-16 code words. (1 word = 2 bytes.) Text functions without the W suffix use ANSI encoded strings (so they require string conversions UTF-16 to ANSI and perhaps ANSI to UTF-16 too and are therefore slower than their W counterparts) and any indices that are byte-indices into the ANSI multi-byte representation of the strings.
There are a number properties in SIS that can be used to format attributes.
These properties are:
|
Title
|
This property changes a string to title case, i.e. makes the first letter of each word uppercase. Example: Title(Street$) |
|
Upper
|
Converts a string to uppercase characters. Example: Upper(postcode$) |
|
Lower
|
Converts a string to lowercase characters. Example: Lower(postcode$) |
|
Local
|
Gets the local representation of a number as a string, i.e. adds a separator for each 1000 Examples: Local(Value#) or to add a £ before the value “£”+Local(Value#) |
|
Sentence
|
Changes a string to sentence case. Example: Sentence(Details$) |
|
Parse Date
|
Formats any Microsoft date format into one format. Example: ParseDate(Date$) |
The first table below shows the original format of the attributes.
The second table below shows the effects of adding the following formatting properties:
| Title prefixed to Street$ / Town$ | Expression field becomes Title(Street$) / Title(Town$) |
| Upper prefixed to postcode$ | Expression field becomes Upper(postcode$) |
| “£”+Local prefixed to Value# | Expression field becomes “£”+Local(Value#) |
| Sentence prefixed to Details$ | Expression field becomes Sentence(Details$) |
| ParseDate prefixed to Date$ | Expression field becomes ParseDate(Date$) |
Note: Expressions passed to other expressions must be enclosed in curly braces.
For example the expression income&/1000 when passed to CalcItems would be:
CalcItems("Addresses",{income&/1000},OP_Sum,ST_Within,ST_Geometry)
A further example of this nesting of expressions is shown in the CalcItems description in Spatial section of this topic.
Area - Gets the area covered by the items on an overlay, in square metres.
Expression Syntax: _area#
Example:
_area#
returns the area covered by the items on an overlay, in square metres.
CentroidX - The X coordinate of the Polygon's centroid, in metres from the dataset origin.
Expression Syntax: _centroidx#
Example:
_centroidx#
returns the X coordinate of the Polygon's centroid, in metres from the dataset origin.
CentroidY - The Y coordinate of the Polygon's centroid, in metres from the dataset origin.
Expression Syntax: _centroidy#
Example:
_centroidy#
returns the Y coordinate of the Polygon's centroid, in metres from the dataset origin.
CentroidZ - The Z coordinate of the Polygon's centroid, in metres from the dataset origin.
Expression Syntax: _centroidz#
Example:
_centroidz#
returns the Z coordinate of the Polygon's centroid, in metres from the dataset origin.
Clockwise - Is the item geometry defined in a clockwise direction?
Expression Syntax: _bClockwise&
Example:
_bClockwise&
returns True for clockwise, False for anti-clockwise.
Closed - Does the geometry of the item form a closed loop?
Expression Syntax: _closed&
Example:
_closed&
returns True for closed loop, False for not closed loop.
End X - The X coordinate of the Line's end point, in metres from the dataset origin.
Expression Syntax: _endx#
Example:
_endx#
returns the X coordinate of the Line's end point, in metres from the dataset origin.
End YExpression Syntax: _endy#
Example:
_endy#
returns the Y coordinate of the Line's end point, in metres from the dataset origin.
Geometry.Origin - Returns an origin from the Geometry of
an item
Expression Syntax: Geometry.Origin
Example:
CalcItems("buffer",{0},OP_Count,ST_Within,ST_Geometry,Geometry.Origin)
This example uses Geometry.Origin in the last argument of a CalcItems expression.
End Z - The Z coordinate of the Line's end point, in metres from the dataset origin.
Expression Syntax: _endz#
Example:
_endz#
returns the Z coordinate of the Line's end point, in metres from the dataset origin.
Length - The length of the geometry, in metres.
Expression Syntax: _length#
Example:
_length#
returns the length of the geometry, in metres.
Mean X - The mean of the item's X coordinates, in metres from the dataset origin.
Expression Syntax: _meanx#
Example:
_meanx#
returns the mean of the item's X coordinates, in metres from the dataset origin.
Mean Y - The mean of the item's Y coordinates, in metres from the dataset origin.
Expression Syntax: _meany#
Example:
_meany#
returns the mean of the item's Y coordinates, in metres from the dataset origin.
Mean Z - The mean of the item's Z coordinates, in metres from the dataset origin.
Expression Syntax: _meanz#
Example:
_meanz#
returns the mean of the item's Z coordinates, in metres from the dataset origin.
Midpoint X - The X coordinate of the Line's midpoint, in metres from the dataset origin.
Expression Syntax: _midpointx#
Example:
_midpointx#
returns the X coordinate of the Line's midpoint, in metres from the dataset origin.
Midpoint Y - The Y coordinate of the Line's midpoint, in metres from the dataset origin.
Expression Syntax: _midpointy#
Example:
_midpointy#
returns the Y coordinate of the Line's midpoint, in metres from the dataset origin.
Midpoint Z - The Z coordinate of the Line's midpoint, in metres from the dataset origin.
Expression Syntax: _midpointz#
Example:
_midpointz#
returns the Z coordinate of the Line's midpoint, in metres from the dataset origin.
Number of loop edges - The number of edges in a geometrical shape.
Expression Syntax: _numedges&
Example:
_numedges&
returns the number of edges in a geometrical shape.
Number of loops - The number of loops used by a Polygon item. Many polygon items have only one loop. However, any holes or islands will be stored as extra loops.
Expression Syntax: _numloops&
Example:
_numloops&
returns the number of loops used by the Polygon item. For example a Polygon with two holes created by the Analysis tab > Boolean Operations > Symmetric Difference command would return 3.
Number of vertices - The number of vertices in the item's geometry.
Expression Syntax: _np&
Example:
_np&
returns the number of vertices in the item's geometry.
Origin Latitude - The latitude of the item's origin.
The latitude is specified in degrees, in the geodetic datum of the dataset CRS.
Expression Syntax: _oLat#
Example:
_oLat#
returns the latitude of the item's origin, in degrees, in the geodetic datum of the dataset CRS.
Origin Longitude - The longitude of the item's origin.
The longitude is specified in degrees, in the geodetic datum of the dataset CRS.
Expression Syntax: _oLon#
Example:
_oLon#
returns the longitude of the item's origin, in degrees, in the geodetic datum of the dataset CRS.
Origin X - The X coordinate of the item's origin, in metres from the dataset origin.
Expression Syntax: _ox#
Example:
_ox#
returns the X coordinate of the item's origin, in metres from the dataset origin.
Origin Y - The Y coordinate of the item's origin, in metres from the dataset origin.
Expression Syntax: _oy#
Example:
_oy#
returns the Y coordinate of the item's origin, in metres from the dataset origin.
Origin Z - The Z coordinate of the item's origin, in metres from the dataset origin.
Expression Syntax: _oz#
Example:
_oz#
returns the Z coordinate of the item's origin, in metres from the dataset origin.
Size in X - The size of the item in the X direction, in metres.
Expression Syntax: _sx#
Example:
_sx#
returns the size of the Item in the X direction, in metres.
Size in Y - The size of the item in the Y direction, in metres.
Expression Syntax: _sy#
Example:
_sy#
returns the size of the Item in the Y direction, in metres.
Size in Z - The size of the item in the Z direction, in metres.
Expression Syntax: _sz#
Example:
_sz#
returns the size of the item in the Z direction, in metres.
Splinter factor - The splinter factor.
Splinter factor is calculated as a ratio of the length and the area such that a circle has a splinter factor of 1.
Long thin Polygons have large splinter factors.
Expression Syntax: _splinter#
Example:
_splinter#
returns the splinter factor for Polygon items.
Start X - The X coordinate of the Line's start point, in metres from the dataset origin.
Expression Syntax: _startx#
Example:
_startx#
returns the X coordinate of the Line's start point, in metres from the dataset origin.
Start Y - The Y coordinate of the Line's start point, in metres from the dataset origin.
Expression Syntax: _starty#
Example:
_starty#
returns the Y coordinate of the Line's start point, in metres from the dataset origin.
Start Z - The Z coordinate of the Line's start point, in metres from the dataset origin.
Expression Syntax: _startz#
Example:
_startz#
returns the Z coordinate of the Line's start point, in metres from the dataset origin.
Straight - Is the geometry completely made up of straight LineString item segments?
Expression Syntax: _straight&
Example:
_straight&
returns True if a geometry is completely made up of straight LineString item segments, returns False if a geometry is not completely made up of straight LineString item segments.
Thin in X - Does all geometry in the item have the same X coordinate?Expression Syntax: _thinX&
Example:
_thinX&
returns True if all geometry in the item, have the same X coordinate, returns False if all geometry in the item do not have the same X coordinate.
Thin in Y - Does all geometry in the item have the same Y coordinate?
Expression Syntax: _thinY&
Example:
_thinY&
returns True if all geometry in the item, have the same Y coordinate, returns False if all geometry in the item do not have the same Y coordinate.
Thin in Z - Does all geometry in the item, have the same Z coordinate?
Expression Syntax: _thinZ&
Example:
_thinZ&
returns True if all geometry in the item, have the same Z coordinate, returns False if all geometry in the item do not have the same Z coordinate.
WGS84 Origin Latitude - The latitude of the item's origin, in degrees, in the WGS84 geodetic datum.
Expression Syntax: _oLatWGS84#
Example:
_oLatWGS84#
returns the latitude of the item's origin, in degrees, in the WGS84 geodetic datum.
WGS84 Origin Longitude - The longitude of the item's origin, in degrees, in the WGS84 geodetic datum.
Expression Syntax: _oLonWGS84#
Example:
_oLonWGS84#
returns the longitude of the item's origin, in degrees, in the WGS84 geodetic datum.
Brush - The Brush used to cover any Polygon covered by the Item.
Expression Syntax: _brush$
Example:
_brush$
returns the brush used to cover any Polygon covered by the Item.
The returned value can be the name of a selected brush or the JSON of a user-defined brush. The brush can be changed by editing the value in the table column or by selecting a brush from the drop-down arrow at the end of the table row.
Feature code - The Feature Code for the item.
If an item has a feature code then its Pen, Brush, Symbol, etc. are taken from a Feature Table.
Expression Syntax: _FC&
Example:
_FC&
returns the feature code for the item. For example if OS (GB) Meridian Feature Table, 6001 Motorway Feature Code is used then 6001 will be returned.
Feature table - The Feature Table which an Item uses to get information about a feature code.
Expression Syntax: _featureTable$
Example:
_featureTable$
returns the feature table an items uses. For example if OS (GB) Meridian Feature Table, 6001 Motorway Feature Code is used then OS (GB) Meridian will be returned.
Layer - The layer attribute of an Item.
The concept of layers is used in many CAD systems, and in the Autodesk DXF and DWG formats.
Expression Syntax: _layer$
Example:
_layer$
returns the layer attribute of an Item.
Level - The level within an overlay that an Item is drawn on.
If a large number of Items are in one overlay, then you can control the order in which thay are drawn by setting their levels.
Expression Syntax: _level&
Example:
_level&
returns the level within an overlay that an Item is drawn on. Levels can be changed in Table View.
Maximum scale - The highest reproduction scale at which an Item will be drawn.If you set this value to 1000, and then zoom out to 1:2000 the Item will become invisible.
Expression Syntax: _scalemax#
Example:
_scalemax#
'_scalemax#' value is added to the schema column and can be changed in Table View. Entering 1000 in the table column in Table View will mean that the Item will be not be displayed above a scale of 1:999.
Minimum scale - The lowest reproduction scale at which an Item will be drawn.
If you set this value to 100, and then zoom out to 1:50 the Item will become invisible.
Expression Syntax: _scalemin#
Example:
_scalemin#
'_scalemin#' value is added to the schema column and can be changed in Table View. Entering 1000 in the table column in Table View will mean that the Item will be not be displayed below a scale of 1:999.
Pen - The Pen used to draw the item.
Expression Syntax: _pen$
Example:
'_pen$'
returns the pen used to draw the line.
The returned value can be the name of a selected pen or the JSON of a user-defined pen. The pen can be changed by editing the value in the table column or by selecting a pen from the drop-down arrow at the end of the row.
Symbol - The Symbol used to draw the Point item.
Expression Syntax: _shape$
Example:
_shape$
returns the name of the Symbol used to draw the Point item. The point item can be changed in Table View.
Bold - Is the text drawn bold?
Expression Syntax: _text_bold&
Example:
_text_bold&
returns True if bold, False if not bold. The bold status can be changed in Table View.
Box - Should a box be drawn around Box Text characters?Expression Syntax: _text_box&
Example:
_text_box&
returns True if Box Text, False if not Box Text. Can be changed in Table View.
Character Height - The height of Box Text characters, in metres.
Expression Syntax: _character_height#
Example:
_character_height#
returns the height of Box Text characters in metres. Box text character height can be changed in Table View.
Font - The True Type™ font used to draw the text.
Expression Syntax: _font$
Example:
_font$
returns the name of the font used or (By Overlay). Font can be changed in Table View.
Horizontal alignment - The horizontal alignment of text about its digitised position.
Expression Syntax: _text_alignH&
Example:
_text_alignH&
returns the horizontal alignment of text, this can be Left, Centre or Right. Horizontal alignment can be changed in Table View.
Italic - Is the text drawn itailc?
Expression Syntax: _text_italic&
Example:
_text_italic&
returns True if text is drawn italic, False if text is not drawn italic. Italic status can be changed in Table View.
Line spacing - The line spacing used for multi-line text.
Expression Syntax: _line_spacing#
Example:
_line_spacing#
returns the line spacing for multi-line text. Line spacing can be changed in Table View.
Markup - Should special characters be interpreted as markup, e.g. to make parts of the text bold or italic?
Expression Syntax: _text_markup&
Example:
_text_markup&
returns True if characters are interpreted as markup, False if characters are not interpreted as markup. Markup status can be changed in Table View.
Number of lines - The number of lines of text. Single-line text will report one line.
Expression Syntax: _lines&
Example:
_lines&
returns the number of lines in the text item.
Opaque - Should text be drawn with an opaque background?
Expression Syntax: _text_opaque&
Example:
_text_opaque&
returns True if opaque, False if not opaque.
Outline - Should text be drawn with an outline?
Expression Syntax: _text_outline&
Example:
_text_outline&
returns True if text is drawn with an outline, False if text is drawn not with an outline. Outline status can be changed in Table View.
Point height - The height of the text in "points".
The item's dataset scale is used to determine how big a "point" is in the real world. Negative point sizes fix the size of the text relative to the screen.
Expression Syntax: _point_height&
Example:
_point_height&
returns point height. Point height can be changed in Table View.
Rounded rectangle - Should the text box be drawn with round corners?
Expression Syntax: _text_roundrect&
Example:
_text_roundrect&
returns True if text box is drawn with rounded corners, False if text box is not drawn with rounded corners. Rounded rectangle status can be changed in Table View.
Strikethrough - Is the text drawn struck out, with a line through it?
Expression Syntax: _text_strikeout&
Example:
_text_strikeout&
returns True if text has a strikeout, False if text does not have astrikeout. Strikeout status can be changed in Table View.
Text - The textual content of the item.
Expression Syntax: _text$
Example:
_text$
returns the textual content. The textual content can be editied in Table View.
Text fill (brush) - The Brush used to fill the text letters.
Expression Syntax: _textFillBrush$
Example:
_textFillBrush$
returns either; the brush colour, (By Overlay), or the JSON if the brush is user-defined. Brush definitions can be changed in Table View.
Text outline (pen) - The Pen used to outline the text letters.
Expression Syntax: _textOutlinePen$
Example:
_textOutlinePen$
returns either; the pen colour, (By Overlay), or the JSON if the pen is user-defined. Pen definitions can be changed in Table View.
Underlined - Is the text drawn underlined?
Expression Syntax: _text_underlined&
Example:
_text_underlined&
returns True if characters are always drawn underlined, False if characters are not always drawn underlined. Underline status can be changed in Table View.
Upright - Are the Text letters always drawn upright, even if the Text or the view is rotated?
Expression Syntax: _text_upright&
Example:
_text_upright&
returns True if characters are always drawn upright, False if characters are not always drawn upright. Upright status can be changed in Table View.
Vertical alignment - The vertical alignment of the text about its digitised position.
Expression Syntax:_text_alignV&
Example:
_text_alignV&
returns the text vertical alignment; Bottom, Baseline, Middle or Top. Vertical alignment can be changed in Table View.
CvDate - Convert year/month/day and optional hours/minutes/seconds numbers to a date/time number.
The returned value can be compared with values read from database columns with type SQL_DATE, SQL_TIME or SQL_TIMESTAMP, or can be formatted as a string using FormatDate.
Expression Syntax: CvDate(year, month, day, hour, minutes, seconds)
year
The year, in the range 100 to 9999.
month
The month, in the range 0 to 12.
day
The day, in the range 0 to 31.
hour
The hour, in the range 0 to 23 (optional).
minutes
The minute, in the range 0 to 59 (optional).
seconds
The second, in the range 0 to 59 (optional).
Example:
CvDate(2001, 6, 29, 11, 27, 0)
returns 29/06/2001 11:27:00
Date - The date/time number of the current date/time, or of the given date/time as a property or as a literal date string.
Expression Syntax: Date(date)
date
A property or a literal string representing a date.
Example:
Date()
returns the current date and time.
Date(someDateAndTime@)
returns a date time value for a date property.
Date("2001-06-29T11:27:00")
returns 29/06/2001 11:27:00.
Day - Gets the day of the month (in the range 1 to 31) represented by a date/time value.
Expression Syntax: Day(date)
date
A date time value of the calendar month required.
Example:
Day(Date())
returns the current day of the month.
Day(someDateAndTime@)
returns the Month day of the date.
Day(Date("2001-06-29T11:27:00"))
returns 29.
DayOfWeek - Gets the day of the week in the range 1 to 7; 1=Sunday, 2=Monday, etc. represented by a date/time value.
Expression Syntax: DayOfWeek(date)
date
A date time value of the week day required
Example:
DayOfWeek(Date())
returns the current day of the week.
DayOfWeek(someDateAndTime@)
returns the week day of the date.
DayOfWeek(Date("2001-06-29T11:27:00"))
returns 6.
DayOfYear - Gets the day of the year in the range 1 to 366; 1=1st January, 32=1st February, etc.) from a date/time value.
Expression Syntax: DayOfYear(date)
date
A date time value of the annual day required
Example:
DayOfYear(Date())
returns the current day of the year.
DayOfYear(someDateAndTime@)
returns the annual day of the date.
DayOfYear(Date("2001-06-29T11:27:00"))
returns 180.
Hour - Gets the hour (in the range 0 to 23) represented by a date/time value.
Expression Syntax: Hour(property)
property
The name of the date/time property.
Example:
Hour(startdate@)
returns the hour in the date/time property entered in the property field, in this example startdate@.
Minute - Gets the minute value (in the range 0 to 59) represented by a date/time value.
Expression Syntax: Minute(property)
property
The name of the date/time property.
Example:
Minute(startdate@)
returns the minute in the date/time property entered in the property field, in this example startdate@.
Month - Gets the month (1=January, 2=February, etc.) represented by a date/time value.
Expression Syntax: Month(property)
property
The name of the date/time property.
Example:
Month(startdate@)
returns the month in the date/time property entered in the property field, in this example startdate@.
Now - Returns a date for the current date and time.
Expression Syntax: Now()
Example:
Now()
Returns today's date and time in the format day/month/year hour/minute/seconds
ParseDate - Converts a string into a date.
Expression Syntax: ParseDate(str$)
Example:
ParseDate(startdate$)
returns the month in the date/time property entered in the property field, in this example startdate$.
Second - Gets the second value (in the range 0 to 59) represented by a date/time value.
Expression Syntax: Second(property)
property
The name of the date/time property.
Example:
Second(startdate@)
returns the second in the date/time property entered in the property field, in this example startdate@.
Timespan - Calculates a timespan value.Timespan returns a decimal value of days from a given number of days, hours, minutes and seconds.
Expression Syntax: Timespan(days, hours, minutes, seconds)
days
The number of days.
hours
The number of hours.
minutes
The number of minutes.
seconds
The number of seconds.
Note: If days, hours, minutes or seconds values are not required enter 0 in the appropriate position in the parameter line.
Examples:
Timespan(13, 19, 30, 0)
will return 13.8125
Timespan(0, 18, 0, 0)
will return 0.75
Year - Gets the year (in the range 100 to 9999) represented by a date/time value.
Expression Syntax: Year(property)
Example:
Year(startdate@)
returns the year in the date/time property entered in the property field, in this example startdate@.
Exists - Does the given property exist on this item?
Expression Syntax: Exists(property)
Example:
Exists("_text_alignV&")
will only return True for Text, BoxText, Label and LineText items.
Returns:
True - The mnemonic exists on the current expression item.
False - The mnemonic does not exist on the current expression item.
Example of use of Exists():
Applying a filter to a new feature
When a new feature is drawn on an overlay its attributes are said to be null, that is they do not exist until a value is assigned to them.
When carrying out a search or applying a filter on an overlay, where a particular attribute is null, special measures need to be taken. Looking for an empty string, or a string of the length 0, or for an integer or float of 0 will not return any results. This is because the property is not empty but is non existent. To check whether a property exists the exists() function can be used within SIS expressions. Exists() will return a value of 0 (false) if a property does not exist and a value of -1(true) if an item does indeed exist.
Consider the senario of a buildings overlay with an attribute called "building_type$". There are four buildings (features) on the overlay, the information contained in "building_type$" is as follows:
ID Building_type
1 FLAT
2 HOUSE
3
4
Example Filter 1
To create a filter that displays all buildings that are houses the following expression can be used:
building_type$ ="HOUSE"
In this case feature 2 will be displayed, features 1, 3 and 4 will not be displayed.
Example Filter 2
To create a filter that displays all the buildings where the building type attribute has not been set (i.e. where the building_type is null) the following expression can be used:
Exists("building_type$") = 0.
In this case features 3 and 4 will be displayed, features 1 and 2 will not be displayed.
Example Filter 3
To create a filter that displays all the buildings where the building type attribute has been set to anything other than null (i.e. the building_type attribute contains a value) the following expression can be used:
Exists("building_type$") = -1
In this case features 1 and 2 will be displayed, features 3 and 4 will not be displayed.
Example Filter 4
To create a filter that displays all buildings that are not flats the following expression can be used:
building_type$ <>"FLAT"
In this case feature 2 will be displayed, features 1, 3 and 4 will not be displayed.
Example Filter 5
To create a filter that displays all buildings that are not flats and all features that have null for building_type$ the following expression can be used:
building_type$ <>"FLAT" or Exists("building_type$") = 0
In this case features 2, 3 and 4 will be displayed, feature 1 will not be displayed.
IsNotNull - Tests whether a mnemonic or expression is not Null.
Expression Syntax: "expression" Is Not Null
Example:
startdate@ Is Not Null
returns -1 if startdate@ exists, 0 if startdate@ does not exist.
IsNull - Tests whether a mnemonic or expression is Null.
Expression Syntax: "expression" Is Null
Example:
Value@ Is Null
returns 0 if startdate@ exists, -1 if startdate@ does not exist.
Abs - Gets the absolute value of a number, or a property representing a number.
Expression Syntax: Abs(number)
number
A number, or a property representing a number.
Examples:
Abs(-1.234)
returns 1.234
Abs(_ox#)
returns the absolute value of Origin X, for example 464358.68270295
Note: When using the Expression Builder numbers as strings that are cast to integer or absolute (decimal, float) values are not always treated as numeric. To ensure they are treated as numeric (especially when sorting) you should use the following casting methods:
Integers as strings - you can use Int(Val("123"))
Floats as strings - you can use Abs(Val("3.1415"))
Acos - Gets the angle, in radians, of a cosine value (the arcosine).
Expression Syntax: Acos(number)
number
A cosine value.
Examples:
Acos(0.785398)
returns 0.667457480002253
Acos(cosine#)
returns the angle in radians of the cosine# value
Asin - Gets the angle, in radians, of a sine value (the arcsine).
Expression Syntax: Asin(number)
number
A sine value.
Examples:
Asin(0.785398)
returns 0.903338846792644
Asin(sine#)
returns the angle in radians of the sine# value
Atan - Gets the angle, in radians, of a tangent value (the arctangent), in the range -n/2 to n/2 .
Expression Syntax: Atan(number)
number
A tangent value.
Examples:
Atan(0.785398)
returns 0.665773648969237
Atan(tan#)
returns the angle in radians of the tan# value
Atan2 - Gets the angle, in radians, of the tangent value y/x (the arctangent), in the range -n to n.
Expression Syntax: Atan2(y, x)
y
The Y component of the tangent value.
x
The X component of the tangent value.
Examples:
Atan2(1.234,1.234)
returns 0.785398163397448
Atan2(Y#,X#)
returns the angle in radians of the tangent value y/x in the range Y#,X#
Chr - Gets a character from an ASCII value.
Expression Syntax: Chr(ascii)
ascii
an ASCII value.
Example:
Chr(97)
will return the character equivalent of the ASCII value, in this example "a".
Cos - Gets the cosine of an angle specified in radians.
Expression Syntax: Cos(angle)
angle
a number of radians or a property in radians.
Examples:
Cos(30)
returns 0.154251449887584
Cos(_angle#)
returns the cosine of _angle#
Cot - Gets the cotangent of an angle specified in radians.
Expression Syntax: Cot(angle)
angle
a number of radians or a property in radians.
Examples:
Cot(30)
returns -0.156119952161657
Cot(_angle#)
returns the cotangent of _angle#
Degrees - Gets the degree value of an angle that is specified in degrees.
Expression Syntax: Degrees()
Examples:
Degrees(2.5)
returns 143.239448782706
Degrees(_angle#)
returns the degree value of _angle# specified in radians
Exp - Gets the natural exponent of a number.
Expression Syntax: Exp(number)
number
a floating point number
Example:
Exp(10)
returns 22026.4657948067
Exp(Magnitude#)
returns the natural exponent of the Magnitude# property.
Fix - Gets the integer portion of a number.
Expression Syntax: Fix(number)
number
a floating point number
Example:
Fix(123.45678)
returns 123
Note: The Fix expression function will fail if the source value is floating point (e.g. double) and outside of the [–2147483648,2147483647] range.
Int - Gets the integer portion of a number.
Expression Syntax: Int(number)
number
a floating point number
Example:
Int(12.345)
returns 12
Note: The Int expression function will fail if the source value is floating point (e.g. double) and outside of the [–2147483648,2147483647] range.
Note: When using the Expression Builder numbers as strings that are cast to integer or absolute (decimal, float) values are not always treated as numeric. To ensure they are treated as numeric (especially when sorting) you should use the following casting methods:
Integers as strings - you can use Int(Val("123"))
Floats as strings - you can use Abs(Val("3.1415"))
Log - Gets the natural logarithm of a number.
Expression Syntax: Log(number)
number
a floating point number
Example:
Log(12.345)
returns 2.51325112279714
Log10 - Gets the base-10 logarithm of a number.
Expression Syntax: Log10(number)
number
a floating point number
Example:
Log10(12.345)
returns 1.09149109426795
Max - Gets the larger of two numbersExpression Syntax: Max(number1, number2)
number1, number2
floating point numbers or integers
Example:
Max(5678,1234)
returns the larger of the two numbers, in this example 5678 will be returned.
Min - Gets the smaller of two numbers.
Expression Syntax: Min(number1, number2)
number1, number2
floating point numbers or integers
Example:
Min(5678,1234)
returns the smaller of the two numbers, in this example 1234 will be returned.
Pi - Gets the value of Pi.
Expression Syntax: Pi()
Examples:
Pi()*2
returns the value of Pi multiplied by 2, i.e. 6.283185307177959.
Pi()^2
returns the value of Pi squared, i.e. 9.86960440108936.
Radians - Gets the radian value of an angle that is specified in degrees.
Expression Syntax: Radians()
Example:
Radians(180)
returns the radian value of 180 degress, i.e. 3.14159265358979.
Radians(_angle#)
returns the radian value of _angle# specified in degrees
Round - Rounds a fractional value to an integer, or a given number of decimal places.
Expression Syntax: Round(number, places)
number
The floating point number to round.
places
Optional number of decimal places. Use 0 for an integer, 1 for one decimal place, -1 for nearest multiple of 10, -2 for nearest multiple of 100 etc.
Examples:
Round(12.3456789,3) - will return 12.345
Round(12.56789,0) - will return 13
Round(1256.567,-1) - will return 1260
Round(1256.567,-2) - will return 1300
Note: The Round expression function will fail if the source value is floating point (e.g. double) and outside of the [–2147483648,2147483647] range.
Sign - Gets the sign of the number entered.
Expression Syntax: Sign()
Examples:
Sign(12)
returns 1
Sign(-12)
returns -1
Sign(_angle#)
returns 1 or -1 depending on the sign of _angle#.
Sin - Gets the sine value of an angle specified in radians.
Expression Syntax: Sin(angle)
angle
An angle, in radians.
Examples:
Sin(1.5708)
returns 0.850903524534118
Sin(_angle#)
returns the sine of _angle#
Sqrt - Gets the square root of a number.
Expression Syntax: Sqrt(number)
number
floating point number or integer
Examples:
Sqrt(169)
returns 13
Sqrt(_area#)
returns the square root of _area#
Tan - Gets the tangent value of an angle specified in radians.
Expression Syntax: Tan(angle)
angle
An angle, in radians.
Examples:
Tan(45)
returns 1.61977519054386
Tan(_angle#)'
returns the tangent of angle
Choose - Selects and returns a value from a list of arguments. This expression can also work with strings.
Expression Syntax: Choose(index, choice-|0, , , , )
index
Numeric expression which evaluates to a number between 1 and the number of available choices.
a, b, c, d,... [choice-1], [choice-2], [...], [choice-n]
The list of choice values [choice-1], [choice-2], [...], [choice-n]. The values may be strings or numbers, but must all be of the same type.
Returns:
The value at the given index. If index evaluates to 1 then the value "a" is returned.
Note: All of the choice values, "a", "b", etc., will be evaluated, even though only one of them will be returned. Therefore, all of them must be valid expressions.
Example:
Choose(Status&,"Pending","Approved","Closed")
will return "Pending" if the value of the user attribute "Status&" on the current expression item equals 1, "Approved" if "Status&" equals 2, etc.
Iif - Returns one of two parts, depending on the evaluation of an expression.
Expression Syntax: Iif(expression, truepart, falsepart)
expression
An expression which evaluates to True or False, for example _length#>1000.
truepart
A regular expression which is returned when the Boolean test expression is True.
falsepart
A regular expression which is returned when the Boolean test expression is False.
If the expression is True the value truepart is returned.
If the expression is False the value falsepart is returned.
Example:
Iif(_length#>1000,"This Line is long", "This Line is short")
returns the string This Line is long if the _length# geometry system property exceeds 1000 dataset units. Otherwise the string This Line is short will be returned.
Note: Both truepart and falsepart will be evaluated, even though only one of them will be returned. Therefore, both of them must be valid expressions. In the case of a non-Boolean expression, the Switch function must be used.
Switch - Selects and returns a value from a list of possible values.
Expression Syntax: Switch (expression,key1,value1,key2,value2 ,…,no matches found)
If expression is equal to key1 then the result is value1
If expression is equal to key2 then the result is value2 .... and so on with more key/value pairs.
If there are no matches the result is no matches found.
Examples:
Switch(expression&, 25,100, 50, 200, 75, 300, 1000)
If the content of expression& is 50 then 200 will be returned.
If the content of expression& is not 25, 50 or 75 then the number 1000 will be returned.
Switch(Purchase$, key1$,value1$,key2$,value2$,key3$,value3$,default$)
If the content of Purchase$ contains the same text as key1$ then the content of value1$ will be returned, if the content of Purchase$ is the same text as key2$ then the content of value2$ will be returned, etc.
If the content of Purchase$ is not the same as any of the text contained in key1$, key2$, etc. then the content of default$ will be returned.
dts - Gets a property from an item's dataset.
Expression Syntax: dts.property
Example:
dts._scale#
accesses the properties/attributes of the dataset of the current item. In this example the dataset scale will be returned.
Geometry - Gets a property from the Geometry of an item.
Expression Syntax: Geometry.Length
Example:
Geometry.length
returns the length of the item
Join - Gets a property from a column in a joined Table.Allows access to properties of the current items that come from a joined database table.
Expression Syntax: Join()
Example:
Join.ADDRESS or Join.[FIRST NAME]
JoinItem - Allows access to the properties/attributes of an item from another overlay.
Expression Syntax: JoinItem
Example:
JoinItem(Overlay(“Overlay 1”,{ST_Intersects(geometry,this.geometry)}))._area#
returns the area of an item from Overlay 1 that intersects with the current item. If there is more than one item found, an arbitrary item is chosen.
swd - Gets a property from an SWD.
Expression Syntax: swd.property
Example:
swd._nOverlay&
returns the number of overlays in the SWD
this - Gets a property from an item.
Expression Syntax: this.property
Example:
this._area#
returns the area of each of the items on the overlay.
Note regarding the use of the "this" operator:
Normally everything inside braces {...} is executed on item(s) in another overlay. However, if you use this, then you can 'point back' to the original, current overlay.
Taking as an example:
'Overlay("Roads",{ST_Intersects(this.geometry,geometry)'.
'this.geometry' evaluates to the geometry of the current item in the current overlay, and 'geometry' evaluates to the geometry of items in the overlay 'Roads'.
'this.geometry' is evaluated first, and only once. The effect is then as if the expression 'ST_Intersects(ST_GeomFromText("POLYGON((...))"), geometry)' would be executed on the overlay 'Roads'.
CalcInterior - Peform a mathematical operation on an expression, or a numeric property, of the items on another overlay whose hook points are inside the current item.
Expression Syntax: CalcInterior(Overlay(name, {filter}), expressionorproperty, operation)
Overlay
The overlay on which to search for contained items, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
expressionorproperty
The expression to evaluate on the contained items (optional, with Property).
operation
The mathematical operation to perform on the results.
0 = Count - the number of results
1 = Sum - the sum of the results
2 = Average - the average of the results
Example:
CalcInterior(Overlay("All Crime",{Crimetype$="Burglary"}), "", 0)
Note: The final parameter "0" in the example means Count how many items from the specified overlay are contained in each polygon. "1" is Sum, "3" is Average. The last two parameters will only work if you specify the third parameter (property) and if the property type is a number (integer, double).
CalcItems - Peforms a mathematical operation on a numeric property, of the items on another overlay that satisfy a spatial relation with the geometry of the current item, or with a given geometry.
Expression Syntax: CalcItems(Overlay(name, {filter}), expressionorproperty, operation, spatialtest, geometrytype, geometry)
Overlay
The overlay on which to search for items using the given spatial relation, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
expressionorproperty
The expression to evaluate on the items (optional, with Property).
operation
The mathematical operation to perform on the results:
OP_Count - The number of results.
OP_Sum - The sum of the results.
OP_Min - The minimum result value.
OP_Mean - The average of the results.
OP_Median - The middle number in the list of results.
OP_Mode - The number which appears most often in the results.
OP_Max - The maximum result value.
OP_Stddev - (Standard Deviation) a measure of the spread of the results.
OP_Variance - The average of the squared differences from the Mean value.
OP_Skewness - Reduces any positive or negative skewing of the results.
spatialtest
The spatial test to apply in the search:
ST_Contain - Does one geometry intersect another geometry (or the geometry of an item)?
ST_Cross - Does one geometry cross another geometry (or the geometry of an item)?
ST_Disjoint - Are one geometry and another geometry (or the geometry of an item) disjoint?
ST_Equal - Is one geometry equal to another geometry (or the geometry of an item)?
ST_Intersect - Does one geometry intersect another geometry (or the geometry of an item)?
ST_Overlap - Does one geometry overlap another geometry (or the geometry of an item)?
ST_Touch - Does one geometry touch another geometry (or the geometry of an item)?
ST_Within - Is one geometry within another geometry (or the geometry of an item)?
The above are constants. However, there are functions with the same or similar names, for some of these the names are pluralised but in some instances they are the same as the constant name, see below:
Constant Function ST_Contain ST_Contains ST_Cross ST_Crosses ST_Disjoint ST_Disjoint ST_Equal ST_Equals ST_Intersect ST_Intersects ST_Overlap ST_Overlaps ST_Touch ST_Touches ST_Within ST_Within
geometrytype
The geometry type to use in the spatial test, for example, just use the extents instead of the geometry
ST_Geometry - The geometry of the candidate item must pass the testing method with the test item.
ST_Envelope - Does one geometry form an envelope Polygon around the extents of an Item.
ST_Origin - The origin of the candidate item (always a single point) must pass the testing method with the test item.
geometry
The geometry to use in the spatial test, instead of the current item's geometry.
Example:
Taking the following SWD as an example:
The Plots overlay Max_no_of_floors column expression is:
CalcItems("Domestic Buildings”,{CalcItems("Domestic Buildings","", OP_Count, ST_Equal, ST_Geometry)}, OP_Max, ST_Contain,ST_Geometry)
In the above expression the inner CalcItems expression (shown in blue in curly braces) calculates the number of floors in each building. The result from this inner calculation is then used in the main CalcItems expression of the Plots overlay to derive the maximum number of floors in each plot (Max_no_of_floors schema column).
CalcRaster - Peforms a mathematical operation on the grid values of the raster items on another overlay that satisfy a spatial relation with the current item.Expression Syntax: CalcRaster(Overlay(name, {filter}), operation, rasterband)
Overlay
The overlay on which to search for raster items, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
operation
The mathematical operation to perform on the results:
OP_Count - The number of results.
OP_Sum - The sum of the results.
OP_Min - The minimum result value.
OP_Mean - The average of the results.
OP_Median - The middle number in the list of results.
OP_Mode - The number which appears most often in the results.
OP_Max - The maximum result value.
OP_Stddev - (Standard Deviation) a measure of the spread of the results.
OP_Variance - The average of the squared differences from the Mean value.
OP_Skewness - Reduces any positive or negative skewing of the results.
rasterband
The zero-based index of the raster band to use (optional, default value is 0).
Example:
CalcRaster("Emissions",{Type$="CO2"},OP_Mean)
Extents - Gets the extents of an item as a comma-separated string in the form X1, Y1, Z1, X2, Y2, Z2.
Expression Syntax: Extents()
Example:
Extents()
returns strings in the form:
387229.514999527,558542.869785585,0,393270.734070659,563812.869400827,0
384144.637175971,551087.748378656,0,390057.319671121,556229.211417917,0
FindContainer - Find an item on another overlay that contains the current item.
Find an item on another overlay which "contains" the current expression item. The "." (dot) operator is used to get a property from a single item found using Spatial Queries. Where more than one item passes the spatial test, only a single item will be found. Care should therefore be taken when using this expression.
FindContainer will find any item: a Polygon contains the current expression item if the current expression item origin is on the Polygon boundary or in the Polygon interior; a LineString contains the current expression item if current expression item origin is on the LineString; a Point contains the current expression item if the current expression item origin is co-incident with the Point.
Expression Syntax: FindContainer(Overlay(name, {filter}))
Overlay
The overlay on which to search for a containing item, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
Returns
The value of a property on the found item, if any, using the dot operator.
Example:
FindContainer("Polygon Overlay")._area#
returns the area of the containing item on the overlay "Polygon Overlay".
FindInterior - Finds an item on another overlay whose hook point is inside the current item.
Finds an item on another overlay whose origin is "contained" by the current expression item. The "." (dot) operator is used to get a property from a single item found using Spatial Queries. Where more than one item passes the spatial test, only a single item will be found. Care should therefore be taken when using this expression.
The current expression item can be any kind of item: a Polygon contains the origin if the origin is on its boundary or in its interior; a LineString contains the origin if the origin is on the line; a Point contains the origin if the origin is co-incident with the Point.
Expression Syntax: FindInterior(Overlay(name, {filter}))
Overlay
The overlay on which to search for a containing item, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
Returns
The value of a property on the found item, if any, using the dot operator.
Example:
FindInterior("Place Names")._text$
Returns the text of the contained item on the overlay "Place Names".
FindItem - Finds an item on another overlay that satisfies a spatial test with the current item.
The current expression item can be any kind of item. The found item will depend upon the chosen geometrytest and geometrymode.
Where more than one item passes the spatial test, only a single item will be found. Care should therefore be taken when using this expression.
Expression Syntax: FindItem(Overlay(name, {filter}), geometrytest, geometrymode)
Overlay
The overlay to be searched.
filter
An optional filter to apply to items on the overlay being searched.
geometrytest
The spatial test to apply:
0 Equal The two items' geometry must be the same. 1 Disjoint The items must be completely separate, with daylight between them. 2 Intersect The items must not be disjoint, so they must have a point in common. This is the fastest, and most common test. 3 Touch The items' interiors must be disjoint, and their boundaries must intersect. 4 Cross If the items are LineString items, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the test item going outside the candidate item. 5 Crossed By If the items are LineString items, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the candidate item going outside the test item. 6 Within The interior of the test item must be strictly inside the candidate item. 7 Contain The interior of the candidate item must be strictly inside the test item. 8 Overlap If the two items are LineString items, then they must be tangential, and neither should contain the other. Otherwise, their interiors must intersect, with neither containing the other.
geometrymode
The testing mode to apply:
0 Origin The origin of the candidate item (always a single point) must pass the testing method with the test item 1 Extent The extents of the candidate item (always a rectangle) must pass the testing method with the test item. 2 Geometry The geometry of the candidate item must pass the testing method with the test item.
Example:
FindItem("Capitals",7,0).Name$
The dot operator is used to get the properties of the found item.
FindItemEx - Finds an item on another overlay that satisfies a spatial test with the current expression item.
The current expression item can be any kind of item. The found item will depend upon the chosen geometrytest and geometrymode.
Where more than one item passes the spatial test, only a single item will be found. Care should therefore be taken when using this expression.
Expression Syntax: FindItemEx(Overlay(name, {filter), geometrytest)
Overlay
The overlay to be searched.
filter
An optional filter to apply to items on the overlay being searched.
geometrytest
The spatial test to apply:
0 Equal The two items' geometry must be the same. 1 Disjoint The items must be completely separate, with daylight between them. 2 Intersect The items must not be disjoint, so they must have a point in common. This is the fastest, and most common test. 3 Touch The items' interiors must be disjoint, and their boundaries must intersect. 4 Cross If the items are LineString items, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the test item going outside the candidate item. 5 Crossed By If the items are LineString items, then they must intersect without being tangential. Otherwise, their interiors must intersect, with the candidate item going outside the test item. 6 Within The interior of the test item must be strictly inside the candidate item. 7 Contain The interior of the candidate item must be strictly inside the test item. 8 Overlap If the two items are LineString items, then they must be tangential, and neither should contain the other. Otherwise, their interiors must intersect, with neither containing the other.
Examples:
FindItemEx("Capitals", "Contain").Name$
FindItemEx("Rivers", "Intersect")._length#
The dot operator is used to get the properties of the found item.
GeoJSON - Gets the GeoJSON representation of the geometry of an item.
Expression Syntax: GeoJSON()
Example:
GeoJSON()
Examples of GeoJSON returns:
Polygon Item:
{"type":"Polygon","coordinates":[[[391280.189856155,583078.245016339],[400577.187441756,587453.30270368],[411104.670001921,580480.55451448],[410967.949449192,578976.628434456],[401260.790205403,577746.143459891],[391280.189856155,583078.245016339]]]}
LineString Item:
{"type":"LineString","coordinates":[[389092.661012485,572960.924114362],[413018.757740133,573371.08577255],[423682.960853028,578839.907881727]]}
Point Item:
{"type":"Point","coordinates":[406182.730103662,567355.381452455]}
Grid - Gets the value of a Grid item on another overlay at the hook point of the current item.
Expression Syntax: Grid(Overlay(name, {filter}))
Overlay
The overlay on which to search for a Grid item.
filter
An optional filter to apply to items on the overlay being searched.
Example:
Grid("Overlay 1")
returns the value of the found Grid item, if any, at the origin of the current expression item.
Origin - Gets the origin of an item as a comma-separated string.
Expression Syntax: Origin
Example:
Origin()
Returns the origin of an item as a comma-separated string.
Example of a return:
535987.39641134,324946.295223841,100
OverlapArea - Gets the area of overlay between an item on another overlay and the current expression item.
The current formula item, and the overlapping items, must be closed area items, e.g. Polygon, Bitmap, QZone, TopoPolygon items, or Surface item which are TINs.
Expression Syntax: OverlapArea(Overlay(name, {filter}), expressionorproperty, valueorrange)
name
The overlay on which to search for items.
filter
An optional filter to apply to items on the overlay being searched.
expressionorproperty
The property to look for on any items found. Setting both this argument and valueorrange to empty will force all items to be considered.
valueorrange
A string value, or range of numerical values. The found item will only be included in the overlap calculation if expressionorproperty is empty, or if the found item value matches valueorrange for string values, or is in the range min#,max# for numerical values.
Example:
OverlapArea("Counties","County$","Hertfordshire")
returns the overlap between the current expression item and the item representing "Hertfordshire" on the overlay "Counties".
Overlay - A reference to another Overlay, used for finding items, with an optional filter.
Expression Syntax: Overlay(name, filter)
Examples:
Overlay("Buildings")
Overlay("Buildings",{Type$="Industrial"})
ST_AsBinary - Gets the OGC Well-known Binary (WKB) representation of a geometry.
Expression Syntax: ST_AsBinary(geometry)
geometry
the OGC Well-known Binary (WKB) representation of a geometry.
Example:
hex(ST_AsBinary(ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))')))
ST_AsGeoJSON - Gets the GeoJSON representation of a geometry.
Expression Syntax: ST_AsGeoJSON(geometry)
geometry
the OGC Well-known Text (WKT) representation of a geometry.
Example:
ST_AsGeoJSON(geometry)
returns the GeoSON for the overlay Items, for example
Polygon Item:
{"type":"Polygon","coordinates":[[[535984.246,324925.094],[535972.19,324917.31],[535974.3,324913.9],[535977.54,324915.94],[535976.74,324917.03],[535980.34,324919.34],[535984.84,324912.23],[535978.04,324907.99],[535980.04,324904.84],[535983.04,324900.08],[535984.44,324900.84],[535985.34,324901.44],[535983.85,324904.23],[535989.21,324907.06],[535986.8,324911.55],[535992.646,324914.707],[535984.246,324925.094]]]}
Linestring Item:
{"type":"LineString","coordinates":[[535970.608562724,324889.624669965],[535985.50001842,324897.261313912]]}
Point Item:
{"type":"Point","coordinates":[535962.238573802,324940.629044215]}
ST_AsText - Gets the OGC Well-known Text (WKT) representation of a geometry.
Expression Syntax: ST_AsText(geometry)
geometry
the OGC Well-known Text (WKT) representation of a geometry.
Example:
ST_AsText(geometry)
returns the OGC Well-known Text (WKT) representation for the overlay Items, for example:
Polygon Item:
POLYGON ((535930.126230952 324933.966902089,535942.860531554 324933.966902089,535942.860531554 324918.367383851,535953.047972035 324918.367383851,535953.047972035 324911.681876035,535960.051837366 324911.681876035,535960.051837366 324894.808927738,535930.126230952 324894.808927738,535930.126230952 324923.779461607,535920.257147985 324923.779461607,535919.620432955 324931.101684453,535930.126230952 324931.101684453,535930.126230952 324933.966902089))
Linestring Item:
LINESTRING (535931.29880034 324895.979548765,536022.869128503 324895.979548765)
Point Item:
POINT (535968.131400613 324941.082338585)
ST_Centroid - Returns the centroid (geometric centre) of a 2D polygonal item, as a point item. ST_Centroid can return a 3D position with a Z value from a TIN item.
Expression Syntax: ST_Centroid(geometry)
Example:
ST_AsText(ST_Centroid(geometry))
ST_Contains - Does one geometry contain another geometry (or the geometry of an item)?
Expression Syntax: ST_Contains(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
ST_Contains((ST_GeomFromText('POLYGON ((536000.483241777 324938.742264814,536013.217542379 324938.742264814,536013.217542379 324923.142746577,536023.404982861 324923.142746577,536023.404982861 324916.457238761,536052.693874245 324916.457238761,536052.693874245 324899.902647978,536000.483241777 324899.584290463,536000.483241777 324928.554824333,535990.614158811 324928.554824333,535989.977443781 324935.877047179,536000.483241777 324935.877047179,536000.483241777 324938.742264814))')),ST_GeomFromText('POLYGON ((536003.715008662 324908.218496757,536009.381188287 324918.870914453,536017.313839763 324919.550856008,536022.980019389 324910.938262977,536031.59261242 324908.898438312,536030.459376495 324904.592141796,536013.687484803 324904.138847426,536003.715008662 324908.218496757))'))
returns -1 if True, i.e. one of the above geometries contains the other geometry. Returns 0 if False.
ST_Crosses - Does one geometry cross another geometry (or the geometry of an item)?
Expression Syntax: ST_Crosses(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
ST_Crosses((ST_GeomFromText('LINESTRING (535940.93373841 324947.655106951,535962.238573802 324940.629044215,535970.397872463 324924.763741264,535981.276937344 324917.284384158,535989.436236005 324906.405319277,535988.75629445 324895.526254395)')),ST_GeomFromText('LINESTRING (535965.411634392 324907.311908017,535968.131400613 324920.004150378,535962.238573802 324930.656568074,535971.531108388 324936.096100515,535979.010465494 324948.561695691)'))
returns -1 if True, one of the above geometries crosses another geometry, returns 0 if False.
ST_Disjoint - Are one geometry and another geometry (or the geometry of an item) disjoint?
Expression Syntax: ST_Disjoint(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
Consider a LineString and Point Item:
ST_Disjoint((ST_GeomFromText('LINESTRING (535895.83094859 324873.994771818,535911.696251541 324883.513953589,535930.961262269 324881.247481739,535937.080736264 324868.555239377,535925.295082643 324854.729761091,535911.242957171 324851.783347685)')),ST_GeomFromText('POINT (535914.189370577 324869.008533747)'))
returns -1 if True, if one geometry and another geometry (or the geometry of an item) are disjoint, returns 0 if False.
ST_Distance - Returns the distance between one geometry and another geometry (or the geometry of an item).
Expression Syntax: ST_Distance(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Examples:
Example 1:
ST_Distance((ST_GeomFromText('POLYGON ((536000.483241777 324938.742264814,536013.217542379 324938.742264814,536013.217542379 324923.142746577,536023.404982861 324923.142746577,536023.404982861 324916.457238761,536052.693874245 324916.457238761,536052.693874245 324899.902647978,536000.483241777 324899.584290463,536000.483241777 324928.554824333,535990.614158811 324928.554824333,535989.977443781 324935.877047179,536000.483241777 324935.877047179,536000.483241777 324938.742264814))')),ST_GeomFromText('POLYGON ((535824.680010114 324902.333035837,535837.414310716 324902.333035837,535837.414310716 324886.7335176,535847.601751197 324886.7335176,535847.601751197 324880.048009784,535854.605616528 324880.048009784,535854.605616528 324863.175061486,535824.680010114 324863.175061486,535824.680010114 324892.145595356,535814.810927147 324892.145595356,535814.174212117 324899.467818202,535824.680010114 324899.467818202,535824.680010114 324902.333035837))'))
Gets the distance between the first Polygon with the above coordinates and the second Polygon with the above coordinates.
Example 2:
ST_Distance((ST_GeomFromText('POLYGON ((536000.483241777 324938.742264814,536013.217542379 324938.742264814,536013.217542379 324923.142746577,536023.404982861 324923.142746577,536023.404982861 324916.457238761,536052.693874245 324916.457238761,536052.693874245 324899.902647978,536000.483241777 324899.584290463,536000.483241777 324928.554824333,535990.614158811 324928.554824333,535989.977443781 324935.877047179,536000.483241777 324935.877047179,536000.483241777 324938.742264814))')),ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))'))
returns the distance between the Polygon with the above coordinates and the LineString with the above coordinates.
ST_DWithin - Is one geometry within a distance of another geometry (or the geometry of an item) disjoint?
Expression Syntax: ST_DWithin(geometry, geometry2, distance)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
distance
the distance that geometry and geometry2 must be within.
Example:
ST_Dwithin((ST_GeomFromText('POLYGON ((536000.483241777 324938.742264814,536013.217542379 324938.742264814,536013.217542379 324923.142746577,536023.404982861 324923.142746577,536023.404982861 324916.457238761,536052.693874245 324916.457238761,536052.693874245 324899.902647978,536000.483241777 324899.584290463,536000.483241777 324928.554824333,535990.614158811 324928.554824333,535989.977443781 324935.877047179,536000.483241777 324935.877047179,536000.483241777 324938.742264814))')),ST_GeomFromText('POLYGON ((536020.940194724 324883.060659219,536000.541948071 324883.060659219,536000.541948071 324861.075882272,536020.940194724 324861.075882272,536020.940194724 324883.060659219))'),200)
returns -1 if True, if one geometry and another geometry are within the specified distance, in this case 200m, returns 0 if False.
ST_Equals - Is one geometry equal to another geometry (or the geometry of an item)?
Expression Syntax: ST_Equals(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Examples:
Example 1:
ST_Equals(Geometry, ST_GeomFromText('POLYGON ((535930.126230952 324933.966902089,535942.860531554 324933.966902089,535942.860531554 324918.367383851,535953.047972035 324918.367383851,535953.047972035 324911.681876035,535960.051837366 324911.681876035,535960.051837366 324894.808927738,535930.126230952 324894.808927738,535930.126230952 324923.779461607,535920.257147985 324923.779461607,535919.620432955 324931.101684453,535930.126230952 324931.101684453,535930.126230952 324933.966902089))'))
Gets the Polygon with the above coordinates.
Example 2:
ST_Equals(Geometry, ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))'))
Gets the LineString with the above coordinates.
ST_GeomFromGeoJSON - Creates a geometry from a GeoJSON representation.
Expression Syntax: ST_GeomFromGeoJSON(geometry, crs)
geometry
the OGC Well-known Text (WKT) representation of a geometry.
crs
the coordinate reference system to be used when creating the geometry.
Example:
ST_AsGeoJSON(ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))',"EPSG:27700"))
ST_GeomFromText - Creates a geometry from an OGC Well-known Text (WKT) representation.
Expression Syntax: ST_GeomFromText(geometry, crs)
geometry
the OGC Well-known Text (WKT) representation of a geometry.
crs
the coordinate reference system to be used when creating the geometry.
Example:
ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))',"EPSG:27700"))
ST_GeomFromWKB - Creates a geometry from an OGC Well-known Binary (WKB) representation.
Expression Syntax: ST_GeomFromWKB(geometry, crs)
geometry
the OGC Well-known Text (WKT) representation of a geometry.
crs
the coordinate reference system to be used when creating the geometry.
Example:
ST_GeomFromWKB(ST_GeomFromText('LINESTRING (535977.197288014 324900.285845281,536010.741071397 324881.474128924))',"EPSG:27700"))
ST_Intersects - Does one geometry intersect with another geometry (or the geometry of an item)?
Expression Syntax: ST_Intersects(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Examples:
- ST_Intersects(ST_GeomFromText('LINESTRING(1 0,0 1)'),ST_GeomFromText('LINESTRING(0 0,1 1)'))
- ST_Intersects(Geometry.Extents,ST_GeomFromText('LINESTRING(0 0, 1 1)'))
Note in this second example “parts” of the current geometry have also been used.
- As a further example consider the Polygons below intersected by a LineString (Road):
In this example the Iif expression is also used to return a value from the following list of arguments:
Iif(ST_Intersects(Geometry, ST_GeomFromText(('LINESTRING(535973 324883, 536041 324928)'))),"Yes","No")
This will create a table column (which can be renamed) giving the values Yes or No.
ST_Overlaps - Does one geometry overlap another geometry (or the geometry of an item)?
Expression Syntax: ST_Overlaps(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
ST_Overlaps((ST_GeomFromText('POLYGON ((535930.126230952 324933.966902089,535942.860531554 324933.966902089,535942.860531554 324918.367383851,535953.047972035 324918.367383851,535953.047972035 324911.681876035,535960.051837366 324911.681876035,535960.051837366 324894.808927738,535930.126230952 324894.808927738,535930.126230952 324923.779461607,535920.257147985 324923.779461607,535919.620432955 324931.101684453,535930.126230952 324931.101684453,535930.126230952 324933.966902089))')),ST_GeomFromText('POLYGON ((535949.667640685 324901.241259763,535962.401941287 324901.241259763,535962.401941287 324885.641741526,535972.589381768 324885.641741526,535972.589381768 324878.95623371,535979.593247099 324878.95623371,535979.593247099 324862.083285412,535949.667640685 324862.083285412,535949.667640685 324891.053819281,535939.798557718 324891.053819281,535939.161842688 324898.376042127,535949.667640685 324898.376042127,535949.667640685 324901.241259763))'))
returns -1 if True, one of the above geometries overlaps the other geometry, returns 0 if False.
ST_PointOnSurface - Returns a point within the interior of a 2D polygonal item, as a point item. In the case of a surface created from a polygon, ST_PointOnSurface returns the origin
of the original polygon. ST_PointOnSurface can return a 3D position with a Z value from a TIN item.
Expression Syntax: ST_PointOnSurface(geometry)
Example:
ST_AsText(ST_PointOnSurface(geometry))
ST_Relate - Does the geometry have the given spatial relationship with another geometry (or the geometry of an item)?
Expression Syntax: ST_Relate(geometry, geometry2, de9im)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
de9im
Dimensionally Extended nine-Intersection Model. Characters can be T, F * and 0.
Example:
IIf(ST_Relate(Geometry, ST_GeomFromText('POLYGON ((536031.991371395 324915.406193349,535992.080560111 324915.406193349,535992.080560111 324898.285593934,536031.991371395 324898.285593934,536031.991371395 324915.406193349))'), "2********"),"True","False")
returns True or False
ST_Touches - Does one geometry touch another geometry (or the geometry of an item)?
Expression Syntax: ST_Touches(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
ST_Touches((ST_GeomFromText('POLYGON ((535916.229195242 324882.607364849,535901.27048103 324882.607364849,535901.27048103 324873.541477448,535916.229195242 324873.541477448,535916.229195242 324882.607364849))')),ST_GeomFromText('POLYGON ((535916.229195242 324873.541477448,535892.884535184 324873.541477448,535892.884535184 324859.715999161,535916.229195242 324859.715999161,535916.229195242 324873.541477448))'))
returns -1 if True, one of the above geometries touches the other geometry, returns 0 if False.
ST_Within - Is one geometry within another geometry (or the geometry of an item)?
Expression Syntax: ST_Within(geometry, geometry2)
geometry
the OGC Well-known Text (WKT) representation of the first geometry.
geometry2
the OGC Well-known Text (WKT) representation of the second geometry.
Example:
ST_Within((ST_GeomFromText('POLYGON ((536003.715008662 324908.218496757,536009.381188287 324918.870914453,536017.313839763 324919.550856008,536022.980019389 324910.938262977,536031.59261242 324908.898438312,536030.459376495 324904.592141796,536013.687484803 324904.138847426,536003.715008662 324908.218496757))')),ST_GeomFromText('POLYGON ((536000.483241777 324938.742264814,536013.217542379 324938.742264814,536013.217542379 324923.142746577,536023.404982861 324923.142746577,536023.404982861 324916.457238761,536052.693874245 324916.457238761,536052.693874245 324899.902647978,536000.483241777 324899.584290463,536000.483241777 324928.554824333,535990.614158811 324928.554824333,535989.977443781 324935.877047179,536000.483241777 324935.877047179,536000.483241777 324938.742264814))'))
returns -1 if True, one of the above geometries is within the other geometry, returns 0 if False.
Tin - Gets the value of a TIN item on another overlay at the hook point of the current item.
Expression Syntax: Tin(Overlay(name, {filter}))
Overlay
The overlay on which to search for the value of a TIN item, either as a string or using the Overlay() expression.
filter
An optional filter to apply to items on the overlay being searched.
Example:
Tin("Overlay 1")
gets the height (Z value) of the TIN in Overlay 1 at the position of the current item.
WKB - Gets the OGC Well-known Binary (WKB) representation of the geometry of an item.
Expression Syntax: WKB()
WKB() is equal to ST_ASBinary(Geometry)
“Geometry” in this case is the built in Function.
Example:
hex(WKB())
will return a readable result from the returned (ã) array of bytes.
WKT - Gets the OGC Well-known Text (WKT) representation of the geometry of an item.
Expression Syntax: WKT()
Example:
WKT()
will return the OGC Well-known Text (WKT) representation for the overlay Items, for example:
Polygon:
POLYGON ((535930.126230952 324933.966902089,535942.860531554 324933.966902089,535942.860531554 324918.367383851,535953.047972035 324918.367383851,535953.047972035 324911.681876035,535960.051837366 324911.681876035,535960.051837366 324894.808927738,535930.126230952 324894.808927738,535930.126230952 324923.779461607,535920.257147985 324923.779461607,535919.620432955 324931.101684453,535930.126230952 324931.101684453,535930.126230952 324933.966902089))
Linestring:
LINESTRING (535931.29880034 324895.979548765,536022.869128503 324895.979548765)
Point:
POINT (535968.131400613 324941.082338585)
Bin - Converts a hexadecimal string into an array of bytesExpression Syntax: Bin(hex)
Examples:
Bin("00010203")
returns binary data of length 4 bytes with values 0, 1, 2, 3.
Find - Finds the position in a string (in bytes, not characters) of a substring within a string.
Expression Syntax: Find(string, searchstring)
string
The string, or property value representing a string, to search.
searchstring
The substring, or property value representing a string, to search for.
Returns
The position in the string of the first occurrence of the searchstring, starting at 0. -1 will be returned if searchstring is not found.
Example:
Find("aaa", "a") - will return 0
Find("The Cat", "Cat") - will return 4
Find("The Cat", "The") - will return 0
Find("The Cat", "Dog") - will return -1
FindW - Finds the position of a substring within a string or -1 if not found.
Expression Syntax: FindW(string, searchstring)
string
The string, or property value representing a string, to search.
searchstring
The substring, or property value representing a string, to search for.
Returns
The position in the string of the first occurrence of the searchstring, starting at 0. -1 will be returned if searchstring is not found.
Example:
FindW("aaa", "a") - will return 0
FindW("The Cat", "Cat") - will return 4
FindW("The Cat", "The") - will return 0
FindW("The Cat", "Dog") - will return -1
FormatDate - Formats a date number as a string using the MFC COleDateTime format specification.
Expression Syntax: FormatDate(date, format)
date
a date or a property representing a date
format
A C printf format specification, see Allowable formats.
Examples:
FormatDate(date(),"%d %B %Y")
would return day month year, i.e. 23 April 2014.
FormatDate(date(),"%x: %X")'
would return an automatic date and time for the current locale in the format; 23/04/2014: 10:30:38
Allowable formats:
The format parameter in the expression syntax can contain a combination of the following codes:
%a ‑ Abbreviated weekday name
%A ‑ Full weekday name
%b ‑ Abbreviated month name
%B ‑ Full month name
%c ‑ Date and time representation appropriate for locale
%d ‑ Day of month as decimal number (01 to 31
%H ‑ Hour in 24-hour format (00 to 23)
%I ‑ Hour in 12-hour format (01 to 12)
%j ‑ Day of year as decimal number (001 to 366)
%m ‑ Month as decimal number (01 to 12)
%M ‑ Minute as decimal number (00 to 59)
%p ‑ Current locale's A.M./P.M. indicator for 12-hour clock
%S ‑ Second as decimal number (00 to 59)
%U ‑ Week of year as decimal number, with Sunday as first day of week (00 to 51)
%w ‑ Weekday as decimal number (0 to 6; Sunday is 0)
%W ‑ Week of year as decimal number, with Monday as first day of week (00 to 51)
%x ‑ Date representation for current locale
%X ‑ Time representation for current locale
%y ‑ Year without century, as decimal number (00 to 99)
%Y ‑ Year with century, as decimal number
%z ‑ %Z ‑ Time-zone name or abbreviation; no characters if time zone is unknown
%% ‑ Percent sign
Example format combinations:
%d:%m:%y would return Day of the month: Month number: Year without century (as a decimal), i.e. 23:04:14.
%H:%M:%S would show Hour (24h-hour format): minute as decimal: second as decimal, i.e. 10:26:36.
Note: Date and time codes can be used in a print template. See the section commencing with the topic About Print Templates for details of print templates.
FormatFlt - Returns a floating point number as a string.
Expression Syntax: FormatFlt(float, format)
float
A floating point number or a property representing a floating point number.
format
A C printf format specification, see Allowable formats.
Example:
FormatFlt(2.3456789, "%10.3f")
Allowable formats:
The format string must have the following general form:
%[flags][width][.precision]type
Where the fields are as follows:
flags - can contain these characters: -+ #0
width - is an optional number
precision - is another optional number
type - is one of the following characters:
e - Scientific notation (mantissa/exponent), lowercase, example 3.9265e+2
E - Scientific notation (mantissa/exponent), uppercase, example 3.9265e+2
f - Decimal floating point, lowercase, example 392.65
g - Use the shortest representation: %e or %f, example 392.65
G - Use the shortest representation: %E or %F, example 392.65
An example format would be %10.3f
FormatInt - Returns an integer as a string.
Expression Syntax: FormatInt(integer, format)
integer
An integer or a property representing an integer.
format
A C printf format specification, see Allowable formats.
Example:
FormatInt(89,"%10.3x")
this will return a Hex value of 059
Allowable formats:
The format string must have the following general form:
%[flags][width][.precision]type
Where the fields are as follows:
flags - can contain these characters: -+ #0
width - is an optional number
precision - is another optional number
type - is one of these characters: cdiouxX
c - Character, example a (if 97 is entered in the integer field)
d - or i Signed decimal integer, example 392
o - Unsigned octal, example 610
u - Unsigned decimal integer, example 7235
x - Unsigned hexadecimal integer, example 7fa
X - Unsigned hexadecimal integer (uppercase), example 7FA
An example format would be %05d
FormatStr - Formats a string.
Expression Syntax: FormatStr(string, format)
string
A string, or a property representing a string.
format
A C printf format specification, see Allowable formats.
Example:
FormatStr("This is a string","%10.16s")
this will return "This is a string".
Allowable formats:
The format string must have the following general form:
%[flags][width][.precision]type
Where the fields are as follows:
flags - can contain these characters: -+ #0
width - is an optional number
precision - is another optional number
type - must be the character s (string of characters)
An example format would be %10.10s
Hex - Returns the hexadecimal representation of an array of bytes.
Expression Syntax: Hex(binary)
binary
The binary representation of an item.
Example:
Hex(WKB())
returns a value in the format “000000000140000000000000004010000000000000”
Left - Gets a number of characters from the left of a string using a byte count.
Expression Syntax: Left(string, bytes)
string
A string, or a property representing a string.
bytes
The number of bytes to return.
Example:
Left("Cadcorp",3)
returns Cad
LeftW - Gets a number of characters from the left of a string using a character count.
Expression Syntax: LeftW(string, bytes)
string
A string, or a property representing a string.
bytes
The number of characters to return.
Example:
LeftW("Cadcorp",3)
returns Cad
Len - Gets the length of a string, in bytes.
Expression Syntax: Len(string)
string
A string, or a property representing a string.
Example:
Len("Cadcorp software")
returns 16
Len(name$)
returns the length of name$
LenW - Gets the length of a string, in characters.
Expression Syntax: LenW(string)
string
A string, or a property representing a string.
Examples:
LenW("Cadcorp software")
returns 16
LenW(name$)
returns the length of name$
Local - Gets the "local" representation of a number as a string.
Expression Syntax: Local(string)
string
A string, or a property representing a string.
Example:
Local(1.000)
returns 1
LocaleMapString - Maps an input character string to another using a specified transformation, or generates a sort key for the input string.See: http://msdn.microsoft.com/en-us/library/dd318702%28v=vs.85%29.aspx
SIS only supports;
LCMAP_HIRAGANA
LCMAP_KATAKANA
LCMAP_HALFWIDTH
LCMAP_FULLWIDTH
Expression Syntax: LocaleMapString
Example:
LocaleMapString("ja-JP",LMS_Katakana|LMS_FullWidth,"しんぶん")
returns シンブン
Lower - Convert a string to lowercase characters.
Expression Syntax: Lower(string)
string
A string, or a property representing a string.
Example:
Lower("CADCORP")
returns cadcorp
Lpad - Pad characters to the left of a string, or trim a string, to achieve a specified number of characters.
Expression Syntax: Lpad(string,number,character)
string
A string, or a property representing a string.
number
The number of characters to pad or trim to.
character
The characters to use to pad the string.
Examples:
Lpad("This is some sample text",10,"x")
returns the first 10 characters, i.e. This is so. In this case the final x parameter has no effect. If the text in the first parameter was less than 10 characters, for example My Text, then x would pad the string to a total of 10 characters, i.e. xxxMy Text.
Lpad(text$,15,"-/")
returns the first 15 characters of text$ or text$ plus -/ padding to make a total of 15 characters, i.e. -/-/Sample Text.
LpadW - Pad characters to the left of a string, or trim a string, to achieve a specified number of characters.
Expression Syntax: LpadW(string,number,character)
string
A string, or a property representing a string.
number
The number of characters to pad or trim to.
character
The characters to use to pad the string.
Examples:
LpadW("This is some sample text",10,"x")
returns the first 10 characters, i.e. This is so. In this case the final x parameter has no effect. If the text in the first parameter was less than 10 characters, for example My Text, then x would pad the string to a total of 10 characters, i.e. xxxMy Text.
LpadW(text$,15,"-/")
returns the first 15 characters of text$ or text$ plus -/ padding to make a total of 15 characters, i.e. -/-/Sample Text.
Ltrim - Trim spaces from the start of a string.
Expression Syntax: Ltrim(string)
string
A string, or a property representing a string.
Examples:
Ltrim(" Cadcorp ")
returns Cadcorp
Ltrim(name$)
returns name$ with no leading spaces
Mid - Gets a number of characters from the middle of a string using a byte count.
Expression Syntax: Mid(string, start, bytes)
string
A string, or a property representing a string.
start
The position of the first byte to extract, the first character of the string being 1.
bytes
The number of bytes to extract.
A negative number will return the characters from the start position to the end of the string.
Examples:
Mid("abcdefghij",1,3)
returns abc
Mid("abcdefghij",4,3)
returns def
Mid("abcdefghij",7,-1)
returns ghij
MidW - Gets a number of characters from the middle of a string using a character count.
Expression Syntax: MidW(string, start, characters)
string
A string, or a property representing a string.
start
The position of the first byte to extract, the first character of the string being 1.
characters
The number of characters to extract.
A negative number will cause the characters from the start position to the end of the string to be returned.
Examples:
MidW("abcdefghij",1,3)
returns abc
MidW("abcdefghij",4,3)
returns def
MidW("abcdefghij",7,-1)
returns ghij
Replace - Replaces a character or string with another character or string.
Expression Syntax: Replace(string,searchstring,replacestring)
string
The string, or property value representing a string, to search.
searchstring
The string, or property value representing a string, to search for and be replaced.
replacestring
The string, or property value representing a string, to replace the searchstring.
Example:
Replace(Name$, "St.", "Saint")
Searches the Name$ property for instances of "St." and replaces these with "Saint", i.e. "St. James Hospital" and "St. Johns Hospital", would become "Saint James Hospital" and "Saint Johns Hospital".
ReverseFind - Finds the position in a string, searching in a backward direction, (in bytes, not characters) of a substring within a string.
Expression Syntax: ReverseFind(string, searchstring)
string
The string, or property value representing a string, to search.
searchstring
The substring, or property value representing a string, to search for.
Example:
Left(Name$, ReverseFind(Name$," "))
Strip the names from the last part of the full name (after the last space). As an example the Name$ property may contain hospital names, i.e. "St. James Hospital", "St. Johns Hospital", "St Helens Paediatric Hospital", etc. This example would strip the word after the first space searching from the right, therefore returning "St. James", "St. Johns" and "St. Helens Paediatric".
ReverseFindW - Finds the position in a string, searching in a backward direction, (in bytes, not characters) of a substring within a string.
Expression Syntax: ReverseFind(string, searchstring)
string
The string, or property value representing a string, to search.
searchstring
The substring, or property value representing a string, to search for.
Example:
Left(Name$, ReverseFindW(Name$," "))
Strip the names from the last part of the full name (after the last space). As an example the Name$ property may contain hospital names, i.e. "St. James Hospital", "St. Johns Hospital", "St Helens Paediatric Hospital", etc. This example would strip the word after the first space searching from the right, therefore returning "St. James", "St. Johns" and "St. Helens Paediatric".
Right - Gets a number of characters from the right of a string using a byte count.
Expression Syntax: Right(string, bytes)
string
A string, or a property representing a string.
bytes
The number of bytes to return.
Example:
Right("abcdefghij",3)
returns hij
RightW - Gets a number of characters from the right of a string using a character count.
Expression Syntax: RightW(string, bytes)
string
A string, or a property representing a string.
bytes
The number of characters to return.
Example:
RightW("abcdefghij",3)
returns hij
Rpad - Pad characters to the right of a string, or trim a string, to achieve a specified number of characters.
Expression Syntax: Rpad(string,number,character)
string
A string, or a property representing a string.
number
The number of characters to pad or trim to.
character
The characters to use to pad the string.
Examples:
Rpad("This is some sample text",10,"x")
returns the first 10 characters, i.e. This is so. In this case the final x parameter has no effect. If the text in the first parameter was less than 10 characters, for example My Text, then x would pad the end of the string to a total of 10 characters, i.e. My Textxxx.
Rpad(text$,15,"-/")
returns the first 15 characters of text$ or text$ plus -/ padding to make a total of 15 characters, i.e. Sample Text-/-/.
RpadW - Pad characters to the right of a string, or trim a string, to achieve a specified number of characters.
Expression Syntax: RpadW(string,number,character)
string
A string, or a property representing a string.
number
The number of characters to pad or trim to.
character
The characters to use to pad the string.
Examples:
RpadW("This is some sample text",10,"x")
returns the first 10 characters, i.e. This is so. In this case the final x parameter has no effect. If the text in the first parameter was less than 10 characters, for example My Text, then x would pad the end of the string to a total of 10 characters, i.e. My Textxxx.
RpadW(text$,15,"-/")
returns the first 15 characters of text$ or text$ plus -/ padding to make a total of 15 characters, i.e. Sample Text-/-/.
Rtrim - Trim spaces from the end of a string.
Expression Syntax: Rtrim(string)
string
A string, or a property representing a string to trim.
Examples:
Rtrim(" Cadcorp ")
returns Cadcorp with end spaces removed.
Rtrim(name$)
returns name$ with end spaces removed.
Sentence - Changes a string to 'sentence case', i.e. makes the first letter uppercase and the other letters lowercase.
Expression Syntax: Sentence(string, fullstop)
string
A string to convert to 'sentence case', or a property representing a string.
fullstop
Should a full stop be added to the end of the string if one is not there? 0 will return the sentence without a full stop, -1 will return the sentence with a full stop.
Examples:
Sentence("the quick brown fox",0)
returns The quick brown fox
Sentence("the quick brown fox",-1)
returns The quick brown fox.
Str - Gets the string representation of a number.
Expression Syntax: Str(number)
number
A number to be returned as a string.
Examples:
Str(1000)
returns 1000 as string.
Str(2^4)
returns 16 as a string.
Title - Changes a string to 'title case', i.e. makes the first letter of each word uppercase.
Expression Syntax: Title(string)
string
A string to convert to 'title case', or a property representing a string.
Example:
Title("computer aided development limited")
returns Computer Aided Development Limited
Toggle - Toggles the case of each character in a string, i.e. makes lowercase characters uppercase and vice-versa.
Expression Syntax: Toggle(string)
string
A string to toggle the case, or a property representing a string.
Example:
Toggle("Cadcorp")
will toggle the case to become cADCORP.
Upper - Convert a string to uppercase characters.
Expression Syntax: Upper(string)
string
A string, or a property representing a string.
Example:
Upper("cadcorp")
returns CADCORP
Val - Gets the numerical value of a string.
Expression Syntax: Val(string)
string
A string, or a property representing a string.
Example:
Val("0.1e6")
returns 100000
Note: When using the Expression Builder numbers as strings that are cast to integer or absolute (decimal, float) values are not always treated as numeric. To ensure they are treated as numeric (especially when sorting) you should use the following casting methods:
Integers as strings - you can use Int(Val("123"))
Floats as strings - you can use Abs(Val("3.1415"))
VarType - Gets the type of a property.
Expression Syntax: VarType(property)
property
The property whose value is to be returned.
Examples of returns:
Integer = 3
FP = 5
date = 7
String = 8
Boolean = 11
returns an empty string
returns a value of 0
returns a null value
returns a value of -1
Examples:
6-10
returns -4
maximumValue# - minimumValue#
returns the difference between maximumValue# and minimumValue#
Examples:
5.678 % 1.234
returns 0.742
_area# % _sx#
returns the remainder from the division of _area# by _sx#
Example:
1.234 * 5.678
returns 7.006652
_area# * _sx#
returns the result of _area# multiplied by _sx#
Example:
1.234/5.678
returns 0.217330045790771
_area#/_length#'
returns the result of _area# divided by _length#
Examples:
1.234^5
returns 2.86138172105142
_length#^2
returns _length# to the power of 2
Examples:
1.234 + 5.678
returns 6.912
_ox# + sx#
returns the sum of _area# and _sx#
< Is one value less than anotherExamples:
valueA#<1.234
returns -1 if valueA# is less than 1.234, returns 0 if valueA# is not less than 1.234 (or if valueA# is equal to 1.234).
valueA#<valueB#
returns -1 if valueA# is less than valueB#, returns 0 if valueA# is not less than valueB# (or if valueA# is equal to valueB#).
Examples:
valueA#<=100
returns -1 if valueA# is less than or equal to 100, returns 0 if valueA# is not less than or equal to 100.
valueA#<=valueB#
returns -1 if valueA# is less than or equal to valueB#, returns 0 if valueA# is not less than or equal to valueB#.
<> Are two values different.
Examples:
valueA#<>1.234
returns -1 if valueA# is different to 1.234, returns 0 if valueA# is not different to 1.234.
valueA#<>valueB#
returns -1 if valueA# is different to valueB#, returns 0 if valueA# is not different to valueB#.
= Are two values equal.
Examples:
valueA#=1.234
returns -1 if valueA# is equal to 1.234, returns 0 if valueA# is not equal to 1.234.
valueA#=valueB#
returns -1 if valueA# is equal to valueB#, returns 0 if valueA# is not equal to valueB#.
> Is one value greater than another.
Examples:
valueA#>1.234
returns -1 if valueA# is greater than 1.234, returns 0 if valueA# is not greater than 1.234.
valueA#>valueB#
returns -1 if valueA# is greater than valueB#, returns 0 if valueA# is not greater than valueB#.
Examples:
valueA#>=1.234
returns -1 if valueA# is greater than or equal to 1.234, returns 0 if valueA# is not greater than or equal to 1.234.
valueA#>=valueB#
returns -1 if valueA# is greater than or equal to valueB#, returns 0 if valueA# is not greater than or equal to valueB#.
Expression Syntax: number Between low And high
number
a number or property to be taken to see if it is between low and high.
low
the lower limit of the range. Can be a number or a property.
high
the higher limit of the range. Can be a number or a property.
Examples:
value# Between 0 And maximumValue#
returns -1 if value# is between 0 and maximumValue#, returns 0 if value# is not between 0 and maximumValue#.
valueA# Between valueB# And valueC#
returns -1 if valueA# is between valueB# and valueC#, returns 0 if valueA# is not between valueB# and valueC#.
Coalesce functions in a manner similar to a concatenate command.
When stringing two columns together a nil return will occur if there is not a value in every single column. Coalesce prevents this possibility by using a value if it exists but taking a value from another column if it doesn't.
Example:
The following code tells Coalesce to use Informal name if it exists but if it does not exist then use Birthname.
Coalesce(Informal$, Birthname$)
Note that not every entry has an Informal name, and it does not need to have.
Can be used to test for similar text strings.
Example:
Fuzzy("This is my sample text for testing results",Text$)
If the text in quotes and the text in Text$ are identical then 1 will be returned, indicating they are identical.
If the two texts differ slightly then a figure of less than 1 will be returned. The more the two texts differ the lower the returned value will be. If the texts bear no similarity at all a value of 0 will be returned.
Can be used to test for identical text strings between strings and properties.
Expression Syntax: Grep(regexp, string)
regexp
The regular expression to be searched for.
string
The text string or property to be searched.
Returns:
Boolean; A signed integer, either -1 for a True statement, or 0 for a False statement. Any non-zero number will be interpreted as True.
Examples:
Grep("Customi[sz]e", _text$)
Matches any of the enclosed characters, i.e. 's' or 'z'.
will return -1 for Customise or Customize
Grep("Customi[^sz]e", _text$)
Matches anything NOT enclosed by the brackets
will return -1 for Customixe, if such a word was found, will return 0 for any other text strings.
Further examples;
i(?=z)
Matches 'i' only if 'i' is followed by 'z'.
x|y
Matches 'x' OR 'y'.
Expression Syntax: In (n, n1, n2 .....)
Example:
5 In (2, 3, 5, 7, 11, 13, 17, 19)
returns -1 (True)
6 In (2, 3, 5, 7, 11, 13, 17, 19)
returns 0 (False)
A comparison operator for two regular string expressions.
Expression Syntax: string Like match
string
The text string or property to be matched to.
match
The text string or property to be matched.
Note: The single ? character is handled as a single character match. The single * character is handled as any number of characters match.
Examples:
"quick brown fox" Like Text$
If the text in the first parameter "quick brown fox" is identical to the text in the second parameter; Text$ property then -1 (True) will be returned. If this is not the case then 0 (False) will be returned:
"quick brown fox" Like "the quick brown fox lives here" returns 0
Text$ Like MoreText$
In this example the text in the two text properties are compared. The return values follow the protocol described above.
The bitwise And operator allows two Boolean expressions to be combined. The statement will be True if both Boolean expressions are True.
Returns:
Boolean; A signed integer, either -1 for a True statement, or 0 for a False statement. Any non-zero number will be interpreted as True.
Example:
"abc" Like text1$ And "xyz" Like text2$
Returns -1 if text1$ is abc and at the same time text2$ is xyz. If one of the Boolean expressions is False then the expression will return 0.
Example:
_bSimple& Or _bClosed&
Example:
Not _bSimple&
The bitwise And operator allows two Boolean expressions to be combined. The statement will be true if both Boolean expressions are true.
Returns:
Boolean; A signed integer, either -1 for a True statement, or 0 for a False statement. Any non-zero number will be interpreted as True.
Example:
"abc" Like text1$ And "xyz" Like text2$
Returns -1 if text1$ is “abc” and at the same time text2$ is “xyz”, if one of the Boolean expressions is False then the expression will return 0.
Perform logical negation on a boolean expression, or get two's complement of an integer expression.
Returns:
Boolean; A signed integer, either -1 for a True statement, or 0 for a False statement. Any non-zero number will be interpreted as True.
Example:
Not(2>1)
As a negation of the true statement 2>1, Not (2>1) will return 0 as the negation of a True statement is False.
The bitwise Or operator allows you to combine two Boolean expressions. The statement will be true if one or another of the Boolean expressions are true.
Returns:
Boolean; A signed integer, either -1 for a True statement, or 0 for a False statement. Any non-zero number will be interpreted as True.
Example:
"abc" Like text1$ Or "xyz" Like text2$
returns -1 if either one or both linked expressions statements (“abc” Like text1$, “xyz” Like tex2t$) are True. Returns 0 if both Boolean expressions are False.
Send comments on this topic.
Click to return to www.cadcorp.com
© Copyright 2000-2017 Computer Aided Development Corporation Limited (Cadcorp).
