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?

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)"

---

Quote:

---

Originally Posted by longwolf

P.S. I haven't been here for quite a while, how are the <vbcode> tags done now?

---

Welcome back! :wave:

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).

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.

Quote:

---

Originally Posted by si_the_geek

Welcome back! :wave:

---

Thx Si