Motorhome Facts Forum banner

New camping POIs spreadsheet

18K views 110 replies 18 participants last post by  Pudsey_Bear 
#1 ·
Hi,

Well our last potential ski day was a no-go so, bored again, I've produced an enhanced version of a camping POI Excel preadsheet.

The main enhancements are:

- I've added in the latest Archies Camping POIs (so 49,000+ entries in total with the Camping Car Info data)

- There's a button to filter the POIs just to show those based on a target longitude/latitude and a radius (in miles)

Other points to note are:

- The sheet is protected, to prevent accidentally changing/deleting the POI information but, if you want to poke around, there is no protection password

- The Google Maps hyperlinks are no longer dynamically created using formulae (it took too long to recalculate when the data was filtered) but, if you do change any coordinates or, indeed, add your own entries, then there is a button ('Refresh Hyperlinks') that runs a program that will sweep through the entries and regenerates the hyperlinks. This can take a good few minutes! Oh... and if you do add any new entries of your own then don't leave any empty rows - the Refresh Hyperlinks logic will stop at the first blank row :)

- The calculation of what POIs are within the search 'radius' is not 100% accurate. It's based on the approximation that 1 degree of latitude or longitude = 68.055 miles (1/360th of the Earth's average circumference). So if you were to enter a search 'radius' of 68.055 miles it will filter in all POIs with a longitude and latitude of +/- 1.0 degrees. A search 'radius' of 34 miles will filter in all POIs with a longitude and latitude of +/- 0.5 degrees. You get the picture.

I reckon it should be accurate to with half a mile or less anyway.

I put 'radius' in inverted commas because it's actually filtering out POIs within a square around the search long/lat rather than a circle.

Please note that this spreadsheet will only work in versions Excel 2002 and above and you will need to enable macros. Again, I'm not sure if it will work correctly in Mac Numbers.

You can download the new sheet HERE

Rgds
CD
 

Attachments

See less See more
1
#6 ·
Thank you for taking the time and the trouble, I know it was not an easy task.
 
#7 ·
I'm getting a warning message
 

Attachments

#8 ·
Kev: You have your Excel macro security levels set too high to run uncertificated macros (in this case the programs I've embedded to do the filtering). That's fine, of course, but I don't have a security certificate - I'm just a casual programmer and certificates cost money, I believe.

Unless anyone else has any suggestions you can either temporarily lower your Excel macro security level in order to use the spreadsheet, then restore it afterwards. Or just ignore the error and use the data in the spreadsheet, without the ability to use the buttons of course.

Or you could copy all the data into another blank spreadsheet and use it from there.

Rgds
CD
 
#11 ·
uncleswede said:
Deckboy:

Oh... it's definitely in my public dropbox folder so should be available to all...

Do you get some sort of 'denied access' or 'permission' error or is it like a broken link?

If still no good PM me and I'll sort out out some other way of getting it to you

Rgds
CD
Got it!

Expected to get a Dropbox message, but it downloaded directly to my Download folder.

Many thanks
 
#12 ·
Thanks again for what you are doing uncleswede.

I'm a MacBook "Numbers" user and for your interest I receive five error messages:

1) Filtered cells might have been imported as hidden. To show them, choose Table > Unhide All Rows and Unhide All Columns.

2) Form controls such as checkboxes and scroll bars were removed.

3) Password protection on sheets isn’t supported and was removed.

4) Sorting criteria were removed.

5) Frozen panes or split windows aren’t supported and were removed.

The "Filter by distance" "Clear all filters" and "Refresh hyperlinks" boxes arrived on the spreadsheet as pieces of coloured text only, and appear to have no dynamic action activated by them.

I'm hopeless with spreadsheets so it may well be me doing something wrong. :oops:
 
#13 ·
HurricaneSmith:

