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.
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.
|"km" ||kilometres |
|"mi" ||miles |
|"m" ||metres |
|"ft" ||feet |
|"yd" ||yards |
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.
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:
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.
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.
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.