Selection in Manifold is choosing some objects in a map, or some records in a table, usually by interactive means like mouse clicks in the table or in the drawing. Labels can also be selected, as can tiles in images. This idea of selection is a different idea than the use of SELECT in SQL, even though the word selection is used to describe both. Selection as we mean it here is a simpler, more interactive thing, like clicking a file in Windows File Explorer to highlight it.
A table, or a drawing's table, must have a key field with a btree index (and not a btreedup or some other form of index) in it for selection to work. If the table does not have a btree index, selection will not be available. To add an index to a table that does not have an index, see the Add an Index to a Table topic.
Manifold provides four distinctive means of working with selections:
Point and Click Selection using the mouse or keyboard shortcuts, to select objects or records of interest in visual display windows or in tables.
Menu Selection / Keyboard Selection using the Edit pull down menu, or using analogous Windows-style keyboard shortcuts, like Ctrl-A to Select All.
Dialog Selection using the Select pane, which provides an interactive, pane based interface for making selections using simplified templates or arbitrarily complex expressions.
Selection using SQL Functions by writing SQL queries that use special functions that are analogous to the above three selection methods.
Selection tools can be used in combination to get exactly the right selection desired. For example, if we already have selected some records in a table using the mouse we can further modify the selection using the Select pane. We can also start with the Select pane and then modify the selection using interactive selection in tables or drawing windows.
This topic focuses mainly on visual selection using mouse and keyboard, with comments on SQL Function Selection at the end of this topic. For dialog selection using the Select pane, see the Select Pane topic.
For simple selections, the Edit main menu choice provides four selection commands, which are matched to keyboard shortcuts as follows:
Ctrl-A - Select everything in the active window (objects in drawings, labels in label components, frames in layouts, records in tables). Selects only within the active layer in maps.
Shift-Ctrl-A - Deselect everything in the active window (objects in drawings, labels in label components, frames in layouts, records in tables). Deselects only within the active layer in maps.
Select None Map
Deselects everything within all layers in a map. Equivalent to Select None for components other than maps.
Ctrl-I - Inverts the selection, deselecting what was selected and selecting what was not selected.
There are two ways to Select None from the keyboard: either do a Ctrl-A followed by a Ctrl-I or do a Shift-Ctrl-A. Which sequence is faster or more convenient depends on taste and dexterity.
The table below shows three selected records. Selected records are shown in red selection color.
If desired, we can change the selection color used by Manifold in the Tools - Options dialog. This allows us to use a color different than red selection color in cases where a map already uses very much red color or to provide a more discernible color in case of color blindness.
A selection in a table is simply a subset of records in that table that we have highlighted in red selection color to belong to that selection. It is a way of choosing records to be a part of a group, usually so we can do something with the records in that group. For example, in the table above we might want to increase the price of selected products by using the Transform pane to increase the Unit Price value of selected records by 10%. An analogy might be highlighting several files in Windows File Explorer so we can copy them all at once.
The Info pane will report the number of selected records in the table:
The Selected readout in the Info pane reports the number of selected records in a table window, as well as the number of selected records for the active layer in a map window when a map window has the focus. That is convenient in cases where selected records in a table or selected objects in a map might not be in view, to remind us we have made a selection.
Press the Selected button in the main toolbar or use View - Filter in the main menu to filter a table view to show only selected records:
The View - Filter command can be used to show only unselected records.
In more technical language, a selection is a set of records, which more precisely we call a selection set when discussing functions and programming. In casual conversation we simply call it a selection. It's easier to say "select a record" than to say "add a record to the selection set."
Selections are often used as "scratch pad" temporary sets of items of interest and are often used interactively. For example, we might browse a table of products where would like to increase or decrease the dealer discount on some items. While scrolling through the table we select those we want to change and then we can use the Transform pane to alter the discount percent only on those records in the selection.
Important: Selection only works in tables that have a btree index on one of the fields in the table. Most Manifold dataports that import drawings will automatically create an mfd_id field in the drawing's table and will create a btree index on that mfd_id field. Tables that are imported from databases usually already have an index in them, since DBMS people almost always will have one or more indexes in their tables for performance reasons.
Some dataports that import from unsophisticated formats, such as .txt or .csv,, to ensure consistency with the original data, will not create an mfd_id field and will not create a btree index. Such tables can be viewed, and we can SELECT from them using SQL, but we cannot edit them and we cannot use selection within them as discussed in this topic until we add a btree index. Lucky for us, a btree index is easy, really easy, to add. See the Adding an Index to a Table topic.
Selection in Manifold has the following characteristics:
One table - A selection is a subset of records in a specific table.
Live records - A selection is not a separate copy of records: it is a way of marking some records in the table as part of a group of interest. We can work with a selected record, such as editing the contents of the record, just as we could with any record. The red selection color will not interfere.
No modification - Selecting a record does not modify the record in any way. Manifold infrastructure maintains the "bookmark," as it were, that indicates a record is part of a particular selection with no modifications to the record or to the table.
Temporary - A selection is a temporary grouping that disappears when the data source in which it was made is closed. For components stored in a .map project file that means a selection persists until that project is closed. For example, if we make a selection in a drawing that is stored in our project, we can close that drawing and when we re-open it the selection will still be there. However, if we close the .map file (which is the data source for all the components that are stored in that .map project file) and then re-open that .map file the selection will no longer be around. If we want to "save" a selection, we can do so by creating a field in the table that is a boolean or other convenient data type and filling the field with a marker value, like true, into every selected record.
Requires a btree index - Selections only work in tables that have a btree index on at least one of the fields in the table. If we do not know if a table has a btree index we can open the table in a window and then choose Edit - Schema to take a look at the schema. The Info pane will also report the primary index field for the table, if any.
In addition to this topic, it is a good idea to read the Select pane topic.
Drawings, either in their own window or as layers in a map window, are just a different visual interface for displaying a table with records that contain vector geometry data. When we select objects in a drawing, which we can do interactively with the mouse, we are simply selecting records in the associated table using a different visual interface. When we make a selection in a drawing window the selection will also be shown in red selection color and the corresponding records in the drawing's table also will be shown in red selection color.
Whether we Ctrl-click objects in the drawing with a mouse or Ctrl-click on rows in a table, in both cases we are simply selecting records in a table. A drawing window is just a specialized way to display vector data from a table. Selecting objects in a drawing also selects the corresponding records in a table. Selecting records in a table automatically selects the corresponding objects in the drawing.
The following examples also appear in the Manifold Future - Shared Selections YouTube video.
Using a drawing of Mexico as an example, we select a record in the drawing's table.
Ctrl-click on a record in a table to select it.
The record is shown in red selection color and the corresponding object in the drawing window is also automatically selected and shown in red selection color.
If other drawings show that same table, they too will automatically show the selected object in red selection color, adjusted for opacity settings if the layer for that drawing is not at 100% opacity.
At left above we see a map that includes as a layer a drawing, Mexico 2, using the same Mexico Table. At right above we see a second map that has a third drawing, Mexico 3, that also is created using the same Mexico Table, but with different formatting. The Mexico 3 layer has 60% opacity applied in the Layers pane.
In every case, whenever a drawing is created from the same table, if a record in the table is selected, all objects in every drawing layer created from that record will also be selected.
Above at left we see the Mexico 2 drawing open in its own drawing window, zoomed in to the border between the selected area and the adjacent area. Above at right we see the Mexico 3 drawing open in a drawing window. In this example we have drawings created from the same table shown in five different windows. All of them have their selections synchronized to whatever is the selection in the table.
If we change the selection in the table, the selected objects in all of the drawings will also change. If we change the selection in any of the drawing layers that show the same layer, all of the other drawings will also have their selection changed to match, as will the table.
Using a drawing of Mexico as an example, we select an additional object in the drawing.
Ctrl-click on an object in a drawing to select it. We have Ctrl-clicked the province of Coahuila.
The object is shown in red selection color and the corresponding record in the table window is also automatically selected and shown in red selection color.
If we have any other windows open using drawings for that same table, those drawings and drawing layers will also be automatically updated.
Images, either in their own window or as layers in a map window, are just a different visual interface for displaying a table with records that contain raster tile data. When we select tiles in an image, which we can do interactively with the mouse, we are simply selecting records in the associated table using a different visual interface. When we make a selection in an image window the selection will also be shown in red selection color and the corresponding records in the drawing's table also will be shown in red selection color.
Whether we Ctrl-click tiles in the image with a mouse or Ctrl-click on rows in a table, in both cases we are simply selecting records in a table. An image window is just a specialized way to display tile data from a table. Selecting tiles in an image also selects the corresponding records in a table. Selecting records in a table automatically selects the corresponding tiles in the image.
Using a satellite photo image of the Pentagon as an example, we select a tile in the image. This is a very small image by Manifold standards, with just a few tiles to show how selection of tiles in images works.
In the table illustration above we have right-clicked the column header for the Tile field and chosen Style, to change the format for the Tile field from the default format that shows the size of the tile and the number of channels, to a format that reports the size of the tile as well as the data type of each channel and the number of channels.
Ctrl-click in an image to select the tile for that location.
The tile is shown in red selection color and the corresponding record in the table window is also automatically selected and shown in red selection color. Ctrl-click at a different location in the image to add that tile to the selection in the image.
The second tile is also shown in red selection color and the corresponding record in the table window is also automatically selected and shown in red selection color.
Important: Despite the identical "select" word that is used, selection in Manifold is a different notion than the use of SELECT statements within SQL queries.
SELECT and selection have differences:
Results table vs. Original table - A SELECT query in SQL produces a report, called a results table, of what the query generates. Simple queries might create a results table that looks very similar to the original table, but queries which do a lot of manipulation can produce results tables that do not look anything at all like the original table. Depending on what the query does, sometimes we might be able to use the results table as a viewport into those portions of the original table that the report shows, such as fields and records from the original table, and reach into and modify the data in those records. In contrast, selection is highlighting records in the original table that is open in a table window. Selection shows "live" records in the original table. They are highlighted in red color to indicate they belong to a group of interest but otherwise are just the same as all other records. We can edit them or modify them just like any other record in the table.
Multiple tables vs. One table - SELECT can generate results from one or more tables, choosing, creating or renaming fields on the fly and possibly creating entirely new records, for example based on aggregate functions. With selection we simply highlight existing records exactly as they are within one, original table.
Command Line vs. Interactive - SELECT is entirely a construct within SQL language, an activity like programming or issuing a series of text commands to a command prompt, while selection tends to be used interactively via point-and-click mouse actions within visual displays or within dialogs.
Abstract scale vs. Human scale - SELECT is the first choice for data sets so big no human can ever browse them interactively, while selection finds more use with smaller data sets that can be productively browsed interactively. We are not going to scroll through a table with a billion records to select a few here and there with a ctrl-click, but in a table with a few hundred records we might find that very convenient.
SELECT and selection have similarities:
Making a Choice - Both SELECT and selection are ways to choose subsets of data, to narrow down many records into those of interest.
Temporary - Both SELECT and selection are temporary. Unless saved in some way, if we close the results table the result of the SELECT goes away and if we close the window in which a selection was made the selection goes away as well. If desired, we can save the result of a SELECT into a table using SELECT INTO.
First Steps - Both SELECT and selection are often the first steps in subsequent doings, SELECT often feeding other actions within SQL and selection leading to combinations between selections or transformation of selected data using the Transform pane or other means.
Programmatic - Both SELECT and selection can be used from within queries and scripts, taking advantage of what they offer within automation. That is unusual for selection, which tends to be more of a casual, interactive facility.
Although SELECT tends to be the first choice for larger data sets, Manifold selection infrastructure can easily work with the same very large data sets as can Manifold SQL with SELECT. That capability is normally used in queries and scripts but the capacity for very large numbers of records to participate in selections pops up during interactive use when drawings can show large numbers of objects, and thus involve many records.
All data in Manifold is stored in a table somewhere. We can see what is in a table by opening a table window to display the records of that table in a classic row and column presentation. Visual displays like drawings, images, and drawing or image layers in maps are just a different way of seeing the data in a table. Drawings take data from the geometry field of a record in a table and show that geometry as an object such as a point, line or area. An image displays pixels contained in tile fields of records of a table.
When we select records in a table we automatically also select the corresponding features for those records in all drawings that take their geometry data from that table. When we select objects in a drawing, all we are doing is using a different user interface, the drawing to select records in the table. Select a feature in a drawing and we automatically select the record in the drawing's table from which that feature is drawn. Selecting a feature in one drawing not only selects the corresponding record in the drawing's table, it also selects that feature in all other drawings which use that table.
Queries are different. A query is one or more SQL statements that can be used to create a results table. When a results table from a query contains a geometry field, we can create a drawing based on that query. A selection in a results table is unique to that results table. It is not automatically also used by any window that may be created from the results table. Likewise, a selection in a drawing or image layer created from a results table is unique to that particular window. It is not automatically also used by the results table window or with any other window created from that same results table.
A variety of mouse, keyboard and menu controls work together to enable selection and de-selection of records in tables. Similar commands work in other grid-style displays in Manifold, such as the Layers pane, although in some cases the display requires clicks on the row handle and not in any cell of the record as table windows allow.
Menu selection commands for a table include Edit - Select None, Edit - Select All, Edit - Select Inverse and Edit - Select... to launch the Select dialog. Selection in general and the Select All, Select Inverse and Select None commands will only work with tables that have a btree index in the table.
Ctrl-click any cell in a record to toggle selection for that record. Ctrl-click selects an unselected record, and deselects a selected record.
Ctrl-Spacebar toggles the selection status of the record in which the cursor is located.
Ctrl-A selects all records.
Shift-Ctrl-A deselects all records.
Ctrl-I inverts the selection, deselecting those records that were selected and selecting those that were not selected.
To Select None from the keyboard, either do a Ctrl-A followed by a Ctrl-I or do a Shift-Ctrl-A. Which sequence is faster or more convenient depends on taste.
Table windows, including the results tables from queries in a command window, can export selected records to a choice of formats, such as CSV, GDB, GPKG, and others. When any records in a table window or results table are selected, the Export Selected command in the Edit main menu will be enabled. Choose Edit - Export Selected to export the selected records to a file.
This technique works in tables to select swaths of records, and also in panes that show rows or lists of items, such as the Layers pane with rows of layers or the Style pane with rows of intervals in thematic formatting, to select swathes of rows in the pane.
A Shift-Ctrl-click on a record applies the selection status from the cursor record to the clicked record and to all records in between. The cursor is the dotted box outline that marks the current cell in a table. The Shift-Ctrl-click works whether the current cell record is above or below the clicked record, to operate on all records in between.
If the cursor is on a selected record, then a Shift-Ctrl-click on some other record will select the clicked record and will also select all records between the clicked record and the record where the cursor was.
If the cursor is on a deslected record, then a Shift-Ctrl-click on some other record will deslect the clicked record and will also deslect all records between the clicked record and the record where the cursor was.
A Shift-Ctrl-Home or a Shift-Ctrl-End applies the selection status from the cursor record to the first (Home) record or to the last (End) record and to all records in between.
Ctrl-click a row (anywhere in the row) to select it.
Shift-Ctrl-click some other row.
Both rows that were clicked and all rows in between will be selected.
Click onto a deselected record to move the table cursor onto that row.
Shift-Ctrl-click a selected record within the swath of selected records.
All the last-clicked record and records between that and the deselected record that was initially clicked will be deselected.
For an illustrated, step-by-step example of selecting a range of records, see the Example: Select a Range of Records in a Table topic.
The Selected button in the main toolbar tells a table window to show only selected records or to show all records.
Suppose we start with a table where provinces with larger areas by square kilometers have been selected.
With the focus on the table window, to show only selected records we click the Selected button in the main toolbar.
Instantly, the table shows only selected records.
A filter icon in the upper left corner of the table grid appears to remind us that we are looking at a filtered view.
The Selected button in the main menu will be highlighted in light blue to indicate it is on.
We can click the Selected button to go back and forth between showing only selected records and all records.
Click the button again to show all records.
Instantly the table window shows all records.
The Selected button remembers status of open table windows on a per window basis. If we have the same table open in two different windows, one of the windows can have the Selected button clicked on to show only selected records, while the other window can have the Selected button clicked off to show all records.
For example, with the focus on the Mexico Table window, in the main menu we choose Window - New Window to open that same table in a second window.
That creates a new window that shows the same table, with the caption in the table window being Mexico Table (2) to indicate it is a second window showing the same table. The records shown as selected in the table of course will be the same in both windows, since selection status is a property of the component, not of a window.
With the focus on the Mexico Table (2) table window, we can click the Selected button to show only selected records in that window.
That allows us to have two different windows open at the same time, that both show the same table, where one of the windows shows only selected records and the other window shows all records.
The Selected button shows only selected records for the table window that has the focus at the moment the button is clicked. If we click the Selected button to show only selected records, and then change the selection in the table, for example, by using the Select pane to add more or fewer records to the selection in the table, the display in the table will not be updated to show only the new set selected records. The table window will continue to show those records listed by the prior click of the Selected button.
To update the display, we must click the Reapply Filter and Order button in the main toolbar. We must do this to get an updated display whether we increase or decrease the number of selected records. Alternatively, we can quickly click the Selected button twice to toggle Selected status off and then on again.
Why does the Selected button not automatically update on any changes? That is because automatically updating on any changes can become confusing in real life. For example, suppose we are working with a table where we have selected some records out of thousands, possibly as a result of multi-step workflow where we have invested significant time in cobbling up just the right selection we want. We press the Selected button to show only selected records, and then we proceed to work with those records.
If we accidentally de-selected one of those records and there was auto-update of a Selected display, that record would disappear from the table window and there would be no way of getting it back except for opening the entire table in a separate window, finding that one record out of thousands, and selecting it again. Having a manual update that requires a single click is a safety measure that avoids such problems. If we accidentally de-select a record in a table where Selected is on, the record is still visible in the table window, but is simply shown as deselected. We can ctrl-click it to select it again with no harm done, without losing the results of prior workflow.
The Copy to Selection command allows copying the value within a selected cell to that field in all selected records. Keyboard shortcut: Ctrl-F2
In the Products table we have selected all products with a Unit Price between 18 and 22 inclusive. We want to set the Unit Price for all selected records to 20. We Right-click on a cell that has 20 as the Unit Price.
In the context menu that pops open we choose Copy to Selection.
Instantly, the value 20 is copied into the Unit Price cells for all selected records.
The easiest way to copy a value in a selected row to that same field in all selected records is to right-click on the cell and to choose Copy to Selection.
That will copy the value in the cursor cell to the same field in all selected records.
We can also use a keyboard shortcut: Ctrl-F2 copies the value of the cell designated by the cursor to all selected records.
Suppose the table cursor is on a cell with a Unit Price of 18. Press Ctrl-F2, or right-click onto that cell and choose Copy to Selection.
That instantly copies the value of 18 into the Unit Price cell of all selected records.
The default mouse mode in drawings is to drag with the left mouse button to pan the window.
Adding the Ctrl key with the mouse makes selections, as follows:
Ctrl-click on an object to select that object.
Ctrl-drag draws a selection box to select all objects any part of which is within the box.
Ctrl-drag <release Ctrl> <finish dragging> draws a selection box to select all objects entirely within the box.
Adding the Shift key in selections means to deselect.
Shift-Ctrl-click on an object to deselect it.
Shift-Ctrl-drag draws a selection box to deselect all objects any part of which is within the box.
Shift-Ctrl-drag <release Ctrl> <finish dragging> draws a selection box to deselect all objects entirely within the box.
See the Example: Mouse Selection in a Drawing Window topic for examples.
Many of the same keyboard shortcut selection commands that work in tables work in various panes that show rows of items such as the Layers pane or interval rows in the Style pane when doing thematic formatting. Panes may require clicking on row handles for a record, unlike with table windows where we can click on any cell in a record.
Ctrl-click a row handle to toggle selection for that the row. Ctrl-click selects an unselected row and deselects a selected row.
Ctrl-Spacebar toggles the selection status of the row in which the cursor is located.
Ctrl-A selects all rows.
Shift-Ctrl-A deselects all rows.
Ctrl-I inverts the selection, deselecting those rows that were selected and selecting those that were not selected.
To Select None from the keyboard, either do a Ctrl-A followed by a Ctrl-I or do a Shift-Ctrl-A. Which sequence is faster or more convenient depends on taste.
The following illustrations show selection using a drawing that contains area objects. These moves are illustrated in the Manifold Future - Five Minutes for Fast Map Selections video as well. What seems complicated when described in words can be seen to be very quick and easy in a video.
Ctrl-click on an area to select t.
The area appears in red selection color. Choose Edit-Select None to deselect it, or simply Shift-Ctrl-click it to deselect it.
Ctrl-click and begin dragging to draw a selection box but while dragging we release the Ctrl key to keep dragging without the Ctrl key pressed down. When we press the Ctrl key while dragging the selection box border is thinner. When we release the Ctrl key the selection box border gets thicker. The thicker border is a visual mnemonic of a stronger wall, meaning only those objects which are entirely within the selection box will be selected. We call this move a Ctrl-click and (no Ctrl) drag.
Only one area was entirely within the selection box, so only that area is selected.
Ctrl-click and drag while keeping the Ctrl key pressed down. When we press the Ctrl key while dragging the selection box border is thinner. The thinner border is a visual mnemonic of a weaker wall, meaning any objects which touch the selection box or any region within the selection box will be selected. We call this move a Ctrl-click and Ctrl-drag.
There were four areas that touched the selection box, so all four areas are selected. We will now use the Shift modifier to deselect. Shift-Ctrl-click on an area to deselect it.
To deselect using a deselection box, we Shift-Ctrl-click and begin dragging to draw a deselection box but while dragging we release the Ctrl key to keep dragging without the Ctrl key pressed down but with the Shift key still pressed. When we release the Ctrl key the deselection box border gets thicker. The thicker border is a visual mnemonic of a stronger wall, meaning only those objects which are entirely within the deselection box will be deselected. The - minus sign by the cursor reminds us that the Shift key is a modifier to deselect. We call this move a Shift-Ctrl-click and Shift-(no Ctrl) drag.
Only one of the selected areas was entirely within the deselection box so only that area is deselected. To deselect any object that touches the region of the deselection box we Shift-Ctrl-click and then drag, keeping both the Shift key and the Ctrl key pressed while dragging, to draw a deselection box. When we press the Ctrl key while dragging the deselection box border is thinner. The thinner border is a visual mnemonic of a weaker wall, meaning any objects which touch the deselection box or any region within the deselection box will be deselected. We call this move a Shift-Ctrl-click and Shift-Ctrl-drag.
Only one of the selected areas touched any part of the deselection box so only that area is deselected.
With a little practise it is easy to remember that Ctrl means to select, adding Shift means to deselect, and lifting the Ctrl key while dragging means to strengthen the box so only what is entirely within will be affected.
Selection commands available in table windows work in settings like query results tables, such as the results table shown when a query is run interactively in a Command Window or when a query component is run.
Consider the results table seen above. We can Ctrl-click on one of the records to select that record.
Just as with a table window it becomes selected and shown using red selection background color. We can Ctrl-click another record to add it to the selection.
It, too, will be shown using red selection background color.
Filters also work.
For example, as seen above we can use View - Filter to show only Selected records.
After using View - Filter to clear the "selected only" filter, to show all records, we can click on the Unit Price column head to sort the results table by that column.
When any records in a results table are selected, with the focus on the Command Window the Export Selected command in the Edit main menu will be enabled. Choose Edit - Export Selected to export the selected records to popular file formats, such as CSV, GDB, GPKG, and others.
Selections are common between a table and all components that are based on that table. Selections for tables are said to be per-table. If we have a table called Mexico Table that has some records selected, any component based on that table, such as any drawing that is based on that table, will have the same records/objects selected. Queries, on the other hand, do not use the same selections. If we launch a query in two different windows, or if we create a component based on a query, than each such different window can have its own selection. Selections for queries are said to be per-window.
To programmatically pick out a selection in a table or a component based on a table, all we need know is the name of the table. To programmatically pick out a selection in a query or a component based on a query, we need to know the name of a table and also the name of the window in which we are interested. Since windows can have layers, we need to know both the window name and the layer name, if any.
Therefore, for each SQL function that deals with selections there are two versions of the function, one for tables that just takes a table name as an argument, such as the Selection function, and a version with "window" in the name for per-window selections that takes a table name as an argument and which also has a window name and a layer name as arguments, such as the SelectionWindow function.
For illustrated examples, see the SQL Example: Using Selection Query Functions topic.
Selection functions make it easy to answer the four most common questions asked about a selection in SQL:
What are the selected records in this table? - The Selection and SelectionWindow functions return a table of selected or unselected records in the table.
Are there any selected records in this table? - The SelectionIsEmpty and SelectionIsEmptyWindow functions return True or False depending if the selection for the table is empty.
Is the selection in this table inverted? - The SelectionIsInverted and SelectionIsInvertedWindow functions return True or False depending if the selection in the table is inverted (see below).
What are the values of key fields for selected records in this table? - The SelectionKeys and SelectionKeysWindow functions return a table of values for key fields for selected records.
Using the above functions in combination with other SQL functions we can do whatever we want with selections in tables and queries. Their syntax is as follows:
Selection(<table>, <selected>) : <table>
For tables and components based on tables, such as drawings. Takes the name of a table and a boolean <selected> flag, and returns a table of either selected or unselected records in the table depending on the value of the flag (True for selected, False for unselected). For drawings, the name of the drawing can be used as the name of the table, or the drawing's table name can be used as the name of the table. The examples below show both usages.
? CALL Selection([Table], True)
Generates a results table that shows all selected records in Table. Suppose we have a drawing named Mexico with some objects in the drawing selected.
? CALL Selection([Mexico], True)
Generates a results table that shows all selected records in that drawing's table.
We liked the ease with which we could not only read, but change the selected drawing objects in Release 8:
UPDATE [t] SET [f]=[f]+1 WHERE [Selection (I)]
This did not work for standalone tables not bound to drawings, but for drawing tables, it worked well.
Here is how we do that in Manifold Release 9:
UPDATE CALL Selection([t], TRUE) SET [f]=[f]+1;
For example, if we are working with the Products table in the nwind example database and we would like to increase the Units On Order by 10 for all selected records:
UPDATE CALL Selection([Products], TRUE)
SET [Units On Order] = [Units On Order] + 10;
This works for all tables, even those coming from other data sources. The only requirement is that the table must have a btree or btreenull index.
We can also do:
EXECUTE CALL Selection([Table], True);
...and edit values for the selected records in the result window.
The Selection function greatly simplifies code generated for the transforms. For example, without this function the code for running Normalize Topology on a selection would have to create temporary components. With this function, restricting a transform to a selection is a matter of substituting ... FROM [component] with ... FROM CALL Selection([component], True).
SelectionWindow(<table>,<windowName>, <layerName>, <selected>) : <table>
For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window. Can also be used for tables and components based on tables. Takes the names of a table, window and layer, and a boolean <selected> flag, and returns a table of either selected or unselected records in the table depending on the value of the flag (True for selected, False for unselected). The layer name should be an empty string for windows other than map windows.
In the example below the Mexico drawing is open in a drawing window called Mexico, and also participates as a layer in an open map window called Map. First, the map window:
? CALL SelectionWindow([Mexico], 'Map', 'Mexico', True)
and second, the drawing window:
? CALL SelectionWindow([Mexico Table],'Mexico', '', True)
SelectionIsEmpty(<table>) : <value>
Given a table or a component based on a table, such as a drawing, returns True if it does not contain a selection.
? SelectionIsEmpty([Mexico Table])
<windowName>, <layerName>) : <value>
For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window. Can also be used for tables and components based on tables. Takes the names of a table, window and layer, and returns True if it does not contain a selection.
? SelectionIsEmptyWindow([Mexico], 'Map', 'Mexico')
In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.
SelectionIsInverted(<table>) : <value>
Given a table or a component based on a table, such as a drawing, returns True if it lists selected records and False if it lists unselected records.
? SelectionIsInverted([Mexico Table])
<windowName>, <layerName>) : <value>
For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window. Can also be used for tables and components based on tables. Takes the names of a table, window and layer, and returns True if it lists selected records and False if it lists unselected records.
? SelectionIsInvertedWindow([Mexico], 'Map', 'Mexico')
In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.
SelectionKeys(<table>) : <table>
Given a table or a component based on a table, such as a drawing, returns a table of key field values for selected records.
? CALL SelectionKeys([Mexico Table])
In the above if Mexico Table uses a mfd_id field as a key field, and records with mfd_id of 3 and mfd_id of 5 are selected, the results table will have one column named mfd_id with two records in it, one with a value of 3 in that column and one with a value of 5.
<windowName>, <layerName>) : <table>
For queries and components based on queries, since a selection for a query or component based on a query is dependent upon the specific window. Can also be used for tables and components based on tables. Takes the names of a table, window and layer, and returns a table of key field values for selected records.
? CALL SelectionKeysWindow([Mexico], 'Map', 'Mexico')
In the above a map window called Map shows a drawing called Mexico in a layer called Mexico.
If the drawing's table uses a mfd_id field as a key field, and records with mfd_id of 3 and mfd_id of 5 are selected, the results table will have one column named mfd_id with two records in it, one with a value of 3 in that column and one with a value of 5.
A very common task when working with selections is to invert a selection, so that all the records which were selected become deselected and those which were not selected become selected. For example, if we want to select all records in a big table except for two or three records, it is quicker to select the two or three records we do not want and to then invert the selection.
That is such a common task that for efficiency with large data sets Manifold internally thinks of selection sets as having polarity: by default a selection contains those records which are selected while a selection which is inverted contains those records which are not selected. Whether or not a selection is inverted becomes a simple matter of flipping a bit that indicates if a selection lists selected records or if it lists unselected records.
The SelectionIsInverted and SelectionIsInvertedWindow functions tell us if a given selection set is inverted. When we want to invert a selection in a large table Manifold need not mess about setting some status bit on zillions of records but instead can simply instantly invert the polarity on the selection, from which all else can follow. That is especially efficient for mass operations such as Select All or Select None involving millions of records.
Tech tip: Have we mentioned that selection is available only for tables that have a btree index? See the Adding an Index to a Table topic.
Btree index required - Selection only works in tables that have a btree index on one of the fields in the table. Most dataports that import a table will automatically create a btree index on the mfd_id field but some dataports to ensure consistency with the original data do not create an mfd_id field and do not create a btree index. See the Notes below for adding an index.
Adding a btree index to a table - Easy! See the step by step procedure in the Adding an Index to a Table topic.
To add a btree index, open the table in table window and choose Edit - Schema. If a suitable field does not already exist, create one of type int64. For example, we could add a field called mfd_id or some other name, such as myindexedfield. Add a btree index and then add to the index the field created. A Manifold convention is to name indexes using the same name as their indexed field plus a _x postfix, so by convention if we created a field mfd_id we would name the index for that field mfd_id_x and if we created a field called myindexedfield we would name the index for that field myindexedfield_x.
Copy to Selection - Copy to Selection is so fast that normally it is instant; however, when doing big Copy to Selection in tables within data sources that are connected through slow communications links or which are slow data sources, there might be time for a progress dialog to appear, which reports progress of the operation and allows it to be canceled, the data source permitting. The total time required for Copy to Selection is logged in the Log Window.
Copy to Selection and Delete commands disabled - Table windows will disable Copy to Selection and Delete commands for values in a field that belongs to an index used by the table as a key.
Either form of SQL Function is OK for tables - Each of the SQL functions comes in two versions, one version for tables and components based on tables where we don't need to know the name of the window, and a "window" version where we supply the name of the window of interest as well, for use with queries and components based on queries where each individual window can have its own selection. If we like, we can use the "window" version of a selection SQL function with tables and components based on tables as well. When using a "window" version of the SQL function with a table the need to supply a window name argument as well as the table name is unnecessarily verbose when the "table" version of the function would work just as well using the name of the table alone. But if we don't mind being unnecessarily verbose we can use the "window" version of the function with tables as well.
Selection works against the entire table - When working with bigger tables where only part of the table might be in view, all selection methods, such as the Select pane or Edit menu selection commands such as Edit - Select Inverse, work against the entire table, and not just the records displayed using table fill strategies. See the Big Tables section of the Tables topic
Casual selections complement formal DBMS - The interactive, browsing nature of selection workflow has emerged from Manifold's GIS products and not from classic DBMS work flows, where enterprise class databases are often so large that work flows tend to be more formal and procedural. The essay on limits of table windows as browsers with very large data sets discusses the benefits in such cases of utilizing SQL and not expecting browsing tables through interactive windows will be as productive as it can be with smaller data sets.
In a sense, then, the selection capabilities within Manifold are more typical of a GIS than of a DBMS tool. But they are still highly useful for DBMS work where previews in table windows or drawings are valuable and where the dynamic workflow of forming selections using dialogs or expressions and then recombining them in boolean operations between different selections in different windows provides flexibility, efficiency and the ability to explore data in creative ways.
Manifold's dynamic updating of previews based on on-the-fly computation of expressions in the Select pane also provides a great way to learn more about how different operators and functions work, by trying out an expression and seeing what it does in the preview.
Selections can also be very helpful even with very large spatial data sets. Spatial data sets that may be too large to grasp in a table window presentation are often perfectly comprehensible when seen visually in a drawing. A drawing can represent millions of objects, and selecting subsets of those interactively with mouse moves in the drawing can be very fast and efficient even if we would never try to do the same interactively by clicking on records in a table window.
In that sense, selection within Manifold is more typical of a DBMS tool than a GIS because the internal infrastructure of Manifold supports the use of selection in even very large data sets. When we use the Select pane to create and to manipulate selections in tables those operations will work just as effectively even on very large tables as does Manifold SQL, with full application of all Manifold infrastructure for large data sets and fully effective computation, such as parallelism and GPGPU, on big data.
Adding an Index to a Table
Example: Select a Range of Records in a Table - We can select or deselect a range of records between any two records in a table window using fast and easy mouse commands.
Example: Mouse Selection in a Drawing Window - Using the mouse to select objects in a drawing window. This could be a drawing layer in a map or simply in a drawing window.
Example: Combining Selections using the Select Pane - How to use selections to solve a one-off problem casually that would be more difficult to solve rigorously with SQL. The objective in the example's scenario is to visit a point of tourist interest while travelling. We will use the Transform pane as well as the Select pane.
SQL Example: Using Selection Query Functions - How to manipulate selections within a query.
Example: Using Select Pane Templates - A sequence of actions using Select pane templates.
Example: Using Select Pane Expressions - Make selections by writing snippets of SQL into the Select pane Expression template.
Example: Construct JSON String using Select and Transform - Use the Select pane and the Transform pane to manually construct a JSON string using values from other fields in a table. Shows how we can manipulate text to build desired contents in a field.
Example: Add a Second Computed Geom Field to a Table - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields. This topic shows how to create a second geom that is a computed field based on the first geom. The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved. We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.
Manifold 9 - Copy to Selection - A short video showing how to copy the same, desired value to fields in all selected records. This is a great way to make changes to many records at once in a table. This technique is a life saver when making edits to tables. Works in the free Viewer, too.
Manifold Future - Shared Selections - Interactive selections in Manifold Future and Future Viewer provide a quick and easy way to zero in on data of interest. This video shows how windows share a selection so selecting records in a table will automatically select the corresponding objects in every drawing that shows that table. Likewise, making a selection in a drawing will automatically select the corresponding records in the table as well as in all other drawings and drawing layers that display that table.
Manifold Future - Five Minutes for Fast Map Selections - Reproduces examples in this topic in video form. Mouse selection moves can be much easier to understand when seen in a video than described in words in this documentation.