PDA

View Full Version : Sorting GPS marks



Aussiebear
12-17-2013, 03:27 AM
I have a list of some 950 GPS marks used for fishing and would like a method of being able to sort these marks into groupings, or be able to check the validity of those marks within a group to determine the accuracy of those marks. Being in the Southern Hemisphere our GPS marks are based on South and East co-ordinates. I beleive that it would be simple to determine any blocks of Marks (using Max and Min values for both South and East, but unfortunately the shape of the designated groups will not always be square or rectangular. Firstly can we simply assume the Square or Rectangular shape to determine a method, and secondly when that is successful try to develop a polygon shape method if possible.

Please find attached a workbook of GPS Marks, sorted into primary groupings. That is by rough checking the approximate position on a chart and shifting the combination to a group of similar marks. Also included is 12 sets of boundries used to determine BGZ ( Back of Green Zone) sections 1 to 12.

Kenneth Hobs
12-17-2013, 07:56 AM
Analytical geometry is fun. When I get time, I will play with it.

For a point in a polygon: http://www.vbaexpress.com/forum/showthread.php?26336-Polygon-s

I would create a UDF to put the string for the GPS in that format into decimal degrees. Split the string. Roughly it is then a(0)+IntegerPart(a(1))/60+FractionalPart(a(1))/3600

SamT
12-17-2013, 09:22 AM
Calculating Mileage Distance in Excel and Access (http://datapigtechnologies.com/blog/index.php/calculating-mileage-distance-in-excel-and-access/#more-4010)

Aussiebear
12-17-2013, 04:18 PM
After following your links, I have converted all "Time" GPS marks to Decimal Degrees….. and the logic of Andy Pope's code I simply don't yet follow.

Aussiebear
12-17-2013, 05:25 PM
Calculating Mileage Distance in Excel and Access (http://datapigtechnologies.com/blog/index.php/calculating-mileage-distance-in-excel-and-access/#more-4010)

I've tried using the GetMiles function from this link but it errors out with an invalid name message. I know that using the values (decimal degrees) -22.93611111, 151.11388889 and -23.28777778, 150.06111111 it result should be approximately 50.6 nMiles (as measured from the map).

Function GetMiles(lat1Degrees As Double, lon1Degrees As Double, lat2Degrees As Double, lon2Degrees As Double)

Dim earthSphereRadiusKilometers As Double
Dim kilometerConversionToMilesFactor As Double
Dim lat1Radians As Double
Dim lon1Radians As Double
Dim lat2Radians As Double
Dim lon2Radians As Double
Dim AsinBase As Double
Dim DerivedAsin As Double

'Mean radius of the earth (replace with 3443.89849 to get nautical miles)
earthSphereRadiusKilometers = 6371

'Convert kilometers into miles (replace 0.621371 with 1 to keep in kilometers)
kilometerConversionToMilesFactor = 0.621371

'Convert each decimal degree to radians
lat1Radians = (lat1Degrees / 180) * 3.14159265359
lon1Radians = (lon1Degrees / 180) * 3.14159265359
lat2Radians = (lat2Degrees / 180) * 3.14159265359
lon2Radians = (lon2Degrees / 180) * 3.14159265359

AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^ 2))
DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))

'Get distance from [lat1,lon1] to [lat2,lon2]
GetMiles = Round(2 * DerivedAsin * (earthSphereRadiusKilometers * kilometerConversionToMilesFactor), 2)

End Function

Kenneth Hobs
12-17-2013, 06:41 PM
I am not sure what you need getmiles for. While that may be needed for some posts and is fun to do, I did not see that need in your post.

Go to Andy's site and download his example file. http://www.andypope.info/fun/pointinarea.htm

The udf_InPolygon() worked best for me.

Let's look at the unit square. When finding polygon areas, one uses a theory called the double angle area theorem. What you do is to replace the first coordinate as the first and last coordinate in the x,y plane. So, if we start at 0,0 vertice coordinate, it will be the last as well. Going clockwise we then get this below. As you can see, the point 1,1 is not in the square polygon but it is on the polygon which may be a matter of contention. I like that it does that myself.

This sort of analogy should work well for you. Just make your South ordinate the x ordinate and the East ordinate the y ordinate. When setting the region limits, as you showed it, the region would be like the unit square though in the shape of a rectangle most likely. So, you have the min and max x and y's, like the unit square example start at the min, min coordinate and the next clockwise coordinate will be the min max point and so on around the polygon or rectangle in this case with the last point repeated as first for a total of 5 coordinates to define the rectangular polygon. Does that make sense?




