In this week's #MapInfoMonday, we will inspect a new feature that we are adding to the upcoming MapInfo Pro v2019.3 that we will release later this week: Variables in the SQL Window.
Until now when you build a query or a small script in the SQL Window it hasn't been that flexible. You couldn't ask the user for input for the script or query, and so you would either have to change the script when you needed it to run using a different table, or you needed to create multiple versions of the script, one for each table.
Now we introduce variables in the SQL Window. This gives you the ability to add placeholders for values that might change each time you run the script or query. To get the values to use, MapInfo Pro will create a dialog asking the end user for input for all the variables in your script or query. Suddenly your scripts and queries have become way more flexible.
Let's take a look which also will give you a better understanding of what this means.
In this example, I have two tables: one with points, and another with polygons, here hexagons. I want to count the number of points inside each polygon/hexagon and create a thematic map based on these numbers. I can do this using Update Column and then create my thematic using the Add Theme option. But if I need to do this often, I can create a small script that wraps it all into one process. As the name of the two tables might change, I'll ask the user to select the two tables to use. That's where the variables come into play.
Here's my map with the points and polygons/hexagons.
And on the right side you can see the SQL Window with my script. The script uses a Select statement to join the two tables, the resulting query is then added to my map and the script creates a thematic layer based on the calculated number of points inside each hexagon. I have used MapInfo Pro to build the statements and then copied them from the MapBasic window.
I'm only using two variables in this example; one for each table. You can see them in the highlighted line below: tabHexagons and tabPoints.
You can see the available variables in the Variables dropdown where you also access the Edit Variables dialog to create and modify variables.
The Edit Variables dialog lets you create as many variables as you want. Remember only to create those that you actually need. MapInfo Pro will will ask for input for all the variables in the list, even those that you aren't using in your script.
Below you can see the definition of the tabHexagons variable. The tabPoints variables is very similar.
The type of variable here is Table. This will let the user pick from all the currently open tables. But I have decided to help the user a bit. I have limited the tables shown in the list to those where the dominant object type is polygons (4) for my tabHexagons variable and points (2) for my tabPoints variable. That's the condition you see in the field Values. You can use this to control the list of tables shown. Often you will do this using one or more expressions using the TableInfo() function as below.
I have only used the type Table in my example but as you can see here, there is a lot of other types to pick from. We will cover more of these in other posts later.
Now let's get to using the script and how the end user will be prompted. When you run the script, a dialog will popup asking the user for some input. The look of this dialog depends on the variable types and the number of variables.
In this example, the dialog only asks for two tables using two dropdowns limited to tables with mostly polygons and tables with mostly points.
The user selects the two tables to use and hits OK to continue. Now the script knows which tables to use and is able to execute the actual work. The final results looks like this. If you had another table with smaller hexagons, you could quickly run the script using this table too and get a different result.
In this example I used the variables in a Select statement, but you can also use the variables in an Update statement or any type of script for that matter.
How do you in general find the new SQL Window we added to MapInfo Pro v2019? Do you think the support for variables will make it more useful to you?
#MapInfoMonday is your weekly little tip to help you get more from your MapInfo Pro.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------