Solved

# Nearest point query

Posted on 2004-04-26
603 Views
I have an access table of points in Latitude/Longitude. I would like to create a query that returns the nearest points given an original point. Any ideas?
0
Question by:mnorma12
• 5
• 3
• 2
• +1

LVL 65

Expert Comment

ID: 10918869

http://www.experts-exchange.com/Databases/MS_Access/Q_11021801.html

0

Author Comment

ID: 10920304
I don't know how much that'll help. I didn't figure this would be simple but it should be possible. Basically I need a query that will return the record with latitude and longitude fields nearest the numbers I give it (since that should be the closest point spatially as well).
0

LVL 10

Expert Comment

ID: 10920695
tthe way I would do it is to assign two variables  the value
to latitude an longitude as In;

Dim currlatitude
Dim currlongitude
Dim Difflatitude
Die  Difflongitude
Dim totaldifference

First of all you need to have you latitude and longitude in decimals not minutes for this to work.

For every time you want to do this you have to create a extra field in the
table where you have the currente coordinates ( If I undesrtand correctly they're already there)
in the table
And then do a loop where you do a substraction of the current longitude and latitude

Difflatitude= currentlatitude - latitude
Difflongitude=currentlongitude -longitude
totaldifference=Difflatitude+difflongitude

An then store  the totaldifference in the  extra field
and finally do a query that will sort the table based on the totaldifference field

Select * from my table
where difference is not null
order by difference

0

Author Comment

ID: 10921497
Good idea, I don't have time to test it out today, but when I come in tomorrow I'll give it a whirl.
The only problem I have with this method is that I have hundreds of thousands of records to go through, but there may be no quick way to do this. If anyone else has a suggestion I'd be happy to try other things as well.
0

LVL 34

Expert Comment

ID: 10922428
maybe something like

SELECT min(sqrt((tblMyTable.Longitude - " & Me.LongToFind & ")^2+ (tblMyTable.Latitude - " & Me.LatToFind & ")^2)) FROM tblMyTable;

Select the closest..  TOPIO may cause probelms with + and - differences adding up to 0.. an Abs may fix it, but mathematcally i think you're better of doing the sqrt(x^2 + y^y) way..

Good Luck!

Dave

0

Author Comment

ID: 10927150
In order to use it in access I used the sqr function instead of sqrt. and it gives me a data type mismatch. I am using abs() but it still doesn't work. If I leave off the Min() it gives me a recordset but I need to find the closest match.
0

LVL 34

Accepted Solution

flavo earned 125 total points
ID: 10927174
SELECT min((((tblMyTable.Longitude - " & Me.LongToFind & ")^2+ (tblMyTable.Latitude - " & Me.LatToFind & ")^2)^(1/2)) as minOfDistanceApart, tblMyTable.Longitude, tblMyable.Latitude FROM tblMyTable GROUP BY tblMyTable.Longitude, tblMyable.Latitude;

Will return the distance between the 2 points and the coresponding longitude and latitude.  any more fields you want added, just add the to the select and group by.

Good Luck!

Dave
0

LVL 65

Expert Comment

ID: 10927185
so much for going to bed Dave!
0

Author Comment

ID: 10927534
I'm probably butchering your SQL statement but this is the query as I have it now

SELECT min((((GNISTable.lon - (-87.76444))^2+ (GNISTable.lat - 46.64083)^2)^(1/2)) as minOfDistanceApart, GNISTable.lon, GNISTable.lat FROM GNISTable GROUP BY GNISTable.lon, GNISTable.lat;

All I did was replace your values with those of my database and also replaced the Me.LongtoFind and Me.LattoFind with real values. When I try to run the query it gives me a syntax error (missing operator) for the expression above.

Any ideas on what I screwing up here?
0

Author Comment

ID: 10927612
After I put an Order By in there and moved around a few paratheses It looks like it is working. I never doubted you, I just could get the stupid thing working on my end.

You earned the points!
0

LVL 34

Expert Comment

ID: 10933119
>> so much for going to bed Dave!

I tried, but this one was intersting

0

## Featured Post

### Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…