Home PageFacebookRSS News Feed
PocketGPS
Web
SatNav,GPS,Navigation
MacFixer, the iPhone, iPod, and iPad specialists
Pocket GPS World - SatNavs | GPS | Speed Cameras: Forums

Pocket GPS World :: View topic - Creating a KML with multiple POI from Excel????
 Forum FAQForum FAQ   SearchSearch   UsergroupsUsergroups   ProfileProfile   Log in for private messagesLog in for private messages   Log inLog in 

Creating a KML with multiple POI from Excel????

 
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Satmap Active 10
View previous topic :: View next topic  
Author Message
Xcell1
Occasional Visitor


Joined: Apr 12, 2011
Posts: 12

PostPosted: Wed Apr 27, 2011 11:50 am    Post subject: Creating a KML with multiple POI from Excel???? Reply with quote

I have an excel spreadsheet which has over 500 POIs with corresponding OS grid references (some of these are 6 fig some 8 and others 10), has anyone got a way of automatically (ie a spreadsheet formula) converting the OS grid references into lat longs so that I can create a KML file that can be imported into GE and my satmap? Ideally the formula would take the OS grid reference from a single cell and populate separate lat and long cells. All my grid references are in the XY000111 or XY00001111 or XY0000011111 format with no spaces between letters and numbers, would that be a problem?

I notice in the Satsync instructions it tells you how to use excel to create a tab delineated file which can be imported as a POI however it is not clear if you can create multiple POIs on the same file. This would allow me to use my OS grid references (I would still have to split them into letter and number parts)
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Wed Apr 27, 2011 7:50 pm    Post subject: Reply with quote

Splitting the OS into letters and numbers is pretty easy (if you know how). If you have them split, does the letter part and number part have to be in separate columns? Will your program then convert the OSGB int lat/lon, and will it generate the KML file from the split cells?

If yo PM me your email, I'll send mine back and then you can send ne a sample file to play with.

The code for splitting is
Code:

Function SplitOSGB()
Dim I As Integer
Dim J As Integer
Dim K As Integer
I = 1
J = 1 'Set J to the column number of your OSGB list
K = 3 ' Set K to the column that you want the letter part of OSGB _
        the number part goes in the next column
Do Until Cells(I, 1) = ""
    Cells(I, K) = Left(Cells(I, J), 2)
    Cells(I, K + 1) = Right(Cells(I, J), Len(Cells(I, 1)))
    I = I + 1
Loop
End Function

There can be no blank rows in the list
Converting OSGB to LatLon is a bit more involved Sad
Back to top
View user's profile Send private message
pcaouolte
Frequent Visitor


Joined: Dec 27, 2006
Posts: 998
Location: South Lincs, UK.

PostPosted: Wed Apr 27, 2011 8:06 pm    Post subject: Reply with quote

Have a look here for an excel macro to convert osgb to lat, long.
_________________
Paul
Back to top
View user's profile Send private message
Xcell1
Occasional Visitor


Joined: Apr 12, 2011
Posts: 12

PostPosted: Thu Apr 28, 2011 11:03 am    Post subject: Reply with quote

Guys my hat is off to both of you, you have no idea how long I've been struggling with this.

M8TJT, I'll PM you my email and send you a sample file, I'm not the best when it comes to macros! from Paul's link there is a macro and a spreadsheet which requires the OS grid ref to be split into 3 columns, (XY, 00000 & 11111) each of the numbers have be 5 figs, so I might need to add a zero or two to the 6 and 8 figure grid references. Adding zeros after they were split would be easier but I could do it before. If I can get the OS grid ref split correctly, the rest should be relatively easy, I'm hoping I can just cut and paste the formula from Paul's sheet into mine. (edit it looks like I will have to paste my OS data into the sheet then cut out the lat and longs but that is no problem if I do it in one go) I may have to recombine the Lat Long into a single cell for the KML conversion, but that should be relatively straight forward, given that you can split cells.

Once again thank you very much for your help.
Back to top
View user's profile Send private message
Oldboy
Pocket GPS Moderator
Pocket GPS Moderator


Joined: Dec 08, 2004
Posts: 10641
Location: Suffolk, UK

PostPosted: Thu Apr 28, 2011 2:36 pm    Post subject: Reply with quote

Late as usual, but I think XLSConverter, from our own Oldie, will fit your bill.

It can handle any OS numbers, as long as the number part is an even number. All 4 of the samples below will give the same result.

SU 84 46
SU 840 460
SU 84000 46000
su840460

It can then output it in csv, ov2 or xls format.
_________________
Richard

TT 910 V7.903: Europe Map v1045
TT Via 135 App 12.075: Europe Map v1120
Back to top
View user's profile Send private message Visit poster's website
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Thu May 05, 2011 6:59 pm    Post subject: Reply with quote

Just to round things off. I found that I had an Excel sheet by Phil Brady, into which I was able to paste a sample of Xcell1's data, which converted it all to his satisfaction to WGS84 datum lat/lon. Very Happy

@pcaouolte. I'll have a look at your ref a bit later, as the code in the Phil Brady sheet is passworded and I can't see what's going on. The sheet has loads of location based formulae, most of which I will never use Sad
Back to top
View user's profile Send private message
Xcell1
Occasional Visitor


Joined: Apr 12, 2011
Posts: 12

PostPosted: Mon May 09, 2011 12:16 pm    Post subject: Reply with quote

