|"Be the Query Ninja" - Using a Sub-select to select all the objects within a circle |
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 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.
|"km" ||Kilometres |
|"mi" ||Miles |
|"m" ||Metres |
|"ft" ||Feet |
|"yd" ||Yards |
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.
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.
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 February 2014 issue.
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.
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 issue.
Do you have questions?
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.