CODEWITHSUNDEEP
phpmysqlsqldistance
Pez Cuckow
Pez Cuckow

Reputation: 14412

PHP&MySQL Store Distance Calculator - Postcodes

Right I have been trying to work out how to compare a given postcode to a database of say store addresses and have them ordered in terms of which one is closest to the given postcode (or ZIP code I guess).

This is mainly out of interest, rather than me asking you for advice and then selling it to a client :-O

First of all after research I discovered that you have to do distance with Lat/Long so I found an API that converts postcodes/zip codes to lat long and now my DB has a structure such as id, store_name, lat, long, postcode and I can convert a given postcode to a lat long.

But how in SQL do I make a query for the ones closest to a given lat long?

Upvotes: 1

Views: 723

Answers (3)

Chuck Burgess
Chuck Burgess

Reputation: 11574

Try something like this:

// get all the zipcodes within the specified radius - default 20
    function zipcodeRadius($lat, $lon, $radius)
    {
        $radius = $radius ? $radius : 20;
        $sql = 'SELECT distinct(ZipCode) FROM zipcode  WHERE (3958*3.1415926*sqrt((Latitude-'.$lat.')*(Latitude-'.$lat.') + cos(Latitude/57.29578)*cos('.$lat.'/57.29578)*(Longitude-'.$lon.')*(Longitude-'.$lon.'))/180) <= '.$radius.';';
        $result = $this->db->query($sql);
        // get each result
        $zipcodeList = array();
        while($row = $this->db->fetch_array($result))
        {
            array_push($zipcodeList, $row['ZipCode']);
        }
        return $zipcodeList;
    }

UPDATE: There is some discussion about efficiency. Here is a little benchmark for you with this query. I have a database that contains EVERY zipcode in the US. Some of them are duplicate because of the way zipcodes work (outside the scope of this topic). So I have just under 80k records. I ran a 20 mile radius distance on 90210:

SELECT distinct(ZipCode) FROM zipcodes  WHERE (3958*3.1415926*sqrt((Latitude-34.09663010)*(Latitude-34.09663010) + cos(Latitude/57.29578)*cos(34.09663010/57.29578)*(Longitude- -118.41242981)*(Longitude- -118.41242981))/180) <= 20

I got back 366 total records and Query took 0.1770 sec. How much more efficient do you need?

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212402

See this answer to a previous question for an example of calculating a bounding box before querying MySQL. This allows the complex formula in the MySQL query to run against a subset of the database entries, rather than against every entry in the table.

Upvotes: 0

Mild Fuzz
Mild Fuzz

Reputation: 30661

check out this great open source project

Disclaimer: Not my project, and nor am I contributor. Purely a recommendation.

Upvotes: 0

Related Questions