X
Y
PtInPoly
udf_InPolygon
Poly X
Poly Y


1
1
FALSE
TRUE
0
0






0
1






1
1






1
0






0
0

Aussiebear
12-17-2013, 09:48 PM
@ Kenneth, I have already downloaded the file, but not sure how its supposed to operate. Obviously I can alter the position of the dot by moving the sliders, I can select each of the chart objects etc but how does this relate to the initial question?

SamT
12-18-2013, 12:56 AM
Ted, My office computer is down, so I refactored it to sharpen my eye for any invalid names. Didn't see any. And, no. I don't understand the math.


Function GetMiles(lat1Degrees As Double, lon1Degrees As Double, lat2Degrees As Double, lon2Degrees As Double)
'All values in nautical miles

'Mean radius of the earth
Const EarthRadiusNauticalMiles As Double = 3443.89849

Dim lat1Radians As Double
Dim lon1Radians As Double
Dim lat2Radians As Double
Dim lon2Radians As Double
Dim AsinBase As Double
Dim DerivedAsin As Double

'Convert each decimal degree to radians
lat1Radians = Degrees2Radians(lat1Degrees)
lon1Radians = Degrees2Radians(lon1Degrees)
lat2Radians = Degrees2Radians(lat2Degrees)
lon2Radians = Degrees2Radians(lon2Degrees)

AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^ 2))
DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))

'Get distance from [lat1,lon1] to [lat2,lon2]
GetMiles = Round(2 * DerivedAsin * EarthRadiusNauticalMiles, 2)

End Function


Private Function Degrees2Radians(Degrees As Double) As Double
Degrees2Radians = (Degrees / 180) * 3.14159265359
End Function

Kenneth Hobs
12-18-2013, 07:12 AM
Aussiebear, in your initial post, you asked how to determine if the coordinate was within a zone/region. This is just another way of saying, is the point in the polygon. Add Andy's Module(s) to your workbook so that you can use the UDF, udf_InPolygon().

Using my unit square analogy, each of your zones can be defined this way as you indicated in your zone table.
S min, E min
S min, E max
S max, E max
S max, E min
S min, E min

Notice how i repeated the first vertice coordinate in the last vertice.

This is the range data input for the last parameter of the udf_InPolygon().

You said that the zone shape may not be so simple but if you know the vertice points, you will have what you need. Just repeat the first vertice as the last as I demonstrated.

IF your zones were always defined by the 4 cells of S min/max and E min/max, a modification of the udf_inPolygon() could be created without the need to layout that data as I did. The ultimate goal would be to write a UDF that can return the zone number for a coordinate. If your zones have more than four coordinates/vertices then you would need to first create a table of all vertices for all zones as I demonstrated for one simple rectangular/polygon zone. udf_InPolygon just returns True or False if the coordinate is in the polygon. Maybe that is all you need, to verify that the zone that is hard coded for the coordinate is accurate?

Obviously, to determine a coordinate's zone number, you have to check for that coordinate in each zone. A simple Select Case would be used to check for the point in each zone.

I can work up a simple example using your real data if needed. If you post your workbook with the converted coordinates, that would save me that step.

Aussiebear
12-18-2013, 02:38 PM
@ Kenneth, I understand the concept of the working out of the vertices points, for I have completed a zone selector basic function which shows the numbers for each of the vertices points within the worksheet attached below. I intend to further develop the actual zones for the other areas (114 in total so far) over the next week or so. What I don't yet follow is how to check if the mark lies within in polygon?

Kenneth Hobs
12-18-2013, 03:22 PM
You did not convert the data points to decimal degrees. I just hard converted them for this example. See the data in red. Maybe it was the hard conversion but it shows that the point was not in zone 9 in the one that I demonstrated. Maybe if the points were properly converted, it would be in zone 9.

Howsoever, if S max is 22, I don't know how 22.1824 would be in that zone.





Unit Zone:
Limits Zone 9 S:
Limits Zone 9 E:



S min, E min
21.45
151



S min, E max
21.45
151.15



S max, E max
22
151.15



S max, E min
22
151



S min, E min
21.45
151














Point South x
Point East y




22.1824
151.0399




In Zone 9:
Original South
Original East



FALSE
22 18.240
151 03.990

Aussiebear
12-18-2013, 03:41 PM
On the Time2 Decimal sheet the table is converted to decimal. I had hidden the relevant columns because I was working on something else at the time of sending you the file.

