#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3
    anyone?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3
    I'm still learning SQL and the syntax... i need an example of what to do so I can understand better.

    Originally Posted by cafelatte
    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.

IMN logo majestic logo threadwatch logo seochat tools logo