Selecting Objects with Queries

This topic explains how to select objects in drawings (or in drawing layers in maps) using queries. Queries can be used to select objects either automatically or manually using mouse-based selection commands within the query table.

Queries are written using SQL, which usually begins with the SELECT command. This meaning of "selection" is the traditional database meaning of picking out records and columns from database tables to construct a new table. It is a different meaning than the Manifold use of the word "selection" to mean choosing objects in drawings or pixels in images or otherwise choosing items in other components.

The normal use of queries is to generate a table display showing the results of the query. If the query includes the ID field in its results, query tables can automatically select the drawing objects corresponding to each row in the query table based upon the setting of the Automatically select query records option in the Tools - Options - Miscellaneous dialog. When this option is checked (the default setting) queries will automatically select objects in the drawing.

That is, with this option on querying a drawing will automatically deselect any selected objects and then select all objects returned by the query. This is equivalent to making a selection using Replace mode. When a query includes more than one drawing, all previously selected objects in all of the drawings will first be deselected and then objects from the query will be selected in the first drawing for which an ID column is found in the query.

If the Automatically select query records option is not checked then running a query will generate a tabular display of the query results, but the objects in that query will not automatically be selected. We can then use such a query-generated table to manually select objects in the drawing with interactive mouse commands much as we could use a drawing's table to select objects in the drawing.

Use the manual option if queries will be used to make incremental, interactive selections, for example using Add or Subtract selection modes with mouse selection within the query window to add the query's results to a previous selection or to subtract the query's results from a previous selection.

Query Selection in Drawings

There are three cases of queries to consider:

§ Queries using one drawing that result in a single ID column in the query table.

§ Queries using two or more drawings that result in multiple ID columns in the query table.

§ Queries joining columns from different tables.

Queries using One Drawing

If the Automatically select query records option is checked, queries that reference one drawing may be used to select objects in that drawing if they include the ID field in the query. If the automatic option is not checked, the query table can be used for interactive selection using mouse commands (clicking on records) just as selection operates with the drawing's table.

SELECT * FROM [Mexico_eg Drawing]

WHERE [Place_name] LIKE "BAJA%";

The above query, for example, will create a table from the example mexico_eg.mif drawing that finds all Mexican provinces with "BAJA" in their name. If the Automatically select query records option is checked the query will also select all objects that have "BAJA" in their name.

If the Automatically select query records option is not checked the query table will display but no objects will be selected. If desired we can select all of the objects in the query table with mouse selection commands. For example, we could click on the first record in the query table and SHIFT click on the last record in the query table to select all of the items in the query.

We can use either a drawing or the drawing's table in a query to exactly the same effect. For example, the above query could have been written…

SELECT * FROM [Mexico_eg Table]

WHERE [Place_name] LIKE "BAJA%";

…with exactly the same effect.

Objects are not selected by queries in Manifold unless the ID system field is selected by the query. The ID field is required either for automatic selection or for manual selection using mouse commands. For example, if we have a drawing of provinces that each has a name, the SQL fragment …

SELECT Name from [Provinces Table] WHERE…

… will create a table, but it will not select the objects associated with the records that appear in that table. In contrast, the fragment…

SELECT ID, Name from [Provinces Table] WHERE…

… will select the objects as well as creating the table.

Queries using Two Drawings

Queries that reference two drawings may be used to select objects in either drawing using the Identity command.

$images\sc_query_selection_01.gif$

Suppose, for example, that we have created a map with a drawing of points, called Points, as a layer above the Mexican boundaries map, which we have renamed Mexico for brevity.

SELECT * from Mexico, Points

WHERE Contains(Mexico.ID, Points.ID);

The above query selects records in both Mexico and Points for those cases where provinces in Mexico contain some of the points. To change which drawing's records are displayed in the query, choose the Identity command in the column context.

If the Automatically select query records option is checked, the query will select objects from the drawing (in this case Mexico) for which the first ID column appears.

Suppose we have two drawings, one called Points that contains points and one called Areas that contains areas. We select some areas in the Areas drawing and now we would like to use a query to select all points in the Points drawing that are inside the selected areas:

SELECT * FROM [Points], [Areas]

WHERE [Areas].[Selection (I)]

AND Contains([Areas].[ID],[Points].[ID]);

Note the use of the Selection (I) intrinsic field to use only selected areas in the query.

Queries Joining Columns from Different Tables

In current editions of Manifold, queries that join columns from tables not associated with drawings cannot be used to select objects in drawings. For example, a query formed with "Union D1, D2, T3" where D1 and D2 are drawings and T3 is a table could not be used to select objects in the drawings.

Selection Modes and Selection from Queries

Selection modes do not affect automatic selection when the Automatically select query records option is checked. Automatic selection always operates in Replace mode.

$images\btns_selmodes.gif$

The selection mode set affects selection only within the query window using interactive mouse commands.

For example, if we have Select Replace set (the default, seen above) any selections we make in the query window will replace the entire selection in the query window. This will change the selection in the referenced drawing window only to the extent that the objects in the drawing are represented in the query window.

Suppose that in the example seen above we have previously selected the province of Oaxaca in the southern part of Mexico.

$images\sc_query_selection_02.gif$

We can then run the following query to find all provinces in Mexico that contain one of the points in the Points layer.

SELECT DISTINCT Mexico.ID, Mexico.Place_name from Mexico, Points

WHERE Contains(Mexico.ID, Points.ID)

$images\btn_run_query.gif$ We can run this query by pressing the Run button in the project pane toolbar.

$images\sc_query_selection_03.gif$

The result will be a table listing the IDs and names of each province (seen with Identity set to the Mexico drawing).

$images\sc_query_selection_04.gif$

With the focus on the query table window we can select all the records in the query table by pressing CTRL-A or by choosing Edit - Select All.

$images\sc_query_selection_05.gif$

The result seen in the map is that all of the objects in the query table are selected. However, even though the selection mode is set to Select Replace the province of Oaxaca is still selected.

$images\sc_query_selection_06.gif$

Next, we can click on the record handle for the Durango province to select it. Because the selection mode is Select Replace, selecting Durango deselects the other records in the query table.

$images\sc_query_selection_07.gif$

We can see in the map that the province of Durango is selected while the other provinces in the query table are no longer selected.

$images\sc_query_selection_08.gif$

Finally, with the focus on the query table window we can choose Edit - Select Inverse to invert the selection so that all records except Durango are selected.

$images\sc_query_selection_09.gif$

In the map window we can see that Durango province has been deselected and all of the other provinces in the query table have been selected. As before, Oaxaca province is still selected.

Lookup Values and CStr and CAST

Invoking CStr on a lookup value or using CAST to convert the value to a string returns the descriptive name of the lookup value.

Example

We have a table T with a lookup column Region with values East and West. We want to select all records with the value of Region being East. We can do this with the following query:

SELECT * FROM [T] WHERE CStr([Region]) = "East";

Example

We have a drawing D and we want to select all areas it contains. We can do this with the following query:

SELECT * FROM [D] WHERE CAST([Type (I)] AS TEXT) = "Area";