David Garamond 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.
>
Have you considered using PostGIS?
http://postgis.refractions.net
It will do all sorts of spatial queries for you including all of what
you metioned and lots lots more. There are plenty of people who use it
(including me) and it performs very well. I am not sure how easy it is
to install, my colleague does that bit, but to use it is really quite
simple.
Nick