Sorting logs by corordinates using a spread sheet and Excel, suggestions for methods of sorting wanted.

[Yorkshire_Pud]

The aim is to simplify sorting the logs into the different countries in which the drones were flown. Obviously I can copy and paste a pair of coordinates form each log into a browser search and look at the results but that is extremely tedious.
There are nearly 2,000 logs from nearly 1,000 drones.

At the moment I am in one sort sorting via Latitude and then Longitiude.
It works quite well fo 'isolated' countries such as Australia, New Zealand and I suspect it will work quite well for most of Canada and Alaska but when I get into places where 'overlapping ' countries in term of lat or long its going to get tedious.
E.g I have just finished sorting from 10or so degs S of the equator to the equator and it I was bouncing from Asia to Oceania to S.America to Africa to Asia to the Caribbean.
Does anyone know of a 'clever' way of sorting the longitudes so that Excel will still sort by Latitude 'first' but still keep the longitiudes grouped together?

 

[YRauste]

I think you need some kind of GIS (geographic Information System) approach.

 

[Yorkshire_Pud]

Thanks, looking at that now.

 

[Dronebike]

You need a database. Excel is a great collection tool but what you need is a watched folder that automatically loads the spreadsheets. Then you can query that data against GIS info from another source.

 

[Xitor]

Well, considering I'm attending the "global excel summit" this week, I couldn't let this challenge pass. Rather than try and sort by coordinates, seems like it would be easier to just dump coordinates into a tool that returns country (reverse geocode), then just sort by that. Works, but takes a few minutes, not sure how how well it would work with hundreds or thousands of locations. May want to do it in batches, and make sure you copy the data out ASAP so its not refreshing country every time the work sheet recalculates. Of course you need to unblock macros to get it to work and see the second tab in the workbook about enabling MS XLM6.0 to get the API to work.

 

[Dronebike]

Doing it in excel is a great challenge and demonstration of your skills. Be interesting to build a database project as phase 2. Good luck with your project.

 

[Xitor]

Pulling the data is taking about 7 seconds a row, so at that rate about 4 hours for 2000 records. Depending on your location / latency may be different, but hands on time should only be a few minutes and the rest runs in the background. This new version you just need to provide the latitude and longitude and it just runs through them one at a time. No links or formulas so you can work with file without it getting stuck in an update loop. To test, had ChatGPT give me 100 random country locations around the world, and all of them were identified with this tool. Did try to break it by picking a location in the ocean, and if you're more than about 10 miles out to sea you get the same results as clicking in the middle of the ocean, "Unable to Geocode." You could use the tool to grab the data, or just view it as a validation of the code, and just move the vbscript to your workbook.