Be the Query Ninja! Selecting objects within a Circle

MapInfo Pro™ Monthly Journal

Be the Query Ninja! Selecting objects within a Circle

MapInfo Professional has a CreateCircle() function that can be used in an SQL statement to select all of the points that are within a given radius of a location.

Practically speaking this is similar to creating a buffer or using the Radius Select tool but the process can be entirely automated, saved in a workspace and the selection re-created when the workspace is open.

So, if you find yourself having to perform repeated analysis of buffers around points then this article may help you to do things more quickly! Instead of having to point and click to run selections you can have the selections be run automatically.

The example today is selecting points that represent customers. The point of origin (i.e. the centre of the circle) will be the Pitney Bowes Software office in Windsor, U.K. 

User-added image
The background map in this screen shot is the Microsoft Bing Roads map. This is part of the MapInfo Premium Services offering.

Introducing The CreateCircle() function 

This is a MapBasic function and the name does adequately describe its purpose! The CreateCircle() function takes three parameters. They are the X coordinate, Y coordinate and the radius of the circle. Note that the CreateCircle() function does not have a parameter to determine the units. The units can be specified in the MapBasic window. 

Format for the CreateCircle() function:

CreateCircle(X coordinate, Y coordinate, radius) 

Setting the Distance Units

If you require the distance units for the CreateCircle() function to be something other than the default distance units to which MapInfo Professional is set, you can use these steps to set the default distance units of MapInfo Professional. 

Use the MapBasic window to change the distance units to kilometres.

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. Close the MapBasic window.

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

Note: The way we are using the CreateCircle() function does not actually draw a circle on your map. All the processing is done in the background

Setting the internal coordinate system

There is another potential "gotcha" when doing this. 

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 the Popular Visualization coordinate system. This is because the Microsoft Bing Roads layer is in the map. (A map window always adopts the coordinate system of any tile server layer that is in the map window.) 

So it is a very simple matter to set the internal coordinate system to this. Type the following into the MapBasic window.

Set Coordsys Table "BingRoads"

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

Create the Query

You will need the coordinates of your point of origin to put into your CreateCircle() function. If you are working with point objects you can double click on the desired point with the select tool. Another way is to change the bottom left corner of your map window to show the cursor location. (Click on the tray to do this.) 

For this example, we are using the coordinates of the Pitney Bowes Software office in Windsor. 

User-added image

Next we will create a query to select all of the customers from the WINDSOR_CUST table that are within 1 kilometre of the office of Pitney Bowes Software. The coordinates of the office 

1. From the Query menu, choose SQL Select.
2. Fill in the dialog box as follows: 

User-added image

3. Click OK.

The query is run.

Note that all of the processing happens in the background. The circle that was used to create the query does not appear in the map. 

In the map below, the customer points within 1 km of the Pitney Bowes Software office are selected. The browser displays some of the points. 

User-added image
Results from the selection of all customers within a 1 km circle of the Pitney Bowes Software office.

Re-using this query in a Workspace

One of the advantages of this technique is it provides a way to automatically re-run a query of objects within a circle. If you wish to save and reuse the query in a workspace then the two MapBasic statements discussed in this article should be added to the workspace. This will ensure that the query runs correctly.

Here are the first few lines of the workspace that created the map above. The text in blue contains the two lines you need to add in. These should be added in before the selection statement. 

!Version 1103
!Charset WindowsLatin1
Open Table "C:\...BingRoads.TAB" As BingRoads Interactive
Open Table "PB Software" As PB_Software Interactive
Open Table "WINDSOR_CUST" As WINDSOR_CUST Interactive

Set Distance Units "Km"
Set Coordsys Table "BingRoads"

Select * from WINDSOR_CUST where obj within CreateCircle ( - 67885.87 , 6706924.18 , 1 ) into Within_1_km noselect 

Map From WINDSOR_CUST,PB_Software,BingRoads 
  Position (0,0.0208333) Units "in"
  Width 7.19792 Units "in" Height 3.34375 Units "in" 

Is there more? 

You might be wondering, if I can use the CreateCircle() function, are there other functions that I can use in an SQL Select? 

The answer is yes and we will explore additional techniques in future articles. If you want to explore on your own one place to look is the MapBasic documentation. This is available from our Web site here

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.
UPDATED:  July 13, 2017