Results 1 to 5 of 5

Thread: Searching for 'closest to'

  1. #1

    Thread Starter
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    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.

  2. #2
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    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)"
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Searching for 'closest to'

    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!

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

  4. #4

    Thread Starter
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    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.

  5. #5

    Thread Starter
    Frenzied Member longwolf's Avatar
    Join Date
    Oct 2002
    Posts
    1,343

    Re: Searching for 'closest to'

    Quote 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
  •  



Click Here to Expand Forum to Full Width