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

Xcell1 · Occasional Visitor Joined: Apr 12, 2011 Posts: 12

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)

M8TJT · Posted: Wed Apr 27, 2011 7:50 pm Post subject:

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

pcaouolte · Posted: Wed Apr 27, 2011 8:06 pm Post subject:

Have a look here for an excel macro to convert osgb to lat, long.
_________________
Paul

Xcell1 · Occasional Visitor Joined: Apr 12, 2011 Posts: 12

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.

Oldboy · Posted: Thu Apr 28, 2011 2:36 pm Post subject:

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 v1140

M8TJT · Posted: Thu May 05, 2011 6:59 pm Post subject:

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

Xcell1 · Occasional Visitor Joined: Apr 12, 2011 Posts: 12

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.

marbar2k · Regular Visitor Joined: Dec 02, 2005 Posts: 67

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

M8TJT · Posted: Thu Jan 12, 2012 10:46 am Post subject:

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

Xcell1 · Occasional Visitor Joined: Apr 12, 2011 Posts: 12

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?

PhilBrady · Occasional Visitor Joined: Jan 21, 2008 Posts: 2

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

M8TJT · Posted: Wed Jun 20, 2012 10:20 am Post subject:

Thanks for the update Phil. I'll give it a good looking at later Very Happy

Kremmen · Posted: Thu Jun 21, 2012 6:27 am Post subject:

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

_________________
DashCam:
Viofo A119 V3

M8TJT · Posted: Thu Jun 21, 2012 8:49 am Post subject:

Kremmen · Posted: Thu Jun 21, 2012 10:50 am Post subject:

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.
_________________
DashCam:
Viofo A119 V3

	PocketGPS
	Web