|"Be the Query Ninja" - Using a Sub-select to select all the objects within a region |
| ||This is the third article on the topic of sub-selecting. If you are just joining in now, here are the first and second articles. |
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 February 2014 issue .
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.
Select * from Customers where Customers.obj within Any ( select obj from Postarea where Postarea In("DG", "TD","NE","CA")) into Cust_within_Four_Postareas
- 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.
Select * from Customers where Customers.obj within Any ( Select obj from Postarea where Territory = "North") into Cust_in_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.
We are not yet done with sub-selecting. Next month, the Query Ninja will bring you a technique to select objects within a circle. This is analogous to automating the Radius Select tool.
Do you have question?
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.