CSV and SQL list of geocoded Belgian ZIP/Cities
For a project I needed a list of cities with their latitude/longitude location.
I used the technic mentionned by Patrick on his blog :
http://www.jedi.be/blog/2009/04/29/geocoding-belgium-postal-codes/
and I grabbed the postal code/city CSV file from Vincent’s blog :
http://www.vinch.be/blog/2010/03/16/villes-de-belgique-aux-formats-csv-xml-json-et-sql/
Aaaand some scripting later, I had a script ready to fill those lat/lng fields I prepared in my SQL table :
$cities = database::getAll('SELECT * FROM cities
WHERE lat IS NULL and lng IS NULL');
foreach($cities as $city) {
list($id,$zip,$name) = $city;
$data = shell_exec('curl -s "http://maps.google.com/maps/geo?q='.
urlencode(utf8_encode($name)).
'+'.$zip.'+Belgie&output=csv&key="')."\n";
list($junk,$junk,$lat,$lng) = explode(',',$data);
echo $id." ".$zip." ".utf8_encode($name).' '.$lat.' '.$lng.", saving...\n";
if((int) $lat != 0)
database::query('UPDATE cities SET lat = ? , lng =? WHERE id = ?',
array($lat,$lng,$id));
}