Postgres find zip codes next to a specific zip code

Question

Assuming a Postgres table containing zip as varchar(10) , I want to get either all the results matching a specific zip, or expand the results with entries close to the requested if there are not enough results. Say:

The user searches for zip "56500" and my result set returns 2 elements with an exact match. In this case, I want to execute some kind of request, which will find the records "565%". In the end, I need to run this in one request.

Any suggestions?

Answer 1

Something like this might be what you want:

 SELECT … FROM atable WHERE zip = @zip UNION ALL SELECT … FROM atable WHERE NOT EXISTS ( SELECT * FROM atable WHERE zip = @zip ) AND zip LIKE CONCAT(LEFT(@zip, 3), '%') 

This may not be the most effective solution, but at least this is the only request that could be the starting point.

Answer 2

ORDER BY delta from desired zip ?

Answer 3

So, this is not special for PostgreSQL - it was actually written for MySQL initially, but it should work. I came across this article a while ago, which showed how to create a database containing zipcodes and latitude / longitude for each zipcode, then using trigonometry to calculate the distance between zip codes. Take a look at the link. I am sure this will help you ...

http://www.chrissibert.com/blog/2009/06/16/mysql-zip-codes-latitude-longitude-distance/