Import geonames data from http://geonames.org/ into postgres.

import_geonames_to_pg.sql

-- Data from http://download.geonames.org/export/dump/
-- More details at http://download.geonames.org/export/dump/readme.txt
-- Steps at https://gist.github.com/EspadaV8/1357237

DROP TABLE geoname CASCADE;

CREATE TABLE geoname (
    geonameid      INT,
    name           VARCHAR(200),
    asciiname      VARCHAR(200),
    alternatenames TEXT,
    latitude       FLOAT,
    longitude      FLOAT,
    fclass         CHAR(1),
    fcode          VARCHAR(10),
    country        VARCHAR(2),
    cc2            VARCHAR(60),
    admin1         VARCHAR(20),
    admin2         VARCHAR(80),
    admin3         VARCHAR(20),
    admin4         VARCHAR(20),
    population     BIGINT,
    elevation      INT,
    gtopo30        INT,
    timezone       VARCHAR(40),
    moddate        DATE
);

ALTER TABLE ONLY geoname ADD CONSTRAINT pk_geonameid PRIMARY KEY (geonameid);

copy geoname (
  geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2,admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate
) from '~/Downloads/allCountries.txt' null as '';