Wikidata:Request a query/Archive/2016/12

This page is an archive. Please do not modify it. Use the current page, even to continue an old discussion.

Non-en.wiki water polo players

Would someone please be kind enough to knock up a list of water polo player (Q17524364) who have a page any language wikipedia other than en.wikipedia. I don't have a better spec than that - see [1]. thanks --Tagishsimon (talk) 15:34, 26 November 2016 (UTC)

SELECT ?item ?itemLabel 
WHERE
{
	?item wdt:P106 wd:Q17524364 .
	FILTER NOT EXISTS { ?wfr schema:about ?item . ?wfr schema:inLanguage "en" }
	SERVICE wikibase:label { bd:serviceParam wikibase:language "it,en" }
}

Try it!

--ValterVB (talk) 16:02, 26 November 2016 (UTC)

(ec)

SELECT ?item ?itemLabel (COUNT(distinct ?article) as ?articles)
WHERE
{
	?item wdt:P106 wd:Q17524364 .
	?article schema:about ?item .
	MINUS { ?wen schema:about ?item . ?wen schema:isPartOf <https://en.wikipedia.org/> }
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "en,de,fr,pl" .
	}
}
GROUP BY ?item ?itemLabel 
ORDER BY DESC(?articles)

Try it!

--Edgars2007 (talk) 16:07, 26 November 2016 (UTC)

(Some sort of EC) Yes I asked for it :). I want to create more English water polo pages. If I know water polo players from other Wikipedia pages, I can see which water polo players are notable, so I can start creating some of them. Sander.v.Ginkel (talk) 16:31, 26 November 2016 (UTC)

Thanks Edgars2007 - you're a star. --Tagishsimon (talk) 16:34, 26 November 2016 (UTC)

Thanks, this is great!! Will start creating upcoming week :) Sander.v.Ginkel (talk) 16:51, 26 November 2016 (UTC)

Edgars2007, Tagishsimon and ValterVB, Thanks again. To show you an update, there were 232 entries and so far I was able to create 110 of them. Thanks! Sander.v.Ginkel (talk) 11:42, 1 December 2016 (UTC)

bridges named after women

here is a report on bridges named after women. 1. Why does the The Duchess' Bridge (Q26293777) not turn up in it and 2. If I wanted to remove all the City elements, how would I do that. My attempts seem to result in crashes. (tbh I think what I'm after is, show related settlements if there are any, and show the waterbody crossed by the bridge.) thanks --Tagishsimon (talk) 21:32, 2 December 2016 (UTC)

I tried to address the issues of your query as good as possible (for me):

