Wikidata:Request a query

Request a query Fishing in the Wikidata river requires both an idea where to look for fish and a suitable fishing method. If you have the former, this page can help you find the latter. This is a page where SPARQL 1.1 Query Language (Q32146616) queries can be requested. Please provide feedback if a query is written for you. For sample queries, see Examples and Help:Dataset sizing. Property talk pages include also summary queries for these. For help writing your own queries, or other questions about queries, see Wikidata talk:SPARQL query service/queries and Wikidata:SPARQL query service/query optimization. Help resources about Wikidata Query Service (Q20950365) and SPARQL: Wikidata:SPARQL query service/Wikidata Query Help and Category:SPARQL. To report an issue about the Query Service (interface, results views, export...) please see Wikidata:Contact the development team/Query Service and search.

Translate this header box! Start a new discussion

On this page, old discussions are archived. An overview of all archives can be found at this page's archive index. The current archive is located at 2024/06.

Ships with multiple operators that do not have multiple official names

Ships often change name when they change operator, and this is currently weakly modelled with label and aliases. I'm trying to formalise this using official name (P1448) with operator (P137) qualifiers. USS Chesapeake (Q1300790) is an example.

To help with this I'd like a list of items which are instances of frigate (Q161705), have 2 or more operator (P137), but don't have a matching official name (P1448) with an operator (P137) qualifier for each of them. Vicarage (talk) 05:50, 21 October 2023 (UTC)

@Vicarage: This seems to be the basic list.

SELECT distinct ?item ?itemLabel WHERE {
  ?item wdt:P31 wd:Q161705 .
  ?item wdt:P137 ?operator1.
  ?item wdt:P137 ?operator2.
  filter (str(?operator1) != str(?operator2) )
  filter not exists {?item p:P1448/pq:P137 ?operator1 . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }  
} order by ?itemLabel

Try it!

--Tagishsimon (talk) 11:43, 21 October 2023 (UTC)

excellent, though I see with 166 items that are just frigates, I've got lots of things to fix! Vicarage (talk) 11:59, 21 October 2023 (UTC)

Worse than that, we can track your progress. 111 just now, so that's not too shabby. Carry on. --Tagishsimon (talk) 13:47, 26 October 2023 (UTC)

Hierarchical list of subclasses

Dear all, is there a way to make SPARQL not only list subclasses of an entity, but rather depict how they are connected? While searching for subclass of (P279)manuscript (Q87167) (there are 187 at the moment) I am getting lots of results that are subclasses of subclasses of manuscript (Q87167), or perhaps triple or quadruple subclasses. I would like a more efficient way of finding out the current "taxonomy", however messy it is.

My original query:

SELECT DISTINCT ?item ?itemLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item WHERE {
      ?item p:P279 ?statement0.
      ?statement0 (ps:P279/(wdt:P279*)) wd:Q87167.
    }
    LIMIT 200
  }
}

Try it!

As always, your help is very much appreciated. Cheers, Jonathan Groß (talk) 06:52, 21 October 2023 (UTC)

Never mind, I found this tool: https://wikidata-todo.toolforge.org/tree.html?q=87167&rp=279 Jonathan Groß (talk) 09:44, 21 October 2023 (UTC)

There is also this "Wikidata Graph Builder" tool which I find often useful: https://angryloki.github.io/wikidata-graph-builder/?item=Q87167&property=P279&mode=reverse ---MisterSynergy (talk) 11:18, 21 October 2023 (UTC)

Norwegian mayors elected in the 2023 local elections

May I have a list of person holdng the position mayor of a place in Norway (Q87008012) elected in the Municipal and county council elections 2023 in Norway. The persons will all have the qualifier start time (P580) for position held (P39) with mayor of a place in Norway (Q87008012) and subclasses. date of birth (P569), residence (P551) and member of political party (P102) should also be appriciated Pmt (talk) 21:23, 23 October 2023 (UTC)

@Pmt: This?

