Query Ninja: Sub-selecting Part 6: Selecting objects that intersect another object

User-added image

Query Ninja: Sub-selecting Part 6: Selecting objects that intersect another object

User-added imageThis is the sixth article on the topic of sub-selecting. It might be the last sub-selecting article too as I am running out of ideas! If you are just joining in now, search the Query Ninja area in the LI360 Community for the previous articles. 

This article will introduce using the Intersects operator (or rather a join condition) with a sub-select.

Introducing the Intersects operator
First a word about the Intersects join/operator keyword. The condition for this operator to be true is if two objects share at least one point. Note, it is also true if one object contains the entirety of another object. The MapInfo Pro Help includes information on the various geographic joins (Contains, Contains Entire, Within, Entirely Within and Intersects).

Example 1: Using Intersects in a Sub-select: 
Let's say you have a table of parcel or property boundaries. You have a building application on one of the properties that requires notification of all the properties that share a border with the applicant. Using a sub-select and the handy Intersects operator, it query the table and get a list. 

What makes this valuable is you only have to select a property boundary and then you can run the SQL. If you have to do this for a number of properties, it is quite fast. It is quicker and more reliable than making the selections by clicking on the polygons.

The first step is to select the object (in this example, the property or parcel) for which you want to select all the objects that intersect it. 

User-added image 

Here is the SQL Select statement needed to select all of the intersecting polygons.

User-added image 

Here is the result. 

User-added image

Note that the result includes the original polygon. Depending on your specific application, the result you require may or may not need to include the original. 

To run the query again, select another parcel. If needed, you can change the name of the result. 

Be sure to only select a single object. If more than one object is selected you will get the error message "Subquery returned more than one value".

This technique is handy when the object being selected and the intersecting objects are in the same table. If you are working with data from different tables you can do a join with the Intersects operator. We'll make that the subject of a future lesson.

Selecting the intersecting objects with a "fixed" starting object.
It is also possible to use an Intersects in a sub-select without needing to have a selection. To do this you need to have a value to identify the object. This technique might be useful if you need to check what is intersecting an object on a regular basis or you have a need to preserve a specific query to re-run in the future. You can set this query up and run it as needed.

In the map below the parcels have been labelled according to their ID number. 

User-added image

Below is the query syntax to select all of the parcels that intersect a parcel with a given ID number. 

User-added image 

As before, the end result includes the original parcel (parcel number 158 in this example). 

Result 2

When performing a subselect in this manner it is possible to exclude the original value by adding an extra condition after the subselect. 

User-added image 

Note that the result does not include Parcel 158. 

User-added image

Choice of workflows
The first technique requires you to click on the needed parcel and then run the query. The second technique requires you to enter a value in the SQL dialog box to specify the parcel. In both cases you may wish to enter a table name. If you do not require the original parcel in the end result, the second technique might be a quicker way to get the result. You can try both and see which works best for you.

Working with data from two different tables
Intersects can be used as a join condition between two tables. We will cover some techniques for taking advantage of this in a future article. 

Questions or suggestions?
If you have questions or suggestions for the Query Ninja, post them in the Discussion are of the Query Ninja in the LI360 Community

Article by Tom Probert, Editor of "The MapInfo Pro" journal 

When not writing articles for "The MapInfo Pro", Tom enjoys talking to MapInfo Pro users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.


UPDATED:  June 29, 2017