Re: "People near me" query

The earthdistance package is great.
In conjunction with one of the many zip code databases available on the
net, here's a simple function PHP that does returns a bunch of zipcodes
close to you, along with the mileage of each.

// Get zipcodes for a radius
function getzipcodes ($zipcode="", $radius=10)
{
        $zip = lib_getsql("SELECT latitude,longitude FROM zipcodes WHERE
zip='$zipcode'");
        $istartlat = $zip[0][latitude];
        $istartlong = $zip[0][longitude];

        $iradius = $radius;

        $latrange  = $iradius / ((6067.0/5280.0) * 60.0);
        $longrange = $iradius / (((cos($istartlat * pi() / 180) *
6076.0) / 5280.0) * 60);

        $lowlatitude = $istartlat - $latrange;
        $highlatitude = $istartlat + $latrange;
        $lowlongitude = $istartlong - $longrange;
        $highlongitude = $istartlong + $longrange;

        $sql = "SELECT zipcode, point($istartlat,$istartlong) <@>
point(latitude,longitude) as miles FROM zipcodes
                WHERE (latitude BETWEEN $lowlatitude AND $highlatitude) AND
                      (longitude BETWEEN $lowlongitude AND $highlongitude)
                ORDER BY miles LIMIT 500";

        $results = lib_getsql($sql);

        return($results);
}

No doubt you can get an apropo data file for other countries.

- Ericson Smith



Bruno Wolff III wrote:

>On Fri, Mar 19, 2004 at 17:05:11 +0700,
>  David Garamond <lists@zara.6.isreserved.com> wrote:
>
>
>>Imagine an Orkut-like site. Suppose we have 'person' table of 100k
>>people. About 75% of these people fill in their location
>>(City/State/Country) information. We also have a 'city' table containing
>>list of cities with their state & country and each city's
>>latitude/longitude. Assume all people's location is registered in the
>>'city' table.
>>
>>How does one design a database to be able to process "Show me people
>>that live no farther than 250 miles from where I live" quickly? I can do
>>"Show me people that live within (A-X to A+X) latitude and (B-X to B+X)
>>longitude" though. (Where A and B is the latitude and longitude [of the
>>person], and X is some numeric value.
>>
>>
>
>The earthdistance contrib package allows you to do these kinds of queries
>with gist indexes. This might be a simpler solution than using PostGIS
>which has a lot of other features you don't appear to be using.
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>