Solved

Nearest point query

Posted on 2004-04-26
11
606 Views
Last Modified: 2008-03-03
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
Comment
Question by:mnorma12
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10918869
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
 

Author Comment

by:mnorma12
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

by:TOPIO
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:mnorma12
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

by:flavo
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

by:mnorma12
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

by:
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

by:rockiroads
ID: 10927185
so much for going to bed Dave!
Pretty addictive is EE
0
 

Author Comment

by:mnorma12
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

by:mnorma12
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

by:flavo
ID: 10933119
>> 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now