SELECT ?item ?itemLabel ?start ?position ?positionLabel ?dob ?residence ?residenceLabel ?party ?partyLabel
WHERE 
{
  ?item p:P39 ?stat . 
  ?stat ps:P39 ?position . 
  ?position wdt:P279 wd:Q87008012.
  ?stat pq:P580 ?start .
  filter(year(?start)=2023)
  optional {?item wdt:P569 ?dob .}
  optional {?item wdt:P551 ?residence .}
  optional {?item wdt:P102 ?party .}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],nn,en". }
}

Try it!

--Tagishsimon (talk) 21:52, 23 October 2023 (UTC)

Thanks a lot. Works perfect Pmt (talk) 18:40, 24 October 2023 (UTC)

Mapping toponyms organized in their linguistic family

Amazing people, I am trying to classify and map municipality of Colombia (Q2555896) into to their language family (Q25295). I understand that every city and town in wikidata, that has the municipality of Colombia as instance, will be contained inside the list. These toponym (Q7884789) contains important information about the linguistic root that will help classify each toponym inside their corresponding linguistic family. There is a property called native label (P1705) that may work for this, but there is no consistency in how this property is used, or maybe, the thing that confuses me the most is that I can't find a connection between the toponym and some data that tells something about its language family.

I think that there is another issue with where to put or where to organize the linguistic information of toponyms. For example, Chía https://es.wikipedia.org/wiki/Ch%C3%ADa_(Colombia) in the Spanish version, when referring to its linguistic family, it writes about "Toponimia", while in the English version, when referring to its language family Chibchan (Q520478), that information is written inside the "Etymology" group. I don't understand how to handle these variations with SPARQL. Actually, I am new into SPARQL and I don't understand how to formulate a proper query with these topics and areas of knowledge.

I also found the Lexicographical data https://www.wikidata.org/wiki/Wikidata:Lexicographical_data/Documentation that can open another way to organize toponyms into linguistic properties, but I am not sure if that can work.

Also, a bonus question, I don't understand why some items like Chía (Q1093102) can be edited, and others like Bogotá (Q2841) that can't.

I thank you all for reading and, if you can help me, for answering. Duityors (talk) 00:55, 25 October 2023 (UTC)

@Duityors: I fear that Wikidata may have little or none of the etymological information on toponyms that you seek. In the Chía example you cite, the information of interest is in the ES Wikipedia article, and is not in Wikidata. There is no SPARQL query which will grab the information from the wikipedia article. Wikidata does have, as you note, native label (P1705), but for municipality of Colombia (Q2555896) items all of the data seems to be in Spanish and so WD does not point to, for instance, the Muisca language of Chía; see the query below which deals only in municipalities that have P1705 data.

You are right to point at wikidata's Lexicographical data; almost certainly this is where toponym to linguistic family information should be found. But a) I tend to doubt that there is anything at all there, right now, for the municipality toponyms and b) I'm afraid I have never interested myself in the Lexicographical data structure and so cannot be your guide there.

Finally, some WD items are protected such that only editors with a certain number of edits and who have had accounts for a certain amount of time can edit them. I don't think either figure is very great, so, stick around, do more edits, you will be able to edit both articles.

