List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:September 25 2006 2:09pm
Subject:Re: Query missing rows in location of zip by distance
View as plain text  
I have found in life that I get stuck, email a list and then figure it out
myself often. It was rather dumb!

Anyhow, here is what I have.

SELECT b.zip, b.state,b.storename, b.address1, b.address2, b.city, b.state,
b.zip, b.id, 
ROUND((3956 * (2 * ASIN(SQRT(
POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
COS(a.lat*0.017453293) *
COS(b.lat*0.017453293) *
POWER(SIN(((a.lon-b.lon)*0.017453293)/2),2))))),2) AS distance
FROM zipcodes a, locations b
WHERE
a.zip = "63385" 
GROUP BY b.id
HAVING distance <= 5
ORDER BY distance, storename;




Thanks

Steffan

---------------------------------------------------------------
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
                                  Lasso Partner Alliance Member
---------------------------------------------------------------



> From: Jay Pipes <jay@stripped>
> Organization: MySQL, Inc.
> Reply-To: <jay@stripped>
> Date: Mon, 25 Sep 2006 09:59:19 -0400
> To: "Steffan A. Cline" <steffan@stripped>
> Cc: MySQL List <mysql@stripped>
> Subject: Re: Query missing rows in location of zip by distance
> 
> On Mon, 2006-09-25 at 06:09 -0700, Steffan A. Cline wrote:
>> Basically all 6 should come up in the big query because they are within the
>> same zip and that any other locations within the same area. They all have
>> the same latitude and longitude but only 1 shows up.
> 
> If they all have the same lat/long, then the distance from the centroid
> of your supplied zip code will be the same for all 6.  Because you are
> grouping on the distance, only 1 record will return.  Remove the GROUP
> BY distance.
> 
> Cheers,
> 
> Jay
> 


Thread
Query missing rows in location of zip by distanceSteffan A. Cline25 Sep
  • Re: Query missing rows in location of zip by distanceJay Pipes25 Sep
    • Re: Query missing rows in location of zip by distanceSteffan A. Cline25 Sep