Sorting GPS Marks

[Aussiebear]

I have a list of GPS marks which I need to sort into groups depending on a known gps location for a home port.

For example if my home port is 23.095.40S 150.472.41E and the maximum safe travel range is 65 minutes (Lat/Long), how do I group all GPS marks that fall within these criteria from a master list?

[p45cal]

While your sheet seems to be in degrees and decimal minutes, can you take me through the format for your home port 23.095.40S 150.472.41E ?
eg. 150.472.41E
that's 150 degrees, but after that I'm not sure!

ps. and that 65 minute distance is the same as 1 degree and 5 minutes right? (Later, would you prefer to give that distance in miles/kilometres?)

[SamT]

Hey Ted! Where in the Solar System is your home port? Uranus?

[p45cal]

Somewhere in the vicinity of Rockhampton, Queensland at a guess.

[Paul_Hossler]

Chip to the rescue (as always)


http://www.cpearson.com/excel/LatLong.aspx


Good writeup and explanation of coordinate systems and computing distances


His



Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double, _
ValuesAsDecimalDegrees As Boolean, _
ResultAsMiles As Boolean) As Double


might get you what you want

Paul

[Kenneth Hobs]

As-well-as Chip's site, see if this helps. http://www.contextures.com/excellatitudelongitude.html

I don't know what the 095 means in 23.095.40S. For most of those sorts of functions, you will want to be dealing in decimal degrees I suspect and then radians. So, =RADIANS(65/60) would be the number that your computation between two locations should be less than or equal to if you are comparing radian distance. One site said there were 69.04 miles per degree latitude. To compare to miles, that would then be 69.04*65/60 or about 74.79 miles. You can use =Convert() to convert miles and kilometers if needed.

[Aussiebear]

Righto.... I've stuffed this up big time. Lets for the absolute sake of simplicity just work on the Degrees South & East and assume the distance of safe travel to be 60 minute ( (or 1 degree). The minutes & seconds I will sort out when correct the layout. If my home port is 23 degrees 0 minutes zero seconds South and 150 degrees zero minutes zero seconds East, the overall range for sorting will be 22 to 24 degrees South and 150 to 151 degrees East. Those astute navigators among you will note that because my home port is on the east coast of Australia, its useless to select 149 as the lower range of the Easting range as I would have run aground.

Therefore I need to find all GPS marks within the total range, those who degrees match 22 to 24 degrees East, which also have a 150 to 151 degrees South. I just need to group these relevant marks from the total marks in the listing.

I don't need to know the distances as the Lowrance 7200C GPS will deliver that information for me.

[SamT]

Uhmm, Ted

a simple Sort A then C shows that all but 1 of the examples given at 22 and 23 degrees fit that criteria.

Will you be working with other GPS coordinates also?

Is that all you need or do you want to use Excel.






22
41.239
150
50.553


22
39.86
151
3.71


22
39.39
151
13.99


22
40.25
151
21.16


22
39.39
151
13.99


22
39.86
151
13.71


22
40.25
151
21.16


22
41.239
150
50.553


23
20.863
151
7.893


23
20.236
151
16.949


23
55.98
151
31.15


23
33.29
151
39.33


23
41.5
151
41.4


23
47.086
151
55.576


23
54.42
151
57.49





23
9
150
53


23
20.235
151
16.949


23
20.863
151
7.893


23
33.29
151
39.33


23
41.239
151
15.949

[Aussiebear]

I hate it when you are so tired you overlook the obvious... but this then gets back to where my scrambled mind took a left turn. Can a group of marks in a similar area be plotted on a piece of paper? I ask this because it may show that a aquatic structure runs along a particular direction, which in turn means further investigation is warranted.

Its a small world with the internet, but on a boat in the middle of nowhere,and your new found best mates are starting to mutter "throw the Capt overboard", you need to bring a sense of security to the fore by suggesting "that a way boys". I could have played Capt Jack Sparrow you know.

[p45cal]

If I've got this right…
in the attached on Sheet2, row 2 is the home port, column F shows the distances in minutes from the home port.
Bright green cells are for adjustment by you.
Adjusting H1 (your 65 minute distance) will result in a change of conditionally formatted cells in column F. The conditional format used is shown also in column G.
The formula in column F is a megaformula derived from Sheet1 which is only there to show my working/exploration.

In answer to your latest query regarding plotting the points - I've added an x-y scatter plot below the data on Sheet1 which plots the radian lat/long, and added a filter. If you filter on column M for less than or equal to 65, the chart will update accordingly. This plot is unlikely to be geographical but may serve to show patterns.

Formulae stolen from:
http://www.movable-type.co.uk/scripts/latlong.html
http://www.movable-type.co.uk/scripts/latlong-db.html


