GOV/Abgleich Geonames

SQL-Abfragen

Vorhandene geonames-IDs im GOV:

select substring(content,10) as geonameid, textual_id as govid from property, gov_item where gov_item.id=gov_object AND type=6 AND content like 'geonames:%';

geoname-IDs eines Staates (hier Liechtenstein = http://gov.genealogy.net/object_220100) im GOV:

SELECT substring(content,10) AS geonameid ,  textual_id FROM relation_index, gov_item LEFT JOIN property ON gov_item.id=property.gov_object AND property.type=6 AND content like 'geonames:%'  WHERE child=gov_item.id AND parent=220100 ORDER BY geonameid;

Die interessanten Einträge eines Staates aus geonames heraussuchen:

select geonameid, name, feature_code from geoname where feature_code in ('ADM1','ADM2','ADM3','ADM4','CMTY','FRM','FRMQ','FRMS','FRMT','HSE','MSTY','PPL','PPLA','PPLA2','PPLA3','PPLA4','PPLC','PPLCH','PPLF','PPLG','PPLH','PPLL','PPLQ','PPLR','PPLS','PPLW','PPLX','PRSH','RNCH','STLMT') AND country_code ='li'  ORDER by geonameid;

Länder

Kürzel	Land	Status	im GOV
AD	Andorra	bis adm1
AL	Albanien	bis adm3
AT	Österreich	Abgleich notwendig	http://gov.genealogy.net/object_215340
AU	Australien	bis adm2, GOV bis adm1 angelegt	http://gov.genealogy.net/object_1023934
BA	Bosnien und Herzogowina	bis adm2 bzw. adm3	http://gov.genealogy.net/object_1127555
BE	Belgien	Abgleich notwendig	http://gov.genealogy.net/object_258578
BY	Weißrussland
CA	Kanada	komplett	http://gov.genealogy.net/object_277497
CH	Schweiz	Abgleich notwendig	http://gov.genealogy.net/object_191050
CZ	Tschechien	Abgleich notwendig	http://gov.genealogy.net/object_262328
DE	Deutschland	Abgleich notwendig	http://gov.genealogy.net/object_149273
DK	Dänemark	Abgleich notwendig	http://gov.genealogy.net/object_190123
EE	Estland	komplett	http://gov.genealogy.net/object_1293
ES	Spanien	komplett	http://gov.genealogy.net/object_985471
FI	Finnland	bis adm3, GOV enthält nichts
FR	Frankreich	komplett	http://gov.genealogy.net/object_190339
GB	Großbritannien	bis adm4	http://gov.genealogy.net/object_290485
GR	Griechenland
HR	Kroatien	komplett	object_269109
HU	Ungarn	komplett	http://gov.genealogy.net/object_269219
IE	Irland		http://gov.genealogy.net/object_382267
IT	Italien	eingetragen, aber Ebene adm3 nicht zugeordnet	http://gov.genealogy.net/object_214468
LI	Liechtenstein	komplett	http://gov.genealogy.net/object_220100
LT	Litauen	bis adm2	http://gov.genealogy.net/adm_368347
LU	Luxemburg	Abgleich notwendig	http://gov.genealogy.net/object_218129
LV	Lettland	bis adm1	http://gov.genealogy.net/adm_368348
MC	Monaco
MD	Moldavien
MK	Mazedonien	bis adm2	http://gov.genealogy.net/object_375683
MT	Malta
NL	Niederlande	Abgleich notwendig	http://gov.genealogy.net/object_216948
NO	Norwegen	komplett	http://gov.genealogy.net/object_382205
PL	Polen	bis adm3	http://gov.genealogy.net/object_190086
PT	Portugal
RO	Rumänien	bis adm2	http://gov.genealogy.net/object_255450
RS	Serbien		http://gov.genealogy.net/object_276528
RU	Russland	bis adm3	http://gov.genealogy.net/object_214266
SE	Schweden	komplett	http://gov.genealogy.net/object_217421
SI	Slowenien	bis adm1	http://gov.genealogy.net/object_364339
SK	Slovakei	bis adm2	http://gov.genealogy.net/adm_368372
SM	San Marino	bis adm1
UA	Ukraine	bis adm3	http://gov.genealogy.net/object_267742
US	USA	komplett	http://gov.genealogy.net/object_276534
VA	Vatikan

"Abgleich notwendig" bedeutet, dass für dieses Land bereits komplette Ortsdaten vorliegen. Es sind jedoch vermutlich noch nicht flächendeckend externen Referenzen auf geonames eingetragen.

Typen

Staat	ADM1	ADM2	ADM3	ADM4
DE	Bundesland	Regierungsbezirk	Kreis	Gemeinde
PL	Wojewodschaft	Powiat	Gmina	-

Abfragen

noch nicht zugeordnete ADM1:

select geonameid , name from geoname
 where country_code='pl' and feature_code='ADM1' AND gov_id is null;

noch nicht zugeordnete ADM2:

select a2.geonameid, a2.name,  a1.gov_id as adm1_gov, a1.name
 FROM geoname a2, geoname a1
 WHERE a2.country_code='pl' and a2.feature_code='ADM2'
 AND a1.country_code=a2.country_code AND a1.feature_code='ADM1'
 AND a1.admin1_code=a2.admin1_code AND a2.gov_id is null
 order by a1.name, a2.name;

noch nicht zugeordnete ADM3:

select a3.geonameid, a3.name, a2.gov_id as adm2GovId, a2.name as adm2Name, a1.name as adm1Name
FROM geoname a1, geoname a2, geoname a3
WHERE
  a1.country_code='pl' AND a1.feature_code='ADM1'
  AND a2.country_code=a1.country_code AND a2.feature_code='ADM2'
  AND a3.country_code=a1.country_code AND a3.feature_code='ADM3'
  AND a2.admin1_code=a1.admin1_code
  AND a3.admin1_code=a1.admin1_code AND a3.admin2_code=a2.admin2_code
  AND a3.gov_id is null;

noch nicht zugeordnete ADM3 für die für ADM2 bereits eine GOV-Kennung gefunden wurde

select a3.geonameid, a3.name, a2.gov_id as adm2GovId, a2.name as adm2Name, a1.name as adm1Name
FROM geoname a1, geoname a2, geoname a3
WHERE
  a1.country_code='pl' AND a1.feature_code='ADM1'
  AND a2.country_code=a1.country_code AND a2.feature_code='ADM2'
  AND a3.country_code=a1.country_code AND a3.feature_code='ADM3'
  AND a2.admin1_code=a1.admin1_code
  AND a3.admin1_code=a1.admin1_code AND a3.admin2_code=a2.admin2_code
  AND a3.gov_id is null
  AND a2.gov_id is not null;

noch nicht zugeordnete ADM4

select a4.geonameid, a4.name,  a3.gov_id as adm3GovId, a3.name as adm3Name,  a2.gov_id as adm2GovId, a2.name as adm2Name, a1.name as adm1Name
FROM geoname a1, geoname a2, geoname a3, geoname a4
WHERE
  a1.country_code='fr' AND a1.feature_code='ADM1'
  AND a2.country_code=a1.country_code AND a2.feature_code='ADM2'
  AND a3.country_code=a1.country_code AND a3.feature_code='ADM3'
  AND a4.country_code=a1.country_code AND a4.feature_code='ADM4'
  AND a2.admin1_code=a1.admin1_code
  AND a3.admin1_code=a1.admin1_code AND a3.admin2_code=a2.admin2_code
  AND a4.admin1_code=a1.admin1_code AND a2.admin2_code=a2.admin2_code AND a4.admin3_code=a3.admin3_code
  AND a4.gov_id is null;