MapInfo Pro has great support for running SQL queries. You can do this through various dialogs that allow you to build the query simply by selecting from dropdown values or you write more advanced queries by hand through the SQL Select dialog or the SQL Window.
Then you can combine your Select
statement with MapBasic statements to convert your Select
statement into a MapBasic script. We will investigate this today.
Today's article originated from a request on Precisely Ideas: Proximity Search created by @Caroline Hilton from our partner Pelican GeoGraphics Ltd. The base request was this: "Find objects which have an identical object (or even a different object) within a given distance, in the same table."
As Caroline points out in the request the problem lies in joining a table with itself. This is where the script shows its value. It allows you to combine a Select
statement with other statements such as saving a table into a copy.
The structure of my script looks like this:
- Save a copy of the input table
- Create buffers around the objects in the copy table
- Update a column in the copy table with a count of objects in the original table
- Select the records from the original table that are inside a buffer with a count higher than 1
Oh, and Caroline also suggested making the script flexible to allow the user to select the input table and set the distance and tolerance. Of course! We will use the variables in the SQL Window for this.
Happy #MapInfoMonday!
The Find Nearby Records Script
Caroline shared a sample dataset of stations created at 30 meters along roads. The issue is that at the road intersections points got quite close. She would like to find the points that are within 20 meters of other points to exclude these.
My final script ended up looking like this.
Let me take you through the script.
At the top, I define two variables to hold the table's name and the file holding the buffers around the original objects.
Next, you can see the query I used to create a copy of the input table but replacing the input objects with buffers. The Print
statement in the first line writes a comment in the Message window. This was added for timing purposes and can be removed. The object
keyword after the Buffer
expression tells MapInfo Pro to use the expression as the object in the resulting query. The variable sTab
is one of the variables allowing the user to pick what table to use. See more in the Variables section below. The fDistance
variable also allows the user to configure the script with a search distance.
I save the resulting query into a base table in the temp folder and open this table. I use the two variables, sTabFile
and sTabBuffer
, to hold the name of the file and the table.
I add a new column to the buffer table and update this with the count of objects from the original table that are inside the buffer. To get this Add Column
statement right, I used the Update Column dialog to run the statement with the MapBasic window open. After this, I could copy the statement from the MapBasic window.
Print Time(24) + " Adding Column to Buffer Table..."
Alter Table sTabBuffer
(Add COUNT_WITHIN Integer)
Print Time(24) + " Updating count in Buffer Table..."
Add Column sTabBuffer (COUNT_WITHIN)
From sTab Set To Count(*) Where within
Commit Table sTabBuffer
Finally, I can run a sub select query selecting all the records from the original table that are Within
an object in the buffer table where the COUNT_WITHIN
column has a value larger than the nTolerance
variable. I use the AggregateCombine()
spatial aggregate to combine all the objects into a single object in the sub select part of the query. This speeds up the query. The third variable used in the script is nTolerance
. This allows the user to set the tolerance of how many records should be within the given distance for them to get selected. The typical value would be one meaning that as soon as more than 1 record is within the distance, these records would get selected.
Print Time(24) + " Querying Table against Buffer Table..."
Select * From sTab
Where OBJ WithIn (
Select AggregateCombine(OBJ) From sTabBuffer
Where COUNT_WITHIN > nTolerance)
Close Table sTabBuffer
Print Time(24) + " All done..."
Variables
You can access the variables in a script from the Variable list in the SQL window. If the script already has variables defined they will show up here. You can also use the Edit Variable to open the Edit Variables dialog to add new or change existing variables.
The variables that you define can be of different types. I created variables of type Table, Float, and Integer. Each type makes the control for these variables behave differently in the Input dialog shown to the user.
These are the settings for the sTab
variable of type Table. The expression in the Values field controls what tables the user can select. The expression I used, allows the user to select normal (or seamless) tables.
When you are working on your script, you can execute the script via the Run button in the SQL Window to test the script as it expands.
It will show the Find Nearby Records dialog to let the user pick the input table and set the variable.
When you on the OK button, the script will run through the statements.
In the SQL Window, you can enable Display script result in a new browser window. This will open a browser window with the selection after running the script.
You can also remove this line from the script if you want to keep the table with the buffers open. Sometimes seeing these can be helpful.
Maybe the script could be improved in a way to ask the user if he wants to browse the result and keep the buffer table open. Any ideas on how this could be done?
If you mark the script as a favorite by clicking the small Star in the Scripts list, you can find it in the SQL dropdown menu and run it from there.
This is handy if you use the script often.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------