Le ven 19/03/2004 à 11:05, David Garamond a écrit :
> 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.
Hi David,
I think the answer depends on the precision you want. Reading your post,
it doesn't seem to me you need a lot of precision.
Usually your technique could do for an approximation. If you want to be
more precise though, you would have to use a completely different
calculation or structure.
Did you think about the fact that longitude is different in terms of
distance if you look it from here or from the equator?
Also, using a difference in terms of longitude and latitude just by
making a subtraction will give you persons which are actually located at
more than sqrt(2) times 250 miles. Making it 350 miles sometimes (and
that's still flying like a bird).
A more precise way of doing this would be to keep a table with distances
between cities, and then calculate your way to your destination by
taking the shorter path (and that's only a question of distance, not
time)... That's really a lot more calculations. It depends on what you
need.
Yannick