Oh ... I'm afraid I don't know Mac Numbers at all so can't really help. But it sounds as though the enhancements won't work. I thought that might be the case :-(

Assuming it has imported the data OK and you can filter rows in Mac Numbers you may still be able to manually search for nearby POIs...

Given that 1.0 degree of latitude or longitude = 68 miles (near as dammit) you can calculate the equivalent number of degrees that represents the miles you want - e.g. 0.147 deg = 10 miles.

If you then add and subtract that amount from latitude and longitude to get a range, you could then filter the longitude and latitude columns by that range to see data rows of nearby POIs

E.g. Assuming you want to search for 10 miles around the following coordinates Long: 45.0000 and Lat: -1.0000, the filter ranges would be:

Long: 44.853 to 45.147
Lat : -1.147 to -0.853

That's basically what the buttons do

Hope that helps,

Rgds
CD
 
#15 ·
uncleswede said:
HurricaneSmith:

Oh ... I'm afraid I don't know Mac Numbers at all so can't really help. But it sounds as though the enhancements won't work. I thought that might be the case :-(

Assuming it has imported the data OK and you can filter rows in Mac Numbers you may still be able to manually search for nearby POIs...

Given that 1.0 degree of latitude or longitude = 68 miles (near as dammit) you can calculate the equivalent number of degrees that represents the miles you want - e.g. 0.147 deg = 10 miles.

If you then add and subtract that amount from latitude and longitude to get a range, you could then filter the longitude and latitude columns by that range to see data rows of nearby POIs

E.g. Assuming you want to search for 10 miles around the following coordinates Long: 45.0000 and Lat: -1.0000, the filter ranges would be:

Long: 44.853 to 45.147
Lat : -1.147 to -0.853

That's basically what the buttons do

Hope that helps,

Rgds
CD
Or you could go out for a beer :D :D

That made my head hurt.
 
#16 ·
uncleswede said:
Kev: You have your Excel macro security levels set too high to run uncertificated macros (in this case the programs I've embedded to do the filtering). That's fine, of course, but I don't have a security certificate - I'm just a casual programmer and certificates cost money, I believe.

Unless anyone else has any suggestions you can either temporarily lower your Excel macro security level in order to use the spreadsheet, then restore it afterwards. Or just ignore the error and use the data in the spreadsheet, without the ability to use the buttons of course.

Or you could copy all the data into another blank spreadsheet and use it from there.

Rgds
CD
Thank you kind Sir, tis now working properly.

Would it be possible for me to add, my paltry few POIs to the same file, IE would it automatically give me the Google link if I copy.dragged it down, I'm OK with the very very basic spreadsheets, but I've only ever used them a couple of times as my head gets in a knot.

Edit

My co-ords are in this format so different to yours,

Hymer preston -2.81432 53.76412

so need to be converted which I think I can do in POIEdit, and the file is password protected which I've not come across for a long while but can see why, that's a lot of data to screw up, a firm copy being essential before messing about.
 
#17 ·
......the filter ranges would be:

Long: 44.853 to 45.147
Lat : -1.147 to -0.853

That's basically what the buttons do

Hope that helps,

Rgds
CD
You are a brilliant, uncleswede, and your help solved the problem for me. :)

I read your logic and then googled this Apple Support Page:
http://support.apple.com/kb/VI138?viewlocale=en_US&locale=en_US

Your spreadsheet works perfectly in Numbers when the right logic is applied. What a fantastic forum MHF is. :D

(I've even worked out how to apply the quotes correctly in my posts thanks to Barry. But don't tell him. :lol: :lol: :lol: )
 
  • Like
Reactions: Pudsey_Bear
#18 ·
Kev:

Hi. You can add your own POIs. You just need to make sure that you don't leave any blank rows and that the coordinates are in the right order (longitude first - for Hymer Preston that would be the 53.76412 figure).

For speed reasons there are no formulae to calculate the Google maps Link so you can't just drag and copy to the new rows. But, fear not, once you've added your rows click the Refresh Hyperlinks button and it will go through all the rows and fill in the map links. It can take 4-5 mins however so be patient (you'll see a progress counter in the bottom status bar)

Rgds
CD
 
#19 ·
Uncle that is excellent.
I think your instructions need to go into a text box cos people will forget.
Now if only you could do a tablet version; there are some apps that display excel sheets but I dont believe that they can run macros.
Dont know why the columns are autofiltered.
I dont use a laptop when travelling anymore but do have an android tab and my biggest problem is finding stopping places near me.
 
#20 ·
uncleswede said:
Kev:

Hi. You can add your own POIs. You just need to make sure that you don't leave any blank rows and that the coordinates are in the right order (longitude first - for Hymer Preston that would be the 53.76412 figure).

For speed reasons there are no formulae to calculate the Google maps Link so you can't just drag and copy to the new rows. But, fear not, once you've added your rows click the Refresh Hyperlinks button and it will go through all the rows and fill in the map links. It can take 4-5 mins however so be patient (you'll see a progress counter in the bottom status bar)

Rgds
CD
Brilliant Uncleswede, I wonder if it will work on my S2, attempt coming up, I just need to email it to myself first, 20Mb will wake it up :D :D

We need the password to edit our own in.

Agree about a text box in the file for any instructions.
 
#21 ·
Emailed it to me, saved it on the phone, but it's is taking an age to open so if it does work I'd advise editing uncles file to one file per country to eliminate the wait.
 
#22 ·
uncleswede said:
Kev:

Hi. You can add your own POIs. You just need to make sure that you don't leave any blank rows and that the coordinates are in the right order (longitude first - for Hymer Preston that would be the 53.76412 figure).

For speed reasons there are no formulae to calculate the Google maps Link so you can't just drag and copy to the new rows. But, fear not, once you've added your rows click the Refresh Hyperlinks button and it will go through all the rows and fill in the map links. It can take 4-5 mins however so be patient (you'll see a progress counter in the bottom status bar)

Rgds
CD
I must be doing something wrong uncle, to test my own file I removed all but the top two and re saved it, added my own, dragged the Google link down and used the refresh hyperlink button and it took a millisecond, on clicking the link it took me to the last of your data IE line two.

I'll have another look to see if I missed something obvious though.
 
#23 ·
Kev:

Adding your own POIs
================

There's no need to enter anything in the 'map link' column for any new entries - the Refresh Hyperlink button will fill in blank map links automatically. You just need to make sure that all the other cells are filled in for new entries then click the button.

If you edited the data down to only a few rows then the Refresh Hyperlink button will run very quickly (it's only when there're 49,000 rows it takes a while!)

I'll add a note into the sheet on how to add your own POIs.

Password
=======
The spreadsheet, as downloaded, is protected but there is no password - i.e. if you select Tools, Protection, Unprotect sheet it will immediately be unprotected.

To protect it again without a password, click Tools, Protection, Protect sheet, leave the password field blank, make sure Select Locked Cells, Select Unlocked Cells and Use Autofilter are ticked then click OK

Tablets
=====
Although you can load and open Excel spreadsheets into Android tablets with some apps, the 'clever' buttons will never work.

Not sure if the iPad has a version of Mac Numbers that would run it or not (I don't have a clue re: Apple stuff). Maybe someone else can weigh in on that.
 
#25 ·
uncleswede said:
Kev:

Adding your own POIs
================

There's no need to enter anything in the 'map link' column for any new entries - the Refresh Hyperlink button will fill in blank map links automatically. You just need to make sure that all the other cells are filled in for new entries then click the button.

If you edited the data down to only a few rows then the Refresh Hyperlink button will run very quickly (it's only when there're 49,000 rows it takes a while!)

I'll add a note into the sheet on how to add your own POIs.

Password
=======
The spreadsheet, as downloaded, is protected but there is no password - i.e. if you select Tools, Protection, Unprotect sheet it will immediately be unprotected.

To protect it again without a password, click Tools, Protection, Protect sheet, leave the password field blank, make sure Select Locked Cells, Select Unlocked Cells and Use Autofilter are ticked then click OK

Tablets
=====
Although you can load and open Excel spreadsheets into Android tablets with some apps, the 'clever' buttons will never work.

Not sure if the iPad has a version of Mac Numbers that would run it or not (I don't have a clue re: Apple stuff). Maybe someone else can weigh in on that.
I'd not read the whole thread properly, so I missed that it wasn't 'password' protected just std :roll:

As for adding my POIs, I've tried as you suggest but it's not working so far, I shall await the in file instructions, cos I'm melting.
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top