Advanced Topics in Selection

Selection in multiple clauses

It is possible to select objects in a number of clauses which are evaluated one after another. The clauses should be separated with a semi-colon e.g. x>10000; y>20000; ground_level > 15.5

Once you have more than one clause, the ‘test’ button on the SQL dialog will no longer tell you how many objects will be selected. Instead, the ‘test’ button will confirm that the query has the correct syntax.

Each clause will be executed in turn, and the objects for which the expression in the clause is true will be added to the selection.

In this case it would be possible to write the query equivalently as x>10000 OR y>20000 OR ground_level > 15.5

As you will see, however, there are more complex things that can be done with multiple clauses that cannot be reframed as a single query.

Overriding the table name

The first example of something that can be done with multiple clauses that cannot be done with a single query is the selection of objects in multiple tables. Whilst an SQL query is for a particular table or group of tables e.g. Node, Pipe, All Nodes, All Links. This is referred to as the 'default' table, and this paper will refer to it as A table, even though it is possible to select things from more than one tables using 'All Nodes', 'All Links' etc.

It is possible to override that table by explicitly naming the table or tables in the clause. This is done by using the 'SELECT FROM' keyword e.g. if the default table is Node and you want to select all conduits with width greater than 100 mm you should say SELECT FROM Conduit WHERE width > 100.

In the unlikely event that you wish to select all conduits you can omit the keyword WHERE and the expression after it and simply say SELECT FROM Conduit. If the table name you wish to use contains spaces in it, it is necessary to put square brackets before and after the name e.g. SELECT FROM [All Links] WHERE width > 100. It is in fact possible to enclose any table name within square brackets so you can in fact say SELECT FROM [Conduit] WHERE width > 100.

Overriding the default selection behaviour

The check-box on the SQL dialog determines whether the query is applied to all objects or to the currently selected objects.

If the check box is unchecked then the query will be applied to all objects of the appropriate type, if it is checked then the query will only be applied to the currently selected objects of that type. This is referred to as the 'default selection behaviour'.

This behaviour can be overridden by using the keywords ALL or SELECTED. ALL will override the behaviour of the check-box to make the query be run for all objects of the appropriate type, whereas SELECTED will override the behaviour of the check-box to make the query be run for only the selected objects of the appropriate type.

If you are using the default table then you can override the behaviour by saying SELECT ALL WHERE followed by the expression or SELECT SELECTED WHERE followed by the expression e.g. SELECT ALL WHERE x > 10000, SELECT SELECTED WHERE y > 20000.

If you are overriding the table name as described above, you should add the keyword ALL or SELECTED between the keywords SELECT and FROM e.g. SELECT ALL FROM [All Nodes] WHERE x > 20000, SELECT SELECTED FROM Conduit WHERE width > 200.

In the unlikely event you wish to select all objects in the default table you can say SELECT ALL. Similarly if you want to select all objects of another type you can say SELECT ALL FROM followed by the table name e.g. SELECT ALL FROM Conduit.

Deselection

In addition to selecting objects, it is possible to deselect objects i.e. make objects that were selected unselected. This is done by using the keyword DESELECT, usually followed by the keyword WHERE followed by an expression e.g. DESELECT WHERE x > 10000. Any expression that can be used to select objects can be used to deselect objects.

It is possible to override the default table as described above by saying DESELECT FROM followed by the table name, usually followed by the keyword WHERE followed by an expression e.g. DESELECT FROM [All Links] WHERE width > 200.

If you wish to deselect all objects from the default table you can say DESELECT. Similarly if you want to deselect all objects from another table you can say DESELECT FROM followed by the table name e.g. DESELECT ALL FROM Conduit.

It is in fact possible to use the extra keywords ALL and SELECTED as described above but in practice they are not required, since deselecting all objects and deselecting selected objects have the same effect.

Clearing the selection

As well as selecting and deselecting objects it is possible to clear the entire selection. This is done using the keywords CLEAR SELECTION on their own as a separate clause. This has a different effect from saying DESELECT or DESELECT FROM followed by a table name as described above, since they remove objects of a particular type or types from the selection whereas CLEAR SELECTION clears the selection completely.

Selecting or deselecting all objects in a table

It is possible to select all objects in the default table by saying SELECT. This also works on multiple tables if the default table is something like 'all nodes' or 'all links'.

There are numerous other ways of selecting all objects which rely on the fact that when the expression is run, all objects for which the expression is true will be selected

e.g.

1, true, 'anystring'

As described above it is possible to select all objects in a table other than the default table by saying SELECT FROM followed by the table name e.g. SELECT FROM [All Nodes].

These queries will only change the selection if the default selection behaviour is to select all objects rather than to apply the query to the current selection, otherwise you are simply selecting all the objects that are already selected.

If you want to explicitly override the default selection behaviour you should say SELECT ALL or SELECT ALL followed by the table name.

As described above it is also possible to deselect all objects in the default table or another table.