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.

I don't understand what you are doing in your query. `?value` is a string literal isn't it? In your example I guess it's `5A1CE163-105F-4BAF 8BF9`, right? So, which RDF triple in Wikidata would be `"5A1CE163-105F-4BAF 8BF9" wdt:P31 wd:Q515 .` ? A literal is never the subject of an RDF triple. If you want to join on the labels, you should use the same label variable and not `?label` and `?label_geo`. And you should indeed use different variables for `?value`. Indeed this will probably still fail because in Wikidata the literals are language tagged. — UninformedUser, Jul 23 '19 at 18:58
Got your point. What about create a new graph in open refine? — Pelide, Jul 24 '19 at 09:13
is it working now if you use just a single label variable? are the same? I don't have any setup to test it. And if possible, you should create labels with a language tag, e.g. `"Aïre"@en` — UninformedUser, Jul 24 '19 at 10:06

score 1 · Answer 1 · answered Aug 20 '19 at 17:45

I solved the problem directly via client

Here my pipeline:

I have created an Excel sheet with a list of place name
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()

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

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

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:

How to enrich places with geonames ID

1 Answers1

Linked