SELECT ?item ?itemLabel ?P1705 ?lang
WHERE 
{
  ?item wdt:P31 wd:Q2555896.
  ?item wdt:P1705 ?P1705. bind(lang(?P1705) as ?lang) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 01:16, 25 October 2023 (UTC)

@Tagishsimon, thank you for the query and for your answers. In short words, you are telling me that the query and classification that I am looking for is possible, only if I manage a model to link each toponym to a etymological or toponymic value?

What I think could work is to edit the following info in WD:

1. Make shure each toponym is under the municipality of Colombia instance.

2. Add a new Statement of "native label" to each toponym that doesn't have.

3. Add, inside each "native label" property, the value of "language family" or directly put, in the case of Chía, the Chibchan (Q520478) value inside it. (Is this even possible?)

I think this method still has problems. I don't understand how can a word, in this case, a toponym, can hold linguistic information. Probably this concerns more to the Lexeme area. Is there a place where I can ask all these existential questions?

Thank you again Tagishsimon! Have a nice day! Duityors (talk) 13:27, 25 October 2023 (UTC)

@Duityors: Yes, that's a fairly good summary; you will need to find a way to build a model of the linguistic family of the tomonyms. However there is a constraint which you will have to work around, which is that properties such as official name (P1448), native label (P1705) and name (P2561) are of the wikidata type 'monolingual string', requiring that a language be associated with them; and the set of languages that can be associated with them is not as granular as you would wish - so Chibchan (Q520478) is not available, for instance. As a suggestion, you can choose 'unsupported language (mis)' and then qualify the statement with language of work or name (P407) taking the value Chibchan (Q520478).

Perhaps go to Wikidata:Project chat to ask questions about lexeme support; it is perhaps the most likely place for a question to be noticed. --Tagishsimon (talk) 00:28, 26 October 2023 (UTC)

@Tagishsimon, thank you again for your collaboration. I've been trying to find a way to link the linguistic info with each toponym but I haven't found a proper way that also respects the fields of knowledge and categories that science has created to organize this data . I am using the property you mention (language of work or name (P407)), applying it to each municipality and adding a specific value of a language family to each. I still think that the "Language of work or name" is not exactly a property linked with onomastics, toponymy nor language studies, but for now, it works for me. I think there should be a property that carries the "linguistic data" of a word or in this case, a place name. Probably, I will mention this in the Property Proposal discussion.

In the Lexemes chat nobody answered.

This is the SPARQL I did, based in the one you shared. I am only working with 11 item for now.

SELECT ?item ?itemLabel ?linguisticFamily
WHERE 
{
 ?item wdt:P31 wd:Q2555896.
 ?item wdt:P407 ?linguisticFamily.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". 
}

Try it!

Please, how can I add the coordinate location (P625) to each toponym that can be found on this list? After adding that, is it possible to visualize this information in a map? I have tried bu I can't figure it out. Thank you!

Thank you again, and have a nice day! Duityors (talk) 15:34, 30 October 2023 (UTC)

Properties of Paintings

Painting (i.e., "visual artwork, surface artistically covered with paint") is Q3305213. I want to download Wikimedia's paintings and certain related data fields (probably artist, title, and date) within the Painting dataset. But this is not the nature of Wikidata's Properties concept. That concept seems to consist of hit-or-miss groupings of Paintings (e.g., Paintings by Gustav Klimt). How can I identify the data fields available for Paintings, or what kind of query should I be trying to construct for this purpose? Thanks in advance. Raywood1 (talk) 01:15, 26 October 2023 (UTC)

@Raywood1: If I understand you, something like this, which shows the properties used as statements in the (first 200,000) items which have P31=Q3305213.

SELECT ?property ?propertyLabel ?propertyType ?count WITH { 
  SELECT ?property (count(distinct ?item) as ?count)  WHERE {
    SERVICE bd:slice {
      ?item wdt:P31 wd:Q3305213.
      bd:serviceParam bd:slice.offset 0 . # Start at item number (not to be confused with QID)
      bd:serviceParam bd:slice.limit 200000 . # List this many items
    }
    hint:Prior hint:runFirst true .
    ?item ?predicate ?value .
    ?property wikibase:directClaim ?predicate .
  } group by ?property  } as %i
WHERE
{
  INCLUDE %i
  ?property wikibase:propertyType ?propertyType .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
} order by desc(?count)

Try it!

--Tagishsimon (talk) 01:32, 26 October 2023 (UTC)

Thank you for that.

But now I am puzzled. The results consist of 635 apparently unique Properties, none of whose labels seem to match the fields of interest in Wikimedia. A sample Wikimedia page names fields such as Artist, Title, and Date. Nothing exactly matching those three appears in the output of this code. There is a partial exception for Title, but the number of records stated in that case is insufficient for the number of paintings in the database.

I seem to be misunderstanding something basic about the Wikidata structure. 2603:8081:4B01:5CBA:9904:B7EA:9463:A316 06:37, 26 October 2023 (UTC)

The information in your example - A sample Wikimedia page - is held in a template on the text page associated with an image in wikimedia commons. The properties in the above report pertain to items about paintings on wikidata. So those are two completely different things. The wikidata query engine has little or no ability to report on information held in templates on text pages. Wikimedia commons has its own structured data, but it is less well developed than that of wikipedia; in your Munch example the structured data amounts to three triples with predicates 'depicts', 'digital representation of' and 'main subject' each taking the argument 'woman picking fruit'. (And to try to be clearer still, you are dealing with two entirely different and mostly separate things here: (1) wikidata, essentially an RDF triplestore, or in other words, a database; and (2) Commons, a repository of mainly image files. Wikidata has information on paintings, and may point to an instance of the painting on Commons. Commons, meanwhile, has textual descriptions of its images, which are most often structured by templates which provide a table structure with defined rows for various facets - title, date, creator, &c), and Commons has (some) structured data on images in RDF format, distinct from that held on wikidata, albeit it draws on wikidata for certain of its values.)

