Query Ninja: Selecting all of the objects of a certain type

User-added image
User-added imageQuery Ninja: Selecting all of the objects of a certain type

The Query Ninja is back with a technique to select all of the objects of a certain type, (points, lines, regions, etc).

This can be helpful in a number of ways. One common use is to ensure consistency within a large table. For example, let's say you have a field in a table that denotes the area of each region. If you find a value of zero in this column, it could either mean the area was not included for that row or that particular object is a point or line, instead of being a region.

Selecting all of the objects of a certain type

To select all of the objects of a certain type, you use the Str$() function and apply it to the map object. It is very easy to do.

Below is a screen shot with the SQL Select dialog box. The example here is we are checking to see if any objects in the World table are points. 

User-added image

Explanation - what is Str$(Obj)? 

Str$() is a function that will take an input and change it into a set of characters. For example a number can be treated as text, perhaps to append values instead of adding them. 

Obj is a reference to the Object column of the table. 

So in short, Str$(obj) returns a text descriptor of the object type. 

Object types: 

Any of the following object types may be used in the query: 

"Point", "Multipoint", "Line", "Polyline", "Region", "Collection", "Rectangle", "Rounded Rectangle", "Arc", "Ellipse", or "Text". 

For example: This will select all of the point objects in a table:
Str$(obj) = "Point"

Select all of the objects that are not of a specific type: 

Let's say you have a table that you are expecting to be all regions and you want to see if any objects are not a region. You can use the not equal to operator. 
Str$(obj) <> "Region"

What if there is no object? 

Note: Str$(obj) will return a null string ( "" ) if there is no object associated with a row. To select all rows in a table that have no associated map object (for example, all ungeocoded rows after a geocoding operation). 

You can use this condition: Str$(obj)="" to select all rows without an object. However, there is a different method that will usually be faster. 
Select * 
  From <your table> 
  Where Not OBJ

The Query Ninja section of the journal includes an article on selecting all the records that do not have an object.

Need the basics on using the SQL capabilities in MapInfo Pro? 

An extensive set of articles on the querying capabilities of MapInfo Pro are available. Check out the Query Ninja topic.  Here is a link to the full list of topics in the Community.


Questions, comments, criticisms, suggestions? 

We welcome all input on this article. Or on the journal. Or if you have questions. A good place to start a discussion is here 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 28, 2017