Pitney Bowes
MapInfo Pro™ Monthly Journal

Something a bit more advanced - Getting Started with the SQL Select dialog box 

adv I would classify last month's article on label expressions as being a pretty advanced topic. This month we will go with a little bit lighter topic and introduce the SQL Select dialog box. This is a big topic and we will cover more uses in future issues.

What is the SQL Select dialog box?

The SQL Select dialog box is a tool that you can use to work with your data in a number of different ways. For background, SQL stands for "Structured Query Language". Anyone familiar with using an SQL from a database system will easily get the concepts in MapInfo Professional's SQL. However, the SQL syntax in MapInfo Professional does not follow the ANSI standard SQL. 

What can you do with the SQL Select dialog box? 

LOTS! For example

  • Filter your data (so you look only at certain records)
  • Sort your data (ascending/descending and across multiple columns)
  • Join tables (work with data from more than one table at a time)
  • Summarise information (calculate sums, averages, counts)
  • Derive new information (create your own new columns from existing columns or from functions)
  • Format your data (currency signs, percent signs, commas, decimal points, etc)
  • Perform geographic calculations

Not for everyone or everything

Simple sorting and filtering can be done either in the Browser window in MapInfo Professional v11.5 or in the somewhat less intimidating Query > Select command. For this article, we will be sure to go a bit beyond these capabilities.

Before we get started...

Here is the table that is used in the examples that follow. This is part of the World sample data. Our overall goal is to create a query that includes the percentage of urban population for each country. 

browser
This Browser window screen shot is taken from MI Pro v11.5. Detail on the new capabilities will be the subject of an article in the next issue.

SQL Select dialog box overview

To bring up the SQL dialog box, click on Query > SQL Select.

Here is a quick overview:

sql

The first thing to tell you about this dialog box is that you do not have to fill in all of the empty boxes! 

Query example: Deriving new information from your existing columns. We will start of by learning how you can create new columns out of your existing data. We will make an attempt to follow this example through and in so doing, introduce a few different aspects of working with SQL queries. 

Notice that the World table includes a column containing the urban population of each country. It does not include a column indicating the percentage of people living in the urban area. For this first example we will create a simple Browser window with the name of the country, the population, the urban popluation and the percentage of urban population. We will name this query World_pct_urban.

The "trick" to understanding this is simply that in the Select columns box, a column can be an expression. In the dialog box below, note the last column (underlined) calculates the percentage of urban population. 

sql

Note that the expression can be given an easy to understand column name. This is done by adding the desired name in quotes to the end of the expression. (An extra tip is that this can be done to rename any column, not just those formed of expressions.)

Also note that the query has been given a name. This is not mandatory. Leaving the default "Selection" results in each successive query being named Query1, Query2, etc.

The following is the result from the first example. Note that we have some incomplete data. Many of the countries have a figure of zero for the urban population. We will exclude these in the next example. 

result

Next: Sort and filter the results 

We will make two changes to this query. We will sort the results in descending order such that the countries with the highest percentage of urban population will be listed first. For the purpose of this example we will assume that an urban population of zero represents missing data and we will exclude these records from the result.

setup

The following is the result. According to the data in this table, Israel has the highest percentage of urban population at nearly 90%

result

Next: Apply formatting

We will make one more adjustment to this example. Note that the percentages are displayed to four decimal places. Also note that there is no percent sign displayed. We can use the Format$() function to control the display format of numbers.

To make this easier to see and understand, here is the column expression with the formatting added:

Format$((Pop_Urban / Pop_1994) * 100, "0.#\%") "Pct_urban"

Here is the screen shot of the dialog box. 

setup

Finally, here is a screen shot of the result. 

result

Rounding instead of formatting

If you would like the numbers rounded off and are not concerned with having a percent sign in the display you can use the Round() function instead of the Format$() function. 

However, with the Round() function, the decimal points may not all line up. This is not an issue if you round off to the nearest whole number. (A difference with the Format$() function is that you can use it to pad leading or trailing zeros.)

Here is the column expression using Round() instead of Format$()

Round((Pop_Urban / Pop_1994) * 100, 1) "Pct_urban"

And here is the result... 

result

Last words

The SQL Select dialog box is a big topic. Heck, the Format$() function is a big topic in its own right and there are many other functions you might want to use! 

If you wish to learn the details of any of the functions then the MapInfo Professional Help is a good place for you to look. If you want the details on the Format$() you can look it up in the index. Also there is a help topic called "Using Functions in Expressions" with info on lots of functions. 

You can learn more about the SQL Select capabilities in the MapInfo Professional documentation as well. Here is a link.

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.