def testWikidataCities(self):
'''
test getting city information from wikidata
'''
# Wikidata time outs in CI environment need to be avoided
if getpass.getuser() != "wf":
return
config = StorageConfig.getSQL(debug=self.debug)
config.cacheRootDir = "/tmp/wdhs"
cachedir = config.getCachePath()
config.cacheFile = f"{cachedir}/hs.db"
# use 2018 wikidata copy
# wikidata.endpoint="http://blazegraph.bitplan.com/sparql"
# use 2020 wikidata copy
wikidata = Wikidata()
wikidata.endpoint = "https://confident.dbis.rwth-aachen.de/jena/wdhs/sparql"
#wikidata.endpoint="http://jena.bitplan.com/wdhs/sparql"
regions = [{
"name": "Singapore",
"country": "Q334",
"region": None,
"cities": 46
}, {
"name": "Beijing",
"country": None,
"region": "Q956",
"cities": 25
}, {
"name": "Paris",
"country": None,
"region": "Q13917",
"cities": 1242
}, {
"name": "Barcelona",
"country": None,
"region": "Q5705",
"cities": 1242
}, {
"name": "Rome",
"country": None,
"region": "Q1282",
"cities": 1242
}]
limit = 1000000 #if self.inCI() else 100
cityList = wikidata.getCities(limit=limit)
sqlDB = SQLDB(config.cacheFile)
entityInfo = sqlDB.createTable(cityList, "hs", withDrop=True)
sqlDB.store(cityList, entityInfo, fixNone=True)
expected = 200000 # if self.inCI() else limit
self.assertTrue(len(cityList) >= expected)
def getWikidataCityPopulation(self,sqlDB,endpoint=None):
'''
Args:
sqlDB(SQLDB): target SQL database
endpoint(str): url of the wikidata endpoint or None if default should be used
'''
dbFile=self.db_path+"/city_wikidata_population.db"
rawTableName="cityPops"
# is the wikidata population database available?
if not os.path.exists(dbFile):
# shall we created it from a wikidata query?
if endpoint is not None:
wikidata=Wikidata()
wikidata.endpoint=endpoint
cityList=wikidata.getCityPopulations()
wikiCitiesDB=SQLDB(dbFile)
entityInfo=wikiCitiesDB.createTable(cityList[:300],rawTableName,primaryKey=None,withDrop=True)
wikiCitiesDB.store(cityList,entityInfo,fixNone=True)
else:
# just download a copy
print("Downloading %s ... this might take a few seconds" % dbFile)
dbUrl="http://wiki.bitplan.com/images/confident/city_wikidata_population.db"
urllib.request.urlretrieve(dbUrl,dbFile)
# (re) open the database
wikiCitiesDB=SQLDB(dbFile)
# check whether the table is populated
tableList=sqlDB.getTableList()
tableName="citiesWithPopulation"
if self.db_recordCount(tableList, tableName)<10000:
# check that database is writable
# https://stackoverflow.com/a/44707371/1497139
sqlDB.execute("pragma user_version=0")
# makes sure both tables are in target sqlDB
wikiCitiesDB.copyTo(sqlDB)
# create joined table
sqlQuery="""
select
geoname_id,
city_name,
cp.cityLabel,
country_iso_code,
country_name,
subdivision_1_iso_code,
subdivision_1_name,
cp.city as wikidataurl,
cp.cityPop
from cities c
join cityPops cp
on c.geoname_id=cp.geoNameId
union
select
geoNameId as geoname_id,
null as city_name,
cityLabel,
countryIsoCode as country_iso_code,
countryLabel as country_name,
null as subdivision_1_iso_code,
null as subdivision_1_name,
city as wikidataurl,
cityPop
from cityPops
where cityPop is not Null
group by geoNameId
order by cityPop desc
"""
cityList=sqlDB.query(sqlQuery)
entityInfo=sqlDB.createTable(cityList,tableName,primaryKey=None,withDrop=True,sampleRecordCount=500)
sqlDB.store(cityList,entityInfo,fixNone=True)