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