Author |
Message |
15/05/2006 18:10:52
|
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
|
|
|
|
01/04/2007 05:15:02
|
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
|
|
|
01/04/2007 09:05:18
|
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
|
|
|
|
22/04/2007 14:15:49
|
samokk
Joined: 13/10/2006 21:56:39
Messages: 82
Offline
|
A 4 character-long code can also be "post", for postal codes
|
|
|
23/04/2007 07:32:06
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4442
Offline
|
Thanks, I have updated the documentation.
Marc
|
|
|
|
23/08/2007 17:57:53
|
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
|
|
|
23/08/2007 18:43:24
|
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
|
|
|
|
23/08/2007 19:10:36
|
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
|
|
|
23/08/2007 20:11:20
|
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
|
|
|
|
24/08/2007 16:26:17
|
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 |
Download
|
Filesize |
11 Kbytes
|
Downloaded: |
81921 time(s) |
|
|
|
11/09/2007 18:05:02
|
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
|
|
|
12/09/2007 17:22:12
|
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
|
|
|
|
12/09/2007 18:15:41
|
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
|
|
|
19/09/2007 13:30:07
|
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
|
|
|
19/09/2007 13:45:08
|
marc
Joined: 08/12/2005 07:39:47
Messages: 4442
Offline
|
Thanks Graham. I have removed the quotes.
Marc
|
|
|
|
26/11/2008 10:23:50
|
bwakkie
Joined: 25/11/2008 12:49:52
Messages: 7
Offline
|
http://forum.geonames.org/gforum/posts/list/926.page does more automatically
|
|
|
26/03/2011 22:02:35
|
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.
|
|
|
26/03/2011 23:29:03
|
snrt
Joined: 26/03/2011 15:24:41
Messages: 6
Offline
|
it's ok
we must define a path
exp: c:/name.txt
|
|
|
15/04/2016 22:20:16
|
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.
|
|
|
15/04/2016 22:20:16
|
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)
)
|
|
|
|