Wikidata:Request a query/Archive/2020/02
This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion. |
Query by wiki edits?
Can we include Wikidata wiki edits in a query - for example, "people with illustrator as an occupation, whose item was created by User:pigsonthewing"? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 16:23, 28 January 2020 (UTC)
- I am pretty sure that this cannot be queried with a simple tool. Information about items is only available via SPARQL, and the user edits data is only available via SQL.
- One can, however, do both queries separately ("people with illustrator as occupation", and "items created by User:Pigsonthewing"), and then intersect both results sets with software. For your particular request, I can currently find 40 items (including subclasses of illustrator (Q644687) as occupations):
- James Lucas (Q60523543), Mary Gernat (Q64067695), Mildred Ratcliffe (Q63872518), Celia Levetus (Q34481210), August Geigenberger (Q37666506), Harry Tuck (Q56396052), Emil Schmidt (Q61000587), Hector Whistler (Q19657576), Donald Chaffin (Q21198134), Katherine Sturges Dodge (Q19857092), James Hart (Q60169477), Earl Oliver Hurst (Q61599608), Nowell Edwards (Q61946233), Reg Gammon (Q15381364), Diane Elson (Q72065094), Frederick Smallfield (Q14916854), William Hawkes Smith (Q76008343), Cecil W. Bacon (Q28031402), Leslie Ashwell Wood (Q78310140), Harry Woolley (Q78510583), Monica Walker (Q79596582), Doris Palethorpe (Q80125831), J. Inder Burns (Q80216545), Cecil Glossop (Q80325220), Althea Willoughby (Q59857851), Hazel Mead (Q81297633), Molly Blake (Q83488439), Doris Pailthorpe (Q65374766), Afua Richardson (Q29786614), Clayton Knight (Q19857082), Piero Ruggeri (Q54860430), Ian Garrard (Q63062221), Naif Al-Mutawa (Q23795283), Katharine Saunders (Q20984930), Fanny Currey (Q20085715), G. Reinert (Q62565942), Rebecca Hey (Q81721599), Bernardus Aretinus (Q60390693), F. Pollard (Q60429528), and Elaine R. Snyder Hodges (Q46998324)
- —MisterSynergy (talk) 18:45, 28 January 2020 (UTC)
- @MisterSynergy: Not the answer I hoped for, but the data you've provded is very useful, thank you. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:37, 1 February 2020 (UTC)
You've Lived Longer Than...
Hello all,
I'm in need of some help writing a query. I need to produce data for notable people that a user has lived longer than.
User inputs birthday > calculate "days lived" > Query people that have shorter lifespans > return results
I'm having trouble converting lifespan to "days lived" to compare with against a user's input.
Any help would be greatly appreciated :)
- You can subtract dateTime values to get the difference in days. An example: List dead physicists who lived longer than a living person who was born 1918-01-01:
SELECT ?person ?personLabel ?person_age ?dateOfBirth ?dateOfDeath
{
{
SELECT ?person ?person_age ?dateOfBirth ?dateOfDeath
{
BIND(NOW() - "1918-01-01"^^xsd:dateTime as ?age).
?person wdt:P31 wd:Q5.
?person p:P569/psv:P569 [
wikibase:timeValue ?dateOfBirth;
wikibase:timePrecision ?dob_precision
].
FILTER(?dob_precision = "11"^^xsd:integer) # Precision is day
?person p:P570/psv:P570 [
wikibase:timeValue ?dateOfDeath;
wikibase:timePrecision ?dod_precision
].
FILTER(?dod_precision = "11"^^xsd:integer) # Precision is day
?person wdt:P106/wdt:P279* wd:Q169470. # occupation is physicist
BIND(?dateOfDeath - ?dateOfBirth as ?person_age).
FILTER (?person_age > ?age)
}
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
How many tunnels can be found in France ? in Germany ? in Italy ? in Europe ? How long and how old are these tunnels ?
In risk engineering, a very simplisitic way to estimate the frequency of accidents in tunnels could be to estimate the following ratio (for a given geographical zone) (kilometers of roads in tunnel) / (overall kilometers of roads). Can Wikidata answers these questions? – The preceding unsigned comment was added by AntoineLogean (talk • contribs) at 09:28, 3 February 2020 (UTC).
- @AntoineLogean: Maybe the following query gives some information:
- Try it!
SELECT ?countryLabel (COUNT(?item) AS ?number_of_objects) (SUM(?length) AS ?sum_length_meters) (ROUND(SUM(?length)/COUNT(?item)) AS ?average_length_meters) { ?item wdt:P31/wdt:P279* wd:Q2354973 . # to get all subclasses (types) of tunnels, change this to wd:Q44377 . ?item wdt:P17 ?country . ?item p:P2043 [ psn:P2043 [ wikibase:quantityAmount ?length ] ] . # psn translates all values for length to a common unit, i.e. meters SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } GROUP BY ?countryLabel ORDER BY DESC(?number_of_objects) ?countryLabel
- --Larske (talk) 11:05, 3 February 2020 (UTC)
- And from the following query that lists almost 2 000 objects that are instance of (P31) road tunnel (Q2354973) you can see that date of official opening (P1619) and length (P2043) are missing for a lot of objects.
- Try it!
SELECT ?countryLabel ?item ?itemLabel (YEAR(?date) AS ?year) ?length_meters { ?item wdt:P31/wdt:P279* wd:Q2354973 . # to get all subclasses (types) of tunnels, change this to wd:Q44377 . ?item wdt:P17 ?country . # ?country wdt:P30 wd:Q46 . # uncomment this line if you want to limit to countries in Europe OPTIONAL { ?item wdt:P1619 ?date } OPTIONAL { ?item p:P2043 [ psn:P2043 [ wikibase:quantityAmount ?length_meters ] ] . } # psn translates all values for length to a common unit, i.e. meters SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,nn,nb,da,fi,nl,cs,sk,lb,fr,el,es,ru". } } ORDER BY ?countryLabel STR(?itemLabel)
- --Larske (talk) 11:28, 3 February 2020 (UTC)
Getting <most recently created items> to work with qualifiers
@Jura1: All Wikidata properties have a link to a '<most recently created items>' query on their talk page but unfortunately the query doesn't work on qualifiers. How can this be fixed? --Trade (talk) 23:12, 3 February 2020 (UTC)
- Like this.
SELECT *
WHERE
{
{ ?item wdt:P4970 [] . }
UNION
{ ?item ?property [pq:P4970 [] ]. }
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item),33)))
LIMIT 10
- It is also possible to add a search for use in references. --Dipsacus fullonum (talk) 23:56, 3 February 2020 (UTC)
BTW. The sorting of item numbers as strings is bad. It will give Q9 > Q80 > Q700 > Q6000 etc.--Dipsacus fullonum (talk) 00:03, 4 February 2020 (UTC) – Somehow I overlooked the conversion to xsd:integer so that isn't a problem. --Dipsacus fullonum (talk) 02:17, 4 February 2020 (UTC)
- Below is code to show the most recent items and lexemes which use P4970 in statements, qualifiers and references (up to 10 of each):
SELECT ?type ?item ?property
WHERE
{
{
SELECT ("Item" as ?type) ?item
WHERE
{
?item wdt:P4970 [] .
FILTER(SUBSTR(STR(?item),32,1) = "Q")
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item),33)))
LIMIT 10
}
UNION
{
SELECT ("Lexeme" as ?type) ?item
WHERE
{
?item wdt:P4970 [] .
FILTER(SUBSTR(STR(?item),32,1) = "L")
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item),33)))
LIMIT 10
}
UNION
{
SELECT ("Qualifier" as ?type) ?item ?property
WHERE
{
?item ?property [pq:P4970 [] ].
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item),33)))
LIMIT 10
}
UNION
{
SELECT ("Reference" as ?type) ?item ?property
WHERE
{
?item ?property _:statement.
_:statement prov:wasDerivedFrom _:refnode.
_:refnode pr:P4970 _:ref.
}
ORDER BY DESC(xsd:integer(SUBSTR(STR(?item),33)))
LIMIT 10
}
}
ORDER BY ?type DESC(xsd:integer(SUBSTR(STR(?item),33)))
- Amazing query .. I will try to update the property documentation template. --- Jura 20:57, 4 February 2020 (UTC)
Communes de France
Bonjour,
La requête suivante donne la liste des communes françaises existant au 1er janvier 2017, triées par leur code INSEE.
SELECT ?insee ?commune
WHERE {
BIND ( "2017-01-01"^^xsd:dateTime as ?dateRecherche ) # date recherchée
?commune wdt:P374 ?insee. # code INSEE
?commune p:P31 ?nature.
{?nature ps:P31 wd:Q484170.} # commune de France
UNION { ?nature ps:P31 wd:Q22927616. } # commune française à statut particulier
OPTIONAL { ?commune wdt:P571 ?dateCréation. } # date création
OPTIONAL { ?commune wdt:P576 ?dateDissolution. } # date dissolution
FILTER ( !BOUND(?dateCréation)|| ?dateCréation <= ?dateRecherche ) # communes futures
FILTER ( !BOUND(?dateDissolution)|| ?dateDissolution > ?dateRecherche ) # communes passées
FILTER ( !STRSTARTS(?insee, "98") ) # - Polynésie
FILTER ( !STRSTARTS(?insee, "975") ) # - Saint-Pierre et Miquelon
}
ORDER BY (?insee)
Elle contient une erreur : la commune d'Avernes (Q55589867) apparaît deux fois car elle a changé de code INSEE. Comment faire pour qu'elle n'apparaisse qu'une fois, avec le code qui lui était attribué au 1er janvier 2017 ?
Merci pour votre aide.
Cordialement, Arpyia (talk) 19:16, 3 February 2020 (UTC)
- @Arpyia: Two ways to sort of the problem. 1) fix the data. 2.) fix the report.
- 1. Fix the data. diff. Done. The current INSEE value should be of Preferred rank, such that it is the only value to be returned when looking for wdt:P374.
- 2. Fix the report. (Not necessary now, but...there is a 9 hour lag before edits update report servers, so) you would have to exclude INSEE values that have a pq:P582 (i.e. an end-date):
- --Tagishsimon (talk) 19:28, 3 February 2020 (UTC)Try it!
SELECT ?insee ?commune WHERE { BIND ( "2017-01-01"^^xsd:dateTime as ?dateRecherche ) # date recherchée # values ?commune {wd:Q55589867} # ?commune wdt:P374 ?insee. # code INSEE ?commune p:P374 ?P374node . # there is a P374 ?P374node ps:P374 ?insee . # with this value filter not exists {?P374node pq:P582 [] .} # and without this qualifier ?commune p:P31 ?nature. {?nature ps:P31 wd:Q484170.} # commune de France UNION { ?nature ps:P31 wd:Q22927616. } # commune française à statut particulier OPTIONAL { ?commune wdt:P571 ?dateCréation. } # date création OPTIONAL { ?commune wdt:P576 ?dateDissolution. } # date dissolution FILTER ( !BOUND(?dateCréation)||| ?dateDissolution > ?dateRecherche ) # communes passées FILTER ( !STRSTARTS(?insee, "98") ) # - Polynésie FILTER ( !STRSTARTS(?insee, "975") ) # - Saint-Pierre et Miquelon } ORDER BY (?insee)
- Hello @Tagishsimon: and thank you for your answer, but this is not what I am looking for. Both your suggestions are about finding the newest information, while my query is designed to work with historical data.
- I am not trying to find the current value, but the right one at the time I'm studying. When a commune changes its INSEE code after 2017, I still need to find it under the code it had in 2017. Conversely, when I use this query to get the list of communes in 1966, I expect Avernes (Q55589867) to show up with its INSEE code from that time.
- Regards, Arpyia (talk) 20:54, 3 February 2020 (UTC)
- Sorry about that. Probably something like the query below (though there are probably many ways of doing much the same). I do think that the current value for INSEE in Avernes (Q55589867) should be preferred rank; it is the current truthy value & that's why we have rank. So I'm sorry to see you have changed it back.
- --Tagishsimon (talk) 21:59, 3 February 2020 (UTC)Try it!
SELECT ?insee ?commune WHERE { BIND ( "2017-01-01"^^xsd:dateTime as ?dateRecherche ) # date recherchée # values ?commune {wd:Q55589867} # ?commune wdt:P374 ?insee. # code INSEE ?commune p:P374 ?P374node . # there is a P374 ?P374node ps:P374 ?insee . # with this value optional {?P374node pq:P580 ?start . } # may have a start date optional {?P374node pq:P582 ?end . } # may have an end date bind(if(!bound(?start), "2016"^^xsd:integer,year(?start)) as ?startdate) bind(if(!bound(?end), "2018"^^xsd:integer,year(?end)) as ?enddate) filter (?startdate < 2017) filter (?enddate > 2017) ?commune p:P31 ?nature. {?nature ps:P31 wd:Q484170.} # commune de France UNION { ?nature ps:P31 wd:Q22927616. } # commune française à statut particulier OPTIONAL { ?commune wdt:P571 ?dateCréation. } # date création OPTIONAL { ?commune wdt:P576 ?dateDissolution. } # date dissolution FILTER ( !BOUND(?dateCréation)|| ?dateDissolution > ?dateRecherche ) # communes passées FILTER ( !STRSTARTS(?insee, "98") ) # - Polynésie FILTER ( !STRSTARTS(?insee, "975") ) # - Saint-Pierre et Miquelon } ORDER BY (?insee)
- Hello @Tagishsimon:, and thank you again for your research. This is going in the direction I was hoping for. There are still issues, because there should be a way to find more precise data, i.e. find information for each day instead of for the whole year. Anyway, this new query returns different and unexpected errors, so I need to investigate the data again.
- Regards, Arpyia (talk) 20:00, 4 February 2020 (UTC)
- It is easy to change the code to use day precision instead of year. The variables ?startdate and ?enddate isn't necessary and the date filters can be changed to:
filter (!bound(?start) || ?start <= ?dateRecherche) filter (!bound(?end) || ?end >= ?dateRecherche)
- Ideally you might want to also add a test for the precision of the date values. --Dipsacus fullonum (talk) 20:53, 4 February 2020 (UTC)
- Hello @Tagishsimon: and @Dipsacus fullonum:, I think this is working! I did some more work and it turns out the data is fine, only the query needed some adjustments. So here it is:
- Try it!
SELECT ?insee ?commune WHERE { BIND ( "2017-01-01"^^xsd:dateTime as ?dateRecherche ) # date recherchée ?commune p:P374 ?P374node . # recherche code INSEE ?P374node ps:P374 ?insee . # valeur code INSEE optional {?P374node pq:P580 ?start . } # may have a start date optional {?P374node pq:P582 ?end . } # may have an end date filter (!bound(?start) || ?start <= ?dateRecherche) filter (!bound(?end) || ?end >= ?dateRecherche) ?commune p:P31 ?nature. {?nature ps:P31 wd:Q484170.} # commune de France UNION { ?nature ps:P31 wd:Q22927616. } # commune française à statut particulier OPTIONAL { ?commune wdt:P571 ?dateCréation. } # date création OPTIONAL { ?commune wdt:P576 ?dateDissolution. } # date dissolution FILTER ( !BOUND(?dateCréation)|| ?dateCréation <= ?dateRecherche ) # communes futures FILTER ( !BOUND(?dateDissolution)|| ?dateDissolution > ?dateRecherche ) # communes passées FILTER ( !STRSTARTS(?insee, "98") ) # - Polynésie FILTER ( !STRSTARTS(?insee, "975") ) # - Saint-Pierre et Miquelon } ORDER BY (?insee)
- I am happy to say that for the first time, Wikidata has a correct list of communes of France, as of the three last years. I will now resume my work on previous years and see if any other problems arise. I am still interested if improvements can be made.
- Thank you a lot! Regards, Arpyia (talk) 12:56, 5 February 2020 (UTC)
- It is easy to change the code to use day precision instead of year. The variables ?startdate and ?enddate isn't necessary and the date filters can be changed to:
All items of Sci-Fi films that were initially published between 1990 to 1999 and that have articles on the Hebrew Wikipedia
I tried to create the code by myself but haven't succeeded so far. WikiJunkie (talk) 00:49, 6 February 2020 (UTC)
- Try this. I sought for items which either is science fiction films, or films with genre = science fiction films. But it seems that all science fiction film is registered as the latter. If you want a link to the Hebrew article, change the blank node _:article to a variable (?article) and add it to GROUP BY and both SELECT. --Dipsacus fullonum (talk) 05:45, 6 February 2020 (UTC)
SELECT ?film ?filmLabel ?first_publication_date
WHERE
{
{
SELECT ?film (MIN(?publication_date) AS ?first_publication_date)
WHERE
{
?film wdt:P31/wdt:P279* wd:Q11424. # film
?film (wdt:P136)|(wdt:P31/wdt:P279*) wd:Q471839. # science fiction film
_:article schema:about ?film; schema:isPartOf <https://he.wikipedia.org/>. # article on hewiki
?film wdt:P577 ?publication_date.
}
GROUP BY ?film
}
FILTER (?first_publication_date >= "1990-00-00"^^xsd:dateTime && ?first_publication_date < "2000-00-00"^^xsd:dateTime)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],he,en" }
}
Queries about "John"
At Talk:Q4925477, I started adding a few queries about John (Q4925477). I'd like to include more and I'm looking for suggestions. They can be basic or more complicated.
Ideally, they'd work for any given name, but some might work just for this name. We now have more than 100,000 uses of the name with given name (P735).
BTW, the interface language needs to be set to English to see the full list (I haven't managed fixing that yet). --- Jura 06:37, 6 February 2020 (UTC)
Verify categories across languages
I'm wondering if there's a way to verify if a page in a category and the corresponding page on a different wikipedia is in the corresponding category. Example given: If en:w:Julia Dujmovits has category en:w:Category:Olympic gold medalists for Austria on english wikipedia and the corresponding page it:w:Julia Dujmovits is not in the corresponding category w:it:Categoria:Vincitori di medaglia d'oro olimpica per l'Austria in italian wikipedia. Thanks --Luckyz (talk) 09:45, 6 February 2020 (UTC)
- Yes, but not with SPARQL queries. I have done checks like that using database dumps from the Wikipedias and Wikidata, but I don't have the setup to do this at the momonet. --Dipsacus fullonum (talk) 11:40, 6 February 2020 (UTC)
- Update: My answer may be wrong as you can in fact call the MediaWiki API of all Wikipedias as a service from the Wikidata Query Service. See mw:Wikidata Query Service/User Manual/MWAPI. I will have to study this more before trying to do this, but maybe others can help. --Dipsacus fullonum (talk) 21:45, 6 February 2020 (UTC)
- @Luckyz: Here is code that lists items in the category Category:Olympic gold medalists for Austria (Q7893678) on enwiki but not itwiki, or inverse. It is a first attempt, and I am sure it can be done better. --Dipsacus fullonum (talk) 01:33, 7 February 2020 (UTC)
- Update: My answer may be wrong as you can in fact call the MediaWiki API of all Wikipedias as a service from the Wikidata Query Service. See mw:Wikidata Query Service/User Manual/MWAPI. I will have to study this more before trying to do this, but maybe others can help. --Dipsacus fullonum (talk) 21:45, 6 February 2020 (UTC)
SELECT ?item ?itemLabel (SUM(?in_en) AS ?Enlish_category) (SUM(?in_it) AS ?Italian_category)
WHERE
{
VALUES ?category { wd:Q7893678 }
[] schema:about ?category; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?cat_title_en.
[] schema:about ?category; schema:isPartOf <https://it.wikipedia.org/>; schema:name ?cat_title_it.
{
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "en.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_en.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_en { 1 }
}
UNION
{
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "it.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_it.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_it { 1 }
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?item ?itemLabel
HAVING (?Enlish_category = 0 || ?Italian_category = 0)
SELECT ?item ?itemLabel ?article_en ?category_en ?article_it ?category_it
{
{
SELECT ?item (COUNT(?in_en) AS ?category_en) (COUNT(?in_it) AS ?category_it)
WHERE
{
VALUES ?category { wd:Q7893678 }
{
[] schema:about ?category; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?cat_title_en.
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "en.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_en.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_en { true }
}
UNION
{
[] schema:about ?category; schema:isPartOf <https://it.wikipedia.org/>; schema:name ?cat_title_it.
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "it.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_it.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_it { true }
}
}
GROUP BY ?item
HAVING (?category_en = 0 || ?category_it = 0)
}
# ?article_en schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>.
# ?article_it schema:about ?item; schema:isPartOf <https://it.wikipedia.org/>.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
- Above is version 2. I moved the API access to a subquery and tried to get links to the articles. Currently the subquery gives 3 results, so I am surprised that getting ?article_en and ?article_it takes long time. With both outcommented as above, the whole query takes about 1 second. But if I try to get ?article_it, it takes 50 seconds. And If I try to get ?article_en, it times out. Can anyone explain, or even better fix, that? --Dipsacus fullonum (talk)
- I found the answer myself. It was (of course) that the engine tried to get all articles before running the subquery, and the solution is to change it to a named subquery which will run first. Version 3 is below. Execution time is about 1 second. --Dipsacus fullonum (talk) 08:49, 7 February 2020 (UTC)
- Above is version 2. I moved the API access to a subquery and tried to get links to the articles. Currently the subquery gives 3 results, so I am surprised that getting ?article_en and ?article_it takes long time. With both outcommented as above, the whole query takes about 1 second. But if I try to get ?article_it, it takes 50 seconds. And If I try to get ?article_en, it times out. Can anyone explain, or even better fix, that? --Dipsacus fullonum (talk)
SELECT ?item ?itemLabel ?article_en ?category_en ?article_it ?category_it
WITH
{
SELECT ?item (COUNT(?in_en) AS ?category_en) (COUNT(?in_it) AS ?category_it)
WHERE
{
VALUES ?category { wd:Q7893678 }
{
[] schema:about ?category; schema:isPartOf <https://en.wikipedia.org/>; schema:name ?cat_title_en.
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "en.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_en.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_en { true }
}
UNION
{
[] schema:about ?category; schema:isPartOf <https://it.wikipedia.org/>; schema:name ?cat_title_it.
SERVICE wikibase:mwapi
{
bd:serviceParam wikibase:api "Generator".
bd:serviceParam wikibase:endpoint "it.wikipedia.org".
bd:serviceParam mwapi:gcmtitle ?cat_title_it.
bd:serviceParam mwapi:generator "categorymembers".
bd:serviceParam mwapi:gcmprop "ids".
bd:serviceParam mwapi:gcmlimit "max".
?item wikibase:apiOutputItem mwapi:item.
}
VALUES ?in_it { true }
}
}
GROUP BY ?item
HAVING (?category_en = 0 || ?category_it = 0)
} AS %API_subquery
WHERE
{
INCLUDE %API_subquery.
?article_en schema:about ?item; schema:isPartOf <https://en.wikipedia.org/>.
?article_it schema:about ?item; schema:isPartOf <https://it.wikipedia.org/>.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
You are a legend! Thank you. Luckyz (talk) 11:40, 7 February 2020 (UTC)
Adjacent constituencies
Is it yet possible, for a given UK parliamentary constituency; to list (or map) all the adjacent constituencies, please. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 13:37, 6 February 2020 (UTC)
- They have geographic coordinates on Wikidata so you can see them as dots on a map (code below). But I cannot see a way to deduce from that which are adjacent to each other. --Dipsacus fullonum (talk) 14:01, 6 February 2020 (UTC)
#defaultView:Map
SELECT ?item ?itemLabel ?coord
WHERE
{
?item wdt:P31 wd:Q27971968.
?item wdt:P625 ?coord.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
- Given one constituency, you can also get a list of the, say, 10 nearest constituencies. But there is no telling which are adjacent. --Dipsacus fullonum (talk) 14:18, 6 February 2020 (UTC)
- A start has been made
- --Tagishsimon (talk) 21:49, 6 February 2020 (UTC)Try it!
SELECT DISTINCT ?item ?itemLabel ?other ?otherLabel WHERE[ { ?item wdt:P31 wd:Q27971968. ?item wdt:P47|^wdt:P47 ?other . ?other wdt:P31 wd:Q27971968. SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } }
- I corrected the output columns and made the query symnetric. --Dipsacus fullonum (talk) 22:12, 6 February 2020 (UTC)
- The information can be obtained using a federated query to the Ordnance Survey SPARQL endpoint, matching via the TOID (P3120) (or alternatively via the GSS code (2011) (P836)), eg Dumfries and Galloway (Q3336017) -> http://data.ordnancesurvey.co.uk/doc/7000000000036336 and then retrieving the values of the "touches" property there.
- Whether information obtained in this way is then (re)usable is left as a topic for discussion. But it's certainly a nice SPARQL demo exercise. Jheald (talk) 22:14, 6 February 2020 (UTC)
- Alternatively one could gather up the shapefiles (eg from [1]), and then see which nearby constituencies have boundaries that share common nodes. Jheald (talk) 22:19, 6 February 2020 (UTC)
- I corrected the output columns and made the query symnetric. --Dipsacus fullonum (talk) 22:12, 6 February 2020 (UTC)
@Pigsonthewing: Maybe a bit "off topic", but the set of constituency of the House of Commons (Q27971968) seems to be in need for some care before any query related to geography could be really meaningful:
- A lot of suspected duplicates (said to be the same as (P460)) exist
- located in the administrative territorial entity (P131) is missing for a lot of objects
- coordinate location (P625) is missing for most objects
- geoshape (P3896) is missing for all objects
- shares border with (P47) is missing for all but ten objects (and maybe incomplete also for those ten)
For details refer to this query:
SELECT ?item ?itemLabel ?located_inLabel ?coord ?geoshape (GROUP_CONCAT(?adjacent_to_label;SEPARATOR=', ') AS ?adjacents) ?said_to_be_same_as ?said_to_be_same_asLabel WHERE
{
?item wdt:P31 wd:Q27971968.
OPTIONAL { ?item wdt:P460 ?said_to_be_same_as }
OPTIONAL { ?item wdt:P131 ?located_in }
OPTIONAL { ?item wdt:P625 ?coord }
OPTIONAL { ?item wdt:P3896 ?geoshape }
OPTIONAL { ?item wdt:P47 ?adjacent_to . ?adjacent_to rdfs:label ?adjacent_to_label . FILTER(LANG(?adjacent_to_label)='en') }
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
GROUP BY ?item ?itemLabel ?located_inLabel ?coord ?geoshape ?said_to_be_same_as ?said_to_be_same_asLabel
ORDER BY ?itemLabel ?located_inLabel
--Larske (talk) 07:13, 7 February 2020 (UTC)
- Probably makes a bit more sense if dissolved, abolished or demolished date (P576) is considered. Ferinstance this gets 652 constituencies ... iirc there are 650, so 2 out:
- --Tagishsimon (talk) 09:46, 7 February 2020 (UTC)Try it!
SELECT ?item ?itemLabel ?located_inLabel ?coord ?geoshape (GROUP_CONCAT(?adjacent_to_label;SEPARATOR=', ') AS ?adjacents) ?said_to_be_same_as ?said_to_be_same_asLabel WHERE { ?item wdt:P31 wd:Q27971968. OPTIONAL { ?item wdt:P460 ?said_to_be_same_as } OPTIONAL { ?item wdt:P131 ?located_in } OPTIONAL { ?item wdt:P625 ?coord } OPTIONAL { ?item wdt:P3896 ?geoshape } OPTIONAL { ?item wdt:P47 ?adjacent_to . ?adjacent_to rdfs:label ?adjacent_to_label . FILTER(LANG(?adjacent_to_label)='en') } filter not exists {?item wdt:P576 [] . } SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } } GROUP BY ?item ?itemLabel ?located_inLabel ?coord ?geoshape ?said_to_be_same_as ?said_to_be_same_asLabel ORDER BY ?itemLabel ?located_inLabel
Thanks, all - both for the queries and the data cleanup suggestions. I'll work on the latter when I get time, and I bet Tagishsimon will. Also pinging User:Jheald and User:Andrew Gray. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 10:01, 7 February 2020 (UTC)
- I left the ball in Andrew's court - User talk:Andrew Gray#Constituency snafu? although I'm not sure at this distance what I was wittering on about. Looks complicated and HMMV. --Tagishsimon (talk) 10:10, 7 February 2020 (UTC)
- I've started documenting this at Wikidata:WikiProject British Politicians/Constituencies, and its talk page. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 10:15, 7 February 2020 (UTC)
- @Larske, Tagishsimon, Pigsonthewing: Yes, there's definitely a lot of work needing doing here, and I haven't had as much time to work on it as I'd hoped. The said to be the same as (P460) links are a bit of a problem - we don't have a clear property to describe what I want to say, which is "these are meaningfully different things with the same name, which are often treated as all being the same thing". We definitely want to keep them seperated, so please don't merge - at the moment there is one item for each distinct start/end, plus for substantial changes like a switch in the number of MPs.
- P131 and so on are all on my "do in the fullness of time" list, but there are some thorny problems yet to figure out - most of the UK admin areas have changed substantially over time so it's not always clear quite what parent areas we should use for P131. (eg all the modern constituencies are mapped into regions - do we want to keep this approach for historic constituencies that pre-date the official regions)
- Geoshapes are desirable but I have held off adding them due to concerns about copyright - my understanding was always that we need these to be CC-0 to go on Commons, though from a chat with @Jheald: recently it seems we might have found a workaround for this, in which case we can import.
- Finally, for "adjacent" - to be honest, this is something I'd never even thought of trying to model! Can't hurt to do it as suggested, though. Andrew Gray (talk) 10:46, 7 February 2020 (UTC)
- @Andrew Gray: Some comments:
- The use of said to be the same as (P460) is just an early signal that the item could/should be merged!
- If you have two (or more) different objects, Qn and Qm, that should be kept apart although they might have the same name and thus becomes mixed up, I think you should add the property different from (P1889) to both of them, i.e. Qn different from (P1889) Qm and Qm different from (P1889) Qn. That will tell others not to merge them. You may also want to change the instance of (P31), like from "X" to "former X", to make the differenct more obvious.
- If the located in the administrative territorial entity (P131) has varied over time, you can use several located in the administrative territorial entity (P131)-statements, each with a relevant start time (P580) and/or end time (P582) as qualifier. As at most one of the located in the administrative territorial entity (P131)-statements should lack a end time (P582) qualifier, the current value could be found by SPARQL filtering for that, i.e. ignore all statements with a end time (P582) qualifier.
- The same could be applied also for number of seats in assembly (P1410) and other properties that varies over time.
- --Larske (talk) 13:43, 7 February 2020 (UTC)
- "an early signal that the item could/should be merged" That's certainly not my understanding of its meaning, nor is it supported by the property's description (at least in English), or its creation proposal. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:17, 7 February 2020 (UTC)
- @Pigsonthewing: Sorry, I missed the disputed part that I now see is part of the English description. The property said to be the same as (P460) doesn't have a Swedish description, just the label. But even if it is not a "signal for merging", I still think it would be good if this property could be avoided in case the object should be considered as "not the same". That's why I suggested that different from (P1889) should be used instead.
- --Larske (talk) 01:11, 8 February 2020 (UTC)
- "an early signal that the item could/should be merged" That's certainly not my understanding of its meaning, nor is it supported by the property's description (at least in English), or its creation proposal. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:17, 7 February 2020 (UTC)
- @Larske: Thanks - I'll get them all switched to "different from". I'm reluctant to use different P31s, though, as really this should be clear from the dates. Andrew Gray (talk) 23:16, 7 February 2020 (UTC)
- @Larske: Running a switch to "different from" now (should be done and through to the query service in a couple of hours). Thanks for the advice :-) Andrew Gray (talk) 17:06, 8 February 2020 (UTC)
@Jheald, Larske, Tagishsimon, Pigsonthewing, Andrew Gray: Jheald suggested using a federated query to the Ordnance Survey SPARQL endpoint. It works and here my version of that: --Dipsacus fullonum (talk) 19:07, 7 February 2020 (UTC)
PREFIX ag: <http://data.ordnancesurvey.co.uk/ontology/admingeo/>
PREFIX sr: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
SELECT ?item ?itemLabel ?adjacentItem ?adjacentItemLabel
WITH
{
SELECT ?item ?adjacent
WHERE
{
?item wdt:P31 wd:Q27971968.
?item wdt:P3120 ?TOID.
BIND(SUBSTR(?TOID,12,5) AS ?unitID)
SERVICE <http://data.ordnancesurvey.co.uk/datasets/os-linked-data/apis/sparql>
{
[] ag:hasUnitID ?unitID; sr:touches ?adjacent.
}
}
} AS %federated_subquery
WHERE
{
INCLUDE %federated_subquery
BIND(SUBSTR(STR(?adjacent),37,16) AS ?adjacentTOID)
?adjacentItem wdt:P3120 ?adjacentTOID.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
- Thank you. On a small sample (my constituency and its neighbour) that seems to produce the right results. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 20:21, 7 February 2020 (UTC)
- @Dipsacus fullonum: this is amazing! I'm really impressed - well done. Andrew Gray (talk) 23:16, 7 February 2020 (UTC)
- @Dipsacus fullonum, Larske, Tagishsimon, Pigsonthewing, Andrew Gray: Just as a bit of fun, here's a plot of the adjacency network:
tinyurl.com/tgpsob9
- (Caveats: straight line may be the shortest distance between two points, but does not necessarily indicate where the constituencies touch; some constituencies may not have TOIDs; some constituencies may be questionably located; query may take some time to run (anything up to the full minute); terms and conditions apply; value of queries may go down as well as up...) Jheald (talk) 23:49, 7 February 2020 (UTC)
- St Ives; Arundel; and Worthing West, amongst others, may need another look. Jheald (talk) 23:57, 7 February 2020 (UTC)
- It is possible with SPARQL to the query Ordnance Survey for their data of constituencies (at least name, located in the administrative territorial entity (P131), coordinate location (P625), shares border with (P47)), compare with Wikidata data and write lists of deviations. --Dipsacus fullonum (talk) 01:48, 8 February 2020 (UTC)
- By using #defaultView:Graph in the federated query above you can see the adjacencies graphically also for those that lack coordinate location (P625) statement. It is a bit messy with all UK constiuencies, but if you limit the set of objects by filtering on located in the administrative territorial entity (P131) it looks quite nice:
- Here is an example with the set limited to objects with located in the administrative territorial entity (P131) equal to Wales (Q25).
- I don't know if it is possible to give a "hint" to the graph rendering on which node to place top(north)most and which node to place right(east)most. Now I guess it is just luck if the graph places the nodes in the correct "geographical order" from a north-south and west-east point of view.
- --Larske (talk) 11:48, 8 February 2020 (UTC)
- By using #defaultView:Graph in the federated query above you can see the adjacencies graphically also for those that lack coordinate location (P625) statement. It is a bit messy with all UK constiuencies, but if you limit the set of objects by filtering on located in the administrative territorial entity (P131) it looks quite nice:
- It is possible with SPARQL to the query Ordnance Survey for their data of constituencies (at least name, located in the administrative territorial entity (P131), coordinate location (P625), shares border with (P47)), compare with Wikidata data and write lists of deviations. --Dipsacus fullonum (talk) 01:48, 8 February 2020 (UTC)
- St Ives; Arundel; and Worthing West, amongst others, may need another look. Jheald (talk) 23:57, 7 February 2020 (UTC)
- @Dipsacus fullonum, Larske, Tagishsimon, Pigsonthewing, Andrew Gray: Just as a bit of fun, here's a plot of the adjacency network:
- I had a look at St Ives & iirc Arundel; reluctant to say that I didn't see any issues, though not much wanting to get more drawn into this, nor to be told exactly how wrong I was. Still. Have we found anything for certain wrong about the results of Dipsacus fullonum's frankly splendid report? --Tagishsimon (talk) 12:22, 8 February 2020 (UTC)
- I've fixed the coordinates for those three constituencies, so their position in the network map should be fixed now, just as soon as WDQS updates. Jheald (talk) 16:36, 8 February 2020 (UTC)
- Also East Kilbride, Strathaven and Lesmahagow (Q3337694) Jheald (talk) 16:57, 8 February 2020 (UTC)
- Oh. Coordinates. Not neighbours. --Tagishsimon (talk) 18:34, 8 February 2020 (UTC)
- I had a look at St Ives & iirc Arundel; reluctant to say that I didn't see any issues, though not much wanting to get more drawn into this, nor to be told exactly how wrong I was. Still. Have we found anything for certain wrong about the results of Dipsacus fullonum's frankly splendid report? --Tagishsimon (talk) 12:22, 8 February 2020 (UTC)
Entities Appointed By Donald Trump
Mick Mulvaney (Q1235731) has in the position held two times a property appointed by (P748) with a value of Donald Trump (Q22686). I've tried to create the query using the property p748 appointed by, however the list wikidata generates actually doesn't inclue Mick Mulvaney in it. I would appreciate help on this query. Thank you.
- Try this query. Mick Mulvaney (Q1235731) appears twice in the result.
- Try it!
SELECT ?person ?personLabel ?position_heldLabel { ?person p:P39 ?p39stm . ?p39stm ps:P39 ?position_held . ?p39stm pq:P748 wd:Q22686 . SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } ORDER BY ?personLabel ?position_heldLabel
- --Larske (talk) 17:18, 8 February 2020 (UTC)
Labels
Hi! I wonder how to query the existence (or not) of labels. For example: every person with Romanian citizenship with a label in English language but not in Portuguese. Thanks in advance. strakhov (talk) 14:36, 9 February 2020 (UTC)
- @Strakhov: Try this query:
- Try it!
SELECT ?person ?enlabel { ?person wdt:P31 wd:Q5 . ?person wdt:P27 wd:Q218 . ?person rdfs:label ?enlabel . FILTER(LANG(?enlabel)='en') FILTER NOT EXISTS { ?person rdfs:label ?ptlabel . FILTER(LANG(?ptlabel)='pt') } } ORDER BY ?enlabel
- --Larske (talk) 15:19, 9 February 2020 (UTC)
- Thanks! :) strakhov (talk) 15:52, 9 February 2020 (UTC)
Page Views
Hi, I wonder if it's possible to query the number of page views. For instance : actors from the English Wikipedia that are not in the French Wikipedia, ordered by number of page views. Thanks in advance, --Grouah (talk) 13:26, 11 February 2020 (UTC)
- No, WDQS doesn't hold this information (yet). --Matěj Suchánek (talk) 14:15, 11 February 2020 (UTC)
- I think you could get a list of articles in English Wikipedia about actors with no page in French Wikipedia using WDQS, copy the list to PagePile, and finally see pageviews for these pages using the Massviews tool. --Dipsacus fullonum (talk) 14:24, 11 February 2020 (UTC)
- Thanks ! That's actually a good idea :)--Grouah (talk) 22:48, 11 February 2020 (UTC)
- I think you could get a list of articles in English Wikipedia about actors with no page in French Wikipedia using WDQS, copy the list to PagePile, and finally see pageviews for these pages using the Massviews tool. --Dipsacus fullonum (talk) 14:24, 11 February 2020 (UTC)
Help with query optimization/rewriting
I am trying to run the following query for "active" instances or subclasses of drinking fountains, fountains, and water wells. While the query on the total globe works relatively well, as soon as I add a bounding box ... the query times out.
BBbox which causes timeout :
SERVICE wikibase:box { # this service allows points within a box to be queried (https://en.wikibooks.org/wiki/SPARQL/SERVICE_-_around_and_box) ?place wdt:P625 ?location . bd:serviceParam wikibase:cornerWest "Point(-10 35)"^^geo:wktLiteral. bd:serviceParam wikibase:cornerEast "Point(-5 40)"^^geo:wktLiteral. }
SPARQL Query : SELECT DISTINCT ?place ?placeLabel ?image ?location ?ispotable WHERE
{ { ?place wdt:P31/wdt:P279* wd:Q1630622. ?place wdt:P625 ?location; MINUS { ?place wdt:P576 [] } MINUS { ?place wdt:P582 [] }} UNION { ?place wdt:P31/wdt:P279* wd:Q483453. ?place wdt:P625 ?location ; MINUS { ?place wdt:P576 [] } MINUS { ?place wdt:P582 [] }} UNION { ?place wdt:P31/wdt:P279* wd:Q43483. ?place wdt:P625 ?location; MINUS { ?place wdt:P576 [] } MINUS { ?place wdt:P582 [] } } # boolean indicator of potability BIND (EXISTS { ?place wdt:P31/wdt:P279* wd:Q1630622 } as ?ispotable) # the wikibase:label service allows the label to be returned easily. The list of languages provided are fallbacks: if no English label is available, use German etc. SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,it,es". } # It is important to place the OPTIONAL after the filters, otherwise the query times out OPTIONAL{ ?place wdt:P18 ?image. } OPTIONAL { ?place wdt:P576 ?detruction_date}. }
Thanks for your advice, Stuart --S2rapoport (talk) 10:45, 12 February 2020 (UTC)
- First point of optimization is to note that drinking fountain (Q1630622) is a subclass of fountain (Q483453), so there is no point in searching separate for Q1630622 because the search for Q483453 will also find them. Then for the box search to work, you shouldn't bind the ?location variable before the service is used. Below is my rewrite: --Dipsacus fullonum (talk) 19:37, 12 February 2020 (UTC)
- PS. I forgot to mention that I removed
OPTIONAL { ?place wdt:P576 ?detruction_date. }
because it doesn't give any results anyway due to the MINUS clauses. --Dipsacus fullonum (talk) 19:50, 12 February 2020 (UTC)
This seems to work well.. Thank you for your help .. I should have asked here earlier. .. This is going to be a real help !--S2rapoport (talk) 20:04, 12 February 2020 (UTC)
SELECT DISTINCT ?place ?placeLabel ?image ?location ?ispotable
WHERE
{
{
?place wdt:P31/wdt:P279* wd:Q483453.
MINUS { ?place wdt:P576 [] }
MINUS { ?place wdt:P582 [] }
}
UNION
{
?place wdt:P31/wdt:P279* wd:Q43483.
MINUS { ?place wdt:P576 [] }
MINUS { ?place wdt:P582 [] }
}
BIND (EXISTS { ?place wdt:P31/wdt:P279* wd:Q1630622 } as ?ispotable) # boolean indicator of potability
OPTIONAL { ?place wdt:P18 ?image. }
SERVICE wikibase:box
{
?place wdt:P625 ?location.
bd:serviceParam wikibase:cornerWest "Point(-10 35)"^^geo:wktLiteral.
bd:serviceParam wikibase:cornerEast "Point(-5 40)"^^geo:wktLiteral.
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,it,es". }
}
How to get a Q id in the result?
SELECT ?item ?itemId
WHERE
{
?item wdt:P31 wd:Q5 .
}
LIMIT 10
instead of ?itemID I would like to see some "Q1234". ?item itself is a link. – The preceding unsigned comment was added by 194.183.183.73 (talk • contribs).
- Try -- Jheald (talk) 11:50, 12 February 2020 (UTC)Try it!
SELECT ?qnum ?item ?itemId WHERE { ?item wdt:P31 wd:Q5 . BIND(STRAFTER(str($item), 'entity/') AS ?qnum) . } LIMIT 10
Find all labels and aliases for two languages for all instances of a class
Hi, I am new to SPARQL and WikiData. Is it possible to find all instances of a class that have (language#1_label AND language#2_label) plus, if available, language#1_aliases OR language#2_aliases; listing them in four different columns?
Exempli gratia: class "written work" (Q47461344), language#1 = English, language#2 = Italian
book | bookLabel (English) | bookAltLabel (English) | bookLabel (Italian) | bookAltLabel (Italian) |
---|---|---|---|---|
wd:Q62879 | Christmas Carol | [A Christmas Carol in Prose, ... ] | Canto di Natale | [Il canto di Natale
, Cantico di Natale, ...] |
Thanks in advance DellaCG (talk) 20:44, 12 February 2020 (UTC)
- @DellaCG: This sort of thing; it's only looking at the class, not the subclasses - too many items.
- --Tagishsimon (talk) 23:59, 12 February 2020 (UTC)Try it!
SELECT DISTINCT ?item ?en_label ?it_label (group_concat(?en_a; separator="; ") as ?en_alias) (group_concat(?it_a; separator="; ") as ?it_alias) WHERE { ?item wdt:P31 wd:Q47461344 . ?item rdfs:label ?en_label . filter (lang(?en_label)="en") ?item rdfs:label ?it_label . filter (lang(?it_label)="it") optional { ?item skos:altLabel ?en_a . filter (lang(?en_a)="en") } optional { ?item skos:altLabel ?it_a . filter (lang(?it_a)="it") } } group by ?item ?en_label ?it_label
- @Tagishsimon: Many thanks DellaCG (talk) 06:52, 13 February 2020 (UTC)
How can I write a query to create a time series of the number of reported cases of the COVID-19 pandemic (Q81068910)?
I don't know how to return the number of cases (P1603) "point in time" results as a table, or preferably a chart with dates along the x-axis. Thanks. Tennii (talk) 06:42, 13 February 2020 (UTC)
- @Tennii: Here is a line chart example:
- Try it!
#defaultView:LineChart SELECT ?time ?number_of_infected { wd:Q81068910 p:P1603 ?p1603stm . ?p1603stm ps:P1603 ?number_of_infected . ?p1603stm pq:P585 ?time . }
- If you prefer a bar chart, change LineChart to BarChart on the first line of the query.
- --Larske (talk) 07:10, 13 February 2020 (UTC)
Problems with FILTER unequal
With the following query, i get 309 articles from the magazine "Die Gartenlaube" which have follows (P155).
SELECT ?Die_Gartenlaube ?Die_GartenlaubeLabel ?follows ?followsLabel ?teilvon ?teilvonFollows WHERE {
?Die_Gartenlaube wdt:P1433 wd:Q655617;
wdt:P155 ?follows.
OPTIONAL { ?Die_Gartenlaube wdt:P179 ?teilvon. }
OPTIONAL { ?follows wdt:P179 ?teilvonFollows. }
#FILTER(?teilvon = ?teilvonFollows)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
But only 29 of them are correct, because they share the same articles series.
SELECT ?Die_Gartenlaube ?Die_GartenlaubeLabel ?follows ?followsLabel ?teilvon ?teilvonFollows WHERE {
?Die_Gartenlaube wdt:P1433 wd:Q655617;
wdt:P155 ?follows.
OPTIONAL { ?Die_Gartenlaube wdt:P179 ?teilvon. }
OPTIONAL { ?follows wdt:P179 ?teilvonFollows. }
FILTER(?teilvon = ?teilvonFollows)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
If i want to receive the 280 articles where ?teilvon is not equal with ?teilvonFollow i change the filter operator as follows:
FILTER(?teilvon != ?teilvonFollows)
but then i receive no results:
SELECT ?Die_Gartenlaube ?Die_GartenlaubeLabel ?follows ?followsLabel ?teilvon ?teilvonFollows WHERE {
?Die_Gartenlaube wdt:P1433 wd:Q655617;
wdt:P155 ?follows.
OPTIONAL { ?Die_Gartenlaube wdt:P179 ?teilvon. }
OPTIONAL { ?follows wdt:P179 ?teilvonFollows. }
FILTER(?teilvon != ?teilvonFollows)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
What i am doing wrong here? Is there a problem filtering on optional values? --Mfchris84 (talk) 13:05, 13 February 2020 (UTC)
- Not sure what exactly you are trying, but to get empty (unbound) values as well, you need to use
FILTER( ?teilvon != ?teilvonFollows || !BOUND(?teilvon) || !BOUND(?teilvonFollows) )
- Another way could be to use something like
FILTER NOT EXISTS { ?Die_Gartenlaube wdt:P179 ?teilvon. ?follows wdt:P179 ?teilvon. }
- --- Jura 13:25, 13 February 2020 (UTC)
- Thanks @Jura1:, :
FILTER( ?teilvon != ?teilvonFollows || !BOUND(?teilvon) || !BOUND(?teilvonFollows) )
solved my problem. i want to receive the articles which are linked with the follows-property and which are not part of the same article series or even not part of any series (so they are more like a cited work). to filter all those articles which are part of the series was not a problem, but all the empty unbound values were the problem. - --Mfchris84 (talk) 14:15, 13 February 2020 (UTC)Try it!
SELECT ?Die_Gartenlaube ?Die_GartenlaubeLabel ?follows ?followsLabel ?teilvon ?teilvonFollows WHERE { ?Die_Gartenlaube wdt:P1433 wd:Q655617; wdt:P155 ?follows. OPTIONAL { ?Die_Gartenlaube wdt:P179 ?teilvon. } OPTIONAL { ?follows wdt:P179 ?teilvonFollows. } FILTER(?teilvon != ?teilvonFollows|| !BOUND(?teilvon) || !BOUND(?teilvonFollows)) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
- Thanks @Jura1:, :
- You are welcome. BTW, I fixed the "FILTER NOT EXISTS" one above. --- Jura 14:18, 13 February 2020 (UTC)
Concatenate Longitude and Latitude
I'm trying to concatenate longitude and latitude to recreate an URL but for some unknown reason it doesn't work:
SELECT ?item ?commune (CONCAT("[https://www.geoportail.gouv.fr/carte?c=", ?longitude, ",", ?latitude, "&z=15&l0=GEOGRAPHICALGRIDSYSTEMS.MAPS::GEOPORTAIL:OGC:WMTS(1)&l1=ADMINISTRATIVEUNITS.BOUNDARIES::GEOPORTAIL:OGC:WMTS(1)&permalink=yes Géoportail]") AS ?geoportail) (CONCAT("[https://commons.wikimedia.org/wiki/Category:", ?commons, " ", ?commons, "]") AS ?lien) WHERE {
?item wdt:P17 wd:Q142;
(wdt:P31/(wdt:P279*)) wd:Q16970;
wdt:P131 ?commune.
?commune p:P625 [ psv:P625 [ wikibase:geoLatitude ?latitude; wikibase:geoLongitude ?longitude ] ];
wdt:P373 ?commons.
MINUS { ?item wdt:P625 []. }
}
LIMIT 100
Could you help me please? Thanks. Ayack (talk) 13:06, 13 February 2020 (UTC)
- str() --- Jura 13:21, 13 February 2020 (UTC)
- Thanks Jura. Ayack (talk) 13:45, 13 February 2020 (UTC)
Show labels in two different languages
Hi all! Given this simple query
SELECT ?item ?itemLabel (URI(CONCAT("http://www.treccani.it/enciclopedia/",?treccani)) AS ?treccaniUrl)
WHERE {
?item wdt:P3365 ?treccani .
SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" . }
}
ORDER BY DESC(?treccani)
could you explain me how can I visualise at the same time the Italian label in one column and the English label in another column? Thank you, --Epìdosis 17:47, 13 February 2020 (UTC)
- Properties used: Treccani ID (P3365)
SELECT ?item ?itemLabel ?itemDescription ?itemAltLabel ?it ?dit ?en ?den
WHERE
{
?item wdt:P3365 [] .
OPTIONAL { ?item rdfs:label ?it . FILTER( lang(?it) = "it") }
OPTIONAL { ?item rdfs:label ?en . FILTER( lang(?en) = "en") }
OPTIONAL { ?item schema:description ?dit . FILTER( lang(?dit) = "it") }
OPTIONAL { ?item schema:description ?den . FILTER( lang(?den) = "en") }
SERVICE wikibase:label { bd:serviceParam wikibase:language "zh,ru,ja" . }
}
LIMIT 10
Above some samples with labels and descriptions. --- Jura 18:06, 13 February 2020 (UTC)
- (same as Jura, conflict) By not using the label service, for example : Try it!
SELECT ?item ?itemLabelIt ?itemLabelEn (URI(CONCAT("http://www.treccani.it/enciclopedia/",?treccani)) AS ?treccaniUrl) WHERE { ?item wdt:P3365 ?treccani . optional { ?item rdfs:label ?itemLabelIt filter (lang(?itemLabelIt) = "it") } optional { ?item rdfs:label ?itemLabelEn filter (lang(?itemLabelEn) = "en") }
- Alternative 1 : two calls to the label service : Does not seem to perform better however. author TomT0m / talk page 18:23, 13 February 2020 (UTC)Try it!
SELECT ?item ?itemLabelIt ?itemLabelEn (URI(CONCAT("http://www.treccani.it/enciclopedia/",?treccani)) AS ?treccaniUrl) WHERE { ?item wdt:P3365 ?treccani . SERVICE wikibase:label { bd:serviceParam wikibase:language "en". ?item rdfs:label ?itemLabelEn . } SERVICE wikibase:label { bd:serviceParam wikibase:language "it". ?item rdfs:label ?itemLabelIt . } } ORDER BY DESC(?treccani) limit 10
- Nice idea to call the service twice! --- Jura 19:10, 13 February 2020 (UTC)
Optimization: query to get all french communes with some datas
Hi,
I'm trying to get all french communes (Q484170) with some datas: latitude / longitude / image / postal code / website / wikipedia article / wikivoyage article.
I made this query:
- Try it!
PREFIX schema: <http://schema.org/> SELECT ?commune ?communeLabel ?wikivoyageTitel ?wikipediaTitel ?communeImage ?communePostalCode ?communeWebsite ?latitude ?longitude WITH { SELECT ?commune (SAMPLE(?lat) AS ?latitude) (SAMPLE(?lng) AS ?longitude) ?communePostalCode WHERE { ?commune wdt:P31 wd:Q484170; p:P625 ?coordinate. ?coordinate psv:P625 ?coordinate_node. ?coordinate_node wikibase:geoLatitude ?lat. ?coordinate_node wikibase:geoLongitude ?lng. ?commune wdt:P281 ?communePostalCode FILTER (?communePostalCode > "0" && ?communePostalCode < "10000") } GROUP BY ?commune ?communePostalCode } AS %results WHERE { INCLUDE %results. OPTIONAL { ?commune wdt:P18 ?communeImage }. OPTIONAL { ?commune wdt:P856 ?communeWebsite }. OPTIONAL { ?wikivoyageArticle schema:about ?commune. ?wikivoyageArticle schema:isPartOf <https://en.wikivoyage.org/>. ?wikivoyageArticle schema:name ?wikivoyageTitel. }. OPTIONAL { ?wikipediaArticle schema:about ?commune. ?wikipediaArticle schema:isPartOf <https://en.wikipedia.org/>. ?wikipediaArticle schema:name ?wikipediaTitel. }. SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } } GROUP BY ?commune ?communeLabel ?wikivoyageTitel ?wikipediaTitel ?communeImage ?communePostalCode ?communeWebsite ?latitude ?longitude ORDER BY ?communePostalCode
But I'm getting a timeout as soon as I request labels (?communeLabel ?wikivoyageArticleLabel ?wikipediaArticleLabel). Even after following query optimizations I can find a way to make it work.
Can you help me to optimize this request ?
Thanks.
- You cannot use the label service for Wikipedia and Wikivoyage articles. These are not items and don't have labels. You can however URI decode the article links to make them more readable for humans. It can be done with:
BIND(wikibase:decodeUri(SUBSTR(STR(?wikivoyageArticle), 32)) AS ?wikivoyageTitel) BIND(wikibase:decodeUri(SUBSTR(STR(?wikipediaArticle), 31)) AS ?wikipediaTitel)
- I don't understand why you have
hint:Query hint:optimizer "None" .
In my tests it only slows down the query. Another issue is that you get too many results because some of coordinate values comes twice. That isn't your fault (see phab:T245135), but until it is fixed you should restrict your results in the subquery with "group by" and aggregation functions to only one result per commune and only add the optional values outside of the subquery. --Dipsacus fullonum (talk) 11:34, 13 February 2020 (UTC)
- gives the article title --- Jura 12:12, 13 February 2020 (UTC)
?wikipediaArticle schema:isPartOf <https://en.wikipedia.org/>; schema:name ?enwiki
- Thanks, I edited my query, following your advices (as I understand them). This is better but I'm still getting timeout --- Aximem 14:03, 13 February 2020 (UTC)
- @Aximem: I have a few more ideas: 1) Use Jura's good suggestion of using schema:name to get the titles:
OPTIONAL { ?wikivoyageArticle schema:about ?commune. ?wikivoyageArticle schema:isPartOf <https://en.wikivoyage.org/>. ?wikivoyageArticle schema:name ?wikivoyageTitel. }
- and likewise for Wikipedia articles. 2) Use sample() instead of min() as aggregation function:
SELECT ?commune (SAMPLE(?lat) AS ?latitude) (SAMPLE(?lng) AS ?longitude)
- Then the engine can just use any value without doing any comparison. That being said I cannot either get the query to run without timeout when including ?communeLabel. I tried to get the label without using the label service, but it doesn't help. I suggest that you either obmit the label and instead look at the Wikipedia titel (the difference is that the latter includes disambiguation), or alternatively limit the number of results, e.g. by only looking at only one region/arrondissement/departement per query. The aggregation and sorting by postal code also takes time. It reduces the number of results from 39551 to 39129, but sorting that many values isn't negligible and could be obmitted if it isn't important. --Dipsacus fullonum (talk) 21:24, 13 February 2020 (UTC)
- @Aximem: I have a few more ideas: 1) Use Jura's good suggestion of using schema:name to get the titles:
- Again Thanks for your advices, indeed I can't make it work when using labels so, as you suggested, I decided to filter request to get less results:
FILTER (?communePostalCode > "0" && ?communePostalCode < "10000")
- And I will use 10 requests (10001 - 20000 ... until 99999 for France) to get all datas. Thanks
- --- Aximem 08:27, 14 February 2020 (UTC)
- @Aximem: That is good idea using the postal codes to get smaller chunks of data. Be aware that not all items for communes have a value for P281, so after the 10 requests going from postal codes 0 to 99999, you will probably want to add a request to find these. For that last request replace
?commune wdt:P281 ?communePostalCode. FILTER (?communePostalCode > "0" && ?communePostalCode < "10000")
withFILTER NOT EXISTS { ?commune wdt:P281 ?communePostalCode. }
--Dipsacus fullonum (talk) 08:04, 14 February 2020 (UTC)- @Dipsacus fullonum: You are right, thanks for the advice. --- Aximem 09:24, 14 February 2020 (UTC)
- @Aximem: That is good idea using the postal codes to get smaller chunks of data. Be aware that not all items for communes have a value for P281, so after the 10 requests going from postal codes 0 to 99999, you will probably want to add a request to find these. For that last request replace
Counts by decade
- Items used: Alain (Q2829505)
- Properties used: given name (P735) , date of birth (P569)
#defaultView:LineChart
SELECT ?decade (COUNT(DISTINCT ?item) as ?ct)
{
hint:Query hint:optimizer "None".
?item wdt:P735 wd:Q2829505 ; wdt:P569 ?b .
?item p:P569/psv:P569 [ wikibase:timeValue ?b ; wikibase:timePrecision ?precision ]
FILTER(?precision > 7 )
BIND( (FLOOR(YEAR(?b)/10)*10) as ?decade)
}
GROUP BY ?decade
ORDER BY ?decade
Any idea how I could get that to not start at "decade" 0? I don't mind if it was a bar chart or an area chart.
Ideally, it would look more like this chart (or the ones on the right side), even if no data is available for some decades.
BTW, this is now one of the queries for #Queries_about_"John". --- Jura 10:24, 14 February 2020 (UTC)
- @Jura1: If you convert ?decade to a string, it will only show points for existing values. The disadvantage is that there will be holes, so the x axis isn't linear:
#defaultView:LineChart
SELECT (STR(?decade_number) AS ?decade) (COUNT(DISTINCT ?item) as ?ct)
{
hint:Query hint:optimizer "None".
?item wdt:P735 wd:Q2829505 ; wdt:P569 ?b .
?item p:P569/psv:P569 [ wikibase:timeValue ?b ; wikibase:timePrecision ?precision ]
FILTER(?precision > 7 )
BIND( (FLOOR(YEAR(?b)/10)*10) as ?decade_number)
}
GROUP BY ?decade_number
ORDER BY ?decade_number
--Dipsacus fullonum (talk) 11:07, 14 February 2020 (UTC)
- Somehow ordering is by count rather than year. It worked out better in my second query above, but that had data for every year.
- Ideally, it would be by decade without holes. --- Jura 12:25, 14 February 2020 (UTC)
- @Jura1: Then just fill the holes with 0 values:
#defaultView:LineChart
SELECT (STR(?decade_number) AS ?decade) (MAX(?count) AS ?ct)
WITH
{
SELECT ?decade_number (COUNT(DISTINCT ?item) as ?count)
WHERE
{
hint:Query hint:optimizer "None".
?item wdt:P735 wd:Q2829505 ; wdt:P569 ?b .
?item p:P569/psv:P569 [ wikibase:timeValue ?b ; wikibase:timePrecision ?precision ]
FILTER(?precision > 7 )
BIND( (FLOOR(YEAR(?b)/10)*10) as ?decade_number)
}
GROUP BY ?decade_number
ORDER BY ?decade_number
} AS %include
WHERE
{
{
INCLUDE %include
}
UNION
{
{
SELECT (MIN(?decade_number) AS ?first_decade) (MAX(?decade_number) AS ?last_decade)
WHERE
{
INCLUDE %include
}
}
?decade_item wdt:P31 wd:Q39911.
?decade_item wdt:P585 ?decade_time.
BIND( (FLOOR(YEAR(?decade_time)/10)*10) as ?decade_number)
FILTER (?decade_number > ?first_decade && ?decade_number < ?last_decade)
VALUES ?count { 0 }
}
}
GROUP BY ?decade_number
ORDER BY ?decade_number
What depicted entities wear in paintings
Hello, I'm trying to retrieve the items worn by depicted entities in paintings.
Example:
https://www.wikidata.org/wiki/Q603703
In this link there is the painting of venus and adonis, and the depict tag (wdt:P180) links the painting with all the depicted entities that appear in it. Some of them have additional properties, like for example Adonis that has written belo "wears" (wdt:wdt:P3828) Sandals. I tried to retrieve it with this query:
SELECT ?item WHERE {
wd:Q603703 wdt:P180 ?depicted . ?depicted wdt:P3828 ?item .
}
But this looks inside the page of every depicted items and looks for element that they were on their page, so the result is just "Woman clothing" (woman is also depicted and in the wikidata page of woman there is the tag wears and the object is woman clothing
I'm trying to find a way to just get the information that is written on the wikidata page of the paintings and not the ones on the pages of the depicted elements. Hope I have been clear enough to get some help,
Thanks in advance
- Like this. It's all to do with the data model and the routes by which you can get to statement qualifiers. Much to explain, which we can do if you wish.
- --Tagishsimon (talk) 12:13, 14 February 2020 (UTC)Try it!
SELECT ?depicted ?depictedLabel ?item ?itemLabel WHERE { wd:Q603703 p:P180 ?P180node . ?P180node ps:P180 ?depicted . ?P180node pq:P3828 ?item . SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
item with specific externe ID
Hello,
Could you help me to write a query to find a item with a specific ID extern?
Example : HDS ID (P902) in Johann Jakob Meyer (Q55362159) have the value 029396 but I can find it with this query :
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P902 ?DHS.
FILTER (?DHS = "029396").
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Thanks in advance! --2le2im-bdc (talk) 06:18, 17 February 2020 (UTC)
- fixed. --- Jura 06:24, 17 February 2020 (UTC)
- Or use the triple
?item wdt:P902 "029396".
, then the filter isn't necessary. --Dipsacus fullonum (talk) 06:35, 17 February 2020 (UTC)- Great! Thanks a lot @Jura1: and @Dipsacus fullonum:--2le2im-bdc (talk) 07:18, 17 February 2020 (UTC)
- Or use the triple
how to select P properties beneath a Q item
The purpose is to display Spanish politicians and monarchs born in Madrid with names, dates of birth, dates of death, positions, the start time and the end time of those positions. The incomplete query starts as follows:
SELECT ?itemLabel ?dateofbirth ?dateofdeath ?positionLabel ?startime ?endtime WHERE {
?item wdt:P31 wd:Q5; # human
wdt:P27 wd:Q29; # citizenship - Spain
wdt:P19 wd:Q2807 # born in Madrid
{ ?item wdt:P106 wd:Q82955. } # occupation - politicians
UNION
{ ?item wdt:P106 wd:Q372436. } # or occupation - statesperson
UNION
{ ?item wdt:P106 wd:Q116. } # or occupation - monarch
UNION
{ ?item wdt:P106 wd:Q2304859. } # or occupation - sovereign
OPTIONAL { ?item wdt:P570 ?dateofdeath . } # date of death
OPTIONAL { ?item wdt:P569 ?dateofbirth . } # date pf birth
OPTIONAL { ?item wdt:P39 ?position .} # position held
OPTIONAL {}
OPTIONAL {}
SERVICE wikibase:label { bd:serviceParam wikibase:language "es". }
I don't know how to deal with the start time and end time. For example, the King of Spain, Felipe VI, took the position on 7 June 2014. How to put "7 June 2014" on the results table? Any help would be appreciated.
Aldo He
- --Dipsacus fullonum (talk) 16:13, 17 February 2020 (UTC)Try it!
SELECT ?itemLabel ?dateofbirth ?dateofdeath ?positionLabel ?starttime ?endtime WHERE { ?item wdt:P31 wd:Q5; # human wdt:P27 wd:Q29; # citizenship - Spain wdt:P19 wd:Q2807 # born in Madrid VALUES ?occupation { wd:Q82955 wd:Q372436 wd:Q116 wd:Q2304859 } # politician, statesperson, monarch, sovereign ?item wdt:P106 ?occupation. OPTIONAL { ?item wdt:P570 ?dateofdeath . } # date of death OPTIONAL { ?item wdt:P569 ?dateofbirth . } # date pf birth OPTIONAL { ?item p:P39 ?p . ?p ps:P39 ?position. OPTIONAL { ?p pq:P580 ?starttime. } OPTIONAL { ?p pq:P582 ?endtime. } } # position held SERVICE wikibase:label { bd:serviceParam wikibase:language "es". } }
Listings of women with nationality
Hi. I currently have a query that only adds the person's name.
I would also like you to add a photograph, the year of birth, the year of death, showing the country where you were born.
- Try it!
#Mujeres mexicanas SELECT ?item ?itemLabel WHERE { ?item wdt:P21 wd:Q6581072 . ?item wdt:P27 wd:Q96 . SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". } }
Greetings. --Hispano76 (talk) 23:31, 18 February 2020 (UTC)
- @Hispano76: --Dipsacus fullonum (talk)Try it!
#Mujeres mexicanas SELECT ?item ?itemLabel ?image (YEAR(?date_of_birth) AS ?year_of_birth) (YEAR(?date_of_death) AS ?year_of_death) ?country_of_birthLabel WHERE { ?item wdt:P21 wd:Q6581072 . ?item wdt:P27 wd:Q96 . OPTIONAL { ?item wdt:P18 ?image. } OPTIONAL { ?item wdt:P569 ?date_of_birth. } OPTIONAL { ?item wdt:P570 ?date_of_death. } OPTIONAL { ?item wdt:P19/wdt:P17 ?country_of_birth. } SERVICE wikibase:label { bd:serviceParam wikibase:language "es,en". } }
List of all partnerships in the district of Offenbach
I‘d like to get a query that return a list of all twinning partnerships of all levels of contained administrative territories of:
District of Offenbach Q7912
contains administrative territorial entity P150
twinned administrative body P190
The problem that I see, is that the P150 can contain further P150 and that P190 can appear in all of these levels...
—Dichter (talk) 10:35, 19 February 2020 (UTC)
- @Dichter: You use property paths when something can happen at multiple levels in a hierarcy. In this case the * after wdt:P150 menaing 0 or more recursions of the property:
SELECT ?item ?itemLabel ?twin ?twinLabel
WHERE
{
wd:Q7912 wdt:P150* ?item.
?item wdt:P190 ?twin.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
From Wikipedia URL to Wikidata Item
Hi, I have a hundred Wikipedia URL in a google sheet and I'd like to have a fast way to get their équivalent Wikidata Item Q number (I of course know how to du this for an individual article). My URL come from the french Wikipedia (fr.wikipedia.org). Could someone write me a SPARQL request in which I could dump my URL and get the Q items as a result in a column ?--René La contemporaine (talk) 10:46, 19 February 2020 (UTC)
- You could dump the page titles them on https://tools.wmflabs.org/wikidata-todo/quick_statements.php followed by a tab, P1, Q0 and fill in "frwiki" in "First column are articles from ". As P1 doesn't exist it will just convert them. --- Jura 11:03, 19 February 2020 (UTC)
- Jura So I tried to follow your procedure. 1)I filled "frwiki" in "First column are articles from " 2) I copy/pasted from a sheet an example with frwikiURL[TAB]P1[TAB]Q0 but the only result I got is MISSING P1 Q0. What did I do wrong ?--René La contemporaine (talk) 11:40, 19 February 2020 (UTC)
Try:
Comte Adrien de Germiny P1 Q0 Comte de Chambord (rose) P1 Q0 Comtesse de Cassagne P1 Q0 Comtesse de Leusse (rose) P1 Q0 Condesa de Mayalde P1 Q0 Constance Spry (rose) P1 Q0 Coquette des Blanches P1 Q0 Crimson Glory (rose) P1 Q0 Crimson Rambler P1 Q0 Cuisse de Nymphe P1 Q0 Cuisse de Nymphe émue P1 Q0
some from fr:Catégorie:Cultivar_de_rosier.
It gets you:
Q60965024 P1 Q0 Q60964318 P1 Q0 Q16473337 P1 Q0 Q64030567 P1 Q0 Q50952180 P1 Q0 Q16626285 P1 Q0 Q64030568 P1 Q0 Q63067618 P1 Q0 Q60964381 P1 Q0 MISSING P1 Q0 Q3007030 P1 Q0
The missing one doesn't have an item associated with it. See https://fr.wikipedia.org/w/index.php?title=Cuisse_de_Nymphe&action=info --- Jura 11:46, 19 February 2020 (UTC)
- Jura Now I understand ! I don't have the article's names, I have the URLs. It works when I enter the names. I can easily transform URL in names and follow the process except for certain URL where diacritics mess up the names (https://fr.wikipedia.org/wiki/Andr%C3%A9_Ch%C3%A9radame example). So would there be a way to do it directrly with URL ?--René La contemporaine (talk) 11:57, 19 February 2020 (UTC)
- There is also a dedicated tool for that at https://tools.wmflabs.org/wikidata-todo/linked_items.php --- Jura 11:53, 19 February 2020 (UTC)
SELECT *
{
VALUES ?frwiki {
<https://fr.wikipedia.org/wiki/Andr%C3%A9_Ch%C3%A9radame>
<https://fr.wikipedia.org/wiki/Andr%C3%A9_Ch%C3%A9radame>
}
?frwiki schema:about ?item ; schema:name ?name
}
Try it! Here we go. --- Jura 12:09, 19 February 2020 (UTC)
- Thanks, Awesome !--René La contemporaine (talk) 14:50, 19 February 2020 (UTC)
Label woe
What's going on here? Quick query yielding 11 results is submitted to the label service, which promptly times out.
SELECT ?thing (count(?thing) as ?count)
WHERE
{
?item wdt:P138 ?thing .
} group by ?thing having (?count >1000)
SELECT ?thing ?thingLabel ?count with {
SELECT ?thing (count(?thing) as ?count)
WHERE
{
?item wdt:P138 ?thing .
} group by ?thing having (?count >1000) } as %i
where
{
include %i
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
--Tagishsimon (talk) 10:52, 19 February 2020 (UTC)
- I think every week queries time-out in new ways ;) --- Jura 11:04, 19 February 2020 (UTC)
- BTW, I get 105931 results in 55645 ms --- Jura 11:06, 19 February 2020 (UTC)
- (EC) :) ... yes, it ran ... 105931 results in 50675 ms ... so it's ignoring the
having (?count >1000)
. That's a clue, at least. Not sure if this is a problem with Blazegraph or me; the use of having in the initial query seems to be cromulent with https://www.w3.org/TR/sparql11-query/#having --Tagishsimon (talk) 11:07, 19 February 2020 (UTC)
- (EC) :) ... yes, it ran ... 105931 results in 50675 ms ... so it's ignoring the
SELECT ?thing ?thingLabel ?count
WHERE
{
{
SELECT ?thing (count(?thing) as ?count)
WHERE
{
?item wdt:P138 ?thing .
}
group by ?thing having (?count >1000)
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
It works better without the named subquery. Bug? --- Jura 11:11, 19 February 2020 (UTC)
- Thank you. Yes, indications point in that direction. --Tagishsimon (talk) 11:16, 19 February 2020 (UTC)
All museums per Austrian states
Hello!
I’m trying to make lists of all museum (Q33506) (including subclasses) in Austria (Q40), per federal state (for example Lower Austria (Q42497)).
The following naive query times out:
SELECT ?item WHERE {
?item (wdt:P31/(wdt:P279*)) wd:Q33506;
wdt:P17 wd:Q40;
wdt:P131+ wd:Q42497.
}
I tried to be smarter with subqueries, eg:
SELECT ?item
WITH {
SELECT ?item WHERE {
?item (wdt:P31/(wdt:P279*)) wd:Q33506;
wdt:P17 wd:Q40.
}
}AS %items
WHERE {
INCLUDE %items.
?item (wdt:P131+) wd:Q1741.
}
which works for Vienna (Q1741) but still times out for Lower Austria (Q42497).
Thoughts? :)
Jean-Fred (talk) 18:04, 19 February 2020 (UTC)
- @Jean-Frédéric: I /think/ this works.
- --Tagishsimon (talk) 18:59, 19 February 2020 (UTC)Try it!
SELECT distinct ?item ?itemLabel ?state ?stateLabel WHERE { ?item (wdt:P31/(wdt:P279*)) wd:Q33506; wdt:P17 wd:Q40; wdt:P131 ?P131 . ?P131 wdt:P131* ?state . ?state wdt:P31 wd:Q261543. SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
- Folks on Telegram came up with
select distinct ?museum where {
{select ?museum { ?museum wdt:P131+ wd:Q42497. } }
{select ?museum { ?museum wdt:P31/wdt:P279* wd:Q33506. } }
}
which I don’t really understand, but it sure works :) Jean-Fred (talk) 19:23, 19 February 2020 (UTC)
- Use "hint:Prior hint:gearing "forward"." to tell Blazegraph to search the property path forwards instead backwards (i.e. from museum to federal state, instead of checking everything in the state if it is a museum
- I get 331 results in 10 seconds.Try it!
SELECT ?item WHERE { ?item (wdt:P31/(wdt:P279*)) wd:Q33506. ?item wdt:P17 wd:Q40. ?item wdt:P131+ wd:Q42497. hint:Prior hint:gearing "forward" . }
- I get 128 results in 2 seconds. --Dipsacus fullonum (talk) 19:32, 19 February 2020 (UTC)Try it!
SELECT ?item WITH { SELECT ?item WHERE { ?item (wdt:P31/(wdt:P279*)) wd:Q33506; wdt:P17 wd:Q40. } }AS %items WHERE { INCLUDE %items. ?item (wdt:P131+) wd:Q1741. hint:Prior hint:gearing "forward" . }
Show only some rows
Hi all! Given this query:
SELECT ?item ?place ?trec
WHERE {
?item wdt:P31 wd:Q5 .
?item wdt:P3365 ?trec .
?item wdt:P19 ?place .
?place wdt:P131* wd:Q1273 .
}
how can I show only items with "?place" value present in more than one row (= how can I hide items with "?place" which doesn't appear in any other item)? Thank you very much, --Epìdosis 11:25, 20 February 2020 (UTC)
- @Epìdosis: You can that by counting the number of items of each place in a subquery:
- --Dipsacus fullonum (talk) 13:06, 20 February 2020 (UTC)Try it!
SELECT ?item ?place ?trec WHERE { { SELECT (count(DISTINCT ?item) AS ?items) ?place WHERE { ?item wdt:P31 wd:Q5 . ?item wdt:P3365 ?trec . ?item wdt:P19 ?place . ?place wdt:P131* wd:Q1273 . } GROUP BY ?place HAVING (?items > 1) } ?item wdt:P31 wd:Q5 . ?item wdt:P3365 ?trec . ?item wdt:P19 ?place . }
Dead Canadians, with a father listed, without a FamilySearch entry
Date of death is Q18748141, father is Q7565, FamilySearch person ID is P2889
Basically, I'd like to add the FamilySearch person ID to Wikidata entries, and if the record here connects to a father entry here, they should be particularly easy to find on FamilySearch. -- Zanimum (talk) 12:00, 20 February 2020 (UTC)
- @ Zanimum: I am not sure that I have understod the request correctlt, but here is a query for dead Canadians with P2889 who have a father without P2889. There is only one result:
- --Dipsacus fullonum (talk) 13:24, 20 February 2020 (UTC)Try it!
SELECT ?person ?personLabel ?dod ?fsID ?father ?fatherLabel WHERE { ?person wdt:P31 wd:Q5 . # is human ?person wdt:P27 wd:Q16 . # Canadian ?person wdt:P570 ?dod . # have a date of death ?person wdt:P2889 ?fsID . # family search ID ?person wdt:P22 ?father. FILTER NOT EXISTS { ?father wdt:P2889 ?father_fsID } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en" } }
Wikidata:Database reports/items with P569 greater than P570
Can this query be broken into two, by gender (gender=male and gender=female), so that each can be completed in the allotted 1 minute. Currently the query can no longer be updated. The query is important for error and vandalism detection, and is usually run daily. Because it has reached the computational limit it has stopped updating. Maybe once a month it gets updated if the query server load is light. --RAN (talk) 21:40, 22 February 2020 (UTC)
How to get the parent taxon of the parent taxon?
SELECT ?item ?taxon_name ?PL_id ?PLLink WHERE {
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
?item p:P225 ?stat .
?stat ps:P225 ?taxon_name.
?item wdt:P1070 ?PL_id. # PlantList ID
?item wdt:P105 wd:Q7432. # taxon rank is species
bind(uri(concat("http://www.theplantlist.org/tpl1.1/record/",?PL_id)) as ?PLLink)
MINUS {?stat pq:P405 ?taxon_author.}
}
Instead of limiting the query on the basis that the species has a Plant List id (P1070) I want to limit it by asking that the parent taxon of the parent taxon (P171) is Rubiaceae (Q156569) MargaretRDonald (talk) 23:13, 22 February 2020 (UTC)
- @MargaretRDonald: Like this?
- Try it!
SELECT ?item ?taxon_name ?PL_id ?PLLink WHERE { SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } ?item p:P225 ?stat . ?stat ps:P225 ?taxon_name . ?item wdt:P171/wdt:P171 wd:Q156569 . # parent taxon of parent taxon is Q156569 OPTIONAL { ?item wdt:P1070 ?PL_id } # PlantList ID ?item wdt:P105 wd:Q7432. # taxon rank is species bind(uri(concat("http://www.theplantlist.org/tpl1.1/record/",?PL_id)) as ?PLLink) MINUS {?stat pq:P405 ?taxon_author.} } ORDER BY ?taxon_name
- --Larske (talk) 09:34, 23 February 2020 (UTC)
- Thanks very much, @Larske:. Very useful (but somewhat disturbing since it indicates that parent taxon is missing for either many genera or many species.. ) MargaretRDonald (talk) 15:23, 23 February 2020 (UTC)
- @MargaretRDonald: No, please see "Target_required_claim_übergeordnetes_Taxon_(P171)"_violations this report. --Succu (talk) 15:47, 23 February 2020 (UTC)
- And thank you @Succu: also for this. (Very helpful). MargaretRDonald (talk) 20:12, 23 February 2020 (UTC)
- @MargaretRDonald: The missing species in the list are not due to missing parent taxon values. The problem is that in many cases there is more than two levels between species and family, like tribus and subfamily in addition to genus. I suggest that you change:
?item wdt:P171/wdt:P171 wd:Q156569 . # parent taxon of parent taxon is Q156569
- to
?item wdt:P171+ wd:Q156569 . # a higher taxon is Q156569
- That will double the number of results. --Dipsacus fullonum (talk) 16:06, 23 February 2020 (UTC)
- Thank you. @Dipsacus fullonum: Much more satisfactory. MargaretRDonald (talk) 20:09, 23 February 2020 (UTC)
- @MargaretRDonald: The missing species in the list are not due to missing parent taxon values. The problem is that in many cases there is more than two levels between species and family, like tribus and subfamily in addition to genus. I suggest that you change:
- Thanks very much, @Larske:. Very useful (but somewhat disturbing since it indicates that parent taxon is missing for either many genera or many species.. ) MargaretRDonald (talk) 15:23, 23 February 2020 (UTC)
MWAPI fu - en wiki articles lacking specified images
Is there any MWAPI or other magic which will establish which en.wiki articles lack the image specified against the article in this report? (The report lists range maps for taxons where the taxon has an en wiki article ... the article infobox has a parameter for the rangemap - e.g. en:Acacia cockertoniana).
SELECT ?item ?itemLabel ?image ?article
WHERE
{
?item wdt:P181 ?image.
bind(str(?image) as ?stri)
filter(strstarts(str(?image),"http://commons.wikimedia.org/wiki/Special:FilePath/Acacia"))
?sitelink ^schema:name ?article .
?article schema:about ?item ;
schema:isPartOf <https://en.wikipedia.org/> .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
--Tagishsimon (talk) 03:18, 25 February 2020 (UTC)
- @Tagishsimon: Here is my solution. Be aware when using the MWAPI that the returned article titles is without language code, so they cannot be directly compared with the language coded sitelink values from Wikidata. That's why the
STRLANG
is used. - --Dipsacus fullonum (talk) 12:50, 25 February 2020 (UTC)Try it!
SELECT ?item ?itemLabel ?image ?article ?sitelink WHERE { { ?item wdt:P181 ?image. filter(strstarts(str(?image),"http://commons.wikimedia.org/wiki/Special:FilePath/Acacia")) ?sitelink ^schema:name ?article . ?article schema:about ?item ; schema:isPartOf <https://en.wikipedia.org/> . } MINUS { SERVICE wikibase:mwapi { bd:serviceParam wikibase:api "Search" . bd:serviceParam wikibase:endpoint "en.wikipedia.org" . bd:serviceParam mwapi:srnamespace "0" . bd:serviceParam mwapi:srlimit "max" . bd:serviceParam mwapi:srwhat "text" . bd:serviceParam mwapi:srsearch 'insource:"range_map = Acacia"' . ?title wikibase:apiOutput mwapi:title . } BIND (STRLANG(?title, "en") as ?sitelink) } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
- Hat's off to you, Dipsacus fullonum. I like mwapi:srsearch 'insource:"range_map = Acacia"'. Thanks. --Tagishsimon (talk) 12:54, 25 February 2020 (UTC)
- @MargaretRDonald: The above will probably be of use; only 484 maps to add to en.wiki :) --Tagishsimon (talk) 12:55, 25 February 2020 (UTC)
- @Tagishsimon: Thanks for thinking of me (and for the query solution Dipsacus fullonum) Brilliant. MargaretRDonald (talk) 17:09, 25 February 2020 (UTC)
- @MargaretRDonald: The above will probably be of use; only 484 maps to add to en.wiki :) --Tagishsimon (talk) 12:55, 25 February 2020 (UTC)
All female British writers with a Project Gutenberg identifier who published their works during the Victorian Era
Is it possible to find all female authors that:
- have a Gutenberg Author ID;
- are from the United Kingdom of England and Ireland;
- published their written works during Victorian Era (1837-1901) ?
Thanks in advance for the help. DellaCG (talk) 14:31, 25 February 2020 (UTC)
- @DellaCG: I can give you female writers with Gutenberg ID who were citizens of United Kingdom of Great Britain and Ireland, but I cannot say when they published their works.
- --Dipsacus fullonum (talk) 15:52, 25 February 2020 (UTC)Try it!
SELECT ?author ?authorLabel ?Gutenberg_author_ID (URI(CONCAT("https://www.gutenberg.org/ebooks/author/", ?Gutenberg_author_ID)) as ?Gutenber_link) WHERE { ?author wdt:P31 wd:Q5 . # is human ?author wdt:P21 wd:Q6581072 . # is female ?author wdt:P106/wdt:P279* wd:Q482980. # is author ?author wdt:P1938 ?Gutenberg_author_ID . ?author wdt:P27 wd:Q174193 . # Citizen of UK of GB and Ireland SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
@Dipsacus fullonum: Thanks DellaCG (talk) 16:21, 25 February 2020 (UTC)
- This would be one approach; looking at their DoB & DoD might be another, though inferential.
- --Tagishsimon (talk) 15:58, 25 February 2020 (UTC)Try it!
SELECT distinct ?item ?itemLabel WHERE { ?item wdt:P1938 ?GId . # has a Gutenberg ID ?item wdt:P21 wd:Q6581072 . # is female ?item wdt:P27 ?UK # UK citizenship VALUES ?UK {wd:Q174193 wd:Q145} # in the broadest sense ?work wdt:P50 ?item . # author of a work ?work wdt:P577 ?date. # which has a publication date FILTER("1836-00-00"^^xsd:dateTime <= ?date && ?date < "1902-00-00"^^xsd:dateTime) # between these two dates SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } }
@Tagishsimon: Many thanks! The approach you are using works really well. DellaCG (talk) 16:21, 25 February 2020 (UTC)
- @DellaCG, Tagishsimon: Be aware that the only 157 out of the 322 authors returned by my query above have any of their works registered on Wikidata. And several of the works don't have any value for publication date. So filtering by work publication date will remove more than half of the potential authors. --Dipsacus fullonum (talk) 21:34, 25 February 2020 (UTC)
- Agreed; a union of by publication date & by some sort of DoB/DoD mashup will give most results. What the best route is, depends on exactly what DellaCG will & will not tolerate in the report - e.g. a Victorian author who was only published in contemporary times, which we might pick up with DoB, but which really might not be wanted. --Tagishsimon (talk) 23:03, 25 February 2020 (UTC)
@Dipsacus fullonum, Tagishsimon: Thanks to both of you for the further explanation.
Largest EU cities
This query:
SELECT DISTINCT ?item ?pocetobyvatel ?pocetobyvatel_upr ?rozloha (ROUND(?rozloha/1000000) AS ?rozloha_upr) ?date WITH {
SELECT DISTINCT ?item WHERE {
?item wdt:P31/wdt:P279* wd:Q515 . } #city, dute to disuniformity expensive P31/wdt:P279* is needed
} AS %subquery WHERE {
INCLUDE %subquery . #optimization
?item wdt:P17 ?zeme .
?zeme wdt:P463 wd:Q458 . #EU member
MINUS { ?zeme p:P463 ?P363node .
?P363node ps:P463 wd:Q458 .
?P363node pq:P582 ?end .} #minus former EU member
?item wdt:P1082 ?pocetobyvatel .
?item p:P1082 ?pobSt .
?pobSt a wikibase:BestRank . #population with best rank
OPTIONAL { ?pobSt pq:P585 ?date }
?item p:P2046/psn:P2046/wikibase:quantityAmount ?rozloha . #normalized area
?item p:P2046 ?ArSt .
?ArSt rdf:type wikibase:BestRank #area with best rank (hope so), not sure how to select best rank before unit normalization
bind(concat("",str(?pocetobyvatel),"") as ?pocetobyvatel_upr)
}
order by desc(?pocetobyvatel) desc(?rozloha)
LIMIT 100
works OK, even it works OK with Listeria on Wikidata or enwiki, but for some unknown reason doesn't work on cswiki.
Any different approach to achieve same results?--Jklamo (talk) 16:06, 26 February 2020 (UTC)
- It is another query on the page at cswiki so what do you mean that is doesn't work? (I fixed the link which were to enwiki). BTW. The query is almost impossible to read as it is formatted. --Dipsacus fullonum (talk) 17:34, 26 February 2020 (UTC)
- Sorry for confusion, I meant that query, where (Listeria) updates don't work for that query since November (see [2] [3]). Which is a bit embarrassing, as I used that query as a showcase for automatic list generation from WD on cswiki. BTW originally linked query has same problem.--Jklamo (talk) 13:05, 27 February 2020 (UTC)
- @Jklamo: ListeriaBot doesn't update a list if there is no changes to the list. Wouldn't that explain the infrequent updates on cswiki? But if data have changed and the list still isn't updated, it is normally because the query timed out. In that case the query can often be optimized. That is also valid for the query of the 100 largest cities in EU above. --Dipsacus fullonum (talk) 15:31, 27 February 2020 (UTC)
- @Dipsacus fullonum: Unfortunately in a case of no changes Listeria is showing "Status: OK" (and simply not updating the page), while in these cases it is showing "Status: No items" (and query apparently has some results). Timeout may be the problem. I have commented on the query a bit, hope it is more readable now.--Jklamo (talk) 17:21, 27 February 2020 (UTC)
- @Jklamo: ListeriaBot doesn't update a list if there is no changes to the list. Wouldn't that explain the infrequent updates on cswiki? But if data have changed and the list still isn't updated, it is normally because the query timed out. In that case the query can often be optimized. That is also valid for the query of the 100 largest cities in EU above. --Dipsacus fullonum (talk) 15:31, 27 February 2020 (UTC)
- Sorry for confusion, I meant that query, where (Listeria) updates don't work for that query since November (see [2] [3]). Which is a bit embarrassing, as I used that query as a showcase for automatic list generation from WD on cswiki. BTW originally linked query has same problem.--Jklamo (talk) 13:05, 27 February 2020 (UTC)
How to extract the languages of labels and aliases?
I have the following query:
The following query uses these:
- Properties: RxNorm ID (P3345)
SELECT DISTINCT ?item ?itemLabel ?itemAltLabel WHERE { VALUES ?item { wd:Q410237 wd:Q422761 } ?item wdt:P3345 ?rx . ?item rdfs:label ?itemLabel . ?item skos:altLabel ?itemAltLabel . } LIMIT 1000
Now, I would like to know for each label and each alias the corresponding language, and I have not yet figured out how to do that. Any pointers would be most welcome. Thanks! --Daniel Mietchen (talk) 12:10, 27 February 2020 (UTC)
- @Daniel Mietchen: You can use the LANG function. For instance you can add
(LANG(?itemLabel) AS ?label_language) (LANG(?itemAltLabel) AS ?alias_language)
- to the SELECT clause in your query above. --Dipsacus fullonum (talk) 12:21, 27 February 2020 (UTC)
- @Dipsacus fullonum: Thanks — works fine!. --Daniel Mietchen (talk) 12:41, 27 February 2020 (UTC)
- @Daniel Mietchen: By the way, if you don't want separate rows in the result for every combination of a label and an alias, you can combine them using UNION and e.g. COALESCE as shown here:
- --Dipsacus fullonum (talk) 12:46, 27 February 2020 (UTC)Try it!
SELECT ?item ?label ?type (LANG(?label) AS ?language_code) WHERE { VALUES ?item { wd:Q410237 wd:Q422761 } ?item wdt:P3345 ?rx . { ?item rdfs:label ?itemLabel . VALUES ?type { "label"} } UNION { ?item skos:altLabel ?itemAltLabel . VALUES ?type { "alias"} } BIND (COALESCE(?itemLabel, ?itemAltLabel) AS ?label) }
Code = code
Hi! Given all the items containing ISIL (P791), I need a query listing only those having a value of ISIL (P791) which is also present in another item as Archives Portal Europe ID (P7764), and that item (or items) should be listed in another column. Example of a line:
- Item 1 containing ISIL (P791) A | ISIL (P791) A | Archives Portal Europe ID (P7764) A | Item 2 containing Archives Portal Europe ID (P7764) A
Ask me if I'm not clear enough. Thanks! --Epìdosis 16:58, 27 February 2020 (UTC)
- @Epìdosis: This query gives you 91 pairs of objects:
- Try it!
SELECT ?item1 ?item1Label ?item1_p791_and_item2_p7764 ?item2Label ?item2 { ?item1 wdt:P791 ?item1_p791_and_item2_p7764 . ?item2 wdt:P7764 ?item1_p791_and_item2_p7764 . FILTER(STR(?item1)<STR(?item2)) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de,it". } } ORDER BY ?item1_p791_and_item2_p7764
- --Larske (talk) 00:36, 28 February 2020 (UTC)
Why UNION doesn't work in the second query?
Sorry about long post, but i am little bit lost on why the third example where i try the combining the two SERVICE requests with UNION doesnt work.
THIS WORKS: UNION works like expected
SELECT ?cattitle ?b WHERE
{
{
VALUES ?cattitle {
"Turku"
}
{
SELECT * WHERE { ?b ?c ?cattitle }
}
}
UNION
{
VALUES ?cattitle {
"Raisio"
}
{
SELECT * WHERE { ?b ?c ?cattitle }
}
}
}
THIS WORKS: If i do a mwapi service query it works too
SELECT ?cattitle ?item WHERE
{
VALUES ?cattitle {
"category:Parainen"
}
SERVICE wikibase:mwapi {
bd:serviceParam wikibase:api "Generator" .
bd:serviceParam wikibase:endpoint "fi.wikipedia.org" .
bd:serviceParam mwapi:gcmtitle ?cattitle .
bd:serviceParam mwapi:generator "categorymembers" .
bd:serviceParam mwapi:gcmprop "ids|title|type" .
bd:serviceParam mwapi:gcmlimit "max" .
bd:serviceParam wikibase:limit 50 .
# out
?subcat wikibase:apiOutput mwapi:title .
?ns wikibase:apiOutput "@ns" .
?item wikibase:apiOutputItem mwapi:item .
}
}
BROKEN Works now: But if I try to combine two mwapi service queries with UNION there is no results.
SELECT ?cattitle ?item WHERE
{
VALUES ?cattitle1 {
"category:Parainen"
}
VALUES ?cattitle2 {
"category:Turku"
}
{
SERVICE wikibase:mwapi {
bd:serviceParam wikibase:api "Generator" .
bd:serviceParam wikibase:endpoint "fi.wikipedia.org" .
bd:serviceParam mwapi:gcmtitle ?cattitle1 .
bd:serviceParam mwapi:generator "categorymembers" .
bd:serviceParam mwapi:gcmprop "ids|title|type" .
bd:serviceParam mwapi:gcmlimit "max" .
bd:serviceParam wikibase:limit 50 .
# out
?subcat wikibase:apiOutput mwapi:title .
?ns wikibase:apiOutput "@ns" .
?item wikibase:apiOutputItem mwapi:item .
}
}
UNION
{
SERVICE wikibase:mwapi {
bd:serviceParam wikibase:api "Generator" .
bd:serviceParam wikibase:endpoint "fi.wikipedia.org" .
bd:serviceParam mwapi:gcmtitle ?cattitle2 .
bd:serviceParam mwapi:generator "categorymembers" .
bd:serviceParam mwapi:gcmprop "ids|title|type" .
bd:serviceParam mwapi:gcmlimit "max" .
bd:serviceParam wikibase:limit 50 .
# out
?subcat wikibase:apiOutput mwapi:title .
?ns wikibase:apiOutput "@ns" .
?item wikibase:apiOutputItem mwapi:item .
}
}
}
Do you have any what is the problem and how this should be done? Just to be I know that i could do this without UNION and question is why combining doesn't work. --Zache (talk) 12:07, 28 February 2020 (UTC)
- It works with this change but I still doesn't understand the why the older version didn't work. --Zache (talk) 15:19, 28 February 2020 (UTC)
- @Zache: The problem was that the binding of ?cattitle was not in scope inside the group pattern in the service call, so the MWAPI was called with an unbound ?cattitle. I recommend reading the article Understanding SPARQL’s Bottom-up Semantics at blog.blazegraph.com which explains this. The other variants of the query only works by luck because Blazegraph reorganizes the query in order to optimize, and apparently don't take the semantics of the service call into account. --Dipsacus fullonum (talk) 19:17, 28 February 2020 (UTC)