Import GeoNames gazetteer into OpenStreetMap database

#!/usr/bin/env bash
# Download the GeoNames gazetteer
curl -O -G http://earth-info.nga.mil/gns/html/geonames_dd_dms_date_20090428.zip
unzip geonames_dd_dms_date_20090428.zip

# Remove all the non-English placenames
awk -F '\t' '/ADM|PPL/ {if($20 == "") print $0}' geonames_dd_dms_date_20090428.txt | sed "s/'//g" > geonames_ppl.txt
echo "DELETE FROM planet_osm_point WHERE osm_id=1;INSERT INTO planet_osm_point (osm_id,name,place,way) VALUES " > geonames_ppl.sql

# Generate SQL statements into an OSM database
awk -F '\t' '/PPL|ADM/ {
      if($14 == "01") print "(1,'"'"'"$24"'"'"','"'"'city'"'"',Transform('"'"'SRID=4326;POINT("$5,$4")'"'"'::geometry,900913))";
      else{
      if($14 == "02") print "(1,'"'"'"$24"'"'"','"'"'city'"'"',Transform('"'"'SRID=4326;POINT("$5,$4")'"'"'::geometry,900913))";
      else {
      if($14 == "03") print "(1,'"'"'"$24"'"'"','"'"'town'"'"',Transform('"'"'SRID=4326;POINT("$5,$4")'"'"'::geometry,900913))";
      else {
      if($14 == "04") print "(1,'"'"'"$24"'"'"','"'"'town'"'"',Transform('"'"'SRID=4326;POINT("$5,$4")'"'"'::geometry,900913))";
      else print "(1,'"'"'"$24"'"'"','"'"'village'"'"',Transform('"'"'SRID=4326;POINT("$5,$4")'"'"'::geometry,900913))";
          }}}
      if(FNR % 1000 == 0)  print ";SELECT '"'"'" FNR "'"'"';INSERT INTO planet_osm_point (osm_id,name,place,way) VALUES "; else print ","}' geonames_ppl.txt | sed '$ s/,/;/' >> geonames_ppl.sql
# last sed change the last , to a ;

# Import into your OpenStreetMap database
psql -U osm -p 5432 osm -f geonames_ppl.sql