If it is your aim to download images from Commons, and it is your wish to download corresponding information from the text page associated with the image, then you're probably going to have to put together your own scraping code. Presumably many of the images will use the Template:Artwork, so study of that will give you some clues as to the 'fields' likely to be found in the text page. Good luck.

There are two and a half other routes available to you, which are to work from wikidata, or find the wikidata item corresponding to the commons image (if there is such an item), or to see what commons structured data has to offer. Again, working from your example, wikidata has the item record Women Picking Fruit (Q18891458) and that a) points to the commons file and b) has a handful of structured data. It would be well possible to create a report on wikidata items for paintings that point to commons images, such that you could abstract the wikidata information on the image, including the commons filename, which would allow you to download the corresponding commons image. --Tagishsimon (talk) 08:30, 26 October 2023 (UTC)

Populating the operators of ship classes from the operators of individual ships

I want list destroyers which have an operator which does not exist in the vessel class's list of operators, so I can propagate the information. The problem is my query below is it finds USS Converse (Q2469026) which was operated by the Spanish Navy (Q676404) and reporting it even though its class Fletcher-class destroyer (Q1428155) says the same thing. I think this is because the class has also a different operators which are confusing it, and me.

SELECT DISTINCT ?item ?itemLabel ?class ?classLabel ?operatorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item ?class ?operator WHERE {
      ?item wdt:P31 wd:Q174736.
      ?item wdt:P137 ?operator.
      ?item wdt:P289 ?class.
      MINUS {?class wdt:P137 ?operator}
    }
    LIMIT 100
  }

Try it!

Vicarage (talk) 09:09, 27 October 2023 (UTC)

@Vicarage: Although I'm not entirely sure what your report is supposed to be doing, the fail on USS Converse (Q2469026) is that its class shows the US Navy as an operator with preferred rank, so MINUS {?class wdt:P137 ?operator} sees only the US navy and not the Spanish navy. Changing that clause to p:P137/ps:P137 might fix it, if my supposition is right. (And thinking on, you might want to use p:P137/ps:P137 on the item clause - it returns all statements, not merely BestRank statements.)

SELECT DISTINCT ?item ?itemLabel ?class ?classLabel ?operatorLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
  {
    SELECT DISTINCT ?item ?class ?operator WHERE {
      ?item wdt:P31 wd:Q174736.
      ?item wdt:P137 ?operator.
      ?item wdt:P289 ?class.
      MINUS {?class p:P137/ps:P137 ?operator .}
    }
    LIMIT 100
  }
  }

Try it!

--Tagishsimon (talk) 10:03, 27 October 2023 (UTC)

I did wonder if it was the preferred rank upsetting things. Always tricky for ships classes, if you run 200 destroyers, but your enemy captures one and rebadges it for a couple of years, you might want a preference, but its a bit mean if some little country bought one as their pride and joy! I've been adding country of origin (P495) (often based on manufacturer (P176)) to give more selection control. Thanks for looking into this. Vicarage (talk) 10:14, 27 October 2023 (UTC)

I get the impression there's a lot of misuse of rank on WD. --Tagishsimon (talk) 11:33, 27 October 2023 (UTC)

List only the highest value

I would like to have this query returning only the highest number of followers for each person:

