Access Query to find closest geographic coordinate and return data for the record

Dear all,

I'm trying to write a query to find the closest coordinate in a table named WeatherData and return related fields from two tables.  The geographic coordinate inputs are from the Locations table.

The WeatherData table contains fields,  
ID (auto generated primary key),
Date (String DD/MM/YYY),
Latitude (Double 3 decimal places),
Longitude (Double 2 decimal places),
Factor (Integer).

The query will need to iterate through the Locations table described below and find the closest Latitude and Longitude in the WeatherData table, returning Date, Latitude, Longitude, Factor (WeatherData table), and LocationID, Name (Locations table).

Locations Table:
LocationID (Number Integer)
Name (String short text)
Latitude (Double 2 decimal places)
Longitude (Double 3 decimal places)

I'm not sure where to start.  I have found an example unfortunately its not MSAccess and couldn't translate it.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4c1326a-c5c6-4d3e-a24a-2c818ddc16ca/querying-a-table-containing-a-geography-datatype-field-to-find-closest-point-to-a-user-defined?forum=sqlspatial

Any help advice will be greatly appreciated!
AndyC1000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
I think the closest point is the smallest distance between your location and the location of the station. If I remember the 5-grade geometry from school correctly it is a basic Pythagoras theorem:
So, the closest point will be the result of this query:
SELECT TOP 1
LocationID,
Name,
Latitude,
Longitude,
SQRT(([YourLatitude] - Latitude) ^ 2 + ([YourLongitude] - Longitude) ^ 2) AS Distance
From WeatherData
ORDER BY 
([YourLatitude] - Latitude) ^ 2 + ([YourLongitude] - Longitude) ^ 2

Open in new window

Please note that the code above will crash if the coordinates are exactly at the point of the weather station. To fix it add this check:
Iif([YourLatitude] = Latitude and [YourLongitude] = Longitude, 0, SQRT(([YourLatitude] - Latitude) ^ 2 + ([YourLongitude] - Longitude) ^ 2)) AS Distance

Open in new window

AndyC1000Author Commented:
Thanks!  I've incorporated the IIF statement there will be situations where a coordinates will match.

I need more fields to be returned so I've added them under the select.

I'm running into a syntax error, I've spent a while looking can't see it.  Will this still work?

