I've done this, it is easy. Well, in the UK anyway. We have something
called the national grid (http://www.gps.gov.uk/natgrid/introduction.asp)
But it should be fairly easy to convert long/lat to a simpler grid for
your country.
If you haven't read the intro to thr grid, it is basicaly a 0 point
somewhere south east of england and coordinates are given in meters east
and north. You can subscribe to databases that map postcodes to
coordinates. Which is what I will do when the site goes live, but in the
mean time I am stealing them from http://www.streetmap.co.uk/. (x and y in
the map page's URL. Search for "SW15 1NY")
Once you have that, the rest is easy. create a column of the type "point"
and store the grid coordinates in there. The just use the "contains"
operator (~) in a query.
(http://www.postgresql.org/docs/7.4/static/functions-geometry.html)
Example:
My coordinates are 523857,175349. So to find anyone living within 10KM of
me, I just do "select * from people where '((523857,175349),10000)' ~
location"
Unfortunately, Postgres doesn't know how to index this. So make sure you
have some other things narrowing it down using an index (m/f, age, etc.)
to avoid a full table scan.
Hope that helps,
Bas.
