import geonames to postgresql

gistfile1.txt

sudo -i -u postgres psql

CREATE DATABASE geonames;

DROP TABLE geoname;
CREATE TABLE geoname (
geonameid int,                                                                  
name varchar(200),                                                              
asciiname varchar(200),
alternatenames varchar(12000),
latitude float,
longitude float,
fclass char(1),
fcode varchar(10),
country varchar(2),
cc2 varchar(100),
admin1 varchar(20),
admin2 varchar(80),
admin3 varchar(20),
admin4 varchar(20),
population bigint,
elevation int,
gtopo30 int,
timezone varchar(40),
moddate date
);

DROP TABLE alternatename;
CREATE TABLE alternatename (
    alternatenameId INT,
    geonameid       INT,
    isoLanguage     VARCHAR(7),
    alternateName   VARCHAR(300),
    isPreferredName BOOLEAN,
    isShortName     BOOLEAN
);
DROP TABLE countryinfo;
CREATE TABLE "countryinfo" (
    iso_alpha2           CHAR(8),
    iso_alpha3           CHAR(3),
    iso_numeric          INTEGER,
    fips_code            CHARACTER VARYING(3),
    country              CHARACTER VARYING(200),
    capital              CHARACTER VARYING(200),
    areainsqkm           DOUBLE PRECISION,
    population           INTEGER,
    continent            CHAR(2),
    tld                  CHAR(10),
    currency_code        CHAR(3),
    currency_name        CHAR(15),
    phone                CHARACTER VARYING(20),
    postal               CHARACTER VARYING(60),
    postalRegex          CHARACTER VARYING(200),
    languages            CHARACTER VARYING(200),
    geonameId            INT,
    neighbours           CHARACTER VARYING(50),
    equivalent_fips_code CHARACTER VARYING(3)
);
DROP TABLE iso_languagecodes;
CREATE TABLE iso_languagecodes(
    iso_639_3     CHAR(8),
    iso_639_2     VARCHAR(50),
    iso_639_1     VARCHAR(50),
    language_name VARCHAR(200)
);
DROP TABLE admin1CodesAscii;
CREATE TABLE admin1CodesAscii (
    code      CHAR(20),
    name      TEXT,
    nameAscii TEXT,
    geonameid INT
);
DROP TABLE admin2CodesAscii;
CREATE TABLE admin2CodesAscii (
    code      CHAR(80),
    name      TEXT,
    nameAscii TEXT,
    geonameid INT
);
DROP TABLE featureCodes;
CREATE TABLE featureCodes (
    code        CHAR(7),
    name        VARCHAR(200),
    description TEXT
);
DROP TABLE timeZones;
CREATE TABLE timeZones (
    timeZoneId VARCHAR(200),
    GMT_offset NUMERIC(3,1),
    DST_offset NUMERIC(3,1),
    raw_offset NUMERIC(3,1)
);
DROP TABLE continentCodes;
CREATE TABLE continentCodes (
    code      CHAR(2),
    name      VARCHAR(20),
    geonameid INT
);
DROP TABLE postalcodes;
CREATE TABLE postalcodes (
    countrycode CHAR(2),
    postalcode  VARCHAR(20),
    placename   VARCHAR(180),
    admin1name  VARCHAR(100),
    admin1code  VARCHAR(20),
    admin2name  VARCHAR(100),
    admin2code  VARCHAR(20),
    admin3name  VARCHAR(100),
    admin3code  VARCHAR(20),
    latitude    FLOAT,
    longitude   FLOAT,
    accuracy    SMALLINT
);
ALTER TABLE ONLY alternatename
    ADD CONSTRAINT pk_alternatenameid PRIMARY KEY (alternatenameid);
ALTER TABLE ONLY geoname
    ADD CONSTRAINT pk_geonameid PRIMARY KEY (geonameid);
ALTER TABLE ONLY countryinfo
    ADD CONSTRAINT pk_iso_alpha2 PRIMARY KEY (iso_alpha2);
ALTER TABLE ONLY countryinfo
    ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);
ALTER TABLE ONLY alternatename
    ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);

copy geoname (geonameid,name,asciiname,alternatenames,latitude,longitude,fclass,fcode,country,cc2,admin1,admin2,admin3,admin4,population,elevation,gtopo30,timezone,moddate) from '/pathtofolder/geonames/allCountries.txt' null as '';
copy postalcodes (countrycode,postalcode,placename,admin1name,admin1code,admin2name,admin2code,admin3name,admin3code,latitude,longitude,accuracy) from '/pathtofolder/geonames/postalCodes.txt' null as '';
copy timeZones (timeZoneId,GMT_offset,DST_offset,raw_offset) from '/pathtofolder/geonames/timeZones.txt' null as '';
copy featureCodes (code,name,description) from '/pathtofolder/geonames/featureCodes_en.txt' null as '';
copy admin1CodesAscii (code,name,nameAscii,geonameid) from '/pathtofolder/geonames/admin1CodesASCII.txt' null as '';
copy admin2CodesAscii (code,name,nameAscii,geonameid) from '/pathtofolder/geonames/admin2Codes.txt' null as '';
copy iso_languagecodes (iso_639_3,iso_639_2,iso_639_1,language_name) from '/pathtofolder/geonames/iso-languagecodes.txt' null as '';
copy countryInfo (iso_alpha2,iso_alpha3,iso_numeric,fips_code,country,capital,areainsqkm,population,continent,tld,currency_code,currency_name,phone,postal,postalRegex,languages,geonameid,neighbours,equivalent_fips_code) from '/pathtofolder/geonames/countryInfo.txt' null as '';

INSERT INTO continentCodes VALUES ('AF', 'Africa', 6255146);
INSERT INTO continentCodes VALUES ('AS', 'Asia', 6255147);
INSERT INTO continentCodes VALUES ('EU', 'Europe', 6255148);
INSERT INTO continentCodes VALUES ('NA', 'North America', 6255149);
INSERT INTO continentCodes VALUES ('OC', 'Oceania', 6255150);
INSERT INTO continentCodes VALUES ('SA', 'South America', 6255151);
INSERT INTO continentCodes VALUES ('AN', 'Antarctica', 6255152);
CREATE INDEX index_countryinfo_geonameid ON countryinfo USING hash (geonameid);
CREATE INDEX index_alternatename_geonameid ON alternatename USING hash (geonameid);