Select TOP 1
Locations.LocationID,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
WeatherData.Factor,
WeatherData.Date,
IIf ([Locations.Latitude] = [WeatherData.Latitude] and [Locations.Longitude] = [WeatherData.Longitude], 0, SQRT(([Locations.Latitude] -[WeatherData.Latitude])^2 + ([Locations.Longitude] - [WeatherData.Longitude])^2) AS Distance
From Locations, WeatherData
ORDER BY (([Locations.Latitude] - WeatherData.Latitude)^ 2 + ([Locations.Longitude] - WeatherData.Longitude)^ 2);

Open in new window

chaauCommented:
It looks like you are trying to locate the closest weather station for each of the records in the weatherdata table. In this case I think you will be better off with a couple of queries The first one will be the one I have provided before (I used a wrong table before, sorry):
Select TOP 1
Locations.LocationID,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
IIf ([Locations.Latitude] = [YourLatitude] and [Locations.Longitude] = [YourLongitude], 0, SQRT(([Locations.Latitude] -[WeatherData.Latitude])^2 + ([Locations.Longitude] - [WeatherData.Longitude])^2) AS Distance
From Locations
ORDER BY (([Locations.Latitude] - [YourLatitude])^ 2 + ([Locations.Longitude] - [YourLongitude])^ 2);

Open in new window

You can save it as a query and use it throughout your application.
If you want to find the locations for all data you can write a query like this
select WeatherData.Factor,
L.LocationID,
L.Name,
L.Longitude,
L.Latitude,
L.Distance
FROM WeatherData,
(Select TOP 1
Locations.LocationID,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
IIf ([Locations.Latitude] = [WeatherData.Latitude] and [Locations.Longitude] = [WeatherData.Longitude], 0, SQRT(([Locations.Latitude] -[WeatherData.Latitude])^2 + ([Locations.Longitude] - [WeatherData.Longitude])^2) AS Distance
From Locations
ORDER BY ([Locations.Latitude] -[WeatherData.Latitude])^2 + ([Locations.Longitude] - [WeatherData.Longitude])^2) as L

Open in new window

I have not tested the query above. I suspect it can produce a duplication if the point is exactly at the same distance from more than one location. If it does not work, there is a workaround. Let me know and I will send you another solution
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AndyC1000Author Commented:
Thanks.  There's a syntax error in the FROM clause.  I haven't been able to run it.  I'm using the second solution.
AndyC1000Author Commented:
I found the syntax error, it now says Underfined function 'Sqrt' in expression.
chaauCommented:
My bad. It is SQR in Access
AndyC1000Author Commented:
Thanks. It's prompting for an input for WeatherData.Latitude and WeatherData.Longitude instead of looking in the table for the closest lat/long.

SELECT WeatherData.Factor, WeatherData.Time, L.ID, L.Name, L.Longitude, L.Latitude, L.Distance 
FROM WeatherData, (Select TOP 1
Locations.LocationID,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)) AS Distance
From Locations
ORDER BY (Locations.Latitude-WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)  AS L;

Open in new window

chaauCommented:
Yes, this is what I was suspected. I think in this case you should use DLookup.
Please save this query as a DistanceCalculator:
Select TOP 1
Locations.LocationID,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
IIf ([Locations.Latitude] = [YourLatitude] and [Locations.Longitude] = [YourLongitude], 0, SQR(([Locations.Latitude] -[WeatherData.Latitude])^2 + ([Locations.Longitude] - [WeatherData.Longitude])^2) AS Distance
From Locations
ORDER BY (([Locations.Latitude] - [YourLatitude])^ 2 + ([Locations.Longitude] - [YourLongitude])^ 2);

Open in new window

And then just use this in the main query:
SELECT WeatherData.Factor, WeatherData.Time, 
DLookup("ID", "DistanceCalculator", "[YourLatitude]=" &  CStr(WeatherData.Latitude) & " And [YourLongitude]="  &  CStr(WeatherData.Longitude)),
DLookup("Distance", "DistanceCalculator", "[YourLatitude]=" &  CStr(WeatherData.Latitude) & " And [YourLongitude]="  &  CStr(WeatherData.Longitude))
FROM WeatherData;

Open in new window

chaauCommented:
With the query above, I guess you can just retrieve the ID using the DLookup function. And then you can just join with the Locations table and extract other information. Using the DLookup to retrieve all fields maybe not a good idea performance-wise, like this:
SELECT L.Factor, L.Time,
Locations.Name,
Locations.Longitude,
Locations.Latitude,
Locations.LocationID
FROM (SELECT WeatherData.Factor, WeatherData.Time, 
DLookup("LocationID", "DistanceCalculator", "[YourLatitude]=" &  CStr(WeatherData.Latitude) & " And [YourLongitude]="  &  CStr(WeatherData.Longitude)) AS ID
FROM WeatherData) AS L INNER JOIN Locations on L.ID=Locations.LocationID

Open in new window

chaauCommented:
Sorry about that. I stuffed up again. DLookup will not work here. It is unusual that it is taking so long to come up with the solution.
Use this one:
SELECT WeatherData.ID,
Locations.LocationID,
WeatherData.Factor, WeatherData.Time, Locations.LocationID, Locations.Name, Locations.Longitude, Locations.Latitude, Locations.Distance 
FROM WeatherData, Locations,
(SELECT WeatherData.ID, 
MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)))  AS Distance
FROM Locations, WeatherData
GROUP BY WeatherData.ID) AS d
WHERE WeatherData.ID = d.ID AND IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)) = d.Distance

Open in new window

AndyC1000Author Commented:
Thanks for your response. I've been away and haven't been able to reply/  get access to the database to run the solution.

I'm now getting the following message:
The specified field 'Locations.Distance' could refer to more than one table listed in the from clause of your SQL statement.
chaauCommented:
Yes, it needs to be d.Distance, like this:
SELECT WeatherData.ID,
Locations.LocationID,
WeatherData.Factor, WeatherData.Time, Locations.LocationID, Locations.Name, Locations.Longitude, Locations.Latitude, d.Distance 
FROM WeatherData, Locations,
(SELECT WeatherData.ID, 
MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)))  AS Distance
FROM Locations, WeatherData
GROUP BY WeatherData.ID) AS d
WHERE WeatherData.ID = d.ID AND IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)) = d.Distance

Open in new window

AndyC1000Author Commented:
Thanks its running and returning every record in the WeatherData table linked to Locations table with a distance field.

How do I extend this further to return only the records in the weather data table with the closest lat/long (distance field) for each distinct LocationID and Time.  

Time is a date/time field, format d/mm/yyyy.

Can this be nested in the above query?

The output format required is below.  I will put together another query to transform the data into this format.  Thought I'd mention it in case it could easily be integrated.