Aussiebear
12-18-2013, 03:43 PM
Righto, lets go back to the UDF that you are applying. How do you make it work?

Kenneth Hobs
12-18-2013, 05:15 PM
The file that I posted in post 11 is not what I thought I had uploaded. That is very odd. Maybe I did not have enough diet coke.

I did not see a Time2 sheet in either of your two attachments.

IF you like, post a coordinate (S and E), and the vertice coordinates (S and E for each vertice). Keep in mind that as you layout the polygon vertice points, go in clockwise order and repeat the first vertice for first and last vertice. The udf is staight forward. You add the x and y points as input parameters 1 and 2 with the 3rd being the zone range laid out as Andy and I demonstrated.

If you know all vertice points for all zones, I can easily write the routine to show the first zone any coordinate (S E) is in.

Aussiebear
12-18-2013, 06:14 PM
I think I'm getting somewhere. Apprecieiate your support here. I'm going to stay out of here for a while until I finish completing the Zone Boundries. Which I will upload to you.

Kenneth Hobs
12-19-2013, 07:09 AM
For post 11, I meant to post this one instead. Guess I was dashing through the snow...

bubleeshaark
12-19-2013, 02:30 PM
I developed a solution for you, but I'm not entirely sure how to read the coordinates. I saw that some of your coordinates had periods, some had spaces, etc. I also noticed that you have not created a zone for each of your coordinates to fit in.

How I Am Reading The Coords (AKA, the logic behind what I wrote)
Coordinates are broken down by Degree.Hour.Minute. You can break down a coordinate into those three attributes, compare it with another coordinate and determine if it is greater than, less than or the same.
If aDegree > bDegree, tthen aCoord is greater than bCoord.
If aDegree = bDegree and aHour > bHour, then aCoord is greater than bCoord.
If aDegree = bDegree and aHour = bHour, and aMinute > bMinute, then aCoord is greater than bCoord.

Based on both of your post count and mine, this is likely wrong. But I at least hope it helps! :)
10984

Aussiebear
07-17-2014, 08:57 PM
Back again for another bash at this issue. In the workbook attached below, I have two sheets. One with GPS data containing over a thousand GPS marks both in time and decimal format. Along with Group Zones ( areas where fishing is currently allowed) and Green Zones ( where fishing is restricted). I have created 33 named ranges ( each of the Group Zones using tbl + Name of Zone). On the second sheet Data Test, I wanted to test each GPS mark using either the udf_inPolygon function or the PtInPoly function but both error out with a #name error message. What is causing this?

Option Explicit
' Routines as posted on Planet Source Code
'
'
'This is a very useful class to store polygon's vertices and
'check if the polygon is convex or not and if a point is inside
'the polygon or not
'By Raul Fragoso on 14-March-2002

'Declares PI
Public Const PI As Double = 3.14159265358979


Public Function udf_InPolygon(ByVal x As Range, ByVal Y As Range, Vertices As Range) As Boolean


Dim intIndex As Integer
Dim sngTotalAngle As Single
Dim intVerticeCount As Integer

intVerticeCount = Vertices.Rows.Count

' Get the angle between the point and the
' first and last vertices.
sngTotalAngle = GetAngle( Vertices.Cells(intVerticeCount, 1).Value, Vertices.Cells(intVerticeCount, 2).Value, x, Y, Vertices.Cells(1, 1).Value, Vertices.Cells(1, 2).Value)
' Add the angles from the point to each other
' pair of vertices.
For intIndex = 1 To intVerticeCount - 1
sngTotalAngle = sngTotalAngle + GetAngle(Vertices.Cells(intIndex, 1).Value, Vertices.Cells(intIndex, 2).Value, x, Y, Vertices.Cells(intIndex + 1, 1).Value, Vertices.Cells(intIndex + 1, 2).Value)
Next
' The total angle should be 2 * PI or -2 * PI if
' the point is in the polygon and close to zero
' if the point is outside the polygon.
udf_InPolygon = (Abs(sngTotalAngle) > 1e-06)
End Function

The formula used is =If(udf_InPolygon(G10, H10, tblBarcoo),"Yes","No"), where the X value is the South Co-ordinate and the Y value is the East Co-ordinate, and tblBarcoo refers to the Barcoo zone boundaries. I have even tried with using GPS Data![tblBarcoo] but to no effect.

