Yannick Warnier 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.
>
> 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?
Yes, I'm aware about the [near-]spherical nature of the Earth. I *was*
afraid that I would need to store the distances between cities since
that would mean the distance table size would be (city table)**2.
I'll check PostGIS out, thanks.
--
dave
