GeoNames Home | Postal Codes | Download / Webservice | About 

GeoNames Forum
  [Search] Search   [Recent Topics] Recent Topics   [Groups] Back to home page 
[Register] Register / 
[Login] Login 
Import GeoNames dump into Postgres  XML
Forum Index -> FAQ - frequently asked questions Go to Page: 1, 2 Next 
Author Message
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

I use simple psql to import the dump into postgres. First I create a file create.sql :

Code:
 create table geoname (
         geonameid       int,
         name            varchar(200),
         asciiname        varchar(200),
         alternatenames  varchar(6000),
         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
 );
 
 create table alternatename (
         alternatenameId  int,
         geonameid          int,
         isoLanguage        varchar(7),
         alternateName     varchar(200),
         isPreferredName      boolean,
         isShortName    boolean,
         isColloquial    boolean,
         isHistoric    boolean
 );
 
 Note: the countryInfo file has changed since this posting was written.
 
 
 CREATE TABLE "countryinfo" (
      iso_alpha2 char(2),
      iso_alpha3 char(3),
      iso_numeric integer,
      fips_code character varying(3),
      name character varying(200),
      capital character varying(200),
      areainsqkm double precision,
      population integer,
      continent char(2),
      languages character varying(200),
      currency char(3),
      geonameId int
 );
 


Then I create the tables with 'psql dabase_name < create.sql'

in the psql command line I enter (copy/paste) :

Code:
 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 '';
 copy alternatename  (alternatenameid,geonameid,isoLanguage,alternateName,isPreferredName,isShortName,isColloquial,isHistoric) from 'alternateNames.txt' null as '';
 copy countryInfo  (iso_alpha2,iso_alpha3,iso_numeric,fips_code,name,capital,areaInSqKm,population,continent,languages,currency,geonameId) from 'countryInfo.txt' null as '';
 


Edit 2006.08.07 : added cc2
Edit 2006.08.08 : added timezone
Edit 2007.03.24 : added admin2
Edit 2007.04.01 : updated table alternatename, added isPreferredName
Edit 2007.05.26 : update table alternateName, added isShortName
Edit 2007.07.25 : add admin3 and admin4
Edit 2007.07.30 : update table alternateName change isoLanguage from char(4) to char(7) to allow the pseudo code fr_1793
Edit 2007.08.23 : update countryInfo add currency and geonameId
Edit 2007.09.19 : remove quotes from countryInfo create table
Edit 2009.01.10 : http://forum.geonames.org/gforum/posts/list/1208.page
Edit 2009.04.13 : alter column population to bigint (from int) to allow for the population of continents.
Edit 2012.05.09 : add table alternatename, added isColloquial and isHistoric

[WWW]
dpc_map



Joined: 07/02/2007 04:02:37
Messages: 16
Offline

Marc,

Can you update the helpful instructions you've provided for creating the alternatenames table and the copy command for loading the alternate names file into Postgres?

alternateNameId : the id of this alternate name, int
geonameid : geonameId referring to id in table 'geoname', int
isolanguage : iso 693 language code 2- or 3-characters, varchar(3)
alternate name : alternate name or name variant, varchar(200)
isOfficialName : '1', if this alternate name is an official/preferred name

Thank you.

Dan
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Dan

Thanks.
I have also updated the documentation for the column isoLanguage. We are now including iata/icao codes for airports :
isolanguage : iso 693 language code 2- or 3-characters; 4-characters 'iata' or 'icao' for airport codes, varchar(4)

Cheers

Marc

[WWW]
samokk



Joined: 13/10/2006 21:56:39
Messages: 82
Offline

A 4 character-long code can also be "post", for postal codes
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Thanks, I have updated the documentation.

Marc

[WWW]
pinhead84



Joined: 23/08/2007 17:50:02
Messages: 4
Location: Berlin, Germany
Offline

Hello,

thanks a lot for your documentation.
I've found some issues, you can possibly fix in your documentation

countryInfo.txt contains two more fields:
currency => character(3),
geonameid => integer NOT NULL

No Primary Keys are defined, i've used the following ones:

Code:
ALTER TABLE ONLY alternatename
     ADD CONSTRAINT pk_alternatenameid PRIMARY KEY (alternatenameid);
 
 ALTER TABLE ONLY geoname
     ADD CONSTRAINT pk_geonameid PRIMARY KEY (geonameid);
 
 ALTER TABLE ONLY countryinfo
     ADD CONSTRAINT pk_iso_alpha2 PRIMARY KEY (iso_alpha2);


I wasn't able to define Foreign-Keys for the geonameid on the tables alternatename and countryinfo. The data seems to be inconsistent on some tupels. So I decided to use the following indices:

