Zip Code Proximity search

[Gregor Melhorn]

I’m planning to do a dating community site in zope, with about 50 000 members and up to 10 000 members being logged in at the same time. Searching for other people is based on distance (search for all people in 50 km distance etc.), and I also have a database with geodata for all zip-codes.

So now there’s the problem: The search function will be used very often, and every different zip-code-query will of course generate different results. Also a new registered user will again generate different results. Searching is subclassed in different distances (“up to 50 km”, “up to 100 km” and so on...).

The returned search list is sorted by distance, with a distance value next to each member:

user1... 0,3 km
userxy... 2,5 km
userab... 5 km etc.

Regarding performance, I thought of creating large lookup-tables in mysql with all distances of all zip’s to all the other zips in this area and building an according select query for the user-list.

Maybe use five different tables, each for a different range of distance (0 - 50 km, 50 to 100 km etc.), adding them in the queries as needed and indexed on the zip-code I want my range search to look for.

I know there's also a solution using an SQL-Query based on longitude/latitude values, but I suppose this to be slower than selecting from multiple tables, since it has to be calculated on every request?

----$lat1 = $aCoords[0]; //--latitude of originating zip
$lon1 = $aCoords[1]; //--longitude of originating zip
$within = $DIS; //--radius sesarch distance in miles

$strSQL = "SELECT ZIPCode, Latitude, Longitude FROM zipcode WHERE
(3958*3.1415926*sqrt((Latitude-'$lat1')*(Latitude-'$lat1') +
cos(Latitude/57.29578)*cos('$lat1'/57.29578)*(Longitude-'$lon1')*(Longitude-'$lon1'))/180)
<= $within";
----

Or is it faster to use another database system for this job? Any help would be appreciated...

Regards
Gregor