Edit post posting:
I went to this site: http://www.darrinward.com/lat-long/ and entered the following which comes from the filtered data columns F ang G on Sheet1, tidied up in MSWord to remove tabs/spaces and add commas (I subsequently found you don't need to tidy up, just paste directly from Excel):

-22.68731667,150.84255
-22.66433333,151.0618333
-22.6565,151.2331667
-22.67083333,151.3526667
-23.34771667,151.13155
-23.33726667,151.2824833
-23.933,151.5191667
-23.55483333,151.6555
-23.69166667,151.69
-22.6565,151.2331667
-22.66433333,151.2285
-22.67083333,151.3526667
-22.68731667,150.84255
-23.15,150.8833333
-23.33725,151.2824833
-23.34771667,151.13155
-23.55483333,151.6555
-23.68731667,151.2658167

and got a geographical plot.

And another one.. this one allows you to paste the same data directly from Excel without tidying up:
http://www.copypastemap.com/index.html

and more:
http://www.hamstermap.com/custommap.html
http://batchgeo.com/
http://www.gpsvisualizer.com/map_input?form=data

[SamT]

Buy the Navigation Chart for $15.70 AU: http://www.msq.qld.gov.au/Charts/Current-editions.aspx

These computerized charts might be more detailed and run $75 -$100 : http://www.hydro.gov.au/webapps/jsp/charts/encVoyagerPack.jsp?id=VOY02

Another Link: http://www.thenavigationcentre.com.au/

For more, Google: "Maritime Charts Queensland Au"

BTW, need any crew? 9 yrs USN, 1 yr PMV and Dive boat. Electronics, Electrical, Carpenter, Firefighting, Damage Control, Deck Hand, Mechanic. ISO permanent position.

[Aussiebear]

Thank you p45cal. Will go through this when I come off swing.

[Aussiebear]

You're hired. Going out on Sunday if weather is good. Departing Rosslyn Bay Marina East Ramp 4.30 am

[JimPark]

Hey Ted! Where in the Solar System is your home port? Uranus?
Uranus you must be joking...........:):)

[Aussiebear]

I have translated the current GPS marks from the old system (Lowrance 7200C) and placed them into the new system (Simrad NSS7), and grouped them into small defined areas known to most fishermen from this area. Now I need to simply expand the groupings into bigger blocks ( apparently allowing 20 per area will not be enough so will rename the units into 40 number blocks). Whilst this "solves' the local area issue, it still leaves a huge task for new areas, if one gains new marks from a different home port.

[SamT]

What is the Simrad NSS7 output format look like. Googling it only returns Simrad equipment ads.

The only location format I'm used to is the old Lat-Long system: 123:23:45:12.345 E 23:45:12.345 N

As for sorting Lat-Long's, it might be easiest to use 2 helper columns and use an Excel function to convert them to decimal degrees

Grouping them into grids only requires that they fall between Lat N and Lat S, and, Long W and Long E.

Using decimal degrees, 2 minutes is roughly 9 Km




Degrees
Minute

Second



Decimal

1
0.041666667
0.000694444


Km
111.3194
4.638310185
0.077305170




BTW, I've just about finished the first iteration of a story board for your Trip log Userform and database sheets. I'll try to post them in that thread this PM.

[Aussiebear]

I have to enter it as Lat Long format. Both systems have a default incremental number system if you press the waypoint button. Initially I had to select each mark on the old system, then go to Show on Map to determine where it was. Write down the co-ordinates and the Location ID then convert it into a grouping based on a structure such as Karamea Banks, Perforated, LisaJane Shoals, Manifold, Wide Grounds etc.

[SamT]

How about UDF's to convert Lat-Longs into DLat-DLongs, and Kms to DDegrees.

A set of points for each Group in the format:



Group

Member
Lat
Long
DLat
DLong
Range


A
1




0.089832


A
2




0.044916


A
3




0.044916


B
1




0.089832


B
2




0.044916




Group = Karamea Banks
Member is a set of point IDs that defines the center line of the group, assuming that the Group perimeter is irregular in shape.
Range for each Point ID is given in DDegrees per desired KMs from center point to perimeter. In these cases; 10Km and 5 Km. (One Kilometer = 0.008983157 Decimal Degrees.)


By comparing known locations, in DDegrees, to the values in the DLat and DLong Columns, Plus-Minus Range, you can assign each location to a group.

To visualize the resultant Group A, take 3 paper squares, one at 2x2 and two at 1x1, and independantly arrange them on flat surface. You will see that the pieces don't have to be touching and that they can overlap. Note that each square is twice the +/- Range value in size.

Sorry about taking so long to upload that Trip Log. I think I'm going to have to use a Sub Form for the actual Fishing log.

[Tommy]

I worked a thread here somewhere where I sorted a lot of coordinates for a helicopter flight path. I will attach the workbook if you want to look at it. I have some pics that need to go with it so that is why it is a zip file.