Code:
 CREATE INDEX index_countryinfo_geonameid ON countryinfo USING hash (geonameid);
 
 CREATE INDEX index_alternatename_geonameid ON alternatename USING hash (geonameid);


Maybe you know a better solution?

Greetings


Andy
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Thanks, I have updated the countryInfo doc.

You could help us sort out the missing or wrong geonameIds in the countryInfo file.

Cheers

Marc

[WWW]
pinhead84



Joined: 23/08/2007 17:50:02
Messages: 4
Location: Berlin, Germany
Offline

Hello Marc,

thanks for your quick answer.

marc wrote:
You could help us sort out the missing or wrong geonameIds in the countryInfo file. 


One simple problem on countryinfo-Table:
Code:
SELECT * FROM countryinfo WHERE geonameid=0;


Brings up 17 tupels with the following iso_alpha2:

Code:
 AQ
 AS
 AX
 BV
 FO
 GF
 GU
 HM
 MP
 MQ
 NC
 PM
 RE
 SJ
 UM
 VI
 WF


If these countries aren't available on the geoname-table, I would suggest to create a dummy-entry with "geonameid=0".

Code:
INSERT INTO geoname (geonameid) VALUES (0);


After this modification, the foreign-key on countryinfo is set up correctly:

Code:
ALTER TABLE ONLY countryinfo
     ADD CONSTRAINT fk_geonameid FOREIGN KEY (geonameid) REFERENCES geoname(geonameid);


On the alternatename-table the problem can't be fixed so easy. I'll try to find out the corrupted tupels and will tell them to you on this thread.

Greetz


Andy
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Hi Andy

These countries are available, only the geonameId is missing in the countryInfo. You could add the geonameId to your list above and I will add it then to the countryInfo.txt file.

Marc

PS: It is related to another thread : http://forum.geonames.org/gforum/posts/list/570.page

[WWW]
pinhead84



Joined: 23/08/2007 17:50:02
Messages: 4
Location: Berlin, Germany
Offline

Hi Marc,

marc wrote:
These countries are available, only the geonameId is missing in the countryInfo. You could add the geonameId to your list above and I will add it then to the countryInfo.txt file. 


I tried to check the broken relations between countryInfo and geoname tables, but had some problems finding out the corresponding geoname-tupels. I would suggest to use the following relations:

Code:
 AS => geonameid=5880801; fclass=A; fcode=ADMD; name=American Samoa, Territory of
 BV => geonameid=3371123; fclass=A; fcode=PCLD; name=Bouvet Island
 FO => geonameid=2622320; fclass=A; fcode=ADM1; name=Faroe Islands
 GF => geonameid=3381670; fclass=A; fcode=ADM1; name=Department of Guiana
 GU => geonameid=4043988; fclass=A; fcode=ADMD; name=Territory of Guam
 MP => geonameid=4041467; fclass=A; fcode=ADMD; name=Northern Islands
 MQ => geonameid=3570311; fclass=A; fcode=ADM1; name=Department of Martinique
 NC => geonameid=2139685; fclass=A; fcode=ADM1; name=Territory of New Caledonia and Dependencies
 PM => geonameid=3424932; fclass=A; fcode=ADM1; name=Territorial Collectivity of Saint Pierre and Miquelon
 RE => geonameid=935317;  fclass=A; fcode=ADM1; name=Department of Reunion
 SJ => geonameid=607072;  fclass=A; fcode=ADM1; name=Svalbard
 UM => geonameid=5854968; fclass=A; fcode=ADMD; name=United States Minor Outlying Islands
 VI => geonameid=4796775; fclass=A; fcode=ADMD; name=Virgin Islands of the United States
 WF => geonameid=4034749; fclass=A; fcode=ADM1; name=Territory of the Wallis and Futuna Islands


Because I'm relative new to the DB-structure of GeoNames, I've added the corresponding fclass and fcode fields to my listing. Is it correct, that "fclass=A" is a condition for this relations? - All the other relations between countryInfo and geoname (e.g. Germany) always have "fclass=A". Please correct me, if I misunderstood something.

Assuming that I'm right, the following countryinfo-entries can not be related correctly to the geoname-table:

Code:
 AQ => no entry in geoname-table (WHERE country='AQ' AND fclass='A')
 AX => no entry in geoname-table (WHERE country='AX')
 HM => no entry in geoname-table (WHERE country='HM')


These entries still relate to geonameid=0, so I still need the dummy-entry in the geoname-table to ensure a valid Foreign-Key-Constraint. Is it possible to create the corresponding entries in the geoname-table?

