The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Solved

Posted on 2009-07-09

I am new to c# and need to write a program that will process GPS postion data from a sql server database and then calculate the total distance traveled for each unit listed by date. Each record contains a transaction id, date/time stamp, unit id, latitude and longitude. I have bound this table to a dataset. I also have a class for the haversine formula which will accepts the fields LAT (A) LON (A), LAT(B), LON (B). Where I am stuck is how to process the data from the data set (Keeping in mind that the data needs to be read one record at a time) and then loop through the records, apply the haversine formula then subtotal by unit and date. The final step would be to insert a record into a new table that contains the Equipment ID, Date, and total miles . Attached is the class I plan to use and sample data. Any suggestions would be greatly appreciated.

Haversine---Copy.txt

Data.txt

Haversine---Copy.txt

Data.txt

3 Comments

http://www.codeproject.com/KB/cs/distancebetweenlocations.aspx

http://megocode3.wordpress.com/2008/02/05/haversine-formula-in-c/

```
Dim dr As SqlDataReader = myCommand.ExecuteReader()
lngRecordCount = 1
While dr.Read()
If lngRecordCount > 1 Then
strEquipID = dr(0)
strInsertDate = DateValue(dr(1))
dblLat2 = dr(2)
dblLon2 = dr(3)
' compare the Equipment ID and InsertDate of the two records to see if they changed. Reset All variables and start over if they did.
If strPriorEquipID <> strEquipID Or strPriorInsertDate <> strInsertDate Then
'Write the prior equipID......
' This IF statement is a temporary work around for faulty data. Lat & Lon need to have a decimal else the haversine brings back bad data
If totalDistance >= 0 Then
myCommandWrite = New SqlCommand("INSERT INTO tblSalOutput VALUES ('" & strPriorEquipID & "', '" & strPriorInsertDate & "', " & totalDistance & ",'" & strBranch & "')", myConnectionWrite)
ra = myCommandWrite.ExecuteNonQuery()
' MessageBox.Show("New Row Inserted" & ra)
End If
lngRecordCount = 1
totalDistance = 0
Else
' Now that you have lat1-lat2 & lon1-lon2, apply your formula here and write back to a serpare table
tmpDistance = distance(dblLat1, dblLon1, dblLat2, dblLon2)
totalDistance = totalDistance + tmpDistance
End If
End If
strPriorEquipID = dr(0)
strPriorInsertDate = DateValue(dr(1))
dblLat1 = dr(2)
dblLon1 = dr(3)
strBranch = dr(4)
lngRecordCount = lngRecordCount + 1
End While
Public Function distance(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double, _
Optional ByVal unit As Char = "M"c) As Double
Dim theta As Double = lon1 - lon2
Dim dist As Double = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + _
Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * _
Math.Cos(deg2rad(theta))
dist = Math.Acos(dist)
dist = rad2deg(dist)
dist = dist * 60 * 1.1515
If unit = "K" Then
dist = dist * 1.609344
ElseIf unit = "N" Then
dist = dist * 0.8684
End If
Return dist
End Function
Public Function Haversine(ByVal lat1 As Double, ByVal lon1 As Double, _
ByVal lat2 As Double, ByVal lon2 As Double, _
Optional ByVal unit As Char = "M"c) As Double
Dim R As Double = 6371 'earth radius in km
Dim dLat As Double
Dim dLon As Double
Dim a As Double
Dim c As Double
Dim d As Double
dLat = deg2rad(lat2 - lat1)
dLon = deg2rad((lon2 - lon1))
a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) + Math.Cos(deg2rad(lat1)) * _
Math.Cos(deg2rad(lat2)) * Math.Sin(dLon / 2) * Math.Sin(dLon / 2)
c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a))
d = R * c
Select Case unit.ToString.ToUpper
Case "M"c
d = d * 0.62137119
Case "N"c
d = d * 0.5399568
End Select
Return d
End Function
Private Function deg2rad(ByVal deg As Double) As Double
Return (deg * Math.PI / 180.0)
End Function
Private Function rad2deg(ByVal rad As Double) As Double
Return rad / Math.PI * 180.0
End Function
```

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Title | # Comments | Views | Activity |
---|---|---|---|

Need help how to find where my error is in UFD | 6 | 25 | |

Return 0 on SQL count | 24 | 28 | |

Sql server, import complete table, using vb.net | 9 | 32 | |

SQL Server: Unable to remove duplicate sets in Header/Detail | 6 | 21 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**8** Experts available now in Live!