i.e.   The values under the dates are the values for WeatherData factor.
Location.ID     1/12/2014   2/12/2014
1                       5                   6
2                       6                   7
3                       7                   7
4                       8                   9
....
chaauCommented:
Can you post what the query returns for Location ID 1, 2, 3, 4 and dates 1/12/2014 and 2/12/2014? I.e. what the query actually returns? What field is duplicating?
AndyC1000Author Commented:
The image below is a subset of what the query returns.  

See the first four rows and the last four rows of the table,  I am looking for one record with the closest distance which I assume is the smallest value in the Distance field for dates (i.e. 28/10/2014) and location ID's (i.e. 26).

Output of query.
Hope this makes sense.
chaauCommented:
OK, I think I understand now. The query needs to be adjusted. Try this one:
SELECT WeatherData.ID,
WeatherData.Factor, WeatherData.Time, Locations.LocationID, Locations.Name, Locations.Longitude, Locations.Latitude, d.Distance 
FROM WeatherData, Locations,
(SELECT WeatherData.ID, Locations.LocationID,
MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)))  AS Distance
FROM Locations, WeatherData
GROUP BY WeatherData.ID, Locations.LocationID) AS d
WHERE WeatherData.ID = d.ID And Locations.LocationID = d.LocationID AND IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)) = d.Distance

Open in new window

AndyC1000Author Commented:
Thanks for responding quickly.

I tried the above query it's not returning one record for a Time and LocationID.  

I've attached the output. See below table the 10th row,  after Location ID 9 its for the same date as the first row.

I've also noticed the value for the factors are the same for each block of dates and LocationID's, i.e. first block is 4, second block is 3.

Output of query.
chaauCommented:
It would be great if you could create and empty access database, import these two tables (just a few rows is enough) and upload it here. It will save a lot of time. In the meantime try this one:
SELECT WeatherData.ID,
WeatherData.Factor, WeatherData.Time, Locations.LocationID, Locations.Name, Locations.Longitude, Locations.Latitude, 
IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)) AS Distance 
FROM WeatherData, Locations
WHERE IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2))  IN 
(SELECT MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)))  AS MinDistance
FROM Locations l, WeatherData w
WHERE l.LocationID = Location.LocationID and w.ID = WeatherData.ID)

Open in new window

AndyC1000Author Commented:
I might of confused you with the ID in the WeatherData table, its just an auto generated primary key.  It doesn't contain the location ID.
chaauCommented:
I think I understand now. Have you tried my last query. I have re-worked it completely
AndyC1000Author Commented:
Yes I tried it, produces the same result as before.  I've been putting the database together to post it.
AndyC1000Author Commented:
I've attached the database.
Database1.accdb
chaauCommented:
Thanks for that. Got in 5 minutes. It makes big difference when you have the data. The All-In-One query looks like this:
SELECT Locations.LocationID, Locations.NAME, WeatherData.ID, WeatherData.Time, WeatherData.Factor, 
WeatherData.Latitude, WeatherData.Longitude, wdlDistance.Distance
FROM Locations INNER JOIN (WeatherData INNER JOIN 
(SELECT IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)) AS Distance, Locations.LocationID, WeatherData.ID
FROM Locations, WeatherData INNER JOIN 
(SELECT WeatherData.ID, 
MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2)))  AS Distance
FROM Locations, WeatherData
GROUP BY WeatherData.ID) AS wdDistance ON WeatherData.ID = wdDistance.ID
WHERE (((IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)))=[wdDistance].[Distance]))) AS wdlDistance ON WeatherData.ID = wdlDistance.ID) ON Locations.LocationID = wdlDistance.LocationID;

Open in new window

It looks complicated but it will be easier for your if you understand how I have come up with this query. First I have created wdDistance query that retrieves the closest distance to a location for each weatherdata record. This query looks like this:
SELECT WeatherData.ID, MIN(IIf ((Locations.Latitude = WeatherData.Latitude) and (Locations.Longitude = WeatherData.Longitude), 0, SQR((Locations.Latitude - WeatherData.Latitude)^2 + (Locations.Longitude - WeatherData.Longitude)^2))) AS Distance
FROM Locations, WeatherData
GROUP BY WeatherData.ID;

Open in new window

Then I have created a wdlDistance query that locates a location from the wdDistance query above. It looks like this:
SELECT IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)) AS Distance, Locations.LocationID, WeatherData.ID
FROM Locations, WeatherData INNER JOIN wdDistance ON WeatherData.ID = wdDistance.ID
WHERE (((IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)))=[wdDistance].[Distance]));

