Query Ninja: Using Geographic functions to calculate area, density and more
Welcome to the latest installment of the "Be the Query Ninja" series. In a previous article we introduced a simple example of deriving new information from your existing data. In this issue we will expand upon this to use some geographic functions and work with some different types of data.
- Given a table of points, add columns to your table containing the X and Y coordinates.
- Given a table of regions, add the area of each region to your table.
- Given a table containing population and area, you can create a column containing the population density.
- Given a table of regions containing population (but not containing an area column) you can still calculate the population density!
- Given a table containing columns for the date a vehicle was stolen and the date the vehicle was recovered, calculate how many days elapsed.
The main concept
In this article we are just going to focus on the Select Columns part of the SQL Select dialog box. The main concept is that a column can be an expression. This means that math can be used on any combination of columns. In addition a number of functions can be used in, or as part of, a column expression. This article will introduce examples of a number of functions.
The example above is calculating the population density of postal areas. The Area() function is being used.
Learning more about the Functions in MapInfo Professional
The following examples take advantage of a number of functions. Very brief descriptions are included but you may wish to learn more. The MI Pro Help system can be very helpful. Search on "Functions" and one of the items returned is a good central list listing. See the screen shot below.
Example 1: Adding coordinates to your query
The following would create a query containing the customer's name and address along with their X and Y coordinates.
Select columns: Customer_name, Address, CentroidX(obj), CentroidY(obj)
Additional comments: CentroidX() and CentroidY() return the X and Y coordinates of an object, respectively. If the object is a region then the coordinates returned are roughly the center of the minimum bounding rectangle. In the case where this location is outside of the polygon itself (imagine a polygon in the shape of the letter 'C'), the centroid is moved to be within the polygon. If the object is a line then it is the coordinates of the mid point of the line.
Example 2: Add the Area and Perimeter of regions to your query
Given a table of regions, add the Area of the regions to your query. Consider a table containing postal areas.
Select columns: Postal_area, Area(obj, "sq km"), Perimeter(obj, "km")
Additional comments: Below are the area and distance units. To change the units returned by the calculation simply replace the abbreviation with the desired one from the table below.
|Area units||Abbreviation|| ||Distance units||Abbreviation|
|square miles||"sq mi"|| ||miles||"mi"|
|square kilometers||"sq km"|| ||kilometers||"km"|
|square inches||"sq in"|| ||inches||"in"|
|square feet||"sq ft"|| ||feet||"ft"|
|square survey feet||"sq survey ft"|| ||survey feet||"survey ft"|
|square yards||"sq yd"|| ||links||"li"|
|square millimeters||"sq mm"|| ||rods||"rd"|
|square centimeters||"sq cm"|| ||chains||"ch"|
|square meters||"sq m"|| ||yards||"yd"|
|square chains||"sq ch"|| ||millimeters||"mm"|
|square links||"sq li"|| ||centimeters||"cm"|
|square rods||"sq rd"|| ||meters||"m"|
|perches||"perch"|| ||nautical miles |
(1 nautical mile represents 1852
Example 3: Perform a density calculation
Given a table of regions and a number for each region, such as the population, level of sales, number of patients, etc, it is possible to calculate a density of these values. The very first screen shot in this article is demonstrating a population density calculation.
Select columns: Postal_area, Population/Area(obj, "sq mi")
Another tip: Renaming the columns
When using a column expression, the default name for the column will be the expression. This is usually not an easy to understand column name. It is very easy to give the column a different name. This is called an alias name.
To provide an alias name for a column simply provide the desired name in quotes after the expression.
Here are some examples:
Area(obj,"sq km") "Area_sq_km"
Population/Area(obj, "sq mi") "Density_sq_mi"
This is not just for SQL Select!
You have many opportunities to take advantage of column expressions in MapInfo Pro!
- Label expressions - when choosing a column to label on, one of the options is to choose Expression.
- The Update Column command
- Creating thematic maps - it is also possible to shade a layer based on an expression.
- The Pick Fields command (in Browser windows)
Want to learn more?
The Query Ninja section of the community has an array of articles for everyone from beginner to advanced. Click here to check out a list.
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.