SELECT DISTINCT ?personLabel ?followers
WHERE
{
?person wdt:P106 wd:Q937857 .
?person wdt:P8687 ?followers .
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
} GROUP BY ?personLabel ?followers
ORDER BY DESC (?followers)

Try it!

And if possible an extra column showing which channel the number is derived from. Cavernia (talk) 22:33, 28 October 2023 (UTC)

@Cavernia: This, I think:

SELECT ?personLabel ?followers ?channelLabel WHERE {
  ?person p:P8687 ?stat . 
  ?stat ps:P8687 ?followers .
  ?stat ?predicate ?value . 
  ?property wikibase:qualifier ?predicate .
  ?property wdt:P1629 ?channel .
  filter not exists {wd:P585 wikibase:qualifier ?predicate.}
  {
    SELECT DISTINCT ?person (max(?followers_) as ?followers) WHERE
    {
    ?person wdt:P106 wd:Q937857 .
    ?person wdt:P8687 ?followers_ .
    } GROUP BY ?person
  } hint:Prior hint:runFirst true .
  SERVICE wikibase:label {bd:serviceParam wikibase:language "en" .}
} ORDER BY DESC (?followers)

Try it!

--Tagishsimon (talk) 23:26, 28 October 2023 (UTC)

@Tagishsimon:Beautiful, thanks a lot! --Cavernia (talk) 08:55, 31 October 2023 (UTC)

Randomly (?) working query

This query works fine to research data on people such as Cristiano Ronaldo, Angela Merkel or Alice Cooper, but it doesn't for other people (e.g. finds nothing for Eileen Bennett, finds Serena William when searching for Bob Ross). Can anybody fix it and help me understand the problem?

SELECT DISTINCT ?item ?itemLabel ?genderLabel ?giorno_nascita ?mese_nascita ?anno_nascita ?image
   WHERE {
     ?item ?label "Cristiano Ronaldo";
           wdt:P31 wd:Q5;
           wdt:P21 ?gender;
           wdt:P569 ?dob.
     BIND(day(?dob) as ?giorno_nascita).
     BIND(month(?dob) as ?mese_nascita).
     BIND(year(?dob) as ?anno_nascita).
     
     OPTIONAL {?item wdt:P18 ?image.}
     MINUS {
       ?item p:P570 ?statement_1.
       ?statement_1 psv:P570 ?statementValue_1.
       ?statementValue_1 wikibase:timeValue ?P570_1.
     }
   SERVICE wikibase:label {bd:serviceParam wikibase:language "it"}
 }

Try it!

Aethelfirth (talk) 18:22, 29 October 2023 (UTC)

@Aethelfirth: The query is not doing what you think it's doing. ?item ?label "Cristiano Ronaldo"; is not finding items with a label of "Cristiano Ronaldo", not least since labels are monolingual strings and so in a triple would be addressed as (for an English monolingual string, such as a label) ?item ?label "Cristiano Ronaldo"@en; . The Bob Ross thing was some vandalism in the Serena Williams article - an external ID which existed as a string.

So, this works:

SELECT DISTINCT ?item ?itemLabel ?genderLabel ?giorno_nascita ?mese_nascita ?anno_nascita ?image
   WHERE {
     ?item ?label "Serena Williams"@en;
           wdt:P31 wd:Q5;
           wdt:P21 ?gender;
           wdt:P569 ?dob.
     BIND(day(?dob) as ?giorno_nascita).
     BIND(month(?dob) as ?mese_nascita).
     BIND(year(?dob) as ?anno_nascita).
     
     OPTIONAL {?item wdt:P18 ?image.}
     MINUS {
       ?item p:P570 ?statement_1.
       ?statement_1 psv:P570 ?statementValue_1.
       ?statementValue_1 wikibase:timeValue ?P570_1.
     }
   SERVICE wikibase:label {bd:serviceParam wikibase:language "it"}
 }

Try it!

