| || |
Be the Query Ninja
Using Geographic Functions to calculate area, density and more
Welcome to the latest instalment of "Be the Query Ninja". In the July issue we introduced a simple exampleof 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.
Zoom Layering allows you to control the visibility of each layer based on whether it is relevant for the current map window zoom. For example a detailed streets layer is best used when you are zoomed in close on the map. A generalised (i.e. not detailed or low resolution) boundary layer is best used when you are zoomed out.
- 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 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 centre 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
|roods ||"rood" || |
|acres ||"acre" || |
|hectares ||"hectare" || |
Previous articles on using the SQL Select capabilitiesExample 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 Professional!
- Label expressions - when choosing a column to label on, one of the options is to choose Expression.
- Table >Update Column
- Creating thematic maps - it is also possible to shade a layer based on an expression.
- Browse > Pick Fields
Here is a list of all the articles we have done on the SQL Select command so far...
July 2012 - Introducing the SQL Select command
August 2012 - Selecting all the records in a table that do not have a map object
September 2012 - Joining tables part 1 (relational joins)
October 2012 - Joining tables part 2 (geographic joins)
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.