Query Ninja: Sub-selecting part 4: Using a Sub-select to select all the objects within a circle

User-added image

Query Ninja: Using a Sub-select to select all the objects within a circle
User-added image

This is the fourth article on the topic of sub-selecting. If you are just joining in now, the first three articles have already covered quite a lot of ground. You can find them under the Query Ninja topic here on the LI360 Community.

Now, we will show you how to use the CreateCircle() function to select objects within a given radius. The CreateCircle function has been introduced in a previous article. The technique covered in the earlier article requires you to have the coordinates of the centre point of your circle. By using a sub-select you do not need to specify coordinates, instead you can refer to an existing object using a value from one of the fields in the table.

This technique is useful where you do not have the coordinates handy and you are looking to automate a process that runs selections within circles. Think of this as a way to automate the Radius Select tool. With some initial effort you could set this up to run for any number of objects. 

Some important background - introducing the CreateCircle() function

This may be a review for those of you who read the previous article on using the CreateCircle() function.

CreateCircle() is a MapBasic function that does exactly what it sounds like it does. Given three parameters (an X coordinate, Y coordinate and a radius value) the CreateCircle() function creates a circle at the given coordinates. 

Note that the CreateCircle() function does not have a parameter to determine the units of the radius. The units can be specified in the MapBasic window.

Here is the format and parameters for the CreateCircle() function: 

CreateCircle(X coordinate, Y coordinate, radius

Setting the Distance Units 

Use the MapBasic window to specify the distance units used by the CreateCircle() function. 

1. From the Window menu, choose Show MapBasic Window
2. Type the following into the MapBasic window:

Set Distance Units "Km"

This table includes some of the common distance unit abbreviations used in MapInfo Professional and MapBasic. 


3. Press ENTER.
4. You no longer need the MapBasic window, you can close it, if desired.

The new distance unit will now be used with the CreateCircle() function.

Setting the internal coordinate system

There is another potential "gotcha" when using the CreateCircle() function.

MapInfo Professional has an internal coordinate system that is used by MapBasic programs. This internal coordinate system is not necessarily the same as what is being used in your map window. 

To be safe you should set this internal coordinate system to match your map. There is a quick and easy way to do this. As with the distance units, you use the MapBasic window. 

The command is called Set CoordSys.

The easy trick here is you can "get" the coordinate system from any open table.

In the example used in this article, the coordinate system in the map is a Longitude/Latitude system for the USA. It is a very simple matter to set the internal coordinate system to one of the tables being used. Type the following into the MapBasic window:

Set Coordsys Table "CITY_125"

Note: If you forget to do this step, a possible result is your query will have no records selected or the wrong records selected. 

Example 01: Selecting all the customers within a circle

For the first example we will select all the customers within 50 miles of Chicago.


Here is the SQL Select dialog box. 

User-added image 

Notice that in the sub-select, the city is specified based on its name. It is not necessary to have to type in the necessary coordinates. Also note that the way we are using the CreateCircle() function does not result in a circle appearing on your map. All the processing of the query is done in memory.

User-added image 

Example 02: Selecting objects from more than one circle

The syntax introduced thus far will only work for a single circle. If the requirement is to select all of the points within a given radius of more than one of the cities then we need to use the Any() keyword. This keyword is similar in function to the In() keyword which was introduced in the article
"Query Ninja: An alternative to "OR"!".

In this example, all of the customers within 50 miles of either Chicago or New York are selected. Note that the end result is a single selection of customers who are near either city.

Here is the SQL written out as it would appear in the MapBasic window.

Select * 
  From USA_Customers 
  Where Obj Within Any 
    (Select CreateCircle( CentroidX(obj), CentroidY(obj) , 50) 
      From CITY_125 
      Where City = "Chicago" or City = "New York") 
  Into within_50_miles

Example 03: Using the Group by clause to avoid selecting records multiple times

If cities are near each other then customers within 50 miles of more than one city will appear in the selection multiple times! 

If you have a unique identifier in your table, then there is a way to manage this. You can group the data by the unique identifier. 

User-added image

Next article

We are not done yet with the technique of sub-selecting. You might be wondering about using a sub-select to select objects near a street, river or boundary of some type. We will explore using the MapBasic Buffer() function in the next article. 

Do you have questions?
If you have questions or suggestions for the Query Ninja, post them in the Discussion area 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