Thanks everyone for your help, the spreadsheet M8TJT povided worked a dream, I was able to paste my OS points in and copy and paste out the Lat Longs all in a couple of steps. With Phil's macros this is an impressive bit of work by M8TJT. I'm now working on my KML conversion so hopefully it's all done. I did try Oldie's convertor but I couldn't get it to download.
Back to top
View user's profile Send private message
marbar2k
Regular Visitor


Joined: Dec 02, 2005
Posts: 67

PostPosted: Thu Jan 12, 2012 1:21 am    Post subject: Reply with quote

sorry to drag up an old thread but @M8TjT would i be able to have a copy of that spread sheet?

I have a list of OS grid ref in excel i need to convert so i can set up POI on a tom-tom
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Thu Jan 12, 2012 10:46 am    Post subject: Reply with quote

I havn't still got it but if you Google for 'Phil Brady spreadsheet' it comes up as the first hit. Let me know how you get on Very Happy
Back to top
View user's profile Send private message
Xcell1
Occasional Visitor


Joined: Apr 12, 2011
Posts: 12

PostPosted: Tue Feb 14, 2012 12:17 pm    Post subject: Reply with quote

M8TJT, I've still got the spreadsheet and use it regularly, if you PM with your email I can send it back to you if you want?
Back to top
View user's profile Send private message
PhilBrady
Occasional Visitor


Joined: Jan 21, 2008
Posts: 2

PostPosted: Wed Jun 20, 2012 10:10 am    Post subject: Reply with quote

This thread is a bit old but I'll chip in anyway.

I'm very pleased that you have found my OS routines useful for generating your POIs but the new version which is available at
www.haroldstreet.org.uk/osgb/
may be even more relevant for this job.

It now has:
import/export of GPX files as referred to in this thread.
output of KML for direct input to Google Earth
conversion of postodes (via streetmap lookup)

The html also looks much prettier thanks to help from Phil Newby and redirection is being arranged from the Bangor site.

Regards
Phil Brady
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Wed Jun 20, 2012 10:20 am    Post subject: Reply with quote

Thanks for the update Phil. I'll give it a good looking at later Very Happy
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Thu Jun 21, 2012 6:27 am    Post subject: Reply with quote

Is the OSGB another word for Eastings and Northings ? If so then there is a tool provided by OS called GridInQuest that you can embed into a Macro to perform the conversion to LatLong. GridInQuest used to be free but I think you have to apply for it now.

Once you have the LatLong, KML is just a renamed XML file. You can therefore use Microsofts DOMDocument via MSXML4, again in an Excel macro, to create the XML but wrap KML tags around it.

It's not as easy as it sounds Sad
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message
M8TJT
The Other Tired Old Man
The Other Tired Old Man


Joined: Apr 04, 2006
Posts: 10118
Location: Bexhill, South Sussex, UK

PostPosted: Thu Jun 21, 2012 8:49 am    Post subject: Reply with quote

Kremmen wrote:
Is the OSGB another word for Eastings and Northings ?
No, it's another word for Ordenance Survey Great Britain Very Happy . Having said that, the OSGB mapping does indeed use a form of Eastings and Northings as its location grid reference based on 100km letter defined squares. I.E. TG 51409 13177.

Be careful not to confuse the issue, as 'conventional' Lat/Lon also uses eastings and northings, with south of the equator and west of Greenwich being negative values.

Note also that an alternative way of expressing OS Grid References is as all-numeric eastings and northings. As square TG is six squares across, three squares up within the grid, grid reference TG 5140 1317 can also be expressed as 65140,31317.

It was the form TG 51409 13177 to which the OP was referring.
Back to top
View user's profile Send private message
Kremmen
Pocket GPS Verifier
Pocket GPS Verifier


Joined: Mar 03, 2006
Posts: 7037
Location: Reading

PostPosted: Thu Jun 21, 2012 10:50 am    Post subject: Reply with quote

Yep - fell for that one Laughing

The conversion I've done is (just a few examples):

164955 525530 51.3697884077992 -0.198026382921423
164891 525469 51.3692266964799 -0.198924800800345
166227 530082 51.3801967115448 -0.132204540509515

Which is Eastings and Northings (as supplied to me) converted to Lat & Long

The subsequent KML witch can't be posted here for tag reasons but contains all the elements like 'placemark' , 'styleurl' 'description', 'point' and 'coordinates' etc.

If you save a POI using GE itself the auto built KML file is overly complicated with all manner of elements that are not required, and it doesn't need to be that complicated.
_________________
Satnav:
Garmin 2599 LMT-D (Indoor test rig)
DashCam:
Viofo A119 V3
Car Average MPG :
Back to top
View user's profile Send private message







Posted: Today    Post subject: Pocket GPS Advertising

Back to top
Display posts from previous:   
Post new topic   Reply to topic    Pocket GPS World Forum Index -> Satmap Active 10 All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Make a Donation



CamerAlert Database

Click here for the PocketGPSWorld.com Speed Camera Database

Download Speed Camera Database
22.034 (27 Mar 24)



WORLDWIDE SPEED CAMERA SPOTTERS WANTED!

Click here to submit camera positions to the PocketGPSWorld.com Speed Camera Database


12mth Subscriber memberships awarded every week for verified new camera reports!

Submit Speed Camera Locations Now


CamerAlert Apps



iOS QR Code






Android QR Code







© Terms & Privacy


GPS Shopping