Bob Phillips
07-18-2014, 12:47 AM
The code is in ThisWorbook Ted, put it in a standard module an d the error disappears.

Aussiebear
07-18-2014, 02:20 AM
Thanks Bob.

Aussiebear
07-18-2014, 09:59 PM
Both functions now work but the results are somewhat disappointing, with maybe 40% accuracy. Is there a way to make the search more accurate given that I'm using 6 decimal places with the data?

Bob Phillips
07-19-2014, 05:55 AM
Can you give an example of one or two that aren't accurate Ted, and what you would expect them to give? Oh, and a couple that are accurate.

p45cal
07-19-2014, 06:42 AM
Can you give an example of one or two that aren't accurate Ted, and what you would expect them to give? Oh, and a couple that are accurate.
I second that.
I plotted the tblBarcoo Banks (red dots) and the 9 points you tested on your Data Test Sheet (green dots) and all were outside:
11988

p45cal
07-19-2014, 10:39 AM
Attached is your workbook with the addition of 2 sheets and a macro. The macro adds a sheet and creates in column A hyperlinks to the areas represented by the Names in your workbook beginning with "tbl". These hyperlinks show your areas on a map. Some areas are definitely suspect - could this be the reason that you're only getting a 40% accuracy? I used the macro to create the Sheet1 and I've added comments in column B. I also added Sheet2 which tries to suggest corrections for the Haberfield and Catfish areas. I've highlighted on GPS Data the coordinates which I think should be the same but aren't.

Aussiebear
07-20-2014, 04:03 AM
Thanks for the heads up. I've rechecked the zone boundries and made a couple of adjustments. Its improved the accuracy somewhat but not to any real benefit. What I intend to do next is look for patterns with those marks coming up in two or more zones and find out if the zone boundries are still causing the errors. Some of the early results are clearly showing a non relationship between zones, as in they are not next to each other. Perhaps the mathematical requirements of the system cannot handle the workload.

snb
07-20-2014, 08:31 AM
It would be very helpful to post a sample containing:

1 area to check against

2 gps-cocordinaties to check
, one of which is definitely outside the area to be checked against
- one of which is definitely inside the area to be checked against

p45cal
07-20-2014, 10:03 AM
Ted, give us something concrete to work with; you tell us accuracy is improved, but how are you testing accuracy and specifically, as xld asked, could you supply a few instances of inaccurate as well as accurate results so that we can do some forensics?

Just for fun, I've been playing with Google maps and your data, I tweaked my macro to produce urls which draw several of your zones on a static google map, just so that I can visualise them. The static maps aren't going to be very good for all zones and marks since there's a character count limit to the url of about 2000, so I could only get about 10 zones drawn. Here's one with 6 zones: Six zones (http://maps.googleapis.com/maps/api/staticmap?zoom=9&size=700x500&maptype=terrain&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-22.9481,151.8511|-22.9481,151.7972|-23.1536,151.7972|-23.1536,151.8511|-22.9481,151.8511&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-22.7169,151.7225|-22.7169,151.8667|-22.9478,151.8667|-22.9478,151.8511|-22.7169,151.7225&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-22.6,151.8669|-22.6,151.9333|-22.9478,151.9333|-22.9478,151.8669|-22.6,151.8669&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-23.1539,151.5003|-23.1539,151.7|-23.,151.7|-23.,151.5003|-23.1539,151.5003&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-22.8481,151.5003|-22.8481,151.8508|-23.1536,151.8508|-23.1536,151.5003|-22.8481,151.5003&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-23.2483,151.0003|-23.2483,151.2539|-23.2375,151.2539|-23.2375,151.0003|-23.2483,151.0003)
This will however speed up the forensic process as I/we will be able to check out individual GPS marks in relation to 1 zone (or more) to see whether the result of any udf is right

Could you upload your corrected file too?

Aussiebear
07-23-2014, 12:25 AM
Sorry for the delay, but I've been away meeting my new grandson (5 weeks old). Please find attached a copy of the workbook vbaExpress48476Working Book.xlsm in which I have re-checked the Group boundaries on GPS Data Sheet for inaccuracies, ( I won't stake my life on it but it should be correct now). The Sheet Data Test has the following detail, Col C Initial suggested location of GPS Mark, Col D Physically checked Group name of GPS Mark (Checked against the actual map), Cols G & H Decimal degrees ( anything in red is a row where the formula used suggested more than one location for a GPS Mark), and Cols J:AR showing either the value Y where the formula thinks a GPS mark might belong, and cells with a light green value showing where I believe the Mark would be correct. Note also that some GPS marks have been allocated when no Group Boundaries were available to be checked against. I'm not sure why the system did that other than overloading.

