List:General Discussion« Previous MessageNext Message »
From:Steffan A. Cline Date:September 25 2006 1:09pm
Subject:Query missing rows in location of zip by distance
View as plain text  
Ran into a strange problem. In this zip code I am searching in I know for
sure I have 6 locations within the 63385 zip.

Doing a simple select * from locations where zip = '63385' returns 6 rows.

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.

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 distance 
HAVING distance <= 5;

A tough one. I restarted MySQL thinking it could somehow be a bad cache or
something but no matter what I only get these results. Now, I did think of
cheating and adding an OR zip=63385 but then what would happen if a
neighboring zip had 5 locations. I'd probably only get 1 record from that as
well. :/



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



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