The Art of PostgreSQL/

...

/

Loading the GeoNames

Loading the GeoNames

Load the data for the GeoNames database.

We'll cover the following...

Note: The GeoNames geographical database covers all countries and contains over eleven million place names that are available for download free of charge.

The website offers online querying, and all the data is made available to download and use. As is often the case, it comes in an ad hoc format and requires some processing and normalization before it’s usable in a PostgreSQL database.

GeoNames: Loading the raw data

We’ll load the data by using the following script/code:

C++
begin;
drop schema if exists raw cascade;
create schema raw;
create table raw.geonames
 (
   geonameid         bigint,
   name              text,
   asciiname         text,
   alternatenames    text,
   latitude          double precision,
   longitude         double precision,
   feature_class     text,
   feature_code      text,
   country_code      text,
   cc2               text,
   admin1_code       text,
   admin2_code       text,
   admin3_code       text,
   admin4_code       text,
   population        bigint,
   elevation         bigint,
   dem               bigint,
   timezone          text,
   modification      date
 );
create table raw.country
 (
  iso                 text,
  iso3                text,
  isocode             integer,
  fips                text,
  name                text,
  capital             text,
  area                double precision,
  population          bigint,
  continent           text,
  tld                 text,
  currency_code       text,
  currency_name       text,
  phone               text,
  postal_code_format  text,
  postal_code_regex   text,
  languages           text,
  geonameid           bigint,
  neighbours          text,
  fips_equiv          text
 );
\copy raw.country from 'countryInfoData.txt' with csv delimiter E'\t'
create table raw.feature
 (
  code        text,
  description text,
  comment     text
 );
\copy raw.feature from 'featureCodes_en.txt' with csv delimiter E'\t'
create table raw.admin1
 (
  code       text,
  name       text,
  ascii_name text,
  geonameid  bigint
 );
\copy raw.admin1 from 'admin1CodesASCII.txt' with csv delimiter E'\t'
create table raw.admin2
 (
  code       text,
  name       text,
  ascii_name text,
  geonameid  bigint
 );
\copy raw.admin2 from 'admin2Codes.txt' with csv delimiter E'\t'
commit;

Once we’ve loaded the raw data from the published files at http://download.geonames.org/export/dump/, we ...