"Be the Query Ninja" - Sub-selecting Part 6: Selecting objects that intersect another object
| ||This 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, see the journal archive for the previous articles. The first one appeared in March 2014. |
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 Professional 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.
Here is the SQL Select statement needed to select all of the intersecting polygons.
Here is the result.
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.
Below is the query syntax to select all of the parcels that intersect a parcel with a given ID number.
As before, the end result includes the original parcel (parcel number 158 in this example).
When performing a subselect in this manner it is possible to exclude the original value by adding an extra condition after the subselect.
Note that the result does not include Parcel 158.
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 our LinkedIn Group.
Article by Tom Probert, Editor of "The MapInfo Professional" journal
When not writing articles for "The MapInfo Professional", Tom enjoys talking to MapInfo Professional users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.