I've tried googling, but with limited luck - what I want to do is use VBA to read in coordinates from a serial GPS dongle (or bluetooth Android phone mimicking the former), log a "visit" record, and find the nearest matching record on a table of our clients. Has anyone seen an opensource script that will allow this?
Thanks in advance. PG
For the serial port data acquisition see the Serial Port Communications page at my web site. Then you can write records to a table using a SQL Insert query or a DAO Recordset with an AddNew. However finding the nearest matching record will likely require using of geometry to figure out the reading through the table looking for the closest location I'd have to refresh my memory as to the exact equations required.
I'm also thinking that to speed up the search you might want to index the latitude and longitude and start your search at nearby lat/longs. That is limit the initial record set to plus/minus, for example, 0.1 of a lat/long which, just guessing would be about 10 kms square.
Here's a rough rundown of what you will need to do.
Lookup Longitude & Latitude For Your Clients' Addresses:
I posted a question a while back on SO asking how to get GPS Coordinates for an address. You can see that question here. There's actually two functions there for you, one to use Google Maps API and another that uses rpc.geocoder.us. Take your pick. Just be aware that each of them having limitations. Google has licensing restrictions as well as maximum queries per day. Geocoder.us has a limit of one query every 15 seconds and I don't remember what their maximum queries per day is, if they even have a limit.
Obviously, you will need to retrieve the Longitude and Latitude for your addresses beforehand, and store this information along with the address. You could possibly try to come up with a zip code for the area and lookup the addresses by that but it could be horribly inaccurate in sprawling urban areas, especially if you have a lot of customers concentrated in a single zip code. You'll need to take Tony's advice here and query your GPS coordinate data using a between statement to get addresses in the near vicinity.
Get GPS Data from the GPS Device
As Tony has already pointed out, you'll need to use something like an ActiveX control, DLL, or API call to perform serial communications with your GPS Device. I have used MS's communication ActiveX control in the past for getting GPS data and it did work satisfactory.
I don't know where my code is to retrieve the incoming data from the GPS device. It's not exactly trivial if you have never programmed serial communications before. You usually have an OnComm event that fires on incoming data. If I remember correctly you loop until an EOF (End of File) code or bit is found which indicates the end of a data stream. If you use MS's Communications Control you can see some sample code here: http://support.microsoft.com/kb/194922
I think I had to use 4800 in my settings instead of 9600 but your requirements may be different depending what type of device you are using.
Extract The Right Data From Your Incoming GPS Data
Step two is to extract the data you need from the incoming GPS Data. You may even find that you have to change it to match the GPS Data you have stored (see below).
In my sample NMEA sentences (above), the Longitude and Latitude are both in the following format:
ddmm.mmmm
For some programs or API's you may have to convert it into Degrees.Degrees, or to word it another way, dd.dddd. The formula to convert it is:
dd.dddd = ddd + mm.mmmm/60
So, for example, if we want to convert my sample data above to exact Longitude and Latitude in degrees, here's what it would look like:
3731.9404 ----> 37 + 31.9404/60 = 37.53234 degrees
10601.6986 ----> 106 + 1.6986/60 = 106.02831 degrees
Here are some functions I wrote back in 2007 to extract certain parts of the data from the NMEA sentence:
Public Function ExtractLatitude(strNMEAString As String, Optional strNMEAStringType As String = "GPRMC") As String
'This function extracts the latitude from an NMEA string and converts it to Decimal Degrees (as a string).
'To use this function you must specify what string type you are passing in, either GPRMC or GPGGA
Dim aryNMEAString() As String
aryNMEAString() = Split(strNMEAString, ",")
Dim dblMinutes As Single, dblLatitude As Single
Select Case strNMEAStringType
Case "GPRMC"
'Latitude is the Number 3 place in the array (4th place in the string)
If aryNMEAString(2) = "A" Then 'A represents a valid string
dblMinutes = (CDbl(Mid(aryNMEAString(3), 3, 7)) / 60)
dblLatitude = CDbl(Left(aryNMEAString(3), 2)) + dblMinutes
ExtractLatitude = CStr(dblLatitude)
End If
Case "GPGGA"
'Latitude is the Number 2 place in the array (3rd place in the string)
If CDbl(aryNMEAString(2)) <> 0 Then 'If string is invalid it will be 0
dblMinutes = (CDbl(Mid(aryNMEAString(2), 3, 7)) / 60)
dblLatitude = CDbl(Left(aryNMEAString(2), 2)) + dblMinutes
ExtractLatitude = CStr(dblLatitude)
End If
End Select
End Function
Public Function ExtractLongitude(strNMEAString As String, Optional strNMEAStringType As String = "GPRMC") As String
'This function extracts the longitude from an NMEA string and converts it to Decimal Degrees (as a string).
'To use this function you must specify what string type you are passing in, either GPRMC or GPGGA
Dim aryNMEAString() As String
aryNMEAString() = Split(strNMEAString, ",")
Dim dblMinutes As Single, dblLongitude As Single
Select Case strNMEAStringType
Case "GPRMC"
'Latitude is the Number 3 place in the array (4th place in the string)
If aryNMEAString(2) = "A" Then
dblMinutes = (CDbl(Mid(aryNMEAString(5), 4, 7)) / 60)
dblLongitude = CDbl(Left(aryNMEAString(5), 3)) + dblMinutes
ExtractLongitude = CStr(dblLongitude)
End If
Case "GPGGA"
'Latitude is the Number 2 place in the array (3rd place in the string)
If CDbl(aryNMEAString(4)) <> 0 Then
dblMinutes = (CDbl(Mid(aryNMEAString(4), 4, 7)) / 60)
dblLongitude = CDbl(Left(aryNMEAString(4), 3)) + dblMinutes
ExtractLongitude = CStr(dblLongitude)
End If
End Select
End Function
Public Function ExtractSpeed(strGPRMC As String) As Integer
'Expects a GPRMC NMEA Sentence
Dim aryGPRMC() As String, dblSpeed As Double
aryGPRMC() = Split(strGPRMC, ",")
If aryGPRMC(7) <> "" Then dblSpeed = CDbl(aryGPRMC(7))
'Convert knots to MPH
ExtractSpeed = CInt(dblSpeed * 1.15077945)
End Function
Public Function ExtractHeading(strGPRMC As String) As Double
'Expects a GPRMC NMEA Sentence
Dim aryGPRMC() As String
aryGPRMC() = Split(strGPRMC, ",")
If aryGPRMC(8) <> "" Then ExtractHeading = CDbl(aryGPRMC(8))
End Function
Public Function ExtractSatelliteCount(strGPGGA As String) As Integer
'Expects a GPGGA NMEA Sentence
Dim aryGPGGA() As String
aryGPGGA() = Split(strGPGGA, ",")
ExtractSatelliteCount = CInt(aryGPGGA(7))
End Function
