Query Ninja: Using a Sub-select to select all the objects within a region

User-added image

Query Ninja: Sub-selecting part 3: Selecting all the objects within a region 
User-added imageThis 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. 

User-added image
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. 

User-added image 

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.

User-added image

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.

 User-added image

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. 

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.

UPDATED:  June 29, 2017