Open in new window

Then I have created the FinalQuery that uses the above query and both tables to retrieve extra columns. The query looks like this.
SELECT Locations.LocationID, Locations.NAME, WeatherData.ID, WeatherData.Time, WeatherData.Factor, WeatherData.Latitude, WeatherData.Longitude, wdlDistance.Distance
FROM Locations INNER JOIN (WeatherData INNER JOIN wdlDistance ON WeatherData.ID = wdlDistance.ID) ON Locations.LocationID = wdlDistance.LocationID;

Open in new window

As you can see this final query is quite simple when you use the wdlDistance. If you do not want to use the wdDistance and wdlDistance you can use AllInOneQuery (the first one). It is sort of all-included query that does not depend on any other queries. You can just paste it into your code.

I have attached the database as well.

Now, when we finished with you problem, let me give you an advice. You need to fix your data entry code (application) to ensure that the LocationID is populated at the time of entry. That way you will not be required to run these queries every time
Database1.accdb
AndyC1000Author Commented:
Thanks, I haven't been able to download the database but I copied and ran the queries in my sample database.  Let me know if I haven't run it correctly.

The below table is a subset of the current output.
Current output from all in one query
And I'm after only one record  (closest lat/ long- distance field ) for each date and location.  Below is a sample.   I will try to reformat the results into the second table below once the first table is created.  

Output-Format-2.png
The numbers in the table are the Factor values.
chaauCommented:
OK, I really stuffed up. The query above got you the closest location for each weatherdata record. Obviously, you wanted quite opposite thing.
This query will give the desired result:
SELECT Locations.LocationID, Locations.NAME, WeatherData.ID, WeatherData.Time, WeatherData.Factor, WeatherData.Latitude, WeatherData.Longitude, wdlDistance.Distance
FROM Locations INNER JOIN (WeatherData INNER JOIN 
(SELECT IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)) AS Distance, Locations.LocationID, WeatherData.ID, WeatherData.Time
FROM Locations, WeatherData, 
(SELECT Locations.LocationID, WeatherData.Time, Min(IIf((Locations.Latitude=WeatherData.Latitude) And (Locations.Longitude=WeatherData.Longitude),0,Sqr((Locations.Latitude-WeatherData.Latitude)^2+(Locations.Longitude-WeatherData.Longitude)^2))) AS Distance
FROM Locations, WeatherData GROUP BY Locations.LocationID, WeatherData.Time)  AS wdDistance WHERE (((IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)))=[wdDistance].[Distance]) AND ((Locations.LocationID)=[wdDistance].[LocationID]) AND ((WeatherData.Time)=[wdDistance].[Time])))  AS wdlDistance ON WeatherData.ID = wdlDistance.ID) ON Locations.LocationID = wdlDistance.LocationID;

Open in new window

If you want a three query version, then here it is:
wdDistance:
SELECT Locations.LocationID, WeatherData.Time, Min(IIf((Locations.Latitude=WeatherData.Latitude) And (Locations.Longitude=WeatherData.Longitude),0,Sqr((Locations.Latitude-WeatherData.Latitude)^2+(Locations.Longitude-WeatherData.Longitude)^2))) AS Distance
FROM Locations, WeatherData
GROUP BY Locations.LocationID, WeatherData.Time;

Open in new window

wdlDistance:
SELECT IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)) AS Distance, Locations.LocationID, WeatherData.ID, WeatherData.Time
FROM Locations, WeatherData, wdDistance
WHERE (((IIf(([Locations].[Latitude]=[WeatherData].[Latitude]) And ([Locations].[Longitude]=[WeatherData].[Longitude]),0,Sqr(([Locations].[Latitude]-[WeatherData].[Latitude])^2+([Locations].[Longitude]-[WeatherData].[Longitude])^2)))=[wdDistance].[Distance]) AND ((Locations.LocationID)=[wdDistance].[LocationID]) AND ((WeatherData.Time)=[wdDistance].[Time]));

Open in new window

FinalQuery:
SELECT Locations.LocationID, Locations.NAME, WeatherData.ID, WeatherData.Time, WeatherData.Factor, WeatherData.Latitude, WeatherData.Longitude, wdlDistance.Distance
FROM Locations INNER JOIN (WeatherData INNER JOIN wdlDistance ON WeatherData.ID = wdlDistance.ID) ON Locations.LocationID = wdlDistance.LocationID;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
The database
Database1.zip
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.