"Be the Query Ninja" - Calculating the Length of Line Objects
In this month's article we will investigate how SQL in MapInfo Professional can be used to measure the length of lines.
When analysing your map data you may want to calculate the total length of line objects. For example, you could be planning new cycle routes and want to know how long each route is or you may work for a road re-surfacing company and need to know the total length of each major road in an area that needs to be re-surfaced.
A single Line Segment
Finding the length of a single line section is very easy. Just take the select tool and double click on the line section that you want to know the length of. The length of the line is displayed in the 'polyline object' dialog box along with other useful information such as the bounding and center coordinates of the line.
Multiple Line Segments
Often lines are drawn in sections, broken at road junctions or where attributes of the line need to change. If you wish to find the total length of more than one line section then SQL is a good option.
In this example the total length of motorways will be calculated. In the screen shot below you will see how each motorway has been drawn as multiple line sections and therefore appears numerous times in the browser window. SQL will help sum the line lengths together for each motorway.
To return the length of a line section in SQL Select the ObjectLen function can be used. The ObjectLen function requires 2 parameters inside the brackets. The first parameter is what you are calculating the length of (which is usually defined by the table you are querying) and the second is the units to measure in.
For example, ObjectLen(obj,"mi") will return the length of the map object (obj) in miles.
In our motorway example we know there are multiple line sections for each motorway so we will need to sum the motorways lengths together (aggregate) where the road name in the same (group).
The example below shows how to achieve this using SQL select. The result returns total line lengths for each motorway in a column called Length_km and the column is ordered descending so that the longest motorway is listed at the top.
The results from the query
If you need the basics of SQL Select in MapInfo Professional this article from the July 2012 issue provides a basic introduction.
Article by Nicola Hall, Training Consultant
When not writing articles for "The MapInfo Professional" journal, Nicola teaches Pitney Bowes Software customers how to use MapInfo Professional and get the most out of the software. When not working she loves trying out new food from across the world.