More generally, ?item rdfs:label "Serena Williams"@en properly targets itemLabels - rdfs:label is the predicate for labels - whereas ?item ?label "Serena Williams" looks for any triple in her item which has a string (not monolingual string) object value of "Serena Williams". Umm. Does that help at all? --Tagishsimon (talk) 18:36, 29 October 2023 (UTC)

It's maybe also worth noting the MWAPI search approach to finding items based on a text string, fwiw ... it'll find more items, and deal with cases where, for instance, the string you're looking for is in the alias list or has an intervening middle name missing from your search argument:

SELECT DISTINCT ?item ?itemLabel 
WHERE {
  hint:Query hint:optimizer "None".
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api "Search";
                    wikibase:endpoint "www.wikidata.org";
                    mwapi:srsearch "Serena Williams".
    ?item wikibase:apiOutputItem mwapi:title .
  }
  ?item wdt:P31 wd:Q5.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

--Tagishsimon (talk) 00:43, 30 October 2023 (UTC)

SPARQL queries to identify different categories of people contained within a set of interviews

Hello, I’m getting stuck with some SPARQL queries and wondering if someone could help me learn how to create them.

They all relate to this project I’m currently working on https://www.wikidata.org/wiki/Wikidata:WikiProject_LSESuffrageInterviewsProject

Firstly, I was looking to create a list of main subjects of the interviews, but only where the main subject is a person.   

I’ve created this query - https://w.wiki/7yks - but it contains all the subjects, and I was hoping to just list people who are included as a subject.  Is that possible?

Next I was hoping to create SPARQL queries to list people who fall into different categories within the project.

People who were notable in the suffrage movement and were interviewed about themselves will have an interview where their name is in the P710 participant, object has role interviewee and is also in the P921 main subject statement.  Is it possible to write a SPARQL query that can do that?  I made a start, but don’t know how to continue.  https://w.wiki/7ymF

People who were notable in the suffrage movement where a friend or family member was interviewed about them will have an interview where their name is in the P921 main subject statement, but that name is not the same as the name in the P710 participant, object has role interviewee.

And conversely I was hoping to create a list of people who were not notable in the suffrage movement, but were interviewed about a friend or family member who was.

Those people will have an interview where their name is in the P710 participant, object has role interviewee, but is not in the P921 main subject.

Is there a way to write queries that would pick the data up in this way?

Any advice will be gratefully received. Thanks!

HelsKRW (talk) 11:35, 31 October 2023 (UTC)

@HelsKRW: All well possible; these, probably:

1. List of main subjects of the interviews, but only where the main subject is a person. 

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 ;             # item is an interview
    wdt:P921 ?person .               # main subject...
  ?person wdt:P31 wd:Q5.           # ...is a person
  OPTIONAL { ?person wdt:P734 ?familyname }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

People who were notable in the suffrage movement and were interviewed about themselves will have an interview where their name is in the P710 participant, object has role interviewee and is also in the P921 main subject statement.

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 .             # item is an interview
   ?item p:P710 ?statement .         # item has a participant statement
   ?statement ps:P710 ?person .      # participant is ?person
   ?statement pq:P3831 wd:Q55534929. # qualifier object has role is interviewee
   ?item wdt:P921 ?person  .         # main subject is same as participant
   OPTIONAL { ?person wdt:P734 ?familyname }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

List of people who were not notable in the suffrage movement, but were interviewed about a friend or family member who was; those people will have an interview where their name is in the P710 participant, object has role interviewee, but is not in the P921 main subject.

I've thrown in the subject as well, but left the family name as the interviewee.

