Solved

Nearest point query

Posted on 2004-04-26
11
611 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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