[BUG] San Francisco, USA and Auckland, New Zealand should be locatable

[WolfgangFahl]

Locator examples that do not work with the 0.1.15 release: San Francisco, USA Auckland, New Zealand

[WolfgangFahl]

select city_name as name,country_iso_code as country,subdivision_1_iso_code as region, geoname_id
from cities 
where city_name in ('San Francisco','Auckland')
order by name,country,region

name    country region  geoname_id
Auckland    NZ  AUK 2193733
San Francisco   AR  X   3837675
San Francisco   CO  CUN 3669860
San Francisco   CR  H   3621911
San Francisco   DO  08  3493170
San Francisco   MX  NAY 3987135
San Francisco   SV  MO  3583747
San Francisco   US  CA  5391959
San Francisco   VE  V   3628374

[WolfgangFahl]

select city_name as name,country_iso_code as country,subdivision_1_iso_code as region,geoname_id
from citiesWithPopulation 
where city_name in ('San Francisco','Auckland')
order by country,region

name    country region  geoname_id
San Francisco   AR  X   3837675
San Francisco   CR  H   3621911
San Francisco   SV  MO  3583747
San Francisco   US  CA  5391959

[WolfgangFahl]

select geoNameId,cityLabel as name,countryIsoCode as country,city 
from cityPops
where cityLabel in ('San Francisco','Auckland')

geoNameId   name    country city
2193732 Auckland    NZ  http://www.wikidata.org/entity/Q37100
5391959 San Francisco   US  http://www.wikidata.org/entity/Q62
1690011 San Francisco   PH  http://www.wikidata.org/entity/Q316332
1690016 San Francisco   PH  http://www.wikidata.org/entity/Q155864
1690015 San Francisco   PH  http://www.wikidata.org/entity/Q173745
1690019 San Francisco   PH  http://www.wikidata.org/entity/Q627190
5391959 San Francisco   US  http://www.wikidata.org/entity/Q62
5550202 Auckland    US  http://www.wikidata.org/entity/Q15193971
5391959 San Francisco   US  http://www.wikidata.org/entity/Q62
5045808 San Francisco   US  http://www.wikidata.org/entity/Q7413896
3669885 San Francisco   CO  http://www.wikidata.org/entity/Q1576269
8880081 San Francisco   MX  http://www.wikidata.org/entity/Q20209614
3621911 San Francisco   CR  http://www.wikidata.org/entity/Q23736933
3701484 San Francisco   PA  http://www.wikidata.org/entity/Q33479006

[WolfgangFahl]

need to fix prefix selection

select 
  distinct name from GeoLite2CityLookup 
  where not name=""
union 
  select distinct wikidataName as name 
  from GeoLite2CityLookup

[WolfgangFahl]

see https://www.wikidata.org/wiki/Talk:Q37100 for the issue with Auckland, New Zealand

[WolfgangFahl]

To fix an outer join is necessary that adds wikidata records that are not in the geolite2 database