GeoNames Forum

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

Hello,

problem solved, you don't need to send the postalcodes again.
I've found the mail you send to me some month ago.

Greetz

Andy

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

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