SELECT DISTINCT ?bridge ?bridgeLabel ?womanLabel ?city ?cityLabel ?waterbody ?waterbodyLabel (BOUND(?enArticle) AS ?hasEn) (BOUND(?frArticle) AS ?hasFr) WHERE {
  ?bridge wdt:P31/wdt:P279* wd:Q12280;
          wdt:P138 ?woman .
  ?woman wdt:P21 wd:Q6581072 .
  OPTIONAL {
    ?bridge wdt:P131+ ?city .
	?city wdt:P31/wdt:P279* wd:Q486972 .
  }
  OPTIONAL {
    ?bridge wdt:P177 ?waterbody .
  }
  OPTIONAL {
    ?enArticle a schema:Article;
               schema:about ?woman;
               schema:isPartOf <https://en.wikipedia.org/>.
  }
  OPTIONAL {
    ?frArticle a schema:Article;
               schema:about ?woman;
               schema:isPartOf <https://fr.wikipedia.org/>.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} ORDER BY ?city

Try it!

Some data need to be queried with OPTIONAL. The Duchess' Bridge (Q26293777) did not show up because it is not located in a subclass of a human settlement. —MisterSynergy (talk) 21:46, 2 December 2016 (UTC)

Thanks very much. The Duchess' Bridge (Q26293777) was shown as located in Alnmouth (to which I've added 'human settlement') and which was was showing as an instance of a village, which is a subclass of a rural settlement, which is a subclass of a human settlement, so I'm still puzzled on that account. --Tagishsimon (talk) 21:58, 2 December 2016 (UTC)

Your query looks for located in the administrative territorial entity (P131) claims, this bridge has the value Northumberland (Q23079) which is not a subclass of human settlement (Q486972) (as far as I can see). Any location (P276) claims are completely irrelevant for the queries in this section until now. —MisterSynergy (talk) 22:15, 2 December 2016 (UTC)

Thank you again. I'm getting there. --Tagishsimon (talk) 23:51, 2 December 2016 (UTC)

Nice, I adapted it and added it to Wikidata:Status_updates/Next.
--- Jura 10:56, 3 December 2016 (UTC)

Streets named after womenwriters but without named after (P138)

Hi, I have this query in which I am looking for streets that are named after female authors that are in the WomenWriters database (WomenWriters ID (P2533)). In the query we make use of the named after (P138) property. I was wondering if it is possible to create a query with which i would find the streets named after these female authors in the WomenWriters database even when the named after (P138) property hasn't been used in the Wikidata item. My (end) goal is to create a map with all the streets named after these women writers, but I assume (hope...) there are a lot of streets that don't have this named after (P138) property yet.

select ?item ?itemLabel ?itemDescription ?WWid where {
  ?item wdt:P31 wd:Q79007 .
    ?item wdt:P138 ?vernoemd .
  ?vernoemd wdt:P2533 ?WWid .
  ?vernoemd wdt:P21 wd:Q6581072 .
  SERVICE wikibase:label {
        bd:serviceParam wikibase:language "nl" .
      }
}

Try it!

Thank you for your help, Ecritures (talk) 20:23, 4 December 2016 (UTC)

I cannot think of a very reliable way. If there is no named after property in the road item, then there is no pointer to a women fullstop. That leaves only the option of trying to work out women name strings from the item title - so Jane Doe Road -> Jane Doe, and then some sort of lookup on records matching Jane Doe ... which may or may not get you some women, but won't reliably choose between Jane Doe Acrobat and Jane Doe Author. I fear we need to roll up our sleeves as I did last night with bridges named after women, and find likely road candidates and add the named after value. --Tagishsimon (talk) 22:05, 4 December 2016 (UTC)

Articles on cswiki which have their commons category set in wd item but dont have the appropriate template

Hi, I am in need of query that would show me which articles on cs.wiki have their Commons category (P373) set but at the same time do not contain the template. I want to add the templates with pwb, but I need the list of articles to work on. Thank you in advance. --Wesalius (talk) 10:01, 5 December 2016 (UTC)

@Wesalius: I made this PetScan list which you can export to wikitext or so for PWB (without using SPARQL) but it seems strange to me that there would be so many articles (one in five). Matěj Suchánek (talk) 17:40, 5 December 2016 (UTC)

Thank you. That is a whole lot of articles. I will do a random manual check on them before I run the bot. --Wesalius (talk) 17:46, 5 December 2016 (UTC)

Sorting in histogram

I take query from Wikidata:Request a query/Archive/2016/10#Histogram, change P577->P571 and Q11424 to Q2088357 and ... sorting breaks. Now X-axis (years) is sorted by Y-values (number of groups). Why? --Infovarius (talk) 18:40, 25 November 2016 (UTC)

To be more specific, compare:

#defaultView:BarChart
SELECT ?year (count(?item) as ?cnt)  WHERE{
  ?item wdt:P31/wdt:P279* wd:Q11424 .
  ?item wdt:P577 ?publicationdate .
  BIND(str(year(?publicationdate)) as ?year)
  FILTER(bound(?year))
} GROUP BY ?year ORDER BY ?year

Try it!

and

#defaultView:BarChart
SELECT ?year (count(?item) as ?cnt)  WHERE{
  ?item wdt:P31/wdt:P279* wd:Q2088357 .
  ?item wdt:P571 ?publicationdate .
  BIND(str(year(?publicationdate)) as ?year)
  FILTER(bound(?year))
} GROUP BY ?year ORDER BY ?year

Try it!

How to get right sorting (by year) in second query? --Infovarius (talk) 13:31, 5 December 2016 (UTC)

Use BIND(YEAR(?publicationdate) as ?year) --Succu (talk) 14:18, 5 December 2016 (UTC)

Hmm, it helps sorting... But several questions arise: 1) why does the first works good? 2) why without str() BarChart turns into ScatterChart? 3) Y-axis centers now at 0, not at minimum value... --Infovarius (talk) 10:46, 6 December 2016 (UTC)

Tree search

Is there any way to do a tree search like this or this. The second one is not working for me anymore, so I would like to get a replacement with a Wikidata Query. Thanks.--Micru (talk) 13:58, 7 December 2016 (UTC)

I found this external tool useful in the past, since it has a graphical representation of the tree. —MisterSynergy (talk) 14:22, 7 December 2016 (UTC)

I could have a graph view with

select ?item ?itemLabel ?parent ?parentLabel where {
        ?item wdt:P279/wdt:P279? wd:Q35120 .
        ?item wdt:P279 ?parent .
  service wikibase:label { bd:serviceParam wikibase:language "fr,en" . }
   }
#defaultView:Graph

Try it!

but it' kind of a torture for my machine to display the graph. author TomT0m / talk page 14:41, 7 December 2016 (UTC)

@Micru: Is this what you'd like ?

select ?item where {
        # ?item wdt:P279{1,4} wd:Q35120 # blazegraph does not seem to support this syntax so ...
        ?item wdt:P279/wdt:P279?/wdt:P279?/wdt:P279? wd:Q35120 . # select subclasses of Q35120 on 4 level and no more
   }
   }

Try it!

@MisterSynergy, TomT0m: I was hoping to have an exact copy to the examples provided, but I reckon it is not totally possible with blazegraph. Thanks for the nice replacements :)--Micru (talk) 07:16, 8 December 2016 (UTC)

Get wikidata item without URL

I'm trying to write a query returning all wikidata items with coordinates inside a bounding box.

