Querying a zip code table to get approximate location

Dev Shed Forums Sponsor:

November 8th, 2012, 11:14 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

Querying a zip code table to get approximate location

(I just need a rough proximity idea so don't care about precision with merging longitude lines etc. I am using roughly 50 miles per degree but this is all irrelevant. My problem is with the query)

I have a php script where I check a given zip code against a customer table with radius, and a huge zip code table IF the customer has a zip code specified in the zip code column.

I am having a hell of a time with this but I'm sure it's obvious and I'm just really tired. Query is getting complex as this is a CRM with new features being added daily.

First I query the zip code table to get longitude and latitude of the GIVEN zip.

Code:

 
SELECT longitude, latitude, city FROM zipcodes WHERE zipcode = $zip

[store longitude as $longitude etc.]

Now I'll try to find customers who meet the WHERE criteria below within long and lat (OR bypass geolocation if that customer zip is NULL) by querying the zip table for matches within the long/lat range. I know JOIN is not the right thing here, I'm sure this requires some kind of CASE statement but I can't work it out.

Code:

 
SELECT customers.lead_type
, ac0
, ac1
, ac2
, ac3
, ac4
, active
, daily 
, timezone
, radius
, customers.zip
, zipcode
, latitude
, longitude
, run
, pri
, customers.id
, customers.carrier
, customers.state
, customers.customer
, MAX(xferleads.timestamp)AS maxTimeStamp
, COUNT( CASE WHEN xferleads.timestamp >= CURRENT_DATE 
                    AND xferleads.timestamp  < CURRENT_DATE + INTERVAL 1 DAY 
                   AND woactive>0
                   THEN xferleads.customer
                   ELSE NULL END ) AS countdaily

FROM customers 
LEFT JOIN xferleads ON xferleads.customer = customers.id
LEFT JOIN fulfillment ON fulfillment.id = customers.id

JOIN zipcodes

 WHERE (customers.lead_type = '$prenon' OR customers.lead_type =  'ANY' )
   AND $callerac IN (ac0,ac1,ac2,ac3,ac4) 
   AND active = 1 
   AND $dow = 1 
   	AND (customers.zip IS NULL OR
   	($longitude BETWEEN 
   	(longitude-(radius/50)) AND (longitude+(radius/50)))
   	AND
   	($latitude BETWEEN 
   	(latitude-(radius/50)) AND (latitude+(radius/50)))
   	
   		
   		
   		 )
 
GROUP 
    BY customers.customer
     , daily
HAVING countdaily < (daily * 1.5)

To summarize, the query should return customers within long/lat limits OR who do not have a zip specified.

November 9th, 2012, 11:56 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

anyone?

November 10th, 2012, 01:14 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

If you know how to write the query to retrieve zips within an area, and you know how to write the query for customers without zips, why not just UNION them together?

Oh, and is the extra overhead in working out the exact distance really significant. Seems to me that it makes your results much more useful and scale able!

Finally, please don't post in multiple forums without mentioning in one or other (or both) of the threads that that's what you're doing. People get insanely upset about that kind of thing.

November 10th, 2012, 03:33 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

I'm still learning SQL and the syntax... i need an example of what to do so I can understand better.

Quote:

Originally Posted by cafelatte

Querying a zip code table to get approximate location

Developer Shed Advertisers and Affiliates