Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Import geonames data from http://geonames.org/ into postgres.
-- 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 '';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.