archive

Geonames – Import

Having difficulties to import tables from Geonames. So, here how it worked finally:

Create table structure:

CREATE TABLE geoname (
geonameid int,
name varchar(200),
asciiname varchar(200),
alternatenames varchar(4000),
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 int,
elevation int,
gtopo30 int,
timezone varchar(40),
moddate date
);

Download allCountries.zip and run in Terminal:

$ psql -e -U USERNAME geodataportal << EOT > \copy geoname (geonameid, name, asciiname, alternatenames, latitude, longitude, fclass, fcode, country, cc2, admin1, admin2, admin3, admin4, population, elevation, gtopo30, timezone, moddate) from allCountries.txt null as ''
> EOT

Comments are closed.