Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 622
  • Last Modified:

Nearest point query

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
mnorma12
Asked:
mnorma12
  • 5
  • 3
  • 2
  • +1
1 Solution
 
rockiroadsCommented:
dunno about this, closest I could find was this, dont know if this will help u any

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

0
 
mnorma12Author Commented:
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
 
TOPIOCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
mnorma12Author Commented:
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
 
flavoCommented:
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
 
mnorma12Author Commented:
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
 
flavoCommented:
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
 
rockiroadsCommented:
so much for going to bed Dave!
Pretty addictive is EE
0
 
mnorma12Author Commented:
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
 
mnorma12Author Commented:
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
 
flavoCommented:
>> so much for going to bed Dave!
>> Pretty addictive is EE

I tried, but this one was intersting

Glad it all worked out...
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now