Loading Geonames Country Info into Postgres

Published on 7/28/2014

---

Download the countryInfo.txt file and strip lines beginning with the “#” character, as Postgres can only skip one line using the COPY command.

mkdir /srv/geonames

cd /srv/geonames

wget http://download.geonames.org/export/dump/countryInfo.txt

sed '/^#/ d' < countryInfo.txt > countryInfoNoComments.txt

In Postgres:

create table country_info (
  iso char(2) primary key,
  iso3 char(3) not null unique,
  iso_numeric char(3) not null,
  fips char(2),
  country text not null unique,
  capital text,
  area_in_sq_km numeric,
  population int not null,
  continent char(2) not null,
  tld text,
  currency_code char(3),
  currency_name text,
  phone text,
  postal_code_format text,
  postal_code_regex text,
  languages text,
  geonameid int,
  neighbours text,
  equivalent_fips_code text
);

copy country_info
from '/srv/geonames/countryInfoNoComments.txt'
with (
  format text,
  null ''
);

... views