-
Sep 3rd, 2008, 12:45 AM
#1
Thread Starter
Frenzied Member
Searching for 'closest to'
I'm using VB6 with ADODB on an Access 2000 DB.
I have a table full of zip codes mapped to their Longitudes and Latitudes.
It's easy to feed it a Zip and get the Lon and Lat.
But I also want to be able to feed it a Lon and Lat to find the nearest Zip Code.
Now I could do it this way:
Code:
sSQL = "SELECT * FROM tblZip WHERE LONGITUDE <= " & dblLon + 0.0005 & _
" AND LONGITUDE >= " & dblLon - 0.0005 & _
" AND LATITUDE <= " & dblLat + 0.0005 & _
" AND LATITUDE >= " & dblLat - 0.0005
That could return a range of zips/lon's/lat's that I'd have to filter to find the one closest to the original coordinates.
What I'd like to know is, is there a way to write the sSQL so that it only returns the LONGITUDE and LATITUDE that are closest to dblLon and dblLat?
P.S. I haven't been here for quite a while, how are the <vbcode> tags done now?
Last edited by longwolf; Sep 4th, 2008 at 08:49 AM.
-
Sep 3rd, 2008, 01:38 AM
#2
Re: Searching for 'closest to'
Back to early days in high school: Find the shortest distant from a given point to a set of other fixed points: I recall Pythagoras.
Assumed that we have a flat Earth. Hope this help (untested):
Code:
sSQL = "SELECT Z1.* FROM tblZIP AS Z1 " & _
"WHERE (Z1.LONGITUTE-" & dblLon & ")^2+(Z1.LATITUTE-" & dblLat & ")^2 = " & _
"(SELECT MIN((Z2.LONGITUTE-" & dblLon & ")^2+(Z2.LATITUTE-" & dblLat & ")^2) " & _
"FROM tblZIP AS Z2)"
-
Sep 3rd, 2008, 01:45 PM
#3
Re: Searching for 'closest to'
Originally Posted by longwolf
P.S. I haven't been here for quite a while, how are the <vbcode> tags done now?
Welcome back!
The old VBCode tag was a "dodgy hack", and can't cope with the current forum software.
Now you can either use [code] [/code] , or [HIGHLIGHT="vb"] [/HIGHLIGHT] , or the equivalent buttons above where you type.
Most of us use Code these days, as there are issues with the HIGHLIGHT system that need to be sorted (it adds line numbers that paste badly, etc). If the issues were sorted, it would be far superior to the old VBCode tags, as it supports multiple languages etc (which reminds me - I'll pester someone who is capable of fixing it, hopefully they'll have the urge and the spare time).
-
Sep 4th, 2008, 08:54 AM
#4
Thread Starter
Frenzied Member
Re: Searching for 'closest to'
Thanks anhn,
I didn't know you could make two separate references to the same table.
I was hoping for some kind of SQL command for 'closest to'.
I tried your code(several ways) but kept getting a 'missing parameter' error.
Finally gave up and did the sorting in VB.
It's working fine now.
-
Sep 4th, 2008, 08:56 AM
#5
Thread Starter
Frenzied Member
Re: Searching for 'closest to'
Originally Posted by si_the_geek
Welcome back!
Thx Si
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|