Messages posted by "pinhead84"

Re:Import GeoNames dump into Postgres

pinhead84 — GMT

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

Aw:No postal codes in database dump

pinhead84 — GMT

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

Re:Import GeoNames dump into Postgres

pinhead84 — GMT

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

Re:Import GeoNames dump into Postgres

pinhead84 — GMT

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