When constructing the Cols J:AR, I built the formulas for each column then dragged each column down individually, often stopping every hundred rows or so so that it didn't overload the system with calculations, but that didn't seem to increase the "correctness" of the results. Very few of the inaccurate allocations were running on or very close to group boundaries, so I'm not sure that any potential overlapping is the cause of the errors.

If all Groups were a box shape (Either square or rectangular), I think the formula could be constructed using 4 values, => the least most East, =< the most east, => the least most South, & =<the most South. However not all figures are four sided, nor square nor rectangular. So we are forced to use the polygon figure. Each mark needs to be checked against numerous arrays of considerable size given that we are in fact using mathematical strings of 8 & 9 units. Can we slow down the checking process, to ensure a better accuracy? Do we need to define the variables ( decimal degrees) to force greater precision with the checking? The length of time taken if it were to be increased wouldn't concern me since this is a kind of one off to create the correct matrix, there after its probably only going to to be one or two new marks on each occasion.

In case anyone is interested I've included a link to the Marine National Park map 17. http://www.gbrmpa.gov.au/__data/assets/pdf_file/0016/28123/Map17-GDA94.pdf. Coming out of Statue Bay, I currently can cover beyond the green zone to the north of the map, Karamea to the north east, all down the eastern side and venture to and around North West Isle in the South east. Some of the non allocated marks will be grouped for fishing out of Stannish, covered in the Map 14, but this not my initial concern.

Aussiebear
07-23-2014, 01:44 AM
@p45cal. Are you entering the marks correctly? Doesn't even look like my groupings.

Aussiebear
07-23-2014, 01:45 AM
It would be very helpful to post a sample containing:

1 area to check against

2 gps-cocordinaties to check
, one of which is definitely outside the area to be checked against
- one of which is definitely inside the area to be checked against

Have a good look at the new workbook, where you will find all the information you have asked for.

p45cal
07-23-2014, 04:19 AM
@p45cal. Are you entering the marks correctly? Doesn't even look like my groupings.
Are we talking the picture in msg#23 (red dots for the group/zone) or the links that the macro creates? If the latter, the macro just grabs your data, no human entering required. If, after clicking a link you examine the url at the top of the browser you can see the coordinate values (it uses all but the last coordinate which should be a repeat of the first - which it incidentally checks upon with a couple of debug.assert statements).
Are we at least in the right part of the world?
Again, can you be specific? Identify just one grouping which "doesn't even look like my grouping" and say what's wrong with it?

Still investigating…

p45cal
07-23-2014, 08:33 AM
I very strongly suspect that your conversion formulae are out.
In both cases, I think you need to add a zero to 3600, this is because you seconds part of the likes of:
22.48.532
viz: 532, is currently being divided by 3600 (60x60), but there are never 532 seconds, more likely 53.2 seconds?

so
=LEFT(C3,2)+(MID(C3,4,2)/60+RIGHT(C3,3)/3600)
becomes:
=LEFT(C3,2)+(MID(C3,4,2)/60+RIGHT(C3,3)/36000)

and:
=LEFT(D3,3)+MID(D3,5,2)/60+RIGHT(D3,3)/3600
becomes:
=LEFT(D3,3)+MID(D3,5,2)/60+RIGHT(D3,3)/36000

later…
and that's a load of bunkum too; looking at the pdf you linked to, I looked at MNP_22_1153
and compared the coordinates on the map with yours.
Yours:
151.36.903
(as if there could be 90.3 seconds in a minute)
map's:
151°36.903'E
which is 151 degrees, plus 36.903 minutes.

So the conversion to decimal should be:
=LEFT(C3,2)+MID(C3,4,100)/60
and
=LEFT(D3,3)+MID(D3,5,100)/60

but to make it a bit more universal for 2 and 3 digit leading degrees we could search for the first decimal point and use the same formula to convert both eastings and southings:
=LEFT(C3,FIND(".",C3)-1)+MID(C3,FIND(".",C3)+1,10)/60

Aussiebear
07-23-2014, 03:22 PM
Okay I'll give that a go and see what happens from there

p45cal
07-23-2014, 03:55 PM
It's looking a lot better, zones groups look a lot more correct, but a few observations on some:

