Selection in Manifold is choosing a subset of records in a table. Selected records are shown in red selection color. The table below shows three selected records.
A selection in a table is simply a subset of records in that table that we have chosen to belong to that selection. It is a way of choosing records to be a part of a group, often done 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 dialog to increase the Unit Price value of selected records by 10%.
Formally a selection is a set of records, which in more precise language as used for discussing functions and programming we call a selection set. However, in casual conversation we simply call it a selection. It's easier to casually say "select a record" than "add a record to the selection set."
Important: 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. Some dataports, to ensure consistency with the original data, do not create an mfd_id field and do not create a btree index. 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 one table.
Live records - A selection is not a separate copy of records: it is a way of marking the original, "live" 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 won't 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.
Multiple Selections OK - There can be more than one selection at the same time for a given table. Each window that shows a given table can have its own selection that is different from other selections for that table in other windows. Scripts and queries can also make selections for a given table, without opening the table in a window.
Combine Selections in the Same Table - If there is more than one selection for a given table, for example, in different windows for that table, we can use the Select dialog to combine those selections, adding two selections, subtracting one from the other, intersecting selections and so on. Such combinations work only for different selections on the same table.
Temporary - A selection is a temporary grouping that disappears when the window in which it was made is closed. If we want to save a selection, we can do so using the Select dialog. Saving a selection to a table modifies that table and the records it contains by creating a new field that marks selected records as belonging to the saved selection set.
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 Dialog topic.
Drawing windows are just a different visual interface for displaying a table with records that contain 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, by selecting objects with a mouse in the drawing instead of clicking on rows in a table. A drawing window is just a specialized form of table window, and like any table window that is open for the same table, it too can have its own selection for that table.
When we make a selection in a drawing window the selection will also be shown in red selection color, but if we open the drawing's table we will not automatically see the same selection shown in the table window. Each window open for a table maintains its own selection, if any, and the selection made in one window does not automatically transfer to other windows for that same table. If we want to the same records to be selected in a table window as were selected in a drawing window for that table, we can use the Select dialog to instantly transfer that same selection to the table window as well.
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 are related notions but they have fundamental differences:
A report vs. Actual table - SELECT displays a results table that is a like a view-only report, which we can look at but which we cannot reach into to directly modify the fields in records that it shows. Selection shows "live" records in the original table. They are displayed in a different 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 synthesizes its result table from possibly multiple tables, choosing, creating or renaming fields on the fly and possibly creating entirely new records, for example based on aggregate functions. Selection simply chooses 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 dialogs or using point-and-click mouse actions within visual displays.
Abstract vs. Human scale - SELECT is the first choice for data sets so big no human can ever browse them while selection finds more use with smaller data sets that can be productively browsed interactively.
SELECT and selection have similarities:
Making a Choice - Both SELECT and selection are ways to choose subsets of data, to narrow down our interest.
Temporary - Both SELECT and selection are temporary. Unless saved in some way (SELECT with SELECT INTO or selections saved using the Select dialog) 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.
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 dialog or other means.
Programmatic - Both SELECT and selection can be used from within queries and scripts, taking advantage of what they offer within automation.
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.
Manifold selections are unique to the specific windows in which they are made. We can have the same table or drawing appear in multiple windows with different selections in each of those multiple windows. Note that a different layer in a map is like a different window. The same table can be the source for different layers in a map and each of those layers could have a different selection. The Select dialog can be used to transfer selections between different windows and it can be used to combine selections made in different windows.
Manifold provides two distinctive means of working with selections:
Visual Selection using the mouse, to select objects or records of interest in visual display windows or in tables.
Dialog Selection using the Select dialog, which provides an interactive, dialog based interface for making selections using simplified templates or arbitrarily complex expressions, and which also allows us to save selections, combine saved and new selections and to transfer selections between windows.
Selection color by default is red: records in tables with a light red background or objects in drawings rendered in red are selected.
Preview color by default is blue. Dialog selection will preview what records would be selected if the current expression is used by showing those records with blue background. In drawing windows, objects in the process of being edited or which are in some sort of not-yet-committed selection process are also previewed in blue.
Selections in tables will often encounter a situation where some records have already been selected in red color but in addition some records are being previewed in blue color as part of a selection process underway. In cases where the same record is already selected in red color but also the system wants to show that it would be selected by the process underway by rendering it in blue, the resulting color is a blend of red and blue.
This effect might be confusing when a table is first seen with a mix of records shown in red, blue, blended violet and white backgrounds. New users might look at that blended color and think "Hmm... looks blue to me..." but the more experienced user will rapidly learn to recognize it as a blend between red and blue that indicates a selected record which also is being previewed. The blended color instantly indicates the intersection of the existing selection and what the previewed action would select.
That is also an effect to keep in mind right after we apply a selection action in the Select dialog, when just after we apply the command, say, by pressing the Replace Selection command button, instead of seeing the previewed records in the table turn from blue to red they all appear in blended color. What is going on is that until we move on in our workflow since the Select dialog is still set to whatever action that made that selection it is still previewing the effect of that action in blue. When we apply that action to select those records they immediately become both selected and also will still be previewed, resulting in the blended color. The moment we move on to doing something else in the Select dialog, say, by changing the expression or by clicking on a different template or tab, the blue color preview overlay, if any, will change or go away and we will see the selected records in red selection color.
The above color effects merit close attention by new users: experienced users rely on them as essential visual cues while beginners who do not learn the basics often find them confusing. If we spend a few minutes getting our heads around what colors convey we can use those visual cues for more rapid, error-free and effective selections, especially when combining selections.
Most 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 dialog.
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 dialog to alter the discount percent only on those records in the selection.
Manifold provides many ways to use selections and to combine them. For example, in a table of possible locations for renting a new warehouse we might select records for warehouses with low rental rates. In a drawing that shows the same locations as points we might use the mouse to select warehouses in particularly appealing locations, such as especially near highway interchanges. We could then combine the selection in the drawing with the selection in the table by intersecting the two to create a selection of records in the table that show only those warehouses with appealing rental rates in attractive locations.
We could, of course, always write an SQL query to do the same. But sometimes it is much easier to choose some points with a mouse to select them in a drawing than it is to write a one-off spatial SQL query that captures algorithmically what the eye sees instantly, such as, "close to a convenient highway interchange." Selections in Manifold, therefore, can provide a level of casual, easily-learned interaction that in some cases can be much quicker and easier than writing the equivalent SQL query, while still being very powerful.
A variety of mouse, keyboard and menu controls work together to enable selection and de-selection of records in tables. 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-A selects all records.
Ctrl-I inverts the selection, deselecting those records that were selected and selecting those that were not selected.
Ctrl-click the row handle for a record to toggle selection for that the record. Ctrl-click selects an unselected record, and deselects a selected record.
Ctrl-click and drag anywhere in a table to draw a selection box that adds to the selection all records any part of which falls within the box.
Alt-Ctrl-click and drag anywhere in a table to draw a deselection box that removes from the selection all records any part of which falls within the box.
To Select None from the keyboard, do a Ctrl-A followed by a Ctrl-I.
See the Example: Using a Selection Box in a Table topic for an example, as well as the topics cited in the See Also section at the end of this topic.
The default mouse mode in drawings is to click and drag 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-click and drag draws a selection box to select all objects entirely within the box.
Shift-Ctrl-click and drag draws a selection box to select all objects any part of which is within the box.
Adding the Alt key in selections means to deselect.
Alt-Ctrl-click on an object to deselect it.
Alt-Ctrl-click and drag draws a selection box to deselect any selected objects entirely within the box.
Alt-Shift-Ctrl-click and drag draws a selection box to deselect all objects any part of which is within the box.
See the Example: Mouse Selection in a Drawing Window topic for examples.
The following illustrations show selection using a drawing that contains area objects.
Ctrl-click on an area to select t.
The area appears in red selection color. Choose Edit-Select None to deselect it, or simply Alt-Ctrl-click it to deselect it.
Ctrl-click and drag to draw a selection box. All object which are entirely within the selection box will be selected.
Only one area was entirely within the selection box, so only that area is selected.
The Shift key modifies the selection so that when we draw a selection box with Shift-Ctrl-click and drag, any object that touches the selection box will be selected.
There were four areas that touched the selection box, so all four areas are selected. We will now use the Alt modifier to deselect. Alt-Ctrl-click on an area to deselect it.
Alt-Ctrl-click and drag draws a deselection box, so that all objects that are entirely within the box are deselected.
Adding the Shift modifier means that any object that touches the box is affected. Alt-Shift-Ctrl-click and drag will deselect any object that touches any part of the deselection box that is drawn.
With a little practise it is easy to remember that Ctrl means to select, Shift increases the action to include anything that touches the box, and Alt means to deselect.
Manifold keeps track of selections using a number for each selection set. The selection set number will be unique for each table. The Info pane will report the selection set number for whatever window has the focus, or we could use the SelectionForWindow query function to ask the query engine to tell us the selection set number for a specified window.
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 set contains those records which are selected while a selection set which is inverted contains those records which are not selected. Whether or not a selection set is inverted becomes a simple matter of whether the index key for a record that is in the selection set means it is a selected record or an unselected record.
The SelectionIsInverted query function tells 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 set, from which all else can follow. That is especially efficient for mass operations such as Select All or Select None involving millions of records.
Query functions serve us when we know or would like to know aspects of selections such as the selection set number for a given window, whether a selection set is empty, whether it is inverted and so on. In general whatever we would like to know about a selection, there's a query function for that, as follows:
Selection(<table>, <selectionSet>, <selected>) : <table>
The Selection query function takes a table, the number of a selection set and a boolean TRUE or FALSE to return a table that lists either the selected records or those records not selected.
SelectionForWindow(<windowName>, <layerName>) : <selectionSet>
SelectionForWindow takes the name of a map window and a layer name and returns the selection set number for that window. For windows other than map windows it takes the name of the window and an empty string '' (two single-quote characters) meaning "no layer" and returns the selection set number.
SelectionIsEmpty(<selectionSet>) : <value>
SelectionIsEmpty returns TRUE if the selection set does not contain any selection keys, that is, if there is no selection.
SelectionIsInverted(<selectionSet>) : <value>
SelectionIsInverted returns TRUE if the selection set is inverted, that is, if the selection keys list records that are not selected. It returns FALSE if the selection set is not inverted, the default situation, meaning that selection keys point at selected records.
SelectionKeys(<table>, <selectionSet>) : <table>
SelectionKeys provides the table's index key values for records that are members of a selection set. The polarity of the selection set (either default or inverted) determines whether those keys point at records which are selected or not selected.
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.
Casual selections complement formal DBMS - The interactive, browsing nature of selection as it is often used in practise is a style of workflow that 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 dialog 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 open the Select dialog to start forming and manipulating 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.
Example: Using a Selection Box in a Table - In tables we can use the mouse to select records by drawing a selection box. Ctrl-click and drag anywhere in the table to select all records with any part in the selection box. Using the Alt key makes that a deselect: Alt-Ctrl click and drag anywhere in the table to deselect records with any part of the record in the deselection box.
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 Dialog - 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 mouse selection as well as the Select dialog.
SQL Example: Using Selection Query Functions - How to manipulate selections within a query.
Example: Using the Select Dialog Template Tab - A sequence of actions using Select dialog templates.
Example: Using the Select Dialog Saved Tab - How to save selections and then use them later.
Example: Using the Select Dialog Window Tab - How to transfer selections between windows.
Example: Multiple Windows with Multiple Select Dialogs - Open multiple windows for the same table and open a Select dialog for each. This shows how multiple Select dialogs providing multiple selections and multiple previews for the same table can be opened and used at the same time.
Example: Construct JSON String using Select and Transform - Use the Select and Transform dialogs 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.