Skip to content

Instantly share code, notes, and snippets.

@ianare
Last active June 28, 2021 16:01
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ianare/7031316 to your computer and use it in GitHub Desktop.
Save ianare/7031316 to your computer and use it in GitHub Desktop.
Import countries to PostgreSQL from geonames data
DROP TABLE "country";
CREATE TABLE "country" (
"iso" character(2) NOT NULL,
"iso3" character(3) NOT NULL,
"iso_numeric" character(3) NOT NULL,
"fips" character(2),
"name" character varying(128) NOT NULL,
"capital" character varying(128),
"area" float,
"population" integer,
"continent" character(2) NOT NULL,
"tld" character(3),
"currency_code" character(3),
"currency_name" character varying(128),
"phone" character varying(128),
"postal_code_format" character varying(128),
"postal_code_regex" character varying(255),
"languages" character varying(16)[],
"geonameid" integer,
"neighbours" character(2)[],
"equivalent_fips_code" character(2),
CONSTRAINT country_pkey PRIMARY KEY ("iso"),
CONSTRAINT country_iso3_key UNIQUE ("iso3"),
CONSTRAINT country_iso_numeric_key UNIQUE ("iso_numeric")
);
/*
wget http://download.geonames.org/export/dump/countryInfo.txt
# remove comments
sed -i '/^\#/d' countryInfo.txt
# remove ending commas
sed -i 's/,\t/\t/g' countryInfo.txt
# format array values for postgres
awk -F"\t" 'BEGIN {OFS = FS} { gsub("(.*)","{"$16"}", $16); gsub("(.*)","{"$18"}", $18); print }' countryInfo.txt > countryInfo_clean.txt
*/
COPY "country" FROM '/home/ianare/countryInfo_clean.txt' DELIMITER ' ' CSV;
@Lysak
Copy link

Lysak commented Jun 28, 2021

For mysql

CREATE TABLE `country`.`country` (
	`iso` varchar(2) NOT NULL,
	`iso3` varchar(3) NOT NULL,
	`iso_numeric` varchar(3) NOT NULL,
	`fips` varchar(2),
	`name` varchar(128) NOT NULL,
	`capital` varchar(128),
	`area` float,
	`population` int(11),
	`continent` varchar(2) NOT NULL,
	`tld` varchar(3),
	`currency_code` varchar(3),
	`currency_name` varchar(128),
	`phone` varchar(128),
	`postal_code_format` varchar(128),
	`postal_code_regex` varchar(255),
	`languages` varchar(16),
	`geonameid` int(11),
	`neighbours` varchar(2),
	`equivalent_fips_code` varchar(2),
	CONSTRAINT country_pkey PRIMARY KEY (`iso`),
	CONSTRAINT country_iso3_key UNIQUE (`iso3`),
	CONSTRAINT country_iso_numeric_key UNIQUE (`iso_numeric`)
)

Аfter import through IntelliJ IDEA

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment