|Be the Query Ninja: How to calculate distances|
This installment of Be the Query Ninja will introduce you to the Distance() function. As the name implies, this function will calculate the distance between two points. This can be done to an entire table (or two joined tables) at once.
The Distance() function requires you to supply it with five pieces of information. As an aside, people who wear lab coats, write MapInfo Pro documentation or who work in universities call these pieces of information the parameters of the function.
Here is a plain language description of these five parameters. The parameters are between the parenthesis ().
Distance (starting X coordinate , starting Y coordinate , ending X coordinate , ending Y coordinate , the units you want the distance calculated in)
Example 1: A table with two pairs of coordinates in it.
For this first example, the table has two pairs of coordinates in it. This makes using the Distance() function very easy.
In this example the table contains two pairs of coordinates (Cust_X, Cust_Y and Store_X, Store_Y). The background map is the Microsoft Bing Roads Layer, available with the MapInfo Pro Premium Services.
You can use the SQL Select command or the Table Update Column command to calculate the distance between the two pairs of coordinates. In this case the data represents the distances between the stores and the customers.
Fill in the SQL Select dialog box as follows:
Here again is the distance function in case the screen shot above is difficult to read:
Distance ( Cust_X, Cust_Y, Store_X, Store_Y, "km") "Dist_to_store_km"
The last parameter (remember that word?) is the units in which you would like the distance calculated. A complete list of these abbreviations is available in the Help. Some of the common ones are as follows:
Kilometers - "km"
Feet - "ft"
Miles - "mi"
Also note that "Dist_to_store_km" is an alias name for the column. This provides an easy to understand name for the distance column. If you do not use an alias then the column name will be the same as the expression that created the column. You can see this alias name in the results below.
Example 2: Calculating distances between points that are in two different tables.
The first example used two pairs of coordinates that were present in the table. Let's say your data is in two different tables and the coordinates are not explicitly in the table. An example of this could be a table of schools and a table of students. You can still use the Distance() function to calculate distances.
Note in the tables below, there are no columns with coordinates. Also note that the students table identifies which school each student goes to. We can use that to join the schools and students tables together and calculate the distance each student is from their school.
To calculate the distance we will join the tables. To get the coordinates we will use the CentroidX() and CentroidY() functions. These functions return coordinates.
Here is the Distance function, as it appears in the example above:
Distance ( CentroidX(obj) , CentroidY (obj), CentroidX (M4_Schools.obj) , CentroidY (M4_Schools.obj),"mi")
Additional info about the above example
- In the above example the students table is listed first. As a result if the query result is mapped, the table will contain the map objects of the students.
- Obj refers to the map object. The CentroidX() and CentroidY() function require a map object to work.
- Related to the first point, the first CentroidX() and Centroid(Y) functions do not explicitly indicate which table's object to use. If no table is specified then the first table listed in the From tables box is used.
Three different distance functions
The astute observer will note that MapInfo Pro includes three different functions for calculating distances. The following chart explains the differences. In the examples above, the Distance() function was used. Most users will probably find this sufficient but just in case you need the other options, here they are.
|Function||What it does||Comment|
|Distance()||Calculates spherical distance when the projection permits and Cartesian distance when it does not||Will always return a distance. In most cases it will return spherical distance (a.k.a. great circle distance). For non-earth coordinate systems it will return Cartesian distance.|
|SphericalDistance()||Calculates distance taking curvature of the earth into account||It works in all cases except when your Session Projection (Options > Preferences> Projection > Session Projection is Non-Earth. In this case it returns -1.|
|CartesianDistance()||Calculates distance as on a flat map - does not take curvature of the earth into account.||It works in all cases except when yourSession Projection (Options > Preferences> Projection > Session Projection is Longitude/Latitude. In this case it returns -1.|
Want to learn more?
The Query Ninja topic in the Community has a great many more articles. Click here to see 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.