The Art of PostgreSQL/

...

/

Features

Features

Learn about GeoNames and its features.

We'll cover the following...

The GeoNames model tags all of its geolocation data with a feature class and a feature. The description for those codes is detailed on the GeoNames codes page and available for download in the featureCodes_en.txt file. Some of the information we need is only available in text form and has to be reported manually.

C++
begin;
drop schema if exists geoname cascade;
create schema geoname;
create table geoname.class
 (
  class        char(1) not null primary key,
  description  text
 );
insert into geoname.class (class, description)
     values ('A', 'country, state, region,...'),
            ('H', 'stream, lake, ...'),
            ('L', 'parks,area, ...'),
            ('P', 'city, village,...'),
            ('R', 'road, railroad '),
            ('S', 'spot, building, farm'),
            ('T', 'mountain,hill,rock,... '),
            ('U', 'undersea'),
            ('V', 'forest,heath,...');
create table geoname.feature
 (
  class       char(1) not null references geoname.class(class),
  feature     text    not null,
  description text,
  comment     text,
  primary key(class, feature)
 );
insert into geoname.feature
     select substring(code from 1 for 1) as class,
            substring(code from 3) as feature,
            description,
            comment
       from raw.feature
      where feature.code <> 'null';
commit;

As we see in this file, we have to deal with an explicit 'null' entry: ...