Solved

Nearest point query

Posted on 2004-04-26
11
612 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

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.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

751 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