andy wrote:
On the alternatename-table the problem can't be fixed so easy. I'll try to find out the corrupted tupels and will tell them to you on this thread. 


I've written a small Java-tool to check the broken relations on the alternatename-table. It found 245 geonameid's, that don't exist in the geoname-table (anymore?). I attach the validation-result to this posting.

If you're interested, I would send you the tool including (GPL'ed) source-code.


Greetz


Andy
 Description Validation-Result [Disk] Download
 Filesize 11 Kbytes
 Downloaded:  81921 time(s)

markdeblois



Joined: 03/09/2007 17:59:47
Messages: 2
Offline

Hi there,

I am trying to fill an empty pgsql database based on the instructions here. I basically want to create a database which I can use to match populated places with in order to geocode them.
I am getting an error when trying to copy the countryInfo.txt file. It complains about the column "geonamei" of relation "countryinfo" not existing. The column is there though is empty.
Can someone point me in the right direction?! I am quite new to postgresql/postgis but eager to get fully acquainted.
Is there a sql dump of a full database by any chance?! That would make things quite easy.

Thanks ahead for any help.

cheers,
Mark de Blois
marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Hi Mark

Did you make sure it is all spelt correctly/consistently? (a column geonamei probably does not exist in your database schema)

Regards

Marc

[WWW]
markdeblois



Joined: 03/09/2007 17:59:47
Messages: 2
Offline

It seems that the allCountries.txt file probably has a few lines at the top which get in the way. As the file is over 700mb big I cannot seem to find a powerful enough editor to modify it. I tried the copy with a country file in which I removed the first few lines (including the columns) which worked well. But I hope there is a better way than importing them one by one

I added a spatial field as well and was able to fill the geometry with the coordinates from the latitude longitude field. The country dataset loads up fine in QGIS, although the geonameid does seem to have become 0 (it has a value when I look at it through pg3admin?!). Generating the primary and foreign keys seems to cause some difficulties...but slowly moving ahead.

BTW, does anyone know of a (semi) automated way of updating the database?!

Thanks!

Mark
marinheiro



Joined: 19/09/2007 13:24:33
Messages: 5
Offline

Just had the same problem with the countryInfo upload claiming that column geonameId doesn't exist. I'm completely new to postgres, but it appears to be a capitalization issue. The sample 'create.sql' file at the head of this thread quotes the field names for the countryInfo table, forcing the I in geonameId to uppercase. psql appears to lowercase field names typed in (I'm using 8.2.4 on debian testing), so it never matches the stored field name.

To fix, just remove the quotes round the field names before creating the table.

Graham

marc



Joined: 08/12/2005 07:39:47
Messages: 4442
Offline

Thanks Graham. I have removed the quotes.

Marc

[WWW]
bwakkie



Joined: 25/11/2008 12:49:52
Messages: 7
Offline

http://forum.geonames.org/gforum/posts/list/926.page does more automatically
snrt



Joined: 26/03/2011 15:24:41
Messages: 6
Offline

Hi,

i have a question:
where i have to ADD "allCountries.txt" file in oder to copy it in postgres database

Thank you.

snrt



Joined: 26/03/2011 15:24:41
Messages: 6
Offline

it's ok
we must define a path

exp: c:/name.txt
Darrius



Joined: 24/03/2016 21:14:32
Messages: 1
Offline

I'm completely new to postgres, but it appears to be a capitalization issue. The sample 'create.sql' file at the head of this thread quotes the field names for the countryInfo table, forcing the I in geonameId to uppercase. psql appears to lowercase field names typed in (I'm using 8.2.4 on debian testing), so it never matches the stored field name.

To fix, just remove the quotes round the field names before creating the table.
mpscheidt



Joined: 25/03/2016 12:48:43
Messages: 2
Offline

Updated countryinfo create table statement:

Code:
 CREATE TABLE countryinfo
 (
   iso_alpha2 character(2) NOT NULL,
   iso_alpha3 character(3),
   iso_numeric integer,
   fips_code character varying(3),
   name character varying(200),
   capital character varying(200),
   areainsqkm double precision,
   population integer,
   continent character(2),
   languages character varying(200),
   currencycode character(3),
   geonameid integer,
   neighbours character varying,
   tld character varying,
   currencyname character varying,
   postalcodeformat character varying,
   postalcoderegex character varying,
   phone character varying,
   equivalentfipscode character varying,
   CONSTRAINT countryinfo_pkey PRIMARY KEY (iso_alpha2)
 )
 
 
Forum Index -> FAQ - frequently asked questions Go to Page: 1, 2 Next 
Go to:   
Powered by JForum 2.1.5 © JForum Team