Find closest Latitude/Longitude in a table using actual location 1

[Vulton]

My code works but is WAY WAY to slow. Please help me to find a better/faster way to find the closest record to given cordinates.

Currently I have code that when given a latitude and longitude it will go record by record thru a table with latitudes and longitudes calculating the distance between the 2 cordinates. As is cycles thru the records it keeps the closest distance records information. At the end of the function it returns the closest "job info" to the given latitude and longitude.

Table is something like
C_JobNum C_PhaseNumber C_Latitude C_Longitude

2345 2.2 34.4356 -123.456
2432 1 25.4653 -435.1231
.
.
.

Option Compare Database
Option Explicit

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MySQL As String
Dim RC As Integer
Dim RI As Integer
Dim CompareDistance As Double
Dim StoreDistance As Double
Dim StoreJobNumber As String
Dim StorePhaseNumber As String

Public Function GeoFence(SearchLatitude As Variant, SearchLongitude As Variant) As Variant

If SearchLatitude > 0 Or SearchLongitude > 0 Then

    Set MyDB = CurrentDb
    MySQL = "SELECT C_JobNum, C_PhaseNumber, C_Latitude, C_Longitude FROM dbo_TblContract"
    Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    While Not MyRS.EOF
    
        MyRS.MoveLast
        RC = MyRS.RecordCount
        MyRS.MoveFirst
        
        StoreDistance = Distance(CDbl(SearchLatitude), CDbl(SearchLongitude), MyRS.Fields("C_Latitude").Value, MyRS.Fields("C_Longitude").Value)
        StoreJobNumber = MyRS.Fields("C_JobNum").Value
        StorePhaseNumber = MyRS.Fields("C_PhaseNumber").Value
        
        For RI = 1 To RC
        
            CompareDistance = Distance(CDbl(SearchLatitude), CDbl(SearchLongitude), MyRS.Fields("C_Latitude").Value, MyRS.Fields("C_Longitude").Value)
            
            If StoreDistance > CompareDistance Then
                StoreDistance = CompareDistance
                StoreJobNumber = MyRS.Fields("C_JobNum").Value
                StorePhaseNumber = MyRS.Fields("C_PhaseNumber").Value
            End If
            
            MyRS.MoveNext
        
        Next RI
        
    Wend
    
    MyRS.Close
    Set MyRS = Nothing
    MyDB.Close
    Set MyDB = Nothing
    
    GeoFence = StoreJobNumber & " " & StorePhaseNumber & " " & StoreDistance

End If

End Function

Public Function Distance(lat1 As Double, Long1 As Double, lat2 As Double, Long2 As Double) As Double

lat1 = lat1 / 57.29577951
lat2 = lat2 / 57.29577951
Long1 = Long1 / 57.29577951
Long2 = Long2 / 57.29577951

If lat1 = lat2 And Long1 = Long2 Then
    Distance = 0
    Else
    If (Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(Long1 - Long2)) > 1 Then
        Distance = 3963.1 * ArcCos(1)
        Else
        Distance = 3963.1 * ArcCos(Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(Long1 - Long2))
    End If
End If

End Function

Function ArcCos(ByVal X As Double) As Double
    If X <> 1 Then
        ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
    Else
        ArcCos = 0
    End If
End Function

Thanks In Advance,
Mark

 

[kjv1611]

Can you find what you are looking for using a Query? If so, do your search with a SQL statemtent. That will probably run tons faster than using a recordset.

Another option might be to use the DLOOKUP function.

 

[Vulton]

I don't know how to make a query that will lookup "the closest" value and I don't think DLOOKUP will do that either.

 

[traingamer]

One way would be to write a query that selects the absolute value of the difference in latitude (so no negative numbers) and the absolute difference in latitude, adds them together and selects the lowest one (MIN function).

If you have some idea of the maximum distance any point could be from one of your readings, you could futher limit the selection to a narrower range: i.e. where latitude between yourlat-10 and yourlat+10 or whatever.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Vulton]

