For our hupskadee application we wanted to have geosearch; people would enter their postal code and it would retrieve all babysits organizations within a range of X km. These are the steps we took, because we couldn’t find a similar list on the internet . (At least not free of charge)
Step1: Download Belgium Postal Codes
On the site of the Post there is an official list of postal codes and names available. You can download it in HTML, XLS and PDF.
Step 2: Convert to CSV file
If you download it in XLS, it is easy using an Excel, Numbers or Openoffice to convert it into a CSV file.
Step 3: Use google geocoder script
Google has nice service called the geocoder that allows you to calculate the longitude and latitude of an address or a partial address. To use this you have to sign up for a googleMapKey
http://maps.google.com/maps/geo?q=<your query>&output=csv&key=#{googleMapKey}"
We made our query like this: q=#{naam}+#{postcode}+Belgie where naam and postcode are URLEncoded.
In order to make it work with our data of De Post, we had to substitute the abbreviated names to their full name. F.i. Antw. -> Antwerpen, Nam. -> Namen.
curl "http://maps.google.com/maps/geo?q=<strong>Berlare+9290+Belgie</strong>&output=csv&key=$MY_KEY" results in 200,5,51.0388449,3.9745067
- HTTP Status Code:
- Exactitude: 5(City Level), 4(Street Level)
- Longitude
- Latitude
So now we can loop over our CSV file, and retrieve the results. While there is no real limitation in the number of requests, there is a kind of throttling going on, so that you can not fire requests to fast. We used a sleep of 200ms and everything worked fine. You can download our results: Belgian Cities Geocoded in CSV.
Step 4: Using the geocode data
Now that we have all the data, how can we integrate in our application? I stumbled upon a presentation on how to use geocode with Mysql. It describes multiple queries you can use to calculate the distance between different records in our database.
@orig_lat=coordinaten.latitude
@orig_lon=coordinaten.longitude
@dist=10
geocodes=Geocode.find_by_sql [ "SELECT *, 3956 * 2 * ASIN(SQRT(POWER(SIN((? - abs(dest.latitude))
* PI()/180 / 2), 2) + COS(? * PI()/180) * COS(abs(dest.latitude) * PI()/180) *
POWER(SIN((? - dest.longitude)* PI()/180 / 2), 2)))
AS distance FROM geocodes dest having distance < ? ORDER BY distance limit 10;"
,@orig_lat, @orig_lat, @orig_lon, @dist]
This results in a list of geocodes of nearby postalcodes we can use to query our data.