Re: "People near me" query

In article <405AC5D7.1090906@zara.6.isreserved.com>,
David Garamond <lists@zara.6.isreserved.com> writes:

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

On a flat surface, this gives you a square where the corners are too
far away, but it's a nice way to weed all the records definitely
outside of the circle.  For the remaining records, you could use sqrt
(dx**2 + dy**2) in your application.

Things get worse if you want to take the spherical nature of the
surface into account, but are you sure you need that?