MapInfo Pro

MapInfo Monday: Finding records that don't intersect

  • 1.  MapInfo Monday: Finding records that don't intersect

    Posted 12-13-2021 10:16

    Happy #MapInfoMonday,

    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.

    Sub-Select Query Statement

    Let us start by inspecting the structure of the Sub-Select before applying it. As I wrote above the basics of the Sub-Select is to return one or more values that you can compare values in a column against.

    For performance reasons, it's recommended to limit the list return by the Sub-Select to a smaller list when possible. This can for instance be done by grouping the results to ensure that each value only appears once in the list.

    Another thing to be aware of is that you can be sure the Sub-Select only returns one value, you can use other operators than if you aren't sure about the number of returned values. If only one value is returned, you can use operators that I would use when comparing one column to another column or object.

    If I'm not sure about the number of returned values, I have to assume that a list of values will be returned and therefore I need to apply operators that can be used to evaluate a list of values. These are Any, All, and In.

    The operator In can be used to determine if one value exists in a list of values. You will often use this with attribute values such as string and numeric values.

    The operators Any and All defines a subset and allows you to test if a given condition is TRUE for any of the values or for all the values in the subset. They can be used for alphanumeric values as well as spatial operators.

    In our example, we will be using the Any operator to see if the measurements objects is within any of the buffer objects returned by the Sub-Select.

    The basic Sub-Select query looks like this:
    Select * 
    From table1
    Where expression1 operator (Select expression2 From table2)

    • table1 is a table or query to select from
    • expression1 is a column expression such as a column in table1 or the spatial object from table1
    • operator is an operator, potentially combined with or exclusively an operator that works on a list
    • expression2 is another expression that returns values similar to the values in expression1
    • table2 is the table to compare values from table1 to determine there is a match or not.
    A couple of examples of such Sub-Select statements:

    Finding addresses that lie inside a building polygon:
    Select *
    From Addresses
    Where OBJ Within Any (Select OBJ From Buildings)

    Finding roads that intersect an area with roadwork:
    Select *
    From Roads
    Where OBJ Intersects Any (Select OBJ From RoadworkZones)

    Finding addresses with a road name in a subset of roads. Note the use of Group By:
    Select *
    From Addresses
    Where RoadName In (Select RoadName From RoadSubSet Group By RoadName)

    In the example above I use an existing column or the spatial objects but the Sub-Select statement also allows you to create derived values on the fly by combining multiple columns into a combined string or by using object functions to create new spatial objects. In the example below, we will use a Buffer function to create derived spatial objects.

    An Example

    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:

    Select *
    From Address_Measurements As "m"
    Where OBJ Within Any (Select CartesianBuffer(OBJ, 12, 1, "m") From Addresses)
    Into Selection

    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:

    Select *
    From Address_Measurements As "m"
    Where Not OBJ Within Any (Select CartesianBuffer(OBJ, 12, 5, "m") From Addresses)
    Into Selection

    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.

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