ColdFusion in Context: Display Families on a Map Grid
Suppose you need to get a physical sense of where the families in your organization live. You can enter the map coordinates of their streets into a computer, but that doesn't help you visually. You could enter dots on a map, but you probably don't know exactly where the houses are on the map, and there isn't room on a map to see names clearly anyway. This tip lets you enter map coordinates for families and then see their names in the appropriate map cell on your browser.
Enter Families
Create a Family table with at least these columns: XLtr (X as a letter), YNr (Y number), Family (name of the head of household), and XNr (X converted to a number). Leave the XNr column empty for now, but fill the rest for each family in your organization. Here's a sample:
FAMILY,XLtr,YNr
Fenwick,M,19
Roberts,M,21
Quales,N,18
Vance,O,19
Crowley,P,15
Dobbs,P,21
Jameson,R,10
Mack,R,18
Lubbock,R,19
Eggbert,R,21
Hicks,S,18
Thompson,S,18
Stanford,S,20
Jones,T,20
Convert Letters to Numbers
The X coordinate for map locations is usually expressed as a letter or as two letters. A simple way to convert these to numbers on the fly is to create a table that associates these letter combinations with their numeric X coordinates.
Create a Label table with fields Label and XNr. Populate it so that the first row says A = 1, B = 2, and so forth where A is the Label and 1 is XNr (the X coordinate expressed as a number). Do this for as many letters as you need.
Begin; Post X as a Number Based on X as a Letter
Put all the code into mapshow.cfm. Create an update query to fill in the X number in Family by cross-referencing its label to the Label table.
<!--- Begin; post X as a number based on X as a letter --->
<cfquery name="setLabel" datasource="context">
update Family, Label
set Family.XNr = Label.XNr
where Family.XLtr = Label.Label
</cfquery>
Get X Boundaries
To print only as much of the map grid as you need, you must know the left-most and right-most X coordinate. You also need to get the list of letters you'll put across the top of the grid. The smallest X number (XNr) is the left edge of the map; the greatest X number (XNr) is the right edge of the map. You'll select the subset of letters needed to span from the left edge to the right edge for later use in printing across the top of the map.
<!--- Get X boundaries --->
<cfquery name="getLeftEdge" datasource="context">
select min(XNr) as LeftEdge
from Family
</cfquery>
<cfquery name="getRightEdge" datasource="context">
select max(Xnr) as RightEdge
from Family
</cfquery>
<cfquery name="getLetters" datasource="context">
select Label, XNr
from Label
where XNr >= #getLeftEdge.LeftEdge#
and XNr <= #getRightEdge.RightEdge#
</cfquery>
Get Points
When entering families, you may not have known the map coordinate for a particular family. Rather than leave that family off of the list, you might enter the family and leave the coordinates empty for the time being. However, you'll need to skip a family when it has a null coordinate - null is a special value meaning you didn't enter anything in this column - when selecting family information to use in driving the map grid. Notice that because you'll build the display a row at a time rather than a column at a time, you order the query by the Y coordinate first, then the X coordinate.
<!--- Get points --->
<cfquery name="getYX" datasource="context">
select YNr, XNr, Family
from Family
where YNr <> NULL
order by YNr, XNr
</cfquery>
Begin the Table and Display the Top Border
Setting a border width lets the grid be seen. The top right corner can't have a letter in it; because, the left column of the grid will contain the row number. Therefore, a neutral character (an asterisk in this case) goes in the left-most cell of the top edge. The rest of the cells are easy. Simply fill them from the Label table query above: getLetters.
<!--- Begin the table and display the top border --->
<table border=1>
<tr><td>*</td>
<cfoutput query=getLetters>
<td>#Label#</td>
</cfoutput>
</tr>
Begin to Display All Rows
It's handy to use variables to keep track of the X and Y coordinates of the current cell. It's also necessary to keep track of the row of the points list (getYX) being used. Set the X column (Xcol) to the left edge. Call the row QueryRow and initially set it to 1. Set the Y row (Yrow) to the Y value of the first point encountered. Begin the row. Fill its first cell with the Y value of the row so the Y coordinates will run down the side of the grid. Plan to work with points until the list of points (getYX) runs out.
<!--- Begin to display all rows --->
<cfset Xcol=getLeftEdge.LeftEdge>
<cfset QueryRow=1>
<cfset Yrow=getYX.YNr[1]>
<tr><td><cfoutput>#Yrow#</cfoutput></td>
<cfloop condition="QueryRow le getYX.recordcount">
Handle the Situation if the Point is on a New Row
If the point is on a new row (as it might be after the first point), you'll want to 1) put placeholders in every cell after this one to finish the current row, 2) reset the X column to the left edge again, 3) start a new row, taking care to label it with the row number, 4) increase the Y row by one, 5) and repeat while the current row is less than the row containing the next point (filling entire empty rows with placeholders if necessary). Choose your own placeholder. The up-caret used here has the advantage of being both readily visible and obviously useless.
<!--- If the point is on a new row... --->
<cfloop condition="Yrow lt getYX.YNr[QueryRow]">
<!--- Finish the current row and reset the X column --->
<cfloop condition="Xcol le getRightEdge.RightEdge[1]">
<td>^</td>
<cfset Xcol=Xcol+1>
</cfloop>
</tr>
<cfset Xcol=getLeftEdge.LeftEdge[1]>
<!--- Start a new row; advance the Y row --->
<cfset Yrow=Yrow+1>
<tr><td><cfoutput>#Yrow#</cfoutput></td>
</cfloop>
Print Cells Prior to this Cell
The first cell in this row may not be the one that should contain this point. If it isn't, print placeholders until the cells prior to the one you need have been filled.
<!--- Print cells prior to this cell --->
<cfloop condition="Xcol lt getYX.XNr[QueryRow]">
<td>^</td>
<cfset Xcol=Xcol+1>
</cfloop>
Print Each Name in the Cell
It may be, as it is in this example, that you have multiple families at the same map coordinate. Make note of the coordinates for the current point, then while the query still has points and the coordinates are unchanged, 1) print the family, 2) step to the next query row (QueryRow), and 3) continue while these conditions are met.
<!--- Print each name in the cell --->
<cfset Xhere=getYX.Xnr[QueryRow]>
<cfset Yhere=getYX.YNr[QueryRow]>
<td>
<cfloop condition="(QueryRow le getYX.recordcount) and (Xhere eq getYX.XNr[QueryRow]) and (Yhere eq getYX.YNr[QueryRow])">
<cfoutput>
#getYX.Family[QueryRow]#
</cfoutput>
<cfset QueryRow=QueryRow+1>
</cfloop>
</td>
Continue to the Next Cell; When Done, Finish the Grid
Once this cell has been taken care of, 1) advance to the next cell by adding one to the X column (Xcol), 2) continue, 3) and when done, fill cells to the right of the last point with placeholders and end the row and table.
<!--- Continue to the next cell --->
<cfset Xcol=Xcol+1>
</cfloop>
<!--- Finish the last row and end the table --->
<cfloop condition="Xcol le getRightEdge.RightEdge[1]">
<td>^</td>
<cfset Xcol=Xcol+1>
</cfloop>
</tr>
</table>
Try It Out; Make It Better
Browse mapshow.cfm. The columns and rows won't be consistent sizes; because, placeholders take less space than family names. However, you will be able to see the relative location of your families on a grid that represents the map you obtained the coordinates from.
Now that you see this working, consider how you might make it fit your purposes better. Perhaps you might letter your families and just display the letters in order to keep the grid small and good-looking. Let us know what you've done. =Marty=