# 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!
Microsoft Access

Last Comment
chaau

8/22/2022 - Mon
chaau

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
``````
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
``````
AndyC1000

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

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

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

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

My bad. It is SQR in Access
AndyC1000

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;
``````
chaau

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

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
``````
chaau

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
``````
AndyC1000

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

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
``````
AndyC1000

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

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?
AndyC1000

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

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
``````
AndyC1000

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.

chaau

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)
``````
AndyC1000

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

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

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

I've attached the database.
Database1.accdb
chaau

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;
``````
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;
``````
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]));
``````
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;
``````
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
AndyC1000

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.