How to enrich places with geonames ID

Question

I have a list of places which I would enrich with the IDs from geonames. Since geonames by default it's embedded into WikiData I chose to go directly via SPARQL using WikiData endpoint.

My workflow:

• I have imported the excel file into OpenRefine and created a new project

• In OpenRefine I have created my graph, then I have downloaded it as RDF/XML. Here a snapshot:

    <rdf:Description rdf:about="http://localhost:3333/0">
        <rdfs:label>Aïre</rdfs:label>
        <crm:P1_is_identified_by>5A1CE163-105F-4BAF 8BF9</crm:P1_is_identified_by>
    </rdf:Description>
  

• I have imported then the RDF file into my local graphDB and I runned the federated query:

PREFIX wd: <http://www.wikidata.org/entity/> 
PREFIX wdt: <http://www.wikidata.org/prop/direct/> 
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
     SELECT  *
             WHERE {?place <http://purl.org/NET/cidoc-crm/core#P1_is_identified_by> ?value;
                         rdfs:label ?label_geo.
                     SERVICE <https://query.wikidata.org/sparql> { 
                         ?value wdt:P31/wdt:P279* wd:Q515;
                                rdfs:label ?label; 
                                wdt:P1566 ?id_value. 
                      } 
                   }  

     limit 10

No results.

The output should be something like this:

|-----------------------|------------------|---------------|
|      Oggetto          |    Place         | GeonamesID    |
|-----------------------|------------------|---------------|
|5A1CE163-105F-4BAF 8BF9|      Aïre        |11048419       |
|-----------------------|------------------|---------------|

Suggestions?

Thanks a lot.

Answer 1

I solved the problem directly via client

Here my pipeline:

1. I have created an Excel sheet with a list of place name
2. I built a Python script that uses as query parameters the values from the excel sheet and save the output in a .txt file. E.g. Aïre,https://www.geonames.org/11048419

import pandas as pd 
import requests
import json
import csv


url = 'http://api.geonames.org/searchJSON?'

#Change df parameters according to excel sheet specification.

df = pd.read_excel('grp.xlsx', sheet_name='Foglio14', usecols="A")

for item in df.place_name:

    df.place_name.head()

    #Change username params with geonames API username

    params ={   'username': "XXXXXXXX", 

                'name_equals': item,

                'maxRows': "1"}

    e = requests.get(url, params=params)

    pretty_json = json.loads(e.text)

    with open("data14.txt", "a") as myfile:

            writer = csv.writer(myfile)

            for item in pretty_json["geonames"]:

                    #print("{}, https://www.geonames.org/{}".format(item["name"], item["geonameId"]))

                    writer.writerow([item["name"], "https://www.geonames.org/{}".format(item["geonameId"])])  #Write row.

    myfile.close()

3. I have copied the output from the .txt file in the column B of the excel sheet.
4. I split then the output values into two columns. E.g.

    |---------------------|-----------------------------------|
    |      ColA           |     ColB                          |
    |---------------------|-----------------------------------|
    |         Aïre        | https://www.geonames.org/11048419 |
    |---------------------|-----------------------------------|

5. Since there is no a 1:1 correspondence between place name and the obtained results I have aligned the values.
   • In the excel sheet I have created a new empty column B
   • In the column B I wrote the formula: =IF(ISNA(MATCH(A1;C:C;0));"";INDEX(C:C;MATCH(A1;C:C;0))) and I have iterated the formula till the end of the list
   • Then I have created a new empty column C
   • In the column C I wrote the formula: =IFERROR(INDEX($E:$E;MATCH($B1;$D:$D;0));"") and I have iterated the formula till the end of the list

Here the final result: