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

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

Select allOpen 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

Select allOpen in new window

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);

Select allOpen in new window

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);

Select allOpen 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

Select allOpen 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

Thanks.  There's a syntax error in the FROM clause.  I haven't been able to run it.  I'm using the second solution.

I found the syntax error, it now says Underfined function 'Sqrt' in expression.

My bad. It is SQR in Access

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;

Select allOpen in new window

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);

Select allOpen 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;

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

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.

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

Select allOpen in new window

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
....

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?

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).


Hope this makes sense.

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

Select allOpen in new window

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.

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)

Select allOpen in new window

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.

I think I understand now. Have you tried my last query. I have re-worked it completely

Yes I tried it, produces the same result as before.  I've been putting the database together to post it.

I've attached the database.
Database1.accdb

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;

Select allOpen 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;

Select allOpen 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]));

Select allOpen 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;

Select allOpen 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

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.

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.  


The numbers in the table are the Factor values.

The database
Database1.zip