MapInfo Pro

 View Only

MapInfo Monday: Querying Data with Select by Attribute in MapInfo Pro v2021

  • 1.  MapInfo Monday: Querying Data with Select by Attribute in MapInfo Pro v2021

    Posted 10-05-2021 02:03

    Happy #MapInfoMonday!

    You may wonder why you see a #MapInfoMonday post on a Tuesday. The explanation is straightforward. As this is the release week for MapInfo Pro v2021, we decided that it's #MapInfoMonday all week. Stay tuned for articles on the improvements made in MapInfo Pro also in the coming days here on this channel!

    With MapInfo Pro v2019, we gave our SQL support an overhaul, We added a brand new SQL Window that gave users a lot of additional capabilities when writing SQL statements, MapBasic statements, and also Python scripts.

    For those of you, not that familiar with the concepts and syntax of SQL language, we added a tool called Select by Location which helped you create and run queries to find objects in one table that were spatially related to objects on another table.

    With MapInfo Pro v2021, we heard your requests for giving you an easy way to query your tables based on the attribute data. We are happy to present the all-new Select by Attribute dialog.

    Accessing the Select by Attribute dialog

    You can access the Select by Attribute dialog from one of the SQL dropdowns that exists on the Table, Map, and Spatial tabs. We have made this tool the default tool for the SQL dropdown which means you can just click on the top part of the dropdown to access the dialog - unless you have used another tool from the dropdown.

    As you can see in the screenshot below, the keyboard shortcut for the tool is Ctrl+Shift+Z.

    Like any other tool in MapInfo Pro, you can add this to the Quick Access Toolbar for easy access.

    The elements of the Select by Attribute dialog

    When you launch the dialog for the first time, it will appear empty.

    If you only have one table open, this table will be preselected in the Table list and the Column Area will list all the columns from the table. If you have multiple tables open, you will have to select the table you want to query and then the Column Area will get populated with the columns.

    The Column Area will show up to 40 columns. If your table has more than 40 columns, the dialog will give indexed columns priority and make sure these are added. You can add more columns if you want by using the Add... button.

    You can also use the Add... button to add a column multiple times to your query, for example, if you want to find values in a column that are above one value and below another value.

    You can also use the Indexed and Empty toggle buttons to control which columns to show in the dialog. If you toggle Indexed, the dialog will only show columns that are indexed. If you toggle Empty it will only show the columns that are used in your current query.

    You can use All and Any to control how your conditions will be used in the query. If you use All which is the default, only records that meet all conditions will be selected. If you switch to Any, records that meet one or more of the conditions will get selected.

    At the bottom of the dialog, you can control how to output and view the result. This area is similar to what you find in the Select by Location dialog and in the SQL Window.

    You can specify a name for the resulting query using the Into table field.

    With the toggle controls below, you can specify to open the resulting query in a browser and/or into a map window. If you add the query to a map, you can also specify an override style color for the query. The last two controls let you zoom to the extent of the selection and control if you want to highlight the query result. Notice that the last option is only applicable if you have given the query a name.

    The Column Area

    In the Column Area, you build your query. This is where you construct the condition that you want your data to meet.

    The elements in the Operator list depend on the column type. In the image below, you can see the operators from four different column types, Integer, DateTime, String, and Object. As you can see the list of Operators varies across the column types with some overlap.

    Instead of just showing the actual operators, we have prefixed these with their meaning in the context of the column type. In (), we have added the actual operator.

    You have all the operators that you would expect to find like equals (=), greater than (>), less than (<), and so on. But we have also added a few more to help you build your queries in a fast and efficient way. Let's have a look at some of these.

    For numeric columns, you can quickly find records with values below average or above average.

    For Date, Time, and DateTime columns, you can use is empty to locate records with no temporal value in a column.

    For string or character columns, you can look for records where the value starts with, contains, or ends with a specific substring. You can also find records that match one or more values (one of). In fact, the one of (In) operator is available for most of the column types.

    For object columns, you can look for records that have objects or do not have objects. And you can use a condition to return records with objects of a certain type, like line, polyline, or point.

    The field to the right of the operators lets you browse for the values in the column to ensure you spell the values correctly. It will start by loading the first 1000 unique values from the column. If the column holds more than 1000 unique values, you can use the Refresh button to load more values. If you have entered a partial value in the field, the list will only show values that contain this partial string.

    Also note that for certain column types such as Date and DateTime, you have a button to the right of the value field that can help you pick the date you want to use.

    Just a couple of final words before I'll let you go and play with this new dialog.

    The dialog is resizable. You can resize it via the lower right corner and make it bigger or smaller to fit your preferences.

    The dialog stays on the screen when you hit the Run button to execute your query. This makes it easy to tweak your query until you are happy with the result. You can still use MapInfo Pro with the dialog on the screen.

    We hope you will find this new feature useful in your daily work when slicing and dicing your data. Feel free to share any feedback on this new dialog with us in the comments.

    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data