MNP_23_1157 top should be horizontal?
MNP_23_1160 may need extra point northwest of lisajane shoals?
MNP_23_1163 northwest point not north enough? North east point could be a shade more north?
tblPerforated crosses over itself - should L5 change from 151.55.111 to 150.55.111?
tblPerforatedEast first coordinate different from last -change L12?
tblStockyard odd, L217 change from 150.00.000 to 150.50.000?

and for fun:
Sub blah2()
For Each nm In ThisWorkbook.Names
' If Left(nm.Name, 3) = "tbl" Then
http1 = "http://www.mapdevelopers.com/area_finder.php?&points="
http2 = "["
Rng = nm.RefersToRange.Value
LoopEnd = UBound(Rng) - 1
If (Rng(1, 1) <> Rng(UBound(Rng), 1)) Or (Rng(1, 2) <> Rng(UBound(Rng), 2)) Then LoopEnd = LoopEnd + 1
For i = 1 To LoopEnd
http2 = http2 & "[" & -Rng(i, 1) & "," & Rng(i, 2) & "],"
Next i
http2 = Left(http2, Len(http2) - 1) & "]"
myhttp = http1 & http2
Application.Goto nm.RefersToRange
ThisWorkbook.FollowHyperlink Address:=myhttp
'Debug.Assert Rng(1, 1) = Rng(UBound(Rng), 1)
'Debug.Assert Rng(1, 2) = Rng(UBound(Rng), 2)
MsgBox nm.Name
'End If
Next nm
End Sub

Aussiebear
07-23-2014, 09:24 PM
@p45cal Excellant suggestions that you have recommended to use. Almost all GPS marks are now corrected. Of the few remaining ones some were not selected at all or had two or more recommendations, but I will spend the next week at work, going through the issues one by one.

So far, I have constructed a list of which groups are involved in each error, and am rechecking both the groups common boundaries, and the lat out point for the GPS Mark.

I have obtained the MPZ-17 Gladstone map which shows better detail of the MNP-23-1157 zone. At some stage I will correct the group boundary for that Green Zone

Thank you for your assistance

Aussiebear
07-23-2014, 10:28 PM
One other thing that I have noticed is that if a top boundary line is say 22.00.000 South and the mark starts 22.00.001 it will not activate the "Y" value, but simply say the mark does not exits within the group. Is this partly because of rounding? Should I adjust the mark to 22.00.002 the function then says "Y".

