I use a static postgresql database to serve web pages containing information
about board game players and their ratings in various games. There are
about 7000 players listed and about 300 games. I don't expect this to grow
quickly in the short run. Most of the players are located in the US.
One of the planned improvements is to publish contact information and
to let people search for other people who live nearby and have common
interests in games. I have street addresses for most of these people,
though they currenty aren't in the database. I am also in the process
of using Daniel Egnor's geocoder project (for the Google contest)
working so that I can get Latitude and Longitude for most of these
addresses. I expect to be able to do this for something like 5000
people.
I am looking for hints on how to best use this information for radius
queries. I have tried looking at Gist and PostGIS documentation.
It looks like for Gist I would need to create a new datatype (since
I don't want to use a flat earth model making point unsuitable) and
figure out an efficient indexing scheme. I am pretty sure this is beyond my
capability to do (in the short run at least). It wasn't clear to me that
PostGIS would be fast for the small amount of data I have as their data
models looked pretty general and probably had a lot of overhead. Can people
recommend using either of the above approaches or any other open source
solution?
I also am not sure if using 2d lat+long or 3d rectangular is a better way
to go. 3d is simpler as there is no wrap around. If I use a spherical
model for the earth, then for any great circle distance I can compute
a secant distance and using that as a bounding box distance for searches.
This seems a lot easier than doing things with lat+long. If I do this
there may be tricks for setting up the coordinates so that indexes work
better. Since initially I will only have US data (and even later I expect
to have mostly US data) setting up coordinates so that they are used from
most discrimating to least discrinimating seems to be a good idea. I didn't
see much on this issue at the main parts of the PostGIS site, but I haven't
tried to read any of the referenced papers so far.