Store Locator - Cannot figure out how to ORDER BY

Results 1 to 3 of 3

Thread: Store Locator - Cannot figure out how to ORDER BY

  1. #1
    Join Date
    Dec 1969

    Default Store Locator - Cannot figure out how to ORDER BY

    1)A user enters their zip code.<BR>2)I pull up the longitude/latitude of that zip code from my database.<BR>3)I pull up a recordset containing local stores and their latitude/longitude.<BR>4)I then have a VBSCRIPT that calculates the distance(in miles) from the user&#039;s location to the store.<BR>5)Now how do I ORDER the Recordset of stores BY the DISTANCE that my VBSCRIPT just calculated???<BR><BR>I can probably do it with an array, but can anyone help me keep it as a Recordset ORDER BY. For example, if I could just add these calculations to a vitual field in the recordset, then I could ORDER BY this new field.<BR><BR>Thanks so so much for any help!!!

  2. #2
    Join Date
    Dec 1969

    Default RE: Store Locator - Cannot figure out how to ORDE

    you COULD use a temporary table, adding all your records via VBScript, then sortiong and selecting back out but it&#039;d hardly be efficient. you&#039;d be much better off using a sorting algorithm of some kind. have a couple of articles on sorts in VBScript.<BR><BR>j<BR>

  3. #3
    Join Date
    Dec 1969

    Default Do it with the array...

    ...and then sort the array.<BR><BR>It will be much more efficient, most likely, than stuffing them back into a recordset.<BR><BR>Your other option is to build the distance calculation into your SQL query. But the formulas don&#039;t lend them well to this unless you can do it in a stored procedure in, say, SQL Server.<BR><BR>One hint: To make your search more efficient, *limit* your query of the stores table to those that are within a (user-specified?) distance of the user&#039;s location.<BR><BR>You can use 40 miles per degree and be quite safe anyplace in the USA outside of Alaska. (It&#039;s more like 60 miles per degree most parts of the US, so using 40 will give you a little overkill.)<BR><BR>That is: Say the user wants all stores within 100 miles.<BR><BR>So 100 divided by 40 gives you 2.5 degrees variance.<BR><BR>[100 divided by 60 would only be 1.7, so if you look at the rest of the math, below, you&#039;ll see what I mean when I say using 40 miles per degree gives you a safety margin.]<BR><BR>So now your query becomes:<BR><BR>SELECT * FROM stores<BR>WHERE storeLat &#062;= (userLat - 2.5) AND storeLat &#060;= (userLat + 2.5)<BR>AND storeLong &#062;= (userLong - 2.5) AND storeLong &#060;= (userLong + 2.5)<BR><BR>where, of course you calculate the values of (userLat - 2.5) et al. in VBScript and pass them in to the query you build up.<BR><BR>If you&#039;ve got 2000 stores in the USA, and there are only 40 within 100 miles of this user, you&#039;ve just cut down the work your VBScript distance calculator has to do by a factor of *50*!<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts