MapInfo Pro

Happy #MapInfoMonday to you all!

Today, I'll show how you can use the SQL Window to automate some of the jobs you tend to do over and over again. Instead of making the automation very static, we'll make it possible for the user to customize the inputs for the automated script using the Variables of the SQL Window.

Running the automated script

First, let's have a look at how it looks when you run this final automated script.

We'll load the script into the SQL Window and run it via the Run button. You can also launch the script from the SQL dropdown on the Table, Map, or Spatial tabs.​

When the script is executed, you will initially be prompted to fill in the values for the variables we use in the script. This is where the script gets dynamic and could be used to load different files with the same structure.

I have chosen to ask the user to select the file to load, the symbol to use when creating points, and what map to add the final table into. You could extend this to also ask where to save the resulting table and maybe let the user decide what coordinate systems to use.

When you hit the OK button, the script starts executing the MapBasic statements it holds.

As I have added some Print statements with a timestamp to the script, you can see these in the Message window. This gives you some hints on how long this script takes to run.

And finally, the resulting table is added to the map you chose to add it into.

Creating the Variables

It does of course help if you already are familiar with MapBasic. If you are new to MapBasic, you can get a good look at the statements required by opening the MapBasic window inside MapInfo Pro and then use the interface to run the statements you want to automate. The statements will now get captured in the MapBasic window and you have a good starting point for your automated script.

In this example, we are also harnessing the power of the Variable support of the SQL Window. This easily lets you ask the user for input that can be used in the script afterward. From the Variables dropdown in the SQL Window select Edit Variables....

We are adding three different variable types. Let me explain these to you one at a time.

First, we want the user to select a file to be loaded into an extended native tab file. We set the Variable Type to File which will present the user with the name of the selected file and a button that lets the user browse to an existing file. The Prompt is shown in the dialog, the Help Text is shown as a tooltip when you place your mouse on the control. I haven't specified a Default Value but you can insert the path and filename of the typical file used with this script. In that way, the user doesn't have to browse and select the file each time. Finally, I set the extension of the file using the Properties field.
 
The second variable is the symbol to use when creating points after loading the file. Set the Variable Type to Symbol. This time I set the Default Value to a fixed symbol string. You can also use the current symbol set in MapInfo Pro by writing this in the Default Value field: CurrentSymbol().

Finally, the third variable is to let the user pick which map to add the result to. Use the Window as your Variable Type and set the Default Value to FrontWindow(). In this way, the current active window will be the default. For the Values, we need to specify that we only want to use the window of type 1 (Mapper Windows). To do so, we use this expression in the Values field: WindowInfo(ScriptContext(SCRIPT_CONTEXT_WINDOWID), WIN_INFO_TYPE)=1. It will for all the open windows check if it is of type 1. If that's the case, the window will be added to the list. Also, note that I via the Properties allow the user to not select any map window from the list. This also makes the script run even though no mapper windows currently are open.

Constructing the Script

That was the easy part. The next step is to write the script and here refer to the variables. Here's the script I'm using.

Dim fileTempTab As String
Dim fileTab As String
Dim tabPOI As String
fileTempTab = TempFileName$("")
fileTab = Left$(fileInput, Len(fileInput) - 3) + "tab"
Print Time(24) + " Loading Text File..."
Register Table fileInput TYPE ASCII Delimiter 124 Titles Charset "UTF-8" Into fileTempTab
Open Table fileTempTab Hide
tabPOI = PathToTableName$(fileTempTab)
Print Time(24) + " Saving into NativeX Tab File..."
Commit Table tabPOI As fileTab TYPE NATIVEX CharSet "UTF-8"
Close Table tabPOI
Open Table fileTab
tabPOI = PathToTableName$(fileTab)
Print Time(24) + " Creating Points..."
Create Map For tabPOI CoordSys Earth Projection 1, 104
Set Style Symbol Str$(symPoint)
Set CoordSys Table tabPOI
Set Table tabPOI FastEdit On Undo Off
Update tabPOI
Set OBJ = CreatePOint(LONGITUDE, LATITUDE)
Commit Table tabPOI
Run Command IIf(nMapID=0, "Map From " + tabPOI, "")
End Program Condition(nMapID = 0)
Add Map Window nMapID Layer tabPOI

The script creates a temporary tab file pointing at the input text file. This temporary table is then opened into MapInfo Pro, and a copy of the table is being saved to the location of the input text file. The temporary table is closed and the copy is opened.

The new table is made mappable using a Lat/Long based coordinate system, the symbol style is changed to the symbol chosen by the user, and points are created for the new table using the columns LONGITUDE and LATITUDE.

Finally, I use some small tricks to determine how to present the new table after saving all changes to it.

nMapID holds the ID of the map window to add the new table into. If this variable is 0, no map window was selected, and therefore the table can't get added to an existing map. Instead, I want to open a new map window with this table.

This line opens a new map window if the value of nMapID is 0:
Run Command IIf(nMapID=0, "Map From " + tabPOI, "")

This line quits the scrip if the variable nMapID is 0:
End Program Condition(nMapID = 0)
This prevents the script from running the final line of code as there is no map window to add the new table to.

I hope this post has given you some ideas on how to use the SQL Window to create dynamic automated scripts.

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