GeoNames Forum

Hi,

I have a dump of the postal code database in PostgreSQL, and I'm trying to do something similar to the findNearbyPostalCodes web service, where the user can specify a radius and the database returns a list of postal codes within that radius. I was wondering if there are any efficient ways to do that? Do I need something like PostGIS?

Thanks!

Bob

edit: btw, I'm storing latitude and longitude in float, is this a bad idea?

I believe if all the numbers maintain original value (I do not use Postgre ) you have quicker lookups with float than double.

You lose precision with float though, but for this data set I am not sure that is a bad trade off.

I am actually looking into not using an integer format and using a varchar with indexes and just to compare speed, I have the data divided into subsets though so the query speed is hard to gauge.

I have looked into PostGis, if you see a speed problem I think that would be a good idea...

You should also have a look at the datatype 'point' : http://www.postgresql.org/docs/8.2/interactive/datatype-geometric.html

'Point' allows you to use indexes and it has a distance operator : http://www.postgresql.org/docs/8.2/interactive/functions-geometry.html

The distance operator, however, is returning distance in degrees and not km or miles. You will have to use an approximate rule of thumb to convert km to degrees (1 degree = 111km; 40.000km/360).

Postfix at the other hand has better support for this.

Marc

This is great, thanks for point it out.