Lookup then find closest entry based on 2 values

[dustaccess]

I am using Access 2007 and am building a database of postal codes & lat/long coordinates. I want to end up with something where you can input a postal code, then return an address from a different table with the nearest latitude & longitude. I have seen many examples of "nearest value" code but none that incorporate finding the minimum absolute difference between 2 values.

In other words, I have 2 tables: PostalReference and SiteList. PostalReference contains every postal code & its associated lat-long:

A0A 47.0073 -52.9589
B0H 45.6051 -61.6975

et cetera. I want to be able to input a postal code, then find the associated lat/long and the entry with the nearest lat/long from the SiteList table.

[mcupito]

You didn't tell us how the 2 tables are connected. Is there a PK in PostalReference that is a FK in SiteList that you use to look up these values?

[dustaccess]

There are matching postal codes in both databases that match up. I have seen that article; the problem I have is in looking up an inputted postal code (like a parameter, [postal code]) and finding its lat & long, then looking up the closest postal code in another table based on the lat & long.

So, that article would be perfect if I was inputting latitude and finding closest latitude, but that is not quite what I need.

[NeoPa]

Not clear on the question, but I expect you need to have three steps to the process :

1. Convert Post Code to Lat/Long.
2. Search for closest match in Lat/Long table.
3. Convert reult back to Post Code.

Is there any of those steps you feel you need help with?

[dustaccess]

Yes, except for 2. It is to search for the closest match in a different table. I need help for all of those steps. Specifically I cannot find the answer to:

1. How to take inputted parameter and find the lat/long from it
2. How to find the nearest value of lat/long in the SiteList table (the min (abs ()) code I have seen examples of do not account for needing to find the nearest of 2 paired values)
3. Return the entry with that closest lat/long.

I have been doing a lot of googling trying to find the answer, to no avail. If this has already been answered somewhere please point me in the right direction.

[mcupito]

Okay - for 1) If you are inputting the postal code into a text box or a form, what is the name of the control on the form for the query? That would go in the "Criteria" section of the query.

Like [Forms]![YourForm]![PostalCodeTxt]

By the design of RDMS, because the PostalCode is a PK, it would give you the correlating Lat/Long associated with the value you pass in for the Postal Code.

For 2.) You're going to have to find the difference of all lat/long values that pertain to the value from #1 within the SiteList Table.

Maybe you can do this with a Temp Table?

For 3) You would take the difference from #2) and take the DMin() of the [DifferenceField] that you're going to create. That way, you are getting the smallest difference in distance, thus - the closest Site.

I believe that is the correct logic. If not, someone should correct me (like usual :) )

[NeoPa]

In your OP (Original Post) you say :
"PostalReference contains every postal code & its associated lat-long"

Getting one from the other is a simple matter running a query with a filter that shows what you already have. Is this something you're likely to have problems with (As this is very much the very basics of working with databases so that could represent a problem)?

[Rabbit]

For 1, you can use a query that takes your parameter and returns the lat and long.

For 2, you can use the haversine formula to calculate the distance. If you don't know the formula, you can read more about it here: https://en.wikipedia.org/wiki/Haversine_formula

For 3, you take the distances calculated from 2 and find the minimum.

Depending on how many sites you have, I would suggest against calculating it every time. It can be an expensive operation and take a while if you have a lot of sites. Instead I would calculate it once for each site and store a foreign key to the sites in the postal code table.

[dustaccess]

Rabbit, I didn't know of the Haversine formula, and the breakdown of the logic is what I needed. Thanks so much everyone for all your help!

[mcupito]

Glad you figured it out.