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