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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
