There's more to experience when you log in!
This is the 13th article in the #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. This also means it can't be the last. I can't end on number 13. And I'm sure my friends in Asia would say that I can't end on number 14 either. So we need another two articles besides this one. Any ideas on topics, please let me know.
In this article, we'll look at the new file format: MBS, MapBasic Script.
As our software engineers were working on the SQL improvements and the new SQL Window, they saw a need for an improved file format.
We already have a file format for queries, the QRY file format. This allows you to save a query to a file and reload it from the SQL Select and Simple Select dialogs. The QRY file format is also used with Query Tables which basically is a tab file referencing one or more tables and a QRY file. The table(s) will get opened and the query in the QRY file will get executed when you open a Query Table.
We also have a workspace, the WOR file format, that stores all information related to a Map Project such as which tables are open, which windows are open, how are the layers setup and potentially also queries that you may have executed during your session.
And finally, we have the files related to MapBasic applications: the source code, the MB file format, the compiled MapBasic source code, the MBO file format and finally the compiled MapBasic executable, the MBX file format.
The need for the new MapBasic script format came as we started to see the SQL Window as way more than a replacement for the existing SQL Select and Simple Select dialog. The SQL Window allows you to do Select, Delete, Insert and Update statements and it allows you to create small MapBasic scripts that can be a combination of the four statements above and more or less any MapBasic function and statement.
We can go into the more technical details of the MapBasic Script file format in a later post. For now, I'll just say that you create a MapBasic Script file by saving your statements in the SQL Window into a file. The file is by default saved into the folder specified in your preferences for Saved Scripts. It's using the name that you give the script in the SQL Window.
Compared to the QRY file, the MapBasic Script format allows you to save more than just a Select statement. As I said above, you can use the SQL Window to create different types of scripts. All these different types can be saved into the MapBasic Script file format.
You can open the content of the MapBasic Script file in the SQL Window. It will either be presented in the list of Scripts that MapInfo Pro has found in the folder for your Saved Scripts, or you can use the Open Saved Script next to the dropdown to select I file that you want to open.
You can execute the MapBasic Script from the SQL Window by loading it into the text area and use the Run button. You can also execute the MapBasic Script from the SQL Dropdown which will hold your favorite scripts and the most recent used scripts.
And finally, you can execute the script using this statement:
Run Script "D:\SQL Scripts\Load Population Data.mbs"
Let's take a look at a practical example for the World around us. You have been asked to keep an eye on the Coronavirus situation. To do so, you decide to load the latest data that is published by Johns Hopkins. The data is posted here on Github. You can download the data as a zip file or set up a local copy of the repository with GitHub Desktop.
The data we are looking to use is in a CSV file that we can open in MapInfo Pro, we can create points from the data using the lat/long values assigned to each record and finally, we can create a thematic map based on the dataset too.
This data is updated daily so I'm sure that you will quickly get tired of doing this task manually. Instead, you can create a small script that will perform this task for you. The statements for the script can be copied from the MapBasic window if you open the MapBasic window when performing the task. You'll have to make some small adjustments to the code for it to work.
Here's my example on how this can look:
Register Table "D:\Covid19\Daily Reports\Today.csv" TYPE ASCII Delimiter 44 Titles Charset "WindowsLatin1" Into "D:\Covid19\Daily Reports\Today CSV.TAB"
Open Table "D:\Covid19\Daily Reports\Today CSV.TAB" Interactive
Commit Table Today_CSV As "D:\Covid19\Daily Reports\Covid Today.TAB" TYPE NATIVEX Charset "WindowsLatin1" Locale "da-DK" Interactive
Open Table "D:\Covid19\Daily Reports\Covid Today.TAB" Interactive
Close Table Covid_CSV Interactive
Create Map For Covid_Today CoordSys Earth Projection 1, 104
Set CoordSys Table Covid_Today
Update Covid_Today Set OBJ = CreatePoint(LONG_, LAT)Commit Table Covid_Today
Map From Covid_TodaySet Map Layer 1 Display Global Global Symbol (35,14708736,2,"MapInfo Symbols",0,0)
Set Map Zoom Entire Layer 1
shade 1 with Deaths ignore 0 graduated 0.0:0 10000:36 Symbol (35,16711680,36,"MapInfo Symbols",0,0) Symbol (35,255,36,"MapInfo Symbols",0,0) Vary size by "SQRT" # color 0 #
Set Legend Layer prev display on shades off symbols on lines off count off Title "Covid-19 Deaths" Font ("Arial",0,9,0) Subtitle auto Font ("Arial",0,8,0) ascending off ranges Font ("Arial",0,8,0) auto display off, auto display off, auto display off, auto display off , auto display on, auto display on, auto display on
Create Designer Legend From Window FrontWindow() Behind Default Frame Style "#" Font ("Arial",0,8,0) Frame From Layer 1
Set Map Layer 2 Display Of
I save this into a MapBasic script file and also mark it as a favorite. This means that I now can run the entire process by clicking on the MapBasic Script in the SQL Dropdown.
The new MapBasic script file format gives you the opportunity to create small scripts and easily run them from within MapInfo Pro. You could earlier do the same via a Workspace but you were missing the capabilities of creating the script directly inside MapInfo Pro which now is possible via the SQL Window.
I hope this gives you some ideas on how you can use the SQL Window to automate some of your data processing. As always, feel free to post any questions or comments in the Comment section below.