Greg,
Great Idea keeping it simple =) and it works kind of ...

I have this SQL statement...

SELECT Min(Abs([C_Longitude]- " & SearchLongitude & ")+Abs([C_latitude]- " & SearchLatitude & ")) AS NearestJobPhase FROM dbo_TblContract;

This gives me a value returned in NearestJobPhase.

But I need to also return the C_JobNum and C_PhaseNumber for the record the MIN function is returning. How do I add that to the SQL statement and just get one record with all three fields?

Thanks in advance,
Mark

 

[kjv1611]

If you're just wanting to add the 2 fields in that you mentioned, it'd just be this:

[blue]Dim strSQL As String
strSQL = [/blue]"SELECT Min(Abs([C_Longitude]- " & SearchLongitude & ")+Abs([C_latitude]- " & SearchLatitude & ")) AS NearestJobPhase[blue], C_JobNum, C_PhaseNumber[/blue] FROM dbo_TblContract;"

The text in [blue]blue[/blue] was added.

 

[Vulton]

I tried that already and I get a runtime error '3122':
You tried to execute a query that does not include the specified expression 'C_JobNum' as part of the aggregate function.

 

[kjv1611]

Hmm, try adding a Group By Claus at the end:

Dim strSQL As String
strSQL = "SELECT Min(Abs([C_Longitude]- " & SearchLongitude & ")+Abs([C_latitude]- " & SearchLatitude & ")) AS NearestJobPhase, C_JobNum, C_PhaseNumber FROM dbo_TblContract [blue]GROUP BY C_JobNum, C_PhaseNumber[/blue];"

 

[Vulton]

Now the function will execute but returns the first C_JobNum and C_PhaseNumber from the table rather than the correct C_JobNum and C_PhaseNumber for the record the Min statement is returning.

 

[gmmastros]

I think it's odd that you have a Longitude that is -435.1231.

Latitude and Longitude values are representative of angles, so anything greater than 360 is odd. In fact, the values for both should be between -180 and 180.

With your latitude and longitude values in a 'normalized' range, you can simplify your calculations by using pythagoreans theorem to get the closest. Of course, the distance won't be right, but it will return the closest value.

Your code would look something like...

Select c_JobNum,
       Min(Sqrt((c_Latitude - Latitude)^2 + (c_Longitude - Longitude)^2)) As FakeDistance
From   TableName
Group By c_JobNum

The syntax may be a little off because I am more familiar with SQL Server than I am with Access, but the process should be the same.

Distance = Square Root( (Lat1 - Lat2) * (Lat1 - Lat2) + (Lon1 - Lon2) * (Lon1 - Lon2))

If you need to know the actual distance, then calculate it after you determine the closest one.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Vulton]

Greg,
I ended up using your math with George's SQL from another post. Thanks for the help!

Here is the code I ended up with...

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MySQL As String

Public Function NearestGeoFence(SearchLatitude As Variant, SearchLongitude As Variant) As Variant

If SearchLatitude <> 0 Or SearchLongitude <> 0 Then

    Set MyDB = CurrentDb
    
    MySQL = "SELECT * From dbo_TblContract "
    MySQL = MySQL & "Where (Abs([C_Longitude] - " & SearchLongitude & ") "
    MySQL = MySQL & "+ Abs([C_latitude] - " & SearchLatitude & ")) "
    MySQL = MySQL & "= (Select Min(Abs([C_Longitude] - " & SearchLongitude & ") "
    MySQL = MySQL & "+ Abs([C_latitude] - " & SearchLatitude & ")) "
    MySQL = MySQL & "From dbo_TblContract)"
   
    Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset, dbSeeChanges)
    
    NearestGeoFence = MyRS.Fields("C_JobNum").Value & " " & MyRS.Fields("C_PhaseNumber").Value

End If

End Function

thanks again,
Mark