Is creating such an interactive bicycle 'map' possible?

[mv2005]

I work for a state road authority and I also have a strong interest in cycling. We often get informal requests for road data from cyclists looking for gradient information etc. It has got me thinking into creating a publicly available interactive route planner for cyclists. The idea would be to have some form of base map showing the mountain range overlooking the city. At selected intersections would be (say) a check box where the user would check to indicate the route they would take. Each time another check box is selected the code obtains the profile data between those two points and cumulates it to a running total (Note we already have profile data pairs [distance / elevation] and spreadsheets that plot profiles/total climbing/max gradient/total distance etc). As the user keeps going a running plot is developed showing the profile (in another sheet) and informs them of the total distance to date. Ideally it might draw a line between the checkboxes to indicate the path taken. I've noted that checkboxes appear to have 'Left' and 'Top' properties so perhaps when selected it can draw a line between the 'coordinates'.

I've seen various online route planners but I don't think anything would provide as much detailed (and accurate) data as what I have in mind (elevations etc every 10m), and certainly not for free.

Obviously this would require a LOT of input so we'd start with major roads and then add others down the track (cyclists could email in requests).

Cheers
Mike

 

[yytsunamiyy]

It certainly is going to be difficult. There is little problem having a map-image as a sheet-background and placing check-boxes on top of it that are linked to cells. Code can easily control that if a checkbox is ticked, a cell is changed to one. You can then work out the distance / elevation data between 2 points. The challenge starts after that. User are certainly going to pick more than 2 checkboxes to make up a path. The difficulty lies in determining which path is the one the user is trying to take. Elevation / Distance could vary wether you take path 1-2-3 or 1-3-2. In both cases the same checkboxes would be ticked, but different result might be expected. Also: how would you deal with circular paths - (where to stop calculating), how would you account for changes in the path, say the user had first ticked 1-2-4, changes it to 1-2-3-4 and changes it again to 2-3-4...

I think a lot more thought should go into this before you even go to the initial design stage.

What I would recommend is to think about providing pre-defined route and maps. that way the user can still have a large selection of routes - especially if you keep on adding routes that resulted from e-mail queries, but you save a lot of hassle.

...Just my 2 pennies worth.

 

[mv2005]

In terms of drawing the path I was thinking of the following:
*Have four variables Start_Left, Start_Top, Finish_Left, Finish_Top listed as Private up top
*When the first check box is checked it sets Finish_Left and Finish_Top as the left and top properties of that box. Finishes code by unchecking box
*When each subsequent check box is checked it sets Start_Left to Finish_Left, Start_Top to Finish_Top and then resets Finish to the properties of the new box.
*Draws line between Start and Finish
*Unchecks box (for future loop use)

In terms of knowing which way (direction) it is going along the road how about;
*Let's say Road A has three consecutive intersections as Road B, then Road C and then Road D respectively.
*Check boxes are named RoadA-RoadB, RoadA-RoadC and RoadA-RoadD for each intersection
*Distance/Elevation data for Road A is in hidden sheet. Rows 1 to 10 are data pairs between Road B and Road C and Rows 11- 30 are data pairs between Road C and Road D. IN that sheet Cell B1 is 'Road B' and Cell B11 is "Road C". Distance is column C and Elevation is column D.
*The Top and Left properties could be placed in Columns E and F for each intersection (ie E1 and F1 for RoadA/RoadB and E11/F11 for RoadA/RoadC
*When you check a subsequent box you could have the code find the common Road name between the two check box names (i.e. RoadA-RoadB and then RoadA-RoadC would establish that we are referring to a section of Road A because it is common to each Check Box name).
*It could then run a Match search to find the row numbers for Start_Left and Finish_Left in the hidden sheet. If Finish row number is higher than Start row number then it knows that we're moving the 'forward' direction and cuts and pastes the Dist/Ele pairs into a 'Profile' data set (for plotting and calculating overall distance etc). If Finish row is less than Start row then it knows the person is going the 'opposite' direction so copies the data pairs in reverse order (haven't established if this is straight forward or not!).

Note the profile would update as you move along. Cutting and pasting data pairs. Alternatively it that is complicated then a simple 'Finish' command button to run the calc's and profile at the end.

Not sure if that made any sense but that's my initial thoughts.

Cheers
Mike

 

[mv2005]

Edit: Just saw your bit about the user changing their mind. You could possibly have an 'Undo Last Selection' command button. It would know what Start_Left, Start_Top, Finish_Left, Finish_Top are so could probably figure out how many data pairs to remove from the cumulative profile. Alternatively an annoying MsgBox "Are you sure you want to go this way" with each Check_Box code before it pastes the data. Another way might be having a "Remove Sections" command button where it doesn't just permit the removal of the last section but allows consecutive removals (though not sure how one goes about deleting the lines - that might have to be manual )