SELECT DISTINCT ?item ?itemLabel ?person ?personLabel ?familynameLabel ?subjectLabel WHERE {
  
  ?item wdt:P5008 wd:Q117322976;     # item on focus list of the LSESuffrageInterviewsProject
    wdt:P31 wd:Q178651 .             # item is an interview
   ?item p:P710 ?statement .         # item has a participant statement
   ?statement ps:P710 ?person .      # participant is ?person
   ?statement pq:P3831 wd:Q55534929. # qualifier object has role is interviewee
   filter not exists {?item wdt:P921 ?person .}        # main subject is not same as participant
   ?item wdt:P921 ?subject .         # main subject is ...
   ?subject wdt:P31 wd:Q5 .          # ...a person
   OPTIONAL { ?person wdt:P734 ?familyname }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY (?familynameLabel)

Try it!

--Tagishsimon (talk) 14:00, 31 October 2023 (UTC)

Thank you so much @Tagishsimon. HelsKRW (talk) 15:14, 31 October 2023 (UTC)

I was so close on the first one I feel that I should have been able to get that final step! Useful learning for next time, thank you. HelsKRW (talk) 15:14, 31 October 2023 (UTC)

Yes. It's not obvious that you can do that sort of thing until it is. The interview dataset looked excellent; exemplary work again. --Tagishsimon (talk) 15:18, 31 October 2023 (UTC)

Thank you! It's an experimental project and has taken a lot to get my head round the data structure. Just made a late change to my original plan last week in order to be able to pull distinct categories of people out of the dataset! We're still working on it, so a bit more data to be added and then I'll move onto next stages. It's been really interesting and I hope the data will be really useful once it's all pulled together. HelsKRW (talk) 15:24, 31 October 2023 (UTC)

There a set of UK diplomats interviewing UK diplomats data somewhere which I've used before. I may copy your model. Fascinating subject-matter for those like me interested in this aspect of government. --Tagishsimon (talk) 15:30, 31 October 2023 (UTC)

I could do with a like button! That sounds really interesting. I had a look at some other interview data before I started modelling ours, but I didn't come across that set. HelsKRW (talk) 15:34, 31 October 2023 (UTC)

The British Diplomatic Oral History Programme - https://bdohp.chu.cam.ac.uk/ --Tagishsimon (talk) 15:45, 31 October 2023 (UTC)

For people who were notable in the suffrage movement and were interviewed about themselves, where they are both the participant, and the main subject statement in the Qid metadata - I'm just trying to get my head round how the SPARQL is pulling out those double instances of names in both statements - is it by identifying the participant as a statement, then narrowing down that statement to a person - and is it then picking up that same person in the P921 ?person? HelsKRW (talk) 15:23, 31 October 2023 (UTC)

Yes and no. A row of data qualifies to be displayed in the output table if the value of ?person associated with P710 is the same as the value of ?person associated with P921. And so here we did not need to check if the main subject was a Q5 human b/c the participant had to be a Q5 human. --Tagishsimon (talk) 15:27, 31 October 2023 (UTC)

I'm with you...thank you for explaining. I'm trying to learn more about the SPARQL as I go so that I'll be able to get further by myself next time, so it's really helpful to get my head round it. HelsKRW (talk) 15:36, 31 October 2023 (UTC)

Like. --Tagishsimon (talk) 15:45, 31 October 2023 (UTC)

Query in Wikimedia Commons structured data

Hello, may I ask for a wikidata-query to search for all Wikimedia Files that have collection (P195) with value Swedish Theatre Helsinki Archive (Q123246077), and to return the file's properties depicts (P180) and creator (P170). Thank you! Robert (talk) 11:18, 1 November 2023 (UTC)

Actually, I realized how the query is done (https://w.wiki/7zdt), but how do I get the label from wikidata for the Q-values? Robert (talk) 11:26, 1 November 2023 (UTC)

I managed to figure it out: https://w.wiki/7zeb . The examples have become so much better lately. Thank you! Robert (talk) 11:55, 1 November 2023 (UTC)

Maps with bicycle stand picture in Verona - Italy

I would like to make a query to return the photos containing the subject "bicycle stand", in the municipality of Verona. I would then like to visualize them on a map

Thanks Giubarba (talk) 16:30, 2 November 2023 (UTC)

Not looking very much like Commons has any.

SELECT DISTINCT ?url ?coord WHERE {
  ?file wdt:P180 wd:Q1392526 .
  ?file schema:url ?url .
  ?file wdt:P276 wd:Q2028. # location = Verona
}

Try it!

SELECT DISTINCT ?url ?coord WHERE {
  ?file wdt:P180 wd:Q1392526 .
  ?file schema:url ?url .
  OPTIONAL {?file wdt:P625 ?coord.} # can we use coords? no.
}

Try it!

--Tagishsimon (talk) 16:44, 2 November 2023 (UTC)

Thank you, works!

Does anyone know if there is a service available where you can paste this query and have a map? I found it but I don't remember it. Giubarba (talk) 16:59, 2 November 2023 (UTC)

You can get a map from WDQS and WCQS, but you need a set of data which has coordinates, and the problem here is that the bicycle rack dataset has no coordinates (at least, no P625 coords in its structured data). --Tagishsimon (talk) 17:12, 2 November 2023 (UTC)

I'm taking photos of the bike racks. Each photo has the coordinate in the "exif data". I had found an OSM-based site, where it was possible to paste a query, and all the placeholders matching that query were shown. Giubarba (talk) 17:15, 2 November 2023 (UTC)

SELECT DISTINCT ?url ?coord WHERE {

?file wdt:P180 wd:Q1392526, wd:Q2028 .

?file schema:url ?url .

} Giubarba (talk) 17:16, 2 November 2023 (UTC)

WCQS cannot report on data in EXIF. Put the depicts and coords in structured data and you might get somewhere. There are this sort of site - https://www.pic2map.com/ - but it's not designed to take a SPARQL report as input. --Tagishsimon (talk) 17:24, 2 November 2023 (UTC)

I found the tool again, I'll share it with you: https://wikishootme.toolforge.org/ Giubarba (talk) 09:11, 3 November 2023 (UTC)

Commons structured data is kind of new, so I am not surprised you find few results. Maybe search mw:Help:CirrusSearch. The deepcat feature may be disabled on high traffic sites, I don't know. Infrastruktur (talk) 21:06, 2 November 2023 (UTC)

All dead people on Wikidata, but still are in Living people category in the article on Wikipedia

Hi, I want all dead people on Wikidata that has Property:P570 in their item page, but in the Wikipedia articles they are still listed in en:w:Category:Living people. I specifically wanted for "ckbwiki". Here is a Quarry query for this purpose, but didn't involve Wikidata. Feel free to add more columns to the fetched data. Thanks! Aram (talk) 21:12, 2 November 2023 (UTC)

Further development of query (narrowing, ref)

Hello! I created a query that was intended to list the elements instance of (P31):town in Hungary (Q13218690), print the value of start time (P580) specified as the qualifier for property Q13218690, and also print what stated in (P248) is in the source provided for it. In which I am asking for help on how to specify NOT to list the additional values given in P31 (so there is no need to list such as million city (Q1637706), city with municipal rights (Q587089), border city (Q902814), spa town (Q4946461), etc.), or the P248 in the source does not show at all, how can this be solved?

This is my query:

Items: town in Hungary (Q13218690)   
Properties: instance of (P31)   , stated in (P248)   , start time (P580)   

SELECT ?item ?itemLabel ?start ?st ?ref ?refLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],hu". }
  ?item wdt:P31 wd:Q13218690.
  ?item p:P31 ?statement.
  ?statement ps:P31 ?start.
  OPTIONAL { ?statement pq:P580 ?st. }
  ?item p:P31 [ prov:wasDerivedFrom ?ref ].
  OPTIONAL { ?ref pr:P248 [] . }                        
}

Try it!

(There are 345 cities in Hungary, that's how many results I want with the source I'm looking for)

Thank you in advance for your help Pallor (talk) 11:52, 3 November 2023 (UTC)

@Pallor: Somthing more like this, probably. Most P31s do not have a start time or a reference. I leave you to work out why 349 rows are returned; duplicate start date or refs, or items that are not towns in the sense you expect having been given the town value, will be the cause.

SELECT distinct ?item ?itemLabel ?start ?ref ?refLabel  WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],hu". }
  ?item p:P31 ?statement .
  ?statement ps:P31 wd:Q13218690.
  OPTIONAL { ?statement pq:P580 ?start. }
  OPTIONAL { ?statement prov:wasDerivedFrom/pr:P248 ?ref . }                        
}

Try it!

--Tagishsimon (talk) 13:42, 3 November 2023 (UTC)