|Query Ninja: Sub-selecting part 3: Selecting all the objects within a region |
|This is the third article on the topic of sub-selecting. If you are just joining in now, you can find the earlier article under the Query Ninja topic on the LI360 Community .|
For this month we will use a sub-select to select all of the points that fall within a region. This will be done by using the map objects so matching attribute data is not required. Think of this as the SQL equivalent of the Boundary Select tool.
This is useful where (1) you do not have the ability to do the selecting directly from the attribute data. For example you want to select all of the customers from a particular county but your customer table does not have a county field in it. This could also apply to various census or administrative boundaries which are not typically part of an address. And (2) it would be useful to automate this process. With some initial effort you could set this up to run for any number of boundaries.
Selecting all the points within a region
To set the scene, check out the map below.
Postarea map is Postmap UK © Collins Bartholomew.
Below is the SQL needed to select all of the points within one of the postal areas. For this example the Postarea called "DG" is being used.
Here is the result:
Selecting objects from more than one region
The syntax introduced thus far will only work for a single region object. If the requirement is to select all of the points within more than one of the postal areas then we need to introduce the Any() keyword. This keyword is similar in function to the In() keyword which was introduced in the earlier article "Query Ninja: An alternative to "OR"!" .
In this example, all of the customers within two regions are selected.
Two more examples
To expand on the idea of selecting all of the points within multiple regions, here are two more examples. These are written out as if they would appear in the MapBasic window.
- Using the In() operator within the Sub-select. This example takes advantage of the In() operator to make it easier to list multiple regions.
Where Customers.obj within Any
Where Postarea In("DG", "TD","NE","CA"))
- Using a condition in the Sub-select. In this example, assume the Postarea table has a column called Territory. This column assigns groups of postal areas into territories.
Where Customers.obj Within Any
Where Territory = "North")
Advantage over using a join and a filter
It is possible to filter a table in a similar way by joining the two tables together and then filtering for the result you need. Generally speaking, this technique will take longer to process. It also may require more work to set up, particularly the columns you wish to include.
Want to learn more!
There are more articles on sub-selecting, as well as many other techniques for using the SQL Select capabilities in MapInfo Pro in this Community. Click here to see a list of all the Query Ninja articles.
Do you have question?
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.