VERIFIED SOLUTION i

Query Ninja: Sub-selecting Part 5: Using the Buffer() function in a sub-select

User-added image
Query Ninja: Using the Buffer() function in a sub-select
 
User-added imageThis is the fifth article on the topic of sub-selecting (although you don't have to go through these articles in order). You can find the earlier articles in the Query Ninja area of the LI360 Community.

Now, we will show you how to use the Buffer() function to select objects near a line. The technique is quite similar to using the CreateCircle function but the Buffer() function can be used with any type of object.

Introducing the Buffer() function

The Buffer() function creates a region object. You can call this function from the MapBasic window in MapInfo Pro or from an SQL Select.

The Buffer() function takes four parameters (pieces of information).

Bufferinput object, resolution, width, unit_name )

Input object is a reference to an object in a table. In our example this will be one or more of the highways from the US_HIWAY table. 
Resolution is the smoothness of the buffer. The maximum value this can be is 500. 
Width - this is the radius or size of the buffer. 
Unit name - this is the distance units ("mi", "km", "m", etc). This applies to the radius.

More detailed documentation on the Buffer() function (and all of the MapBasic functions) is available from the MapBasic Reference manual.

A nice thing about the Buffer() function, as compared to the CreateCircle() function is that it does include the distance units as a parameter. You can use this with point objects instead of the CreateCircle() function, if desired.

Setting the internal coordinate system

If the information in this section seems familiar that is because it appeared in the previous article as well!

MapInfo Pro 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 "US_HIWAY"

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

Selecting all of the customers within a buffer region

For our example we have a table of hypothetical customers. We are going to select all of the customers within 20 miles of the highway known as I 90 (Interstate 90). For reference, the highway in question is in blue in the map below. 

User-added image
The blue highway in the map is I 90. Note that some of the highways have joint names so there are actually five records that need to be included.

A side issue to achieving this is we need to ensure we select all of the records that include "I 90" in them. Note in the screen shot above that some of the highways have joint highway numbers. To ensure we use the entirety of I 90 we can use the Like operator to do the selection.

Here is the SQL Select dialog box. 

User-added image 

Here is the same query as above, in text format.

Select * 
  From USA_Customers 
  Where Obj Within ANY 
    (Select Buffer(US_HIWAY.obj, 100, 20, "mi" )
      From US_HIWAY 
      Where US_HIWAY.Highway Like "%I 90%") 
  Group by ID_Acct 
  Into Buffer_I90

And here are some of the results...

User-added image

A note about the Like operator

The Like operator allows a query to select from a character column if it contains a string of specific characters anywhere in the column. In this example this is used to select all of the records that contain "I 90" in the Highway field. The "wildcard" character used for this is the % (percent) symbol.

US_HIWAY.Highway Like "%I 90%"

And, once again, using the Group by clause to avoid selecting records multiple times

We went through the same process using the CreateCircle() function in the Sub-selecting Part 4 article. If a customer point object happens to be near more than one of the selected segments, it will appear in the resulting selection more than once! This is managed by grouping the results on a unique column in the data. This can happen where the segments join. Their buffer regions will overlap.

An added point that was not made in the above mentioned article is to remember that if you use a Group by clause then the resulting table will not have any map objects.

Do you have questions?

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