Sorting GPS marks

[Aussiebear]

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]

Analytical geometry is fun. When I get time, I will play with it.

For a point in a polygon: http://www.vbaexpress.com/forum/show...6336-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]

Calculating Mileage Distance in Excel and Access

[Aussiebear]

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]

Calculating Mileage Distance in Excel and Access

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]

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]

@ 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]

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]

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]

@ 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]

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]

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]

Righto, lets go back to the UDF that you are applying. How do you make it work?

[Kenneth Hobs]

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]

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]

For post 11, I meant to post this one instead. Guess I was dashing through the snow...

[bubleeshaark]

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!
VBAX GPS Marks.xlsm

[Aussiebear]

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?

[vba]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[/vba]

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]

The code is in ThisWorbook Ted, put it in a standard module an d the error disappears.

[Aussiebear]

Thanks Bob.