-
Notifications
You must be signed in to change notification settings - Fork 0
/
RSPARQL
82 lines (68 loc) · 16.8 KB
/
RSPARQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
Using R to extract Linked Data via the RSPARQL package
http://cran.r-project.org/web/packages/SPARQL/index.html
General template to run SPARQL queries in R using the RSPARQL package
> library(SPARQL)
> endpoint <- "put the endpoint here for e.g. http://dbpedia.org/sparql"
> query <- "put the query within these double quotes"
> res <- SPARQL (endpoint,query)$results
> res
=========================================
LinkedCT
=========================================
# Query to extract the total number of trials per country per disease per year
> endpoint <- "http://db0.aksw.org:8895/sparql"
> query <- "prefix rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns> prefix rdfs:<http://www.w3.org/2000/01/rdf-schema#> prefix linkedct:<http://data.linkedct.org/vocab/resource/> SELECT DISTINCT ?countryname ?conditionname count(distinct(?trial)) AS ?NoOfTrials FROM <http://data.linkedct.org> WHERE { ?trial ?type linkedct:trial. ?trial linkedct:trial_condition ?condition. ?condition rdfs:label ?conditionname. ?trial linkedct:trial_location_countries ?country. ?country rdfs:label ?countryname . ?trial linkedct:completion_date ?date. FILTER regex(?date, '1999')}GROUP BY ?countryname ?conditionname ORDER BY DESC(count(distinct(?trial)))"
=========================================
USPTO Patents
=========================================
# Query to extract the total number of patents per country per year
> endpoint <- "http://patents.aksw.org/patents/sparql"
> query <- PREFIX patent:<http://us.patents.aksw.org/schema/> PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#> PREFIX dcterms:<http://purl/org/dc/terms/> SELECT COUNT(DISTINCT (?s)) AS ?NoOfPatents ?countryname FROM <http://aksw.patents.org> WHERE {?s patent:hasCountry ?country. ?country rdfs:label ?countryname. ?s dcterms:date ?year. FILTER (?year IN('2000-01-01'^^xsd:date, '2000-02-01'^^xsd:date, '2000-03-01'^^xsd:date, '2000-04-01'^^xsd:date, '2000-05-01'^^xsd:date, '2000-06-01'^^xsd:date, '2000-07-01'^^xsd:date, '2000-08-01'^^xsd:date, '2000-09-01'^^xsd:date, '2000-10-01'^^xsd:date, '2000-11-01'^^xsd:date, '2000-12-01'^^xsd:date))}"
=========================================
Scimago
=========================================
# Query to extract the total number of documents and h-index per country per year (1999 to 2010)
> endpoint <- "http://db0.aksw.org:8895/sparql"
> query <- "PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX scimago:<http://scimago.org/> PREFIX rdfs:<http://www.w3.org/2000/01/rdf-schema#> SELECT DISTINCT ?countryname ?year ?hindex ?noOfDocs FROM <http://scimago.org> WHERE { ?s rdf:type scimago:journalRanking. ?s scimago:country ?country. ?country rdfs:label ?countryname. ?s scimago:hIndex ?hindex. ?s scimago:docs ?noOfDocs. ?s scimago:year ?year. FILTER ((?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/20010>) ) }"
=========================================
World Bank
=========================================
# Query to extract specific variables (by specifying the corresponding indicator) per country per year (1999 to 2010)
> endpoint <- "http://worldbank.270a.info/sparql"
> query <- "PREFIX property: <http://worldbank.270a.info/property/> PREFIX indicator: <http://worldbank.270a.info/classification/indicator/> PREFIX sdmx-dimension: <http://purl.org/linked-data/sdmx/2009/dimension#> PREFIX sdmx-measure: <http://purl.org/linked-data/sdmx/2009/measure#> PREFIX dbo: <http://dbpedia.org/ontology/> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> SELECT ?label ?obsValue ?year WHERE { ?observationURI property:indicator indicator:SP.ADO.TFRT; sdmx-dimension:refArea ?refAreaURI; sdmx-dimension:refPeriod ?year; sdmx-measure:obsValue ?obsValue. ?refAreaURI a dbo:Country . ?refAreaURI skos:prefLabel ?label. FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
List of 13 variables used in this project and their respective indicators:
* Adolescent fertility rate ( births per 1,000 women ages 15-19) - SP.ADO.TFRT
* Birth rate, crude (per 1,000 people) - SP.DYN.CBRT.IN
* Death rate, crude (per 1,000 people) - SP.DYN.CDRT.IN
* GDP per capita (current US$) - NY.GDP.PCAP.CD
* Health expenditure public (% of total health expenditure) - H.XPD.PUBL
* High-technology export (% of manufactured exports) -TX.VAL.TECH.MF.ZS
* Immunization DPT (% of children ages 12 - 23 months) - SH.IMM.IDPT
* Immunization measles (% of children ages 12 - 24 months) SH.IMM.MEAS
* Incidence of tuberculosis (per 100,000 people) - SH.TBS.INCD
* Mortality rate, infant (per 1,000 live births) - SP.DYN.IMRT.IN
* Public spending on education, total (% of govt expenditure) - SE.XPD.TOTL.GB.ZS
* Research and development expenditure (% of GDP) - GB.XPD.RSDV.GD.ZS
* Researchers in R&D (per million) - SP.POP.SCIE.RD.P6
=========================================
OECD*
=========================================
# Query to extract specific variables (by specifying the corresponding graph of the indicator) per country per year
# This query is for the indicator "Development Aid: Foreign direct investment flows by country"
> endpoint <- "http://oecd.270a.info/sparql"
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/FDI_FLOW_PARTNER> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/YEA> ?year. ?s <http://oecd.270a.info/dimension/1.0/COU> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/PC> <http://oecd.270a.info/code/1.0/CL_FDI_FLOW_PARTNER_PC/A1>. ?s <http://oecd.270a.info/dimension/1.0/CUR> <http://oecd.270a.info/code/1.0/CL_FDI_FLOW_PARTNER_CUR/USD>. ?s <http://oecd.270a.info/dimension/1.0/FLOW> <http://oecd.270a.info/code/1.0/CL_FDI_FLOW_PARTNER_FLOW/IN>. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en') FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
Queries for specific indicators:
# Educational personnel (1999 to 2003)
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/RPERS> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/YEAR> ?year. ?s <http://oecd.270a.info/dimension/1.0/COUNTRY> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/DSTAFF> <http://oecd.270a.info/code/1.0/CL_RPERS_DSTAFF/110>. ?s <http://oecd.270a.info/dimension/1.0/DMODETYP> <http://oecd.270a.info/code/1.0/CL_RPERS_DMODETYP/2>. ?s <http://oecd.270a.info/dimension/1.0/DSEX> <http://oecd.270a.info/code/1.0/CL_RPERS_DSEX/90>. ?s <http://oecd.270a.info/dimension/1.0/DLVLEDUC> <http://oecd.270a.info/code/1.0/CL_RPERS_DLVLEDUC/1>. ?s <http://oecd.270a.info/dimension/1.0/DAGEGR> <http://oecd.270a.info/code/1.0/CL_RPERS_DAGEGR/900000>. ?s <http://oecd.270a.info/dimension/1.0/DINSTTYP> <http://oecd.270a.info/code/1.0/CL_RPERS_DINSTTYP/10>. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en') FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>))}"
# Educational personnel (2004 to 2010)
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/RPERS> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/YEAR> ?year. ?s <http://oecd.270a.info/dimension/1.0/COUNTRY> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/DSTAFF> <http://oecd.270a.info/code/1.0/CL_RPERS_DSTAFF/115>. ?s <http://oecd.270a.info/dimension/1.0/DMODETYP> <http://oecd.270a.info/code/1.0/CL_RPERS_DMODETYP/2>. ?s <http://oecd.270a.info/dimension/1.0/DSEX> <http://oecd.270a.info/code/1.0/CL_RPERS_DSEX/90>. ?s <http://oecd.270a.info/dimension/1.0/DLVLEDUC> <http://oecd.270a.info/code/1.0/CL_RPERS_DLVLEDUC/1>. ?s <http://oecd.270a.info/dimension/1.0/DAGEGR> <http://oecd.270a.info/code/1.0/CL_RPERS_DAGEGR/900000>. ?s <http://oecd.270a.info/dimension/1.0/DINSTTYP> <http://oecd.270a.info/code/1.0/CL_RPERS_DINSTTYP/10>. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en') FILTER ((?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
# Purchasing Power Parities for GDP and related indicators (1999 to 2010)
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/PPPGDP> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/TIME> ?year. ?s <http://oecd.270a.info/dimension/1.0/LOCATION> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/INDIC> <http://oecd.270a.info/code/1.0/CL_PPPGDP_INDIC/PPP>. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en')FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
# Doctors: Physicians, by age and gender (1999 to 2010)
# Total health and social employment (1999 to 2010) Replace the code in the query below with "GEHETHSM"
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/HEALTH_REAC> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/VAR> ?o. ?o skos:notation 'GEHETHSM'. ?s <http://oecd.270a.info/dimension/1.0/YEA> ?year. ?s <http://oecd.270a.info/dimension/1.0/COU> ?country. ?s <http://oecd.270a.info/dimension/1.0/UNIT> <http://oecd.270a.info/code/1.0/CL_HEALTH_REAC_UNIT/PERSMYNB>. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en') FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
# Hospital beds density per 1,000 population (1999 to 2010
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/HEALTH_REAC> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/VAR> ?o. ?o skos:notation 'HOPITBED'. ?s <http://oecd.270a.info/dimension/1.0/YEA> ?year. ?s <http://oecd.270a.info/dimension/1.0/COU> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/UNIT> <http://oecd.270a.info/code/1.0/CL_HEALTH_REAC_UNIT/NOMBRENB>.?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),'en') FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
# All causes of mortality (1999 to 2010)
> query <- "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX skos: <http://www.w3.org/2004/02/skos/core#> PREFIX qb: <http://purl.org/linked-data/cube#> SELECT DISTINCT ?countryname ?year ?value FROM <http://oecd.270a.info/graph/HEALTH_STAT> FROM <http://oecd.270a.info/graph/meta> WHERE { ?s a qb:Observation. ?s <http://oecd.270a.info/dimension/1.0/VAR> ?o. ?o skos:notation "CICDALLC". ?s <http://oecd.270a.info/dimension/1.0/YEA> ?year. ?s <http://oecd.270a.info/dimension/1.0/COU> ?country. ?s <http://oecd.270a.info/measure/1.0/OBS_VALUE> ?value. ?s <http://oecd.270a.info/dimension/1.0/UNIT> <http://oecd.270a.info/code/1.0/CL_HEALTH_STAT_UNIT/NBPOPUPC>. ?s <http://oecd.270a.info/dimension/1.0/VAR> <http://oecd.270a.info/code/1.0/CL_HEALTH_STAT_VAR/CICDALLC>. ?country skos:prefLabel ?countryname. FILTER langMatches(lang(?countryname),"en") FILTER ((?year=<http://reference.data.gov.uk/id/year/1999>) || (?year=<http://reference.data.gov.uk/id/year/2000>) || (?year=<http://reference.data.gov.uk/id/year/2001>) || (?year=<http://reference.data.gov.uk/id/year/2002>) || (?year=<http://reference.data.gov.uk/id/year/2003>) || (?year=<http://reference.data.gov.uk/id/year/2004>) || (?year=<http://reference.data.gov.uk/id/year/2005>) || (?year=<http://reference.data.gov.uk/id/year/2006>) || (?year=<http://reference.data.gov.uk/id/year/2007>) || (?year=<http://reference.data.gov.uk/id/year/2008>) || (?year=<http://reference.data.gov.uk/id/year/2009>) || (?year=<http://reference.data.gov.uk/id/year/2010>))}"
*The OECD variables contained a large number of "NA" values and were thus discarded at the end.