p45cal
07-24-2014, 05:09 PM
The udf_InPolygon definitely has its idiosyncrasies, while testing I came across quite a number of wrong results. Andy Pope wrote a version to increase accuracy (see: http://www.vbaexpress.com/forum/showthread.php?26336-Polygon-s&p=183177&viewfull=1#post183177) and I tried using 1000 as a factor; while this again improved accuracy it wasn't 100% by any means (I would expect such a function to be 100% accurate). I'm going to try with PtInPoly later.

I've written another utility to help in testing; designed for use in your Data Test sheet (as in the file you uploaded in msg#28) after correcting the coordinate conversion formulae of course. I placed a button on that sheet in the vicinity of cell H1, which runs the following code.
The way you're meant to use it is you select a cell (with or without a 'Y' in it) from the results grid, then you click the button. It then creates a url which it opens in your browser. It draws the zone/group named in row 1 above the selected cell with the point in columns G:H to the left of the selected cell, on a static google map. Actually, you can use it on multiple cells (contiguous or not), either from the same column (most useful) or from any column(s), the code will try to create a url with the zones needed and the marks plotted, but there is a limit to the size of the url (some 2000 characters) so in practice you can easily have 16 points and one zone plotted, but as the number of zones requested increases the number of marks plottable decreases quickly.
Anyway, it makes it really easy to verify formula results, but be aware that google maps has a usage limit too!

Here's the code, for putting in the Data Test sheet's code-module:
Private Sub CommandButton1_Click()
Dim ZonesPlotted As Range, MarksPlotted As Range, AtLeast1Mark As Boolean
Set ZonesPlotted = Range("H1"): Set MarksPlotted = Range("G1:H1") ' just never to have them as Nothing.
ZoneArray = Array("tblBarcoo", "tblBarcooNorth", "tblBarcooNorthEast", "tblBarcooSouth", "tblBarcooWest", "tblBarren", "tblBGZ1", "tblBGZ2", "tblCapricornGroup", "tblCatfish", "tblCurtisNorth", "tblCurtisSouth", "tblDouglas", "tblEmuPark", "tblFerns", "tblFlat", "tblFlatEast", "tblGoodwin", "tblHaberfield", "tblHummocky", "tblKaramea", "tblKarameaEast", "tblKarameaSouth", "tblKeppelGroup", "tblKeppelSouth", "tblLisaJane", "tblPerforated", "tblPerforatedEast", "tblPinnicales", "tblScallopEast", "tblScallopWest", "tblWideGroundsEast", "tblWideGroundsWest", "tblCorio", "tblStockyard")
zzz = "###.######"
Set CellsToCheck = Intersect(Range("J2:AR1111"), Selection)
Selection.Select
myHttp = "http://maps.googleapis.com/maps/api/staticmap?&size=640x640&scale=2"
'myMarkers = "&markers=size:tiny" markers a bit fuzzy!
myMarkers = "&markers="
'size: (optional) specifies the size of marker from the set {tiny, mid, small}. If no size parameter is set, the marker will appear in its default (normal) size.
'color: (optional) specifies a 24-bit color (example: color=0xFFFFCC) or a predefined color from the set {black, brown, green, purple, yellow, blue, gray, orange, red, white}
'label: (optional) specifies a single uppercase alphanumeric character from the set {A-Z, 0-9}. (The requirement for uppercase characters is new to this version of the API.) Note that default and mid sized markers are the only markers capable of displaying an alphanumeric-character parameter. tiny and small markers are not capable of displaying an alphanumeric-character.

If Not CellsToCheck Is Nothing Then
'MsgBox CellsToCheck.Address
For Each are In CellsToCheck.Areas
For Each colm In are.Columns
'zone plotting:
'check it 's not already plotted:
If Intersect(Cells(1, colm.Column), ZonesPlotted) Is Nothing Then
'look up the name:
myNamedRange = ZoneArray(colm.Column - 10)
'plot the zone:
myHttp = myHttp & PlotZone(ThisWorkbook.Names(myNamedRange).RefersToRange, zzz)
'mark it as plotted:
Set ZonesPlotted = Union(ZonesPlotted, Cells(1, colm.Column))
'Cells(1, colm.Column).Select
End If
For Each cll In colm.Cells
'mark plotting:
'check it 's not already plotted:
If Intersect(Cells(cll.Row, "G").Resize(, 2), MarksPlotted) Is Nothing Then
'plot the mark:
AtLeast1Mark = True
myMarkers = myMarkers & PlotMark(Cells(cll.Row, "G").Resize(, 2), zzz)
'mark it as plotted:
Set MarksPlotted = Union(MarksPlotted, Cells(cll.Row, "G").Resize(, 2))
End If
Next cll
Next colm
Next are
If AtLeast1Mark Then myHttp = myHttp & myMarkers
'Debug.Print myHttp
ThisWorkbook.FollowHyperlink Address:=myHttp
End If
End Sub


Function PlotZone(rngZone As Range, myFormat)
'Debug.Print "Zone " & rngZone.Address
myHttp = "&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033"
Rng = rngZone.Value
LoopEnd = UBound(Rng) 'not done anything about not using last point if it's the same as the first.
For i = 1 To LoopEnd
http3 = http3 & "|" & Format(-Rng(i, 1), myFormat) & "," & Format(Rng(i, 2), myFormat)
Next i
PlotZone = myHttp & http3
'Debug.Print myHttp
End Function


Function PlotMark(rngMark As Range, myFormat)
'size: (optional) specifies the size of marker from the set {tiny, mid, small}. If no size parameter is set, the marker will appear in its default (normal) size.
'color: (optional) specifies a 24-bit color (example: color=0xFFFFCC) or a predefined color from the set {black, brown, green, purple, yellow, blue, gray, orange, red, white}
'label: (optional) specifies a single uppercase alphanumeric character from the set {A-Z, 0-9}. (The requirement for uppercase characters is new to this version of the API.) Note that default and mid sized markers are the only markers capable of displaying an alphanumeric-character parameter. tiny and small markers are not capable of displaying an alphanumeric-character.
Rng = rngMark.Value
http3 = http3 & "|" & Format(-Rng(1, 1), myFormat) & "," & Format(Rng(1, 2), myFormat)
PlotMark = http3
'Debug.Print http3
'Debug.Print " Mark " & rngMark.Address
End Function
You get something like this:
http://maps.googleapis.com/maps/api/staticmap?&size=640x640&scale=2&path=color:0xff0000ff|weight:1|fillcolor:0xFFFF0033|-23.38705,151.046517|-23.38705,151.224083|-23.489383,151.224083|-23.489383,151.046517|-23.38705,151.046517&markers=|-23.453483,151.102917|-23.3979,151.151717|-23.4,151.15|-23.437333,151.052|-23.407,151.183333

Aussiebear
07-25-2014, 01:43 AM
Thank you this new addition, however for the next few days I'm away from my computer and will be unable to try the code.

Aussiebear
07-26-2014, 01:46 AM
Actually in hindsight, it may be better to try and print out each zone as a map with those marks contained within. Would that be hard to do?

p45cal
07-26-2014, 03:14 AM
Actually in hindsight, it may be better to try and print out each zone as a map with those marks contained within. Would that be hard to do?Well, focusing on the word map above, we're already 3/4 of the way there with the last little utility I wrote (mag#37) which gives a static map, but it's limited by the length of the url, so trying to plot 20 points on a zone which is oddly shaped might be pushing it a bit (although there are avenues to shorten the url with 'encoded polylines', the algorithm for creating them (here (https://developers.google.com/maps/documentation/utilities/polylinealgorithm)) is full of holes). Using Google maps API V.3 would give an interactive map but I know little of how to implement it. I'll have an explore of Bing maps which may not have such limitations.
Coming away from the word map for a second, I imagine it would be quite easy to plot the area(s) and points on an Excel xy scatterplot chart, and have no such limitations. I'll have a play with that.

As an aside, I did try the PtInPoly udf and compared it to udf_InPolygon and PtInPoly produced very similar results to the increased accuracy version of udf_InPolygon (with the factor thing included), but it did it out-of-the-box and more quickly too. On balance, I'd use PtInPoly. (The occasional difference in result was when a GPSMark was exactly on the line of a zone.)
So far, I haven't found a GPSMark wrongly categorized with PtInPoly.

What version of Excel are you using? I ask because there are some interesting products that MS has brought out, Power View, Power Map among them, see here (http://www.microsofttrends.com/2014/02/15/powerview-vs-powerpivot-vs-power-bi/) and here (http://blogs.msdn.com/b/powerbi/archive/2014/02/25/power-map-for-excel-now-generally-available-automatically-updated-for-office-365.aspx).

Aussiebear
07-26-2014, 11:44 AM
I think it's excel v 10 for Mac.

p45cal
07-26-2014, 02:56 PM
The attached version of your file in msg#28 has 5 sheets:
GPS Data
The same as yours except that the coordinate conversion formulae have been changed.

Data Test
The highlighting on this one won't be exactly the same as yours (see orig Data Test copied as values instead, for that).
This uses the udf_InPolygon formula, but tweaked to include the factor of 1000 to increase accuracy. Some cells have comments, there are some highlighted rows (rows 187, 736 and 867) being points which are a long way from the rest.
This sheet has 2 buttons, one to produce a map in a browser, the other to produce a chart in excel. More on these buttons below.

Data Test ptinpoly
This sheet is the same as Data Test except the formula used is ptinpoly and there's no highlighting in the results grid.

orig Data Test copied as values
This is just the Data Test sheet, copied over 'as values' to retain your original values and highlights.

Sheet2
This is just a grid comparing the results grids in Data Test ptinpoly and Data Test, to compare the results of the two formulae. There are only 3 cells different (conditionally formatted red).


The buttons on the sheets:
Both buttons to be used in the same way as described in msg#37. When the button to produce an Excel chart is clicked a new sheet is added with a chart on it. Be aware that I've let Excel do its own thing with chart scales, so the aspect ratios will change according to what's being plotted. I've labelled the GPS marks on the chart with the row they came from. It can get very crowded if you, say, plot the entire grid, but if you then zoom the sheet out to 10%, stretch the chart in all directions then zoom the sheet in again you can see the detail. Of course you can safely delete any such new sheets.

I hope all this will work in Excel for the Mac; do say if it doesn't.

…so, the file's too big for attaching, so here's link to it:
https://app.box.com/s/qw86lmaeply4uqf1vn1g

Aussiebear
07-27-2014, 11:53 AM
Thank you for your continual assistance here, I can't download anything until next Friday Australian time as this is being posted from my iPad..

From a user perspective, I will simply increase the view until it covers a wanted area, print that out, and then mark off the relevant latitude and longitudes to show the relationship. Then on the opposite page print out a list of the marks, but also include room to add details like depth, type of bottom and fish type found.

The idea is to gradually build a reference of as many spots as possible.