Find nearest location

[wnicole]

I have Territory Mgmt database. Sometimes when I get a new lead I need to quickly find other leads in the database that are closest to it.
Im currently not tracking latitude/longitude etc. Do I need to? Is there a way to set certain location marks by address?


If so, once I put in the latitudel/longitude how do I create a search to find the nearest?
Any suggestions, ideas or sample database would be helpful....Thanks

[ItsMe]

How is your VBA coming along? You would need to know Intermediate to Advanced VBA techniques to use apps like MapPoint. If you also have skills in XML, you can use Advanced VBA and XML to employ Google Maps API's.

[thebigthing313]

You may want to look into Google Maps Javascript API - https://developers.google.com/maps/d...on/javascript/
You would have to find the most suitable way for you to have your Access application interact with webpages to get the sort of result you want. Finding the nearest leads may be tricky and may exceed the limitations of free use, requiring you to spend money.

If you want to use Access and only Access, then you will need Lat/Long for each lead. This is just a general outline of what steps to take:

- Geocode all points of interest.
- Find a way to geocode the new lead you are inputting.
- Look into the Haversine Formula to determine the distance between the new lead's coordinates and ALL the other leads (this distance is as the crow flies). Then you can find the nearest ones.

You'll probably want to do most of this using VBA.

[wnicole]

Not advanced at all. Only know self taught coding with VBA. Never trained on it.

[wnicole]

I think I would prefer to just use Access. I can do the leg work of putting all the lats and longs. I just need to know now how to enter a new lat and long and ask the database to tell me what address are closest?
Any simple ideas?

[ItsMe]

You could look up the distance between two points using your web browser and type the value into a table.

[thebigthing313]

wnicole,

If you only want to use Access, you would require the following:

• Each lead must have a stored address, latitude, and longitude
• Any new lead must be entered with all 3 as well

If you are able to do the above, then you can create a user-defined function that uses the Haversine formula to find the distances. For example, you can create a VBA Module and then have in it code that looks something like this

Public Function Haversine(OrigLat as Double, OrigLong as Double, DestLat as Double, DestLong as Double) as Double

    Dim pi as Double, AsinBase as Double
    pi = 4 * Atn(1)

    OrigLat = (OrigLat / 180) * Pi
    DestLat = (DestLat / 180) * Pi
    OrigLong = (OrigLong / 180) * Pi
    DestLong = (DestLong / 180) * Pi
    AsinBase = Sin(Sqr(Sin((OrigLat - DestLat) / 2) ^ 2 + _
    Cos(OrigLat) * Cos(DestLat) * Sin((OrigLong - DestLong) / 2) ^ 2))
    AsinBase = (AsinBase / Sqr(-AsinBase * AsinBase + 1))
    AsinBase = 2 * AsinBase * 6371      '6371 is the radius of the Earth in km
    AsinBase = AsinBase * 0.621371      'this is for conversion to miles
    Haversine = Round(AsinBase, 2)       'rounds to 2 decimal points

End Function

And then in a query, you can have a column with a calculated field that looks like this:

Distance: Haversine(<LatOfNew>,<LongOfNew>, [Table Name]![Latitude], [Table Name]![Longitude])

That should give you a general idea of what to do.

HTH

[wnicole]

When adding lat and long to my table. The field format for it should be what? Long integer? decimal places? Format. Im asking because when I copy the long and lat into the database it only give me whole numbers not the decimal places. Can you assist?

[thebigthing313]

Lat and Long fields should be datatype "Number", with Field Size as "Double".

[wnicole]

I can only hope to be as skilled as you. Thanks so much. I'm now going to try your code. Scary :-)
My thinking is I will put this is my query: Distance: Haversine(<LatOfNew>,<LongOfNew>, [Table Name]![Latitude], [Table Name]![Longitude])
Then create a form and use the button to output the code? Correct.

[ItsMe]

Nice UDF.......

[thebigthing313]

wnicole,

To have this available on a form, in the query, put something like this for a calculated field:

Distance: Haversine([Forms]![Form Name]![Lat Field], [Forms]![Form Name]![Long Field], [Table Name]![Latitude], [Table Name]![Longitude])

Then on your form, you can either have a button to open the query itself, or you can have a Subform whose Recordsource is the query.

Do you want to only return the nearest site? Or any number of them based on distance?

[wnicole]

Just like the top 5 nearest sites

[thebigthing313]

Then I would suggest going the subform route I described above. If you need help trying to apply all this, don't hesitate to ask!

[orange]

wnicole,
How accurate do you need to be? If you don't have lat/long, you may not need them
I have a sample database using Access with Google maps that will find distance between 2 points(lat/long) or 2 zips etc or any combination.
see post #38 here