ColdFusion in Context: Get Distance Between Map Coordinates

Suppose you have the map coordinates for the Families in your organization and you have the task to assign some of them to visit others periodically. The next logical step would be to determine the distance between every Family:Helper pair so you can share the workload logically.

Enter Families; Indicate Status

Let's start with the nature of these pairs. Some Familes both visit and receive visits. Some just visit. (Perhaps they're adults still living with their parents, and they're available to visit others without needing a separate visit for themselves.) Most Families just receive visits.

If you enter map coordinates in the traditional fashion, the Y coordinate is entered directly as a number, but the X coordinate needs to be represented twice: once as a letter or pair of letters, and once as a numeric coordinate.

The Family table would have at least these fields to hold this data: Family (text), Helps (yes/no), Needs (yes/no), XLtr (text), XNr (number), and YNr (number). Define the X and Y coordinates as numeric; because, you'll soon have the database do math with them.

Here are some sample records to help you experiment: 

FAMILY,HELPS,NEEDS,XLtr,XNr,YNr
Fenwick,0,1,M,13,19
Roberts,0,1,M,13,21
Quales,1,1,N,14,18
Vance,0,1,O,15,19
Crowley,0,1,P,16,15
Dobbs,1,1,P,16,21
Jameson,0,1,R,18,10
Mack,1,0,R,18,18
Lubbock,0,1,R,18,19
Eggbert,0,1,R,18,21
Hicks,1,1,S,19,18
Thompson,1,1,S,19,18
Stanford,0,1,S,19,20
Jones,1,1,T,20,20

Convert X Letters to Numbers

If you didn't enter X coordinates as numbers in the first place, make a table do it for you. Call it Label. Give it fields Label and XNr. Populate the Label column with A, B, C...; populate the XNr column with 1, 2, 3.... You get the idea.

Put all the code into mapdist.cfm. This query will update XNr with the appropriate numbers. 

<!--- Convert X letters to numbers --->
<cfquery name="setLabel" datasource="context">
update Family, Label
set Family.XNr =  Label.XNr
where Family.XLtr = Label.Label
</cfquery>

Develop an Algorithm

The shortest distance between two points on the map is a straight line, and the path between these two points is the longest side of a right triangle. If the line is vertical or horizontal rather than diagonal, that's OK. It just gives one of the sides of the "triangle" zero length. Recall that the Pythagorean theorem says that the square of this longest side, called the hypotenuse, may be found by adding the squares of the other two sides together. To put it another way, if you square the East-West (X coordinate) difference between two points, square the North-South (Y coordinate) difference between two points, add these squares together, and get the square root of the result, you know how far apart they are when you multiply the result by the miles between adjacent map coordinates (i.e., the size of one grid unit). Sure your city isn't perfectly flat, people can't drive as the crow flies, and grid coordinates are approximate. However, you can get a rough idea of the distance between two different points this way.

Do a Self-Referencing Distance Query

Logically, you would like to join two tables: a Helper table of Families performing visits and a Family table of Families receiving visits. However, it isn't convenient to enter the data this way. So, we'll make the computer do the work. Hold on tight, it may get a little breathless.

Use the same table two different ways by giving it two different aliases. For this example, let the "a" alias represent Families and the "b" alias represent Helpers. So, the Family result from this query will be a.Family. Its X and Y coordinates are a.XNr and a.YNr. (Its X-lettered coordinate is a.XLtr.) The Helper result from this query will be b.Family because of the way that these records are picked (as you'll see later). Its X and Y coordinates are b.XNr and b.YNr (and its X-lettered coordinate is b.XLtr). Finally, the big equation to determine distance must be used. It's just the formula you're used to using for the hypotenuse except that the final result is multiplied by the distance between adjacent map coordinates: the size of a map cell. For this example, let the size of a map cell be .857 miles. Because the alias "distance" won't be recognized within the query, you have to repeat the entire formula each place it is needed.

The rows in the Family table used as the "a" alias are those whose Needs field is true. The rows in the Family table used as the "b" alias are those whose Helps field is true. You don't want rows whose distance could not be calculated. (Perhaps the coordinates weren't available.) You don't want the situation where a Family visits itself to be part of the query; so, exclude rows where Family (a.Family) equals Helper (b.Family). Finally, sequence the query by distance so you can consider the pairs having the shortest distance first. 

<!--- Determine the distance between each pair
and order by shortest distance first --->
<cfquery name="tryAll" datasource="context">
select a.family as Helper, a.XLtr as HelperLtr,
a.XNr as HelperX, a.YNr as HelperY,
b.Family as Family, b.XLtr as FamilyLtr,
b.XNr as FamilyX, b.YNr as FamilyY,
((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5*.857 as Distance
from Family a, Family b
where a.helps = TRUE
and b.needs = TRUE
and a.family <> b.family
and ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5*.857 <> NULL
order by ((a.XNr-b.XNr)^2+(a.YNr-b.YNr)^2)^.5*.857
</cfquery>

Prove it Works

To readily prove this works, here's the query displayed: 

<table>
<tr><td>HELPER</td><td>GRID</td>
<td>FAMILY</td><td>GRID</td>
<td>MILES</td></tr>
<cfoutput query="tryAll">
<tr><td>#Helper#</td><td>#HelperLtr##HelperY#</td>
<td>#Family#</td><td>#FamilyLtr##FamilyY#</td>
<td>#evaluate(round(Distance*1000)/1000)#</td></tr>
</cfoutput>
</table>

Browse mapdist.cfm to view the result. =Marty=