Home
Manager's Handbook
AssumptionsEntering Data Into the Database.
Ok, you’ve spent 3 days checking and adding controls to Z, the area across from the cooling pond. Now what:
Importing data into Ozi.
Fire up OziExplorer. Import data from the GPS. (Garmin -> get waypoints) While the MAP is NAD27, data comes from the GPS in WGS84. Be sure that Ozi knows that it’s coming in WGS84. (This is the default)
Do NOT adjust the location of controls to fit the map. At this point, the map is wrong, not the controls. With the current MapMaker map, there seems to be a consistent error that controls plot about 13m ENE of the true location. This is within reasonable search radius. Just don’t use the map and the plotted location to determine a bearing.
Saving data for later use in Ozi.
File > Save > Waypoints. Name is composed as follows: Z_III_dd-mon-yr.wpt where Z is the zone, III are the initials of the fieldman, and date in day, 3 letter month abbreviation, yr is the 4 digit year.. Correction: 2007. I’m now naming files YYYY-mm-dd_Z_III so that they show up in date order.
File > Save > Tracks. Name as above with the track extension used by ozi.
Why, you ask, when this data is just going to be moved into access.
A lot of these processes have at their heart, redundancy, to make it possible to recover from various errors. This particular one saved me from putting 3 bad mistakes in the database the last time I redid Mothersole’s Heights.
As you can imagine, the entering of data is subject to error. While in the field I had forgotten to note that waypoint 51 was a shed. When writing it up in Access, I put M5, which I had verified, at 51. Later, I found I had waypoint 50 leftover. Since sometimes I punch the waypoint button twice, (this is why you do things in the same order EVERY time.) I discarded it.
Week goes by. I’m setting a race to use this area. I remember marking the shed, and want to use it for a calculated bearing. But it’s not in the database. Match the date of the control with the date of the waypoint file. Reload both track and waypoint file. This gives me the waypoints, and my wanderings. Start a couple waypoints before the problem area, and alternate between the map and the database. The combination of waypoint number in the info field in access, and the First Placed field lets me find the original GPS file. From that I can reconstruct what should have gone where. Once discovered this took about an hour to deal with.
Why bother? You set 6 controls per hour. Answer: Wrong controls destroy credibility of the entire database. Despite everything, I usually end up with a couple of goofs on each race. This particular error would have resulted in a wrong control and a lost landmark.
Saving data for import into Access.
In Ozi Remove all but the new waypoints (numbered waypoints) (View → Lists → Waypoints. Usual combinations of control click and shift click for multiple selections.)
Export the waypoints as a text file, using UTM coordinates. Use the filename Rogaine\Waypoints\temp.txt. Export as NAD27 data. (File → Save → Export waypoints as text file → Dialog box for Datum & coordinate system → Dialog box for file save)
Entering data into access.
To bring the data INTO access, go to macros and click “Import waypoints from OziExplorer”
Open Table Control Information 1. If you left the controls numbered they will be at the top.
I do the transition in several passes
Assign the zone for the new points.
In the info field, put the number of the waypoint. This allows you to unscramble latter if you miss a line, or copy from two blocks. (After you type the first two, hitting the down arrow should auto fill.
Filter for the given zone. (This makes scrolling ever so much easier.)
Select the first placed data field of all the new waypoints. Control-C to copy. Select all the blank squares corresponding to verified data. Control V to paste.
Label the control waypoints. Locations are filled in as I label controls. If a control is a verification it gets a + appended to its name. If it’s a replacement for a current control it gets the old name as a prefix. E.g The waypoint that is a verification for MP is called MP+. If I just installed M41 to replace MQ, I call it MQ_M41. Using this system results in verifications/replacements being adjacent alphabetically to what they refer to.
Write the clues for the new controls. Routine ones can be postponed. Ones that are clever, or depend on some remembered detail should be written up NOW while they are fresh.
By preference do this on a dual monitor computer. One monitor holds access database, the other runs ozi explorer. Keep referring back and forth between what you are writing (access) and where it was on the map (ozi). This has two effects for me. Mainly it gives me a sanity check that I’m writing about the correct control. But it also associates the description with my memory of the location with the aerial view of the terrain. In the long run this helps me find things later. If you highlight the current waypoint in the waypoint list, it also highlights on the map.
It is easy to associate the wrong waypoint with control. A good fraction of unfindable controls have this cause. This is why I leave the waypoint number in the info field while I’m working.
I leave it in semi-permanently. In combination with the ‘First placed’ or ‘Verified’ info, it identifies the batch when it was added. Since mishandling the data usually results in all of the data being mangled the same way, this is a huge aid in recovering. (When I verify a control, I update the waypoint number in the info field.)
This also allows me to go back to my field notes to match things up.
Starting in 2005-6 the First Placed data field timemarks refer to the date and time that the waypoint was taken. Prior to this, the date refers to the date it was imported into Ozi. This way of doing it allows two things that may help: A: You can sort the controls by First Placed, to create a track of what order you did things. B: The time stamp may help you unscramble a mess. (If I tell you once, I tell you twice. Redundancy is good.)
Re-sort by control ID. This puts MP+ next to MP and MQ_M41 next to MQ. (Right click on column header => sort ascending)
For verifies, compare the locations. If significantly different, and your data this day seems to be good, replace the location in the old control with those of the new one. Enter the date into the Last Verified. (How do you know today was good? Check that the locations of standard landmarks stay close to their old recorded places. Check the value of the accuracy field. One reason I record landmarks every time. If the data is WAY different, then you probably have a data set that was entered using a different datum. In the info section I’ll record something like: “Old coordinates were 200m North and 45 m west of here” or “Old locations is 220m at 332 T” If this happens several times, then with some clever sorting by first placed, you may be able to determine the rest of the batch, and ‘voila!’ you may have just found 20 missing controls. (2006-7 I’m using this system to recover the NN02 series of controls. A surprising number are in good shape.)
For replaces, change the old ID to the new id; change the mixed ID to the old ID. Codeword for old id is GONE, maintenance info says “replaced by new id”. In the new control info field put “Replaces old ID”. I don’t know if I will need this information again, but I’m an information packrat. Gotten burned many times by discarding data that I thought no longer useful. Having GONE as the codeword allows you to filter it out of controls to use. (In one case I was mistaken about which control was replaced. Having the old one in the data base allowed me to sort it out later.
In both cases reread the locations and the clues. You’ve just been there, so the area is fresh in your mind. Many of the older clues are badly written. In particular check directions as one my most common mistakes is to swap east for west..
Not all of the waypoints are controls. About a 20% of them are various artifacts that make good navigation points. For these I code as follows:
The first letter corresponds to the section. So a fence corner in Z is Zfc.
The next group says what it is: At present there are:
End, jnc, for ends, junctions trails, cutlines, FAR’s
Int for intersections between unlike things. (stream & trail; trail & cutline)
FC, FJ, FG Fence corners & junctions, and gates.
PK,PE, for Powerlines to Keephills, Edmonton, (Metal Towers); GK Medium voltage powerlines (Wood poles, but at least 6 section insulators);
GW, WW for waterwell and gas well.
Dam, for beaver dam. Generally record these only if they are 3’ or higher, (even if broken) or if the pond upstream is 3 times as wide as the creek downstream. At present I don’t distinguish between broken and whole dames, as this can change way too fast.
MP for marker posts for gas right of way, telephone company cable access.
Sgn for road sign.
Shed for sheds, old granaries, hunting shacks, abandoned farmhouses.
Msc for miscellaneous – anything that doesn’t match.
Sur for Alberta Survey Markers.
CJ for creek joining.
Finally each has a number to make it unique.
When entering them you probably don’t remember how many F junctions are in the database. So initially call new ones FjncA, FjncB, etc. After the re-sort, you can change the letters to numbers. Watch out: Jjnc10 comes between Jjnc1 and Jjnc2.
The codeword is always LANDMARK.
Type and faces are left blank. Location has the expansion of whatever abbreviation I used for the control plus any other relevant info.
How to write clues.
Every control should ideally have three different clues with three different point classes. Since roughly a third of each race is standards (or variants) one of the clues should be some form of standard. In actual fact, I almost never write a standard until I need it, as it can be an edited copy of the description. To make it a search, edit it harder, making less of the information obvious; either by restricting the specific part of the clue. (Don’t tell them that it’s on a double birch) or by making the general part less specific (801 19?) To make it a puzzle, use Ozi, and measure from one or more mapped landmarks.
If there is a good riddle or navigator or an idea for a stunt, I’ll write it in at this time.
If I took bearings in the field they are written up with the control description, or are used immediately to write clues.
Often however I don’t know what sort of clue I need until race time. I select the controls I want to use, then write or rewrite clues to suit. This is a bit of an art. Read the location first. I have at times written a stunningly clever clue only to have it found by a team stumbling on it. My talent, wasted. Pearls before swine. Certain locations don’t lend themselves to clever.
Suppose you have a control that is on an isolated fencepost, 250 meters due south of a corner. Here’s a bunch of different clues you could write for it:
| Point Class | Clue | Grid Reference |
| Standard | On isolated fencepost | 761 165 |
| Navigator | From this control, there’s a fence corner bearing 352M. Control is on isolated fencepost. | 76? 16? |
| Puzzle | This control is 1.5km from a road intersection to the northeast, and .5 km from the Genesee Cemetery gate. | ??? ??? |
| Riddle | The wind blows cold. Bare is the back without a brother. | 761 165 |
| Standard plus | On isolated fencepost. X+Y=6 | 76x 16y |
| Stunt | This control is 250m due south of fence corner at 763 167. One team member must travel from this corner to the control blindfolded. Team members are allowed to break trail and give verbal instructions but may not have any form of physical contact. | 763 167 |
| Puzzle | 350 NW of the SE corner of Section 21 Township 51 Range 3 West of the 5th Meridian. | ??? ??? |
!!! Access Warning !!!
Access is a hostile programming environment. It is very easy to do things that you can’t easily undo. Trouble shooting is very hard, and the help files are incomplete.
If you want to play with the database structure, and add or modify new features:
Before working on your production database, make 3 copies of it. One is called controls.backup-10Nov05, one is called ExpControls, one is called TestControls. ExpControls is where you experiment with new techniques build new forms, and so on. Once you get it working in ExpControls, you can open a second copy of access, and open TestControls. Copy and paste your new query, table, form or whatever into TestControls. Run it there. See if it does everything you expect. One you are convinced that it does everything you want, only then paste it into your production copy. Don’t chuck the dated backup. There are often unintended side effects.
Access does not accept renaming things well. You can rename anything, and there is a nominal autofix dependencies feature, but it doesn’t work well. The net result is that if you rename a table, then reports, forms and queries that depend on that table stop working.
Don’t experiment during the Rogaine season. Really. No matter how cool the feature is. Don’t do it. I spent 3 nights recovering after shooting myself in the foot this way, trying to get a usable clue list for Monday morning.