Today I'll take a stab at a question I have seen a couple of times lately: You can in several ways find records that do intersect but how do you find records in one table that don't intersect objects in another table?
This question often talks about the way we find joins between two tables using a spatial operator. You can in this way find records that in some way interact with objects in another table using one of the spatial operators Intersects, Within, or Contains. The problem is to locate the remaining records, that is those that do not interact. You can for example not create a join using a negated spatial operator, and if you could it would result in many joins as one object probably doesn't interact with far more objects than it does interact with.
The way I would solve this is by using a Sub-Select query instead of a Join query. The Sub-Select basically creates a list of values, or spatial objects, that you can compare the value or spatial object of your table against. And, you can even negate this comparison.
Where expression1 operator (Select expression2 From table2)
Where OBJ Within Any (Select OBJ From Buildings)
Where OBJ Intersects Any (Select OBJ From RoadworkZones)
Where RoadName In (Select RoadName From RoadSubSet Group By RoadName)
In this example, we have two tables. A table of addresses and a table of measurements taken nearby these addresses. Our problem is that these measurements only have the spatial relation to the addresses. There is no ID or other attribute that could link the measurements to the addresses.
I'm using the SQL Window to run the queries but you can also use the "classic" SQL Select dialog.
The big red dots are the measurements and the smaller orange dots are the addresses. Not every address has been measured as you can see.
Let's try to use a Sub-Select statement as discussed in the previous chapter to locate all the measurements that are "nearby" an address. The query would look like this:
From Address_Measurements As "m"
Where OBJ Within Any (Select CartesianBuffer(OBJ, 12, 1, "m") From Addresses)
Notice how the Sub-Select returns 1-meter buffers around the addresses. You can change the distance to a value that fits your tolerance and in this way modify the "search distance".
When I run the query, the resulting query only holds 8 records. This means that only 8 measurements are within a 1-meter distance of the addresses. On the lower-left side of the statusbar, you can see a small text displaying "Selected records: 8 of 164". It is the add-in WindowHelper that gives me this information about the current selection. Quite a handy way to quickly see the number of selected records compared to the total amount of records in the base table. You can download and install the add-in from the MapInfo Marketplace directly inside MapInfo Pro.
Anyway, this is a poor result so let us try to make the search distance a bit bigger. Let's try with 5 meters. As you can see in the image below, this gives a better result. We are now able to match 160 of the measurements to an address.
But this doesn't really answer our question: Which measurements do not intersect/interact with an address. These queries find the measurements that do interact with an address.
A quick way to find those measurements that don't interact with an address is to invert the selection. You do this by using the Invert Selection tool in the Selection group. It is located on the Table, Map, and Spatial tabs.
Click on Invert Selection and browser from the selection to see the result. Below you can see the result of inverting my initial query that found the measurements that interacted with an address within a 1-meter distance.
Another way to find the measurements that don't interact with any addresses is to negate that Where condition comparing the objects to the result of the Sub-Select:
This negated query statement looks like this:
Where Not OBJ Within Any (Select CartesianBuffer(OBJ, 12, 5, "m") From Addresses)
The trick lies in the line starting with Where. Notice the small Not word that I have inserted here? This basically means that it will revert the result of the following expression: True will become False, and False will become True.
Below you can see the result of this query using a 5-meter search distance. As you can see it returns the 4 measurements that aren't interacting with any addresses within this 5-meter distance.
I hope you have found this useful. If you have situations where this doesn't quite fit what you are trying to do, let us know in the comments and we can help you solve your situation too, I'm sure.
Also if you already have examples of where you have applied Sub-Select statements feel free to share these too.