Happy #MapInfoMonday,
MapInfo Pro helps you see, find and analyze spatial relationships.
One query I often need to use is to find features in one layer that relates to the currently selected feature. This can be when I have created a drivetime zone and want to find the postal or administrative boundaries that intersect this drivetime zone.
In order to get exactly what I was looking for, I decided to write a small MapBasic script, in fact just a small query, that could get me what I wanted. The query basically compares the objects in the current selection to the objects in a table and selects those records that intersect.
All I have to do is to select the object I want to use as input.
And then run this custom query from the SQL dropdown.
The query will when run, ask me which table I want to query. This makes it very flexible as I can use the same query for any table.
The result will just get highlighted, that is show the records as selected in any map or browser. In the image below, I have turned off the visibility of the thematic as it was hard to see the selection against this purple thematic layer.
The Query
Let us dive a bit into the technicalities of the script or the query that I'm using.
Here's the query in the SQL Window which really has become my favorite tool when writing queries and small scripts inside MapInfo Pro. The SQL Window was added when we launched MapInfo Pro v2019.
And is the query as text in case you want to use it yourself.
Select * From sTabToQuery Where OBJ Intersects (Select AggregateCombine(obj) From Selection) Into SelectionLet's look a bit closer at the query.
The table I have specified to select from,
sTabToQuery, is a Variable. I have specified this using the Edit Variable dialog you can see below.
The type of the variable is
Table. This means that MapInfo Pro will prompt me to select a table from a list of tables. In the Values field, I have used an expression,
TableInfo(ScriptContext(SCRIPT_CONTEXT_TABLEALIAS), TAB_INFO_MAPPABLE), to limit the list of tables to only show mappable tables. I might also have added an additional condition that the table should be a native table or not be a raster table to avoid seeing raster tables in the list.
Notice two things in the
Where part of the query. The first is that I have specified
Intersect as the spatial operator. If you know you are comparing points with polygons, you can also use
Within/
Contains.
Within/
Contains do not work that well when comparing polygons with polygons or polylines. That's because these operators are comparing the centroid of one of the objects to the other objects. And centroids of a polygon or polyline don't give a good representation of the object when comparing these to other spatial objects.
The second thing to notice is that I use a subselect where I merge the objects from the current selection into one object using the spatial aggregate
AggregateCombine. Combining the selected objects will typically run very quickly. Especially if you are using typical-sized polygons such as small drivetime zones, administrative or postal boundaries. If you are using bigger polygons such as huge telco coverage polygons, you might see some slowness in the query.
A few things to keep in mind
Even though my query is sound, it may not give you exactly what you are looking for. My query returns any record that intersects the selection in any way. This means the returned records might not overlap the selection very much. Maybe they only share a single node.
Also note that if you use the result from this, you will use the full polygon returned and not just the part that overlaps the selection. The same goes for the attribute values. You may want to have a look at using the ProportionalOverlap function to calculate proportional values. If you know what columns your table has, you can include these proportional calculations directly in the query.
Instead of using a simple Intersect operator, you could also add more complex methods for deciding if you want to include a record in the resultset. This could be based not only on the fact that the two objects intersect but maybe also calculate either a proportional overlap or an actual overlapping area and add these to the conditions.
Anyway, I hope you found this useful. I use this small scrip quite often.------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------