#defaultView:Map
SELECT  ?wikidata ?placeLabel ?whatisitLabel ?location ?latitude ?longitude WHERE {
  SERVICE wikibase:box {
    ?place wdt:P625 ?location .
    bd:serviceParam wikibase:cornerSouthWest "Point(82.5 35)"^^geo:wktLiteral .
    bd:serviceParam wikibase:cornerNorthEast "Point(93 43)"^^geo:wktLiteral .
  }
  OPTIONAL {?place wdt:P31 ?whatisit.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,es,it,ru,zh". }
  ?place p:P625 ?statement . # coordinate-location statement
  ?statement psv:P625 ?coordinate_node .
  ?coordinate_node wikibase:geoLatitude ?latitude .
  ?coordinate_node wikibase:geoLongitude ?longitude .
  BIND (?place AS ?wikidata).
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,fr,es,it,ru". }
}

Try it!

Now I want to have a field named "wikidata" in the results without URL ("http://www.wikidata.org/entity/Q..."), instead it should simply say "Q...". --SevenGrader (talk) 19:07, 11 December 2016 (UTC)

Add a line like BIND(SUBSTR(STR(?place), 32) AS ?placeID) . and output ?placeID as well. —MisterSynergy (talk) 19:29, 11 December 2016 (UTC)

Thank you! BIND(SUBSTR(STR(?place), 32) AS ?wikidata). works as well. SevenGrader (talk) 20:17, 11 December 2016 (UTC)

List of items

How can get a full list of items with original language of film or TV show (P364)? I used to get it with wdq.wmflabs.org before it's died... Now I am trying to use Query service, but it fails to get >700,000 items. Petscan (PSID=620097) shows the first 100,000 but it cannot export JSON with full list. Are there any other possibilities? --Infovarius (talk) 10:03, 12 December 2016 (UTC)

Have you tried to run this query? --Pasleim (talk) 10:16, 12 December 2016 (UTC)

Maybe you can build the full list by using OFFSET and LIMIT for parts of it.
--- Jura 10:18, 12 December 2016 (UTC)

Find members and his/her role in a given cabinet

Hi, how to find members and his/her role in a given cabinet? For example, I'd like to list all ministers of Renzi Cabinet (Q15772498) and their role in the cabinet. Thank you very much. --★ → A ir on 90 18:15, 7 December 2016 (UTC)

Data is incomplete and incosistent:

SELECT ?item ?itemLabel ?ministerLabel ?ministryLabel ?start ?end {
  wd:Q15772498 wdt:P580 ?beginning .
  OPTIONAL { wd:Q15772498 wdt:P582 ?finish } .
  ?item p:P39 ?statement .
  {
    ?statement ps:P39/wdt:P360? ?minister .
    {
      ?minister wdt:P279 wd:Q3858501 .
    } UNION {
      ?minister wdt:P279 wd:Q83307;
                wdt:P17 wd:Q38 .
    }
  } UNION {
    ?statement pq:P642 ?ministry .
    ?ministry wdt:P31 wd:Q192350;
              wdt:P17 wd:Q38 .
  } .
  ?statement pq:P580 ?start .
  OPTIONAL { ?statement pq:P582 ?end } .
  FILTER( !BOUND(?end)|| ?end > ?beginning ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" } .
}

Try it!

Matěj Suchánek (talk) 15:27, 12 December 2016 (UTC)

@Matěj Suchánek: Thank you. I know that data are incomplete, that's why I want a query ;) However there is an error: Flavio Zanonato (Q3746528) is listed but he was minister during Letta Cabinet (Q12080459). --★ → A ir on 90 18:03, 13 December 2016 (UTC)

nlwiki sourced P625 with P31:Q14839548

Items with instance of (P31):minor locality in Sweden (Q14839548) tends to often have P625-statements imported from nlwiki. These coordinates are frustratingly often wrong, located outside these small entities. (typical area: 0,01-0,50 km2, why 200 meters far to the west could put you in the neighbour area.) Can somebody provide me with a list of such items? If the list could be updated by Listeriabot, it would be appreciated! -- Innocent bystander (talk) 10:27, 13 December 2016 (UTC)

I guess it’s something like this:

SELECT ?item ?itemLabel ?coordinates WHERE {
  ?item wdt:P31 wd:Q14839548; p:P625 [ ps:P625 ?coordinates; prov:wasDerivedFrom/pr:P143 wd:Q10000 ] .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

97 results. You can use this query for a ListeriaBot list, cf. en:Template:Wikidata list for details. —MisterSynergy (talk) 11:41, 13 December 2016 (UTC)

Women encoded as male

I wonder if I could ask for some help identifying wikidata items which point to biographies of women in en.wiki, where the sex or gender (P21) value is set as male (Q6581097)? The only idea I can come up with right now, absent any other data, is to match the start of the itemlabel with the set of records for which instance of (P31) is set as female given name (Q11879590)? Grateful if someone would a) write a query based on that idea and b) suggst/write other queries that come to mind and which might help us address a concern that too many women items end-up coded male due to poor petscan use. thanks --Tagishsimon (talk) 16:38, 10 December 2016 (UTC)

May be you should look at Wikidata:WikiProject_Names#Regularly_updated_reports_and_dynamic_lists. --Infovarius (talk) 09:37, 12 December 2016 (UTC)

Interesting to see how many of those definitely have correct P21 - names are slipperier things than we might expect at first glance! Andrew Gray (talk) 13:32, 14 December 2016 (UTC)

Property path problem…

SELECT ?item (COUNT(DISTINCT ?sitelink) as ?articlecount) WHERE {
  ?item wdt:P570 ?deathdate;
        wdt:P31 wd:Q5 .
  FILTER (YEAR(?deathdate)=1945) .
  ?sitelink schema:about ?item .
  
  VALUES ?allowedRank { wikibase:NormalRank wikibase:PreferredRank } .
  ?item p:P106 ?occupationStatement .
  ?occupationStatement ps:P106/wdt:P279* wd:Q2500638;
                       wikibase:rank ?allowedRank .
} GROUP BY ?item ORDER BY DESC (?articlecount)

Try it!

Using this query I always run into timeouts. The problematic line is the property path search ?occupationStatement ps:P106/wdt:P279* wd:Q2500638, since ?occupationStatement ps:P106/wdt:P279?/wdt:P279? wd:Q2500638 still works, but adding another step of P279 results in a timeout. If I just queried ?item wdt:P106/wdt:P279* wd:Q2500638 instead without the ranking stuff, there is no problem as well.

Any idea how I could speed things up here? It is important that P106 claims of perferred and normal rank are found in this query, not just claims of highest rank. Thanks! —MisterSynergy (talk) 10:58, 12 December 2016 (UTC)

It might be more efficient to filter for the absence of DeprecatedRank. Not really convinced that we should have preferred occupations.
--- Jura 11:02, 12 December 2016 (UTC)

I’m not convinced as well, but I prefer to adapt the query to the data, not vice versa. It’s about him in particular… —MisterSynergy (talk) 11:07, 12 December 2016 (UTC)

Still, it is good idea to look for the absence of DeprecatedRank. Still a heavy (~25–30 s) operation, but working:

SELECT ?item (COUNT(DISTINCT ?sitelink) as ?articlecount) WHERE {
  ?item wdt:P570 ?deathdate;
        wdt:P31 wd:Q5 .
  FILTER (YEAR(?deathdate)=1945) .
  ?sitelink schema:about ?item .
  
  ?item p:P106 ?occupationStatement .
  ?occupationStatement ps:P106/wdt:P279* wd:Q2500638 .
  MINUS { ?occupationStatement wikibase:rank wikibase:DeprecatedRank . }
  
} GROUP BY ?item ORDER BY DESC (?articlecount)

Try it!

I would be happy to hear about other optimizations to make sure it “never” times out… —MisterSynergy (talk) 11:20, 12 December 2016 (UTC)

I think you might be able to do some re-ordering of the parts in terms of the likely occurrence, but my attempts didn't really make it quicker. Eventually, you could use "wikibase:sitelinks", but that doesn't quite work yet. BTW, the queries currently omit items without any sitelinks.
--- Jura 11:43, 12 December 2016 (UTC)

Thanks for your input. I already thought about re-ordering, but this is already pretty much the best I could imagine (and the differences are rather small anyway). The query is from a group of editors at dewiki, and they use it for a Listeria list with descending ?articlecount to identify important creator (Q2500638)s whose works went to public domain this year. Items without any sitelinks would appear in the end of the list, and with OPTIONAL{} it’d be easy to include them as well if they need it.

I just found that ignoring ranks and simply going with ?item wdt:P106/wdt:P279* wd:Q2500638 isn’t much faster anyway. Maybe I was confused in the beginning because the result was cached or whatever and appeared within fractions of a second. In the end it is just the property path ...:P106/wdt:P279* which is (too) expensive when starting from the creator (Q2500638) item… —MisterSynergy (talk) 12:25, 12 December 2016 (UTC)

By checking with squid : https://tools.wmflabs.org/sqid/#/browse?activepage=15&type=classes&lang=en&supercfilter=2500638 it seems that this class has way too much subclasses. First we should not mix up scientists with artists I guess ... It has amongst other stuffs pediatrician (Q1919436)  

as a subclass. Which is weird because a physician do more care than creation. author TomT0m / talk page 19:14, 13 December 2016 (UTC)

@MisterSynergy: I removed the

⟨ scientist ⟩ subclass of (P279) ⟨ creator ⟩

claim and it seems it fixed the query. author TomT0m / talk page 19:49, 13 December 2016 (UTC)

@TomT0m: this is what I already thought of earlier this day, so I looked at the subclasses tree with this external graph tool. This is a pretty … “complex” structure for a subclass tree. Unfortunately the organization of our structural items is very difficult, and things tend to become messy by time. Removing parts of the tree is only an option to my opinion, if it was a wrong connection – but scientists are definitely creators as well. —MisterSynergy (talk) 21:14, 13 December 2016 (UTC)

@MisterSynergy: scientists are definitely creators as well : unsatisfying argument in my humble own. We need to get straight on definitions. There is stuffs that seems wrong in that subclass tree indeed, like "a singer is a creator" "a camera operator is a creator" "a physician is a creator". I'm not saying this is easy to clean but I think we should solve all those problems. There is an interwiki conflict between "scientist" and "researcher" ... I think this will get messier if we don't get good basis indeed, that is if we continue on that slope, but it's not something that has to be like that. Maybe it's time that we clean the highest levels of our ontology. author TomT0m / talk page 09:19, 14 December 2016 (UTC)

One other axis of solving might be "is creator the right root for this query ? isn't this class two broad or ill defined ? Couldn't Doctor (Q4618975)  

be a better match for this query, as any doctor as published something ? author TomT0m / talk page 09:22, 14 December 2016 (UTC)

Specific url in a specific property

Hi! i'd like to obtain a list of items having a specific url in described at URL (P973). For example, a list of items with a url starting with https://www.redib.org/recursos/Serials/Record/oai_revista in P973 (example, Hispania Nova, https://www.redib.org/recursos/Serials/Record/oai_revista1608), that url, and their titles (title (P1476) and issn (ISSN (P236)) if included (optional these last two). Thanks in advance! Strakhov (talk) 22:07, 14 December 2016 (UTC)

SELECT ?item ?title ?issn ?url WHERE {
  ?item wdt:P973 ?url .
  FILTER (SUBSTR(STR(?url), 1, 57) = "https://www.redib.org/recursos/Serials/Record/oai_revista") .
  OPTIONAL { ?item wdt:P1476 ?title . }
  OPTIONAL { ?item wdt:P236 ?issn . }
}

Try it!

—MisterSynergy (talk) 06:11, 15 December 2016 (UTC)

Thank you very much. Much appreciated! :) Strakhov (talk) 12:43, 15 December 2016 (UTC)

