<jupyter_start><jupyter_text>Introduction to SPARQL  What is SPARQL?* Query language for the semantic web* since 2008 W3C recommendation* since 2013 SPARQL 1.1 W3C recommendation ![title](img/sparql_anatomy.png)  SPARQL* Types of SPARQL Queries    * SELECT: select values and return them (we will only use that)    * CONSTRUCT: create a new Graph    * ASK: Boolean (True/False)    * DESCRIBE: Describe a resource ![title](img/4_types_sparql.png)  Representational State Transfer (Rest)* set of software architectual constraints to be used for web services* requests made to a resource's URI will elicit a response with a payload (in JSON, XML etc)    * response can e.g. be a confirmation that an operation on the resource has been done    * or a hyperlinked relation to another resource  most common HTTP methods* GET: Retrieve data* POST: Create new data* PUT: Update whole object* PATCH: Update part of the object* DELETE: Delete object  HTTP Header* used to define operating parameters    * Accept: Used to set the media type that is accepted by the request: "Accept: application/json"    * Accept-Charset: encoding that is accepted: "utf-8"    * Authorization: Authentication credentials: "Token TOKEN"  Some useful examples for Rest services in the DH* Lobid: https://lobid.org/* VIAF: https://platform.worldcat.org/api-explorer/apis* Peripleo (Pelagios): https://github.com/pelagios/peripleo/blob/master/README.md* ACDHs spacyapp: https://spacyapp.acdh-dev.oeaw.ac.at/query/nlppipeline/  SPARQL query against local RDF<jupyter_code>g=Graph()
g.parse('https://d-nb.info/gnd/118566512/about/lds.rdf')
properties = g.query("""
   SELECT ?s ?p ?o 
   WHERE {
      ?s ?p ?o .
   }
""")
df_kreisky =  pd.DataFrame(properties)
df_kreisky
properties = g.query("""
   SELECT (COUNT(*) AS ?cnt) {
      ?s ?p ?o .
   }
""")
df_kreisky =  pd.DataFrame(properties)
df_kreisky<jupyter_output><empty_output><jupyter_text>Example: SPARQL request wikidata<jupyter_code>from SPARQLWrapper import SPARQLWrapper, JSON
import pandas as pd
import requests
sparql = SPARQLWrapper("https://query.wikidata.org/sparql")
sparql.setQuery("""
SELECT 
  ?country ?population ?gnd ?capital
WHERE {
  ?country wdt:P31 wd:Q261543.
  ?country wdt:P1082 ?population .
  ?country wdt:P227 ?gnd .
  ?country wdt:P36 ?capital
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['capital.value', 'country.value', 'gnd.value', 'population.value']]
sparql.setQuery("""
SELECT 
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel
WHERE {
  ?country wdt:P31 wd:Q261543.
  ?country wdt:P1082 ?population .
  ?country wdt:P227 ?gnd .
  ?country wdt:P36 ?capital
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['capitalLabel.value', 'countryLabel.value', 'gnd.value', 'population.value']]<jupyter_output><empty_output><jupyter_text>Combining graph patterns in SPARQL* Conjunction:  A . B >> Join results of solving A and B* Left join: A OPTIONAL { B } >> keep all the results from A whether or not there are results from B* Disjunction: { A } UNION { B } >> keep results from solving A and solving B* Negation: A MINUS { B } >> Keep only those results from A that are not compatible with B<jupyter_code>sparql.setQuery("""
SELECT 
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel
WHERE {
  ?country wdt:P31 wd:Q261543.
  OPTIONAL { ?country wdt:P1082 ?population }
  OPTIONAL { ?country wdt:P227 ?gnd }
  OPTIONAL { ?country wdt:P36 ?capital }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['capitalLabel.value', 'countryLabel.value', 'gnd.value', 'population.value']]
sparql.setQuery("""
SELECT DISTINCT
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel ?populationCapital ?populationShareCapital
WHERE {
  ?country wdt:P31 wd:Q261543.
  OPTIONAL { ?country wdt:P1082 ?population }
  OPTIONAL { ?country wdt:P227 ?gnd }
  OPTIONAL { ?country wdt:P36 ?capital .
          ?capital wdt:P1082 ?populationCapital
  }
  FILTER(str(?capitalLabel) = 'Graz')
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df
sparql.setQuery("""
SELECT DISTINCT
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel ?populationCapital ?populationShareCapital
WHERE {
  ?country wdt:P31 wd:Q261543.
  OPTIONAL { ?country wdt:P1082 ?population }
  OPTIONAL { ?country wdt:P227 ?gnd }
  OPTIONAL { ?country wdt:P36 ?capital .
          ?capital wdt:P1082 ?populationCapital;
                    rdfs:label ?labelcapital
  }
  FILTER(str(?labelcapital) = 'Graz')
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['capitalLabel.value', 'countryLabel.value', 'gnd.value', 'population.value', 'populationCapital.value']]<jupyter_output><empty_output><jupyter_text>![title](img/filters_sparql.png)<jupyter_code>sparql.setQuery("""
SELECT DISTINCT
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel ?populationCapital ?populationShareCapital
WHERE {
  ?country wdt:P31 wd:Q261543.
  OPTIONAL { ?country wdt:P1082 ?population }
  OPTIONAL { ?country wdt:P227 ?gnd }
  OPTIONAL { ?country wdt:P36 ?capital .
          ?capital wdt:P1082 ?populationCapital
  }
  BIND(?populationCapital / ?population AS ?populationShareCapital)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['capitalLabel.value', 'countryLabel.value', 'gnd.value', 'population.value', 'populationShareCapital.value']]
sparql.setQuery("""
SELECT DISTINCT
  ?country ?countryLabel ?population ?gnd ?capital ?capitalLabel ?populationCapital ?lat_long
WHERE {
  ?country wdt:P31 wd:Q261543.
  OPTIONAL { ?country wdt:P1082 ?population }
  OPTIONAL { ?country wdt:P3529 ?medianIncome }
  OPTIONAL { ?country wdt:P227 ?gnd }
  OPTIONAL { ?country wdt:P36 ?capital .
          ?capital wdt:P1082 ?populationCapital
  }
  OPTIONAL { ?country wdt:P625 ?lat_long }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df[['countryLabel.value', 'lat_long.value',]]
import re
for idx, row in results_df.iterrows():
    print(row['lat_long.value'], type(row['lat_long.value']))
for idx, row in results_df.iterrows():
    lat_long = re.match('Point\(([0-9\.]+)\s([0-9\.]+)\)', row['lat_long.value'])
    if lat_long:
        long = lat_long.group(1)
        lat = lat_long.group(2)
        print(lat, long)
        results_df.at[idx, 'lat'] = lat
        results_df.at[idx, 'long'] = long
results_df[['countryLabel.value', 'lat_long.value', 'lat', 'long']]
from ipyleaflet import Map, basemaps, basemap_to_tiles, Marker
watercolor = basemap_to_tiles(basemaps.Stamen.Watercolor)
center = (results_df.at[0, 'lat'], results_df.at[0, 'long'])
m = Map(layers=(watercolor, ), center=center, zoom=3)
marker = Marker(location=center, draggable=False)
m.add_layer(marker)
m
from ipyleaflet import Map, basemaps, basemap_to_tiles, Marker
watercolor = basemap_to_tiles(basemaps.Stamen.Watercolor)
center = (results_df.at[0, 'lat'], results_df.at[0, 'long'])
m = Map(layers=(watercolor, ), center=center, zoom=6)
marker = Marker(location=center, draggable=False)
m.add_layer(marker)
for idx, row in results_df.loc[1:,:].iterrows():
    center = (row['lat'], row['long'])
    marker = Marker(location=center, draggable=False)
    m.add_layer(marker)
m
results_df[['capitalLabel.value', 'countryLabel.value', 'gnd.value', 'population.value', 'lat', 'long']]
url = 'https://histogis.acdh-dev.oeaw.ac.at/api/where-was'
res_geo = []
for idx, row in results_df.iterrows():
    params = {'lat': row['lat'], 'lng': row['long'], 'when': '1870'}
    res = requests.get(url, params=params)
    df_geo = pd.json_normalize(res.json()['features'])
    df_geo.at[:,'wikidata'] = row['country.value']
    res_geo.append(df_geo)
res_geo[0][['@id', 'names', 'properties.adm_name', 'properties.start_date', 'properties.end_date']]
url = res_geo[0].at[0, '@id']
HTML('<a href="{}">{}</a>'.format(url, url))
url = res_geo[0].at[0, 'wikidata']
HTML('<a href="{}">{}</a>'.format(url, url))<jupyter_output><empty_output><jupyter_text>Example request to lobid<jupyter_code>import requests
headers = {'accept': 'application/json'}
params = {'q': 'preferredName:<NAME>', 'filter': 'type:Person'}
url = 'https://lobid.org/gnd/search'
res = requests.get(url, params=params, headers=headers)
res
res.json()
res_dict = res.json()
res_df = pd.json_normalize(res_dict['member'])
res_df[['@context', 'preferredName', 'describedBy.id', 'biographicalOrHistoricalInformation', 'dateOfBirth', 'dateOfDeath']]
headers = {'accept': 'application/json'}
params = {'q': 'preferredName:Graz', 'filter': 'type:TerritorialCorporateBodyOrAdministrativeUnit'}
url = 'https://lobid.org/gnd/search'
res = requests.get(url, params=params, headers=headers)
res
res.json()
res_dict = res.json()
res_df = pd.json_normalize(res_dict['member'])
res_df[['@context', 'preferredName', 'describedBy.id', 'biographicalOrHistoricalInformation']]
res_df[['sameAs', 'preferredName']]<jupyter_output><empty_output><jupyter_text>Researchspace/Metaphacts* CMS based on triplestore* out of the box RDF navigation and viewing* allows to use SPARQL directly in HTML templates* webpack components for visualization of SPARQL results<jupyter_code>from SPARQLWrapper import SPARQLWrapper, POST, BASIC
sparql = SPARQLWrapper("https://triplestore.acdh-dev.oeaw.ac.at/summer2020/sparql")

sparql.setHTTPAuth(BASIC)
sparql.setCredentials("", "")
sparql.setMethod(POST)
sparql.setQuery("""
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX crm: <http://www.cidoc-crm.org/cidoc-crm/>
PREFIX frbroo: <http://iflastandards.info/ns/fr/frbr/frbroo#>
PREFIX bds: <http://www.bigdata.com/rdf/search#>
SELECT ?name ?sub ?prof_val WHERE {
  ?sub a crm:E21_Person ;
    rdfs:label ?name ;
        crm:P14i_performed ?prof .
  ?prof rdf:value ?prof_val .
  FILTER CONTAINS(str(?prof), "Architekt")
}

LIMIT 100
""")
sparql.setReturnFormat(JSON)
results = sparql.query().convert()
results_df = pd.json_normalize(results['results']['bindings'])
results_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1485023457235655, 'fraction_numerical': 0.019307109346806208, 'mean_word_length': 5.722255912674348, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 43, 'https://': 10, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '425277', 'license': 'MIT', 'n_tokens_mistral': 4146, 'n_tokens_neox': 3915, 'n_words': 854, 'path': 'Databases/SPARQL/SPARQL Tutorial/SPARQL Tutorial.ipynb', 'repo_name': 'okara83/Becoming-a-Data-Scientist', 'size': '11092'}