ORCID IDs by institution

Please can we have a query for people with ORCID iD (P496) who are associated with a specific institution (say, Harvard University (Q13371)), which may be their employer, or alma mater. The query should work where the employer or alma mater is a sub-unit of the institution in question (e.g. a specific school of a university, or a subsidiary of a company). The result should give their label, description, ORCID iD, the label of the institution, and the nature of the affiliation.

I will feed this query back to the ORCID community, with guidance notes on tweaking it for any other given institution. Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 12:24, 16 December 2016 (UTC)

What do you mean by “nature of the affiliation”? For Harvard University I can offer:

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?orcid ?organization ?organizationLabel ?organizationDescription ?p WHERE {
  ?item wdt:P496 ?orcid.
  ?item (p:P69|p:P108) [ (ps:P69|ps:P108) ?organization ] .
  ?organization (wdt:P361|wdt:P749)* wd:Q13371 .
  ?item ?p ?organization .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

—MisterSynergy (talk) 12:39, 16 December 2016 (UTC)

@MisterSynergy: Thank you. Nature of the affiliation == "employer" and/ or "alma mater". Also, could we have a similar query, for living people without an ORCID iD, per organisation? Andy Mabbett (Pigsonthewing); Talk to Andy; Andy's edits 14:37, 16 December 2016 (UTC)

Okay I understand, and extended the query above by a parameter ?p which is either wdt:P69 (for alma mater) or wdt:P108 (for employer). If necessary, I could start to look for a more descriptive output, but technically you can already distinguish the nature with this query. I also added parent organization (P749), which might be useful for non-academic organizations. I’ll write another query for the other request. —MisterSynergy (talk) 15:00, 16 December 2016 (UTC)

The other one:

SELECT DISTINCT ?item ?itemLabel ?itemDescription ?organization ?organizationLabel ?organizationDescription ?p WHERE {
  ?item (p:P69|p:P108) [ (ps:P69|ps:P108) ?organization ] .
  ?organization (wdt:P361|wdt:P749)* wd:Q13371 .
  ?item ?p ?organization .
  
  ?item wdt:P31 wd:Q5 .
  MINUS { ?item wdt:P496 ?dummy0 . }
  MINUS { ?item wdt:P570 ?dummy1 . }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

—MisterSynergy (talk) 15:06, 16 December 2016 (UTC)

Articles about Canada that has no French Wikipedia article

Hello I would like to have a little help with a query. I just found an article in German about a lake in Canada, that has no French articles in WP-fr.

I would like to have a query that list

Item: Canada (Q16)
- in property: country (P17) / country of citizenship (P27) / basin country (P205)
  - that have no articles on: French Wikipedia (Q8447)

Thanks a lot for your help! Best regards, Benoit Rochon (talk) 15:10, 14 December 2016 (UTC)

SELECT ?item (COUNT(?sitelink) AS ?cnt) WHERE {
  ?item wdt:P27|wdt:P205|wdt:P17 wd:Q16 .
  ?sitelink schema:about ?item .
  FILTER NOT EXISTS {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://fr.wikipedia.org/> .
  }
} GROUP BY ?item ORDER BY DESC (?cnt) LIMIT 1000

Try it!

This is an item list, descending by sitelink count and limited to 1000 items for performance reasons. --MisterSynergy (talk) 15:33, 14 December 2016 (UTC)

Thank you very much MisterSynergy. Really apreciated. Best Benoit Rochon (talk) 17:25, 14 December 2016 (UTC)

French WikiProjects Québec & Canada are very glad to have this query to focus on specific articles, thank you again MisterSynergy. Now just a question: is it possible to replace the item Q1XX by words (label in English, French, Spanish, or whatever)? For instance, on the top of the list, we see Q7846376 but we don't know to what article that item refers to...

This list is very useful to pick an article and create it on wp-fr ; but it would be good if we could see the label without clicking on it. I don't know if this is possible. Thank you again. Benoit Rochon (talk) 01:15, 18 December 2016 (UTC)

Here you are:

SELECT ?item ?itemLabel ?cnt WHERE {
{
  SELECT ?item (COUNT(?sitelink) AS ?cnt) WHERE {
  ?item wdt:P27|wdt:P205|wdt:P17 wd:Q16 .
  ?sitelink schema:about ?item .
  FILTER NOT EXISTS {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://fr.wikipedia.org/> .
  }
  } GROUP BY ?item ORDER BY DESC (?cnt) LIMIT 1000
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,de," }  
}

Try it!

--Edgars2007 (talk) 06:11, 18 December 2016 (UTC)

Yep, I just wanted to post something similar, so thanks Edgars! The query is somewhat expensive and I skipped labels etc. for the beginning. If it is useful, it might be a good idea to use it with Listeria in a list in your wiki, Benoit Rochon. Feel free to ask for help… —MisterSynergy (talk) 06:20, 18 December 2016 (UTC)

Thank you Edgars2007 and MisterSynergy. This is exactly what I'm looking for ! One last little thing. With this last query, the order is not the same, the first query put Gavin McInnes (34 sitelinks) on the top of the list. The second query put Johnny Devine (only 6 sitelinks) on the top of the list. And I can hardly see why... Thank you again guys. Benoit Rochon (talk) 15:42, 18 December 2016 (UTC)

Sorry, my mistake.

SELECT ?item ?itemLabel ?cnt WHERE {
{
  SELECT ?item (COUNT(?sitelink) AS ?cnt) WHERE {
  ?item wdt:P27|wdt:P205|wdt:P17 wd:Q16 .
  ?sitelink schema:about ?item .
  FILTER NOT EXISTS {
    ?article schema:about ?item .
    ?article schema:isPartOf <https://fr.wikipedia.org/> .
  }
  } GROUP BY ?item ORDER BY DESC (?cnt) LIMIT 1000
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,fr,de," }  
} ORDER BY DESC (?cnt)

Try it!

--Edgars2007 (talk) 16:10, 18 December 2016 (UTC)

Political party membership of swiss politicians

Hello everyohne. I need your help. For school i have to generate a list of swiss politicians and their political party membership(s) using SPARQL. At a first www.wikidata.org seams just fine, but my queries don't work. Here ist what I have but the result set is empty. What am I missing?

select ?person ?personLabel ?party ?partyLabel ?country

where {

?person    p:P102 ?party ; #Parteizugehörigkeit

p:P27  wd:Q39.  # Staatszugehörigkeit

SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }

}
LIMIT 100

Try it!

select ?person ?personLabel ?party ?partyLabel where {?person p:P102 ?party; p:P27 wd:Q39. SERVICE wikibase:label {bd:serviceParam wikibase:language "en"} }

Try it!

If it is “for school”, you would probably want to find the solution by yourself. Learn about the data model and which prefixes to use, e.g. by studying mw:Wikibase/Indexing/RDF Dump Format. Hint: there is a difference between p: and wdt: which is relevant here. —MisterSynergy (talk) 10:21, 20 December 2016 (UTC)

You might like this for comparison: Wikidata_talk:EveryPolitician#Wikidata_query:_occupations_of_parlamentarians --Atlasowa (talk) 09:12, 21 December 2016 (UTC)

Missing labels in a specific language compared to the English label

Probably I am not the first one, who has this problem, but I can't find a solution in the archives. I want to find for P106 with occupation Q13141064 missing labels (later on also descriptions) in other languages. For this reason it would be nice to get as result a table with item, English label and the (latin based) language to be compared with (only items where the label is missing). --Florentyna (talk) 19:00, 22 December 2016 (UTC)

SELECT ?item ?itemLabel ?itemDescription ?emptyOtherLanguageLabel WHERE {
  ?item wdt:P106 wd:Q13141064 .
  OPTIONAL {
    ?item rdfs:label ?emptyOtherLanguageLabel .
  	FILTER( LANG(?emptyOtherLanguageLabel) = "fr" ) . 
  }
  FILTER(!BOUND(?emptyOtherLanguageLabel))
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}

Try it!

Replace rdfs:label by schema:description for missing descriptions. Replace "fr" by another language code for another missing language. —MisterSynergy (talk) 20:06, 22 December 2016 (UTC)

Thank you very much - it looks good and works very well. Is it also possible to display additionally the column with the empty labels (here in FR) to see on the first view, that everything is working fine and I will not overwrite already existing data? --Florentyna (talk) 21:37, 22 December 2016 (UTC)

I changed the query to clarify what’s always empty here. The ?emptyOtherLanguageLabel is what you would change by adding French labels in this case… However, the newly added labels will not show up in the query result, instead the items will just disappear. —MisterSynergy (talk) 21:53, 22 December 2016 (UTC)

Thank you very much and also a big thank for your very fast answer. Another task is related to this, but I will give it a new heading. Florentyna (talk) 07:01, 23 December 2016 (UTC)

Item with a specific number of properties

It's possible to have a query that extract item with a specific numbers of properties? ex. item with only 2 properties or alternately a query that extract item that have only specific properties? ex. item that have only P31 and P106. --ValterVB (talk) 12:58, 23 December 2016 (UTC)

Eventually this should work:

SELECT ?item { ?item wikibase:statements 2 ; wdt:P31 [] ; wdt:P106 [] } LIMIT 10

Try it!

Currently, it only works approximately (off by 1 or 2). If you need precise counts, you'd need to recount:

SELECT ?item (COUNT(DISTINCT ?p) as ?ct) 
{  
  	{ SELECT ?item { ?item wikibase:statements 2 ; wdt:P31 [] ; wdt:P106 [] } LIMIT 1000 }
	hint:Query hint:optimizer "None" .
  	?item ?wdt [] .
	?p wikibase:directClaim ?wdt .
}
GROUP BY ?item
HAVING (?ct = 2)

Try it!

Both exclude items with multiple P106.
--- Jura 14:15, 23 December 2016 (UTC)

Not sure can be useful for my purpose, below is my query, I need item that have only P31=Q5 and have P21, alternative just report n° of claim.

SELECT DISTINCT ?item 
WHERE
{
  ?item wikibase:sitelinks 0 .
  ?item wdt:P31 wd:Q5 .
  ?item wdt:P21 ?dummy1 .
  OPTIONAL{
    ?backlink ?p1 ?item .
  }
  FILTER(!bound(?backlink))  
  OPTIONAL{
    ?item ?claim ?dummy0 .
    ?property wikibase:propertyType wikibase:ExternalId .
    ?property wikibase:directClaim ?claim
  }
  FILTER(!bound(?dummy0))
  OPTIONAL{
    ?item ?p2 [prov:wasDerivedFrom ?ref] .
    ?ref ?pr [] .
  }  
  FILTER(!bound(?ref))
}
Limit 100

Try it!

--ValterVB (talk) 15:04, 23 December 2016 (UTC)

SELECT ?item (COUNT(DISTINCT ?p) as ?ct) 
{  
  	{ SELECT ?item { 	?item wikibase:statements 2 
                              ; wikibase:sitelinks 0 
                              ; wdt:P31 wd:Q5 
                              ; wdt:P21 [] 
                   } LIMIT 1000 }
	hint:Query hint:optimizer "None" . 	# avoid timeout on wikibase:claim / ?sp 
  	?item ?sp ?claim .  				# double-check wikibase:statements
	?p wikibase:claim ?sp .				# double-check wikibase:statements
  	OPTIONAL{ ?backlink ?s ?item } 					FILTER(!BOUND(?backlink)) 
  	OPTIONAL{ ?claim prov:wasDerivedFrom ?ref . ?ref ?p ?v . FILTER NOT EXISTS { ?v wdt:P31 wd:Q10876391 } }  	FILTER(!BOUND(?ref)) # exclude no source and wikipedia only source
  	# OPTIONAL{ ?claim prov:wasDerivedFrom ?ref }  	FILTER(!BOUND(?ref)) # exclude any reference
  	OPTIONAL{ ?art schema:about ?item } 			FILTER(!BOUND(?art))	# double-check wikibase:sitelinks
}
GROUP BY ?item
HAVING (?ct = 2)	# double-check wikibase:statements

Try it!

Maybe like this?
--- Jura 17:34, 23 December 2016 (UTC)

Perfect, thanks :) --ValterVB (talk) 18:01, 23 December 2016 (UTC) *<|:-)>

Comparison of labels in different languages

Aim of this task is to find out the correctness of Chinese labels by comparison. The following columns would be needed: Item, label Chinese simple, label Chinese traditional. It should be displayed only items, where at least one of the Chinese versions exists. Items, where both labels are missing should not be displayed (again for occupation badminton player like in the case before). Florentyna (talk) 07:01, 23 December 2016 (UTC)

Hello again. I dared to set up a slightly different query which I think could be more useful for your task:

SELECT ?item ?itemLabel ?numberOfDifferentChineseLabels WHERE {
  {
    SELECT ?item (COUNT(DISTINCT ?plainlabel) AS ?numberOfDifferentChineseLabels) WHERE {
      {
        SELECT ?item WHERE {
          ?item wdt:P106 wd:Q13141064 .
        }
      }
      OPTIONAL {
        ?item rdfs:label ?label .
        BIND(STR(?label) AS ?plainlabel) .
      }
      FILTER( SUBSTR(LANG(?label), 0, 2) = "zh" ) . 
      FILTER( BOUND(?label) ) .
    } GROUP BY ?item
  }
  FILTER(?numberOfDifferentChineseLabels > 1) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} ORDER BY DESC(?numberOfDifferentChineseLabels)

Try it!

It compares all existing labels whose language code starts with zh (which are at least zh, zh-cn, zh-hk, zh-tw, zh-sg, zh-hans, zh-hant, zh-mo, and zh-my) and outputs all items in which more than one “Chinese label” variant is found. If all existing Chinese labels are identical, the item is not listed. HTH, otherwise feel free to ask for another query. Btw. I think systematic usage of the property name in native language (P1559) on badminton player items would be a good idea. —MisterSynergy (talk) 07:47, 23 December 2016 (UTC)

Thank you very much, this a good starting point. It helps to identify correct labels if the number is equal 2. The remaining problem is, that sometimes all labels must be the same, sometimes there is a slight difference between traditional and simple Chinese. To identify, if the labels are correct, it would be helpful to get a list with the columns of two (or more) Chinese versions (like zh-cn vs. zh-hk) to read the labels one beside the other. This helps to see, if the correct version of Chinese is used. PS: Two more Chinese variants would be yue and Wu Chinese. Regarding the property name in native language (P1559) I already started to add this manually, but it would be better to use quick_statements. But there I was not able to identify, which property I must use for the mandatory language. I'm sure you know it... Thanx again. --Florentyna (talk) 13:33, 23 December 2016 (UTC)

@Florentyna: it is a bit difficult to compile a useful list. I now tried

SELECT ?item ?itemLabel ?plainlabel (GROUP_CONCAT(?lang; separator=", ") as ?langs) WHERE {
  {
    SELECT ?item WHERE {
      ?item wdt:P106 wd:Q13141064 .
    }
  }
  OPTIONAL {
    ?item rdfs:label ?label .
    BIND(STR(?label) AS ?plainlabel) .
    BIND(LANG(?label) AS ?lang) .
  }
  FILTER( REGEX(LANG(?label), '^(zh.*|yue|wuu)$') ) . 
  FILTER( BOUND(?label) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
} GROUP BY ?item ?itemLabel ?plainlabel ORDER BY ASC(?item)

Try it!

This does not yet include the two Chinese language codes you've mentioned, and I don't know whether the list should receive more filtering. Please let me know...

Regarding quick_statements: AFAIK something like Q4115189<TAB>P1559<TAB>en:"Name" should work (adding the English monolingual text "Name" to P1559 of Q4115189 in this case). You could try that with Wikidata Sandbox (Q4115189) before modifing real items. Regards, --MisterSynergy (talk) 12:54, 24 December 2016 (UTC)

Thank you very much for the solutions to my requests. By using FILTER( LANG(?label) = "zh-hant" ) and wikibase:language "zh-hans" I am now also able to compare 2 languages of interest. I hope the only one remaining question for this year is, if it is possible to add yue and wuu to your code above? Merry Christmas Florentyna (talk) 22:36, 24 December 2016 (UTC)

Sure it is; I've updated the query to include these language codes as well. --MisterSynergy (talk) 22:49, 24 December 2016 (UTC)

Thanks again for your quick answer. Based on your codes I am now able to do some programming by myself by varying your solutions. But I am sure the next problems will come... Best regards --Florentyna (talk) 00:15, 25 December 2016 (UTC)

Pokémon not having egg group

I don't know what I'm doing :D I'd like to create a query that lists all Pokémon that doesn't have an item about its Egg group (Q26037540). I created this query but I'm not sure it is correct:

SELECT ?no ?item ?itemLabel
WHERE
{
    ?item wdt:P31 ?type.
    ?type wdt:P279 wd:Q3966183.
      ?item p:P1112 ?no .
      ?no pq:P642 wd:Q20005020.
 
      MINUS {
      ?item wdt:P31 ?type.
      ?type wdt:P279 wd:Q3966183.
      ?item wdt:P361 ?egg.
      ?egg wdt:P279 wd:Q26037540.
    }
 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "it" }
}
ORDER BY ?no

Try it!

First of all, items aren't ordered by National Pokédex number. Then, many items are inserted twice as those Pokémon have two types. Would someone help me? :) --★ → A ir on 90 10:27, 28 December 2016 (UTC)

@Airon90: To get the items orderd by National Pokédex number you need to return this number. In you query, ?no isn't the number but a statement. To get the number, you need to replace line 6 and 7 by

?item p:P1112 ?statement .
?statement ps:P1112 ?no;
           pq:P642 wd:Q20005020.

To remove double entries, write

SELECT DISTINCT ?no ?item ?itemLabel

--Pasleim (talk) 22:37, 28 December 2016 (UTC)

Thank you but it seems not to be the correct query for me. It returns all Pokémon but Ivysaur (Q1636903)

SELECT DISTINCT ?no ?item ?itemLabel
WHERE {
&nbsp;&nbsp;&nbsp; ?item wdt:P31 ?type.
&nbsp;&nbsp;&nbsp; ?type wdt:P279 wd:Q3966183.
&nbsp;&nbsp;&nbsp; ?item p:P1112 ?statement .
&nbsp;&nbsp;&nbsp; ?statement ps:P1112 ?no;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; pq:P642 wd:Q20005020.
&nbsp;&nbsp;&nbsp; MINUS {
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?item wdt:P31 ?type.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?type wdt:P279 wd:Q3966183.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?item wdt:P361 ?egg.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ?egg wdt:P279 wd:Q26037540.
&nbsp;&nbsp;&nbsp; }
&nbsp;
&nbsp;&nbsp;&nbsp; SERVICE wikibase:label { bd:serviceParam wikibase:language "it" }
}
ORDER BY ?no

Try it!

--★ → A ir on 90 22:57, 28 December 2016 (UTC)

There is a problem with the ranks. wdt: always only returns values with the best rank. Since some Pokemons have statements with preferred ranks, the normal ranks are ignored. To fix this, one can replace wdt:P361 by p:P361/ps:P361. Simplifying results in

SELECT DISTINCT ?no ?item ?itemLabel WHERE {
  ?item wdt:P31/wdt:P279 wd:Q3966183.
  ?item p:P1112 ?statement .
  ?statement ps:P1112 ?no;
             pq:P642 wd:Q20005020.
  MINUS {
    ?item p:P361/ps:P361/wdt:P279 wd:Q26037540.
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "it" }
} ORDER BY ?no

Try it!

This still returns many items but I think that the part of egg group statements weren't yet added to many items. --Pasleim (talk) 23:26, 28 December 2016 (UTC)

I didn't think that so much Pokémon don't have an egg group statement! Thank you very much for your help! I learn more and more how to make a query ;) --★ → A ir on 90 08:42, 29 December 2016 (UTC)

Including items with statements only in deprecated rank

[2] Is there a way to include the items that have an item in head coach (P286) with deprecated rank? I mean if the item had only one value with P286 and this value is in deprecated rank, is must included in my results. If the item has two values, one deprecated and the other not deprecated (normal or preferred) then the item should not be included be in my results.

For example, Doxa Katokopias F.C. (Q1253472) must be included on my results. It has one value for P286 but with deprecated rank. Xaris333 (talk) 02:22, 31 December 2016 (UTC)

I think I answered it at Wikidata:Project_chat#Help_for_queries. Sorry for the delay.
--- Jura 10:41, 31 December 2016 (UTC)