Commons talk:SPARQL query service/queries/examples

Get contributor details

I'd like to create a query where I find all instances of Listed Building in an area on wikidata, filtering by ones which have a P18 image. This I can do. See https://w.wiki/d5H

How could I get via WikiCommons the ID of the person who uploaded the image? This would enable me to group and count by ID.

I've looked everywhere, it seems, but can't find that missing piece.

Thanks

Watty62 (talk) 07:39, 22 September 2020 (UTC)

Get M-ID via Wikidata federated SPARQL

Tracked in Phabricator
Task T258769

Tracked in Phabricator
Task T258776

Is there a way to get the M-ID of a media based on a federated wikidata sparql query? i query images linked in wikidata items like this way and want to get their corresponding m-ids for further edits on it:

SELECT * with {
  select * {
    # SELECT all articles published in German magazine 'Die Gartenlaube'
    service <https://query.wikidata.org/sparql> {
      ?article wdt:P1433 wd:Q655617;
               wdt:P18 ?images.
    }
  }
} as %items
where {
  include %items .
}

Try it!

Thanks, Mfchris84 (talk) 11:58, 23 July 2020 (UTC)

@Mfchris84: Yes. It would be nice to be able to take the value of eg a {{P|18} statement on wikidata, and look up the properties of that image here in WCQS.

Also it would be nice, given a list of images here produced by some statement(s), to be able to look up to see which ones are the objects of properties like P18 on wikidata.

This would be easy if there were triples connecting the M-ID of a file to a file-url that is of the form that Wikidata uses for objects of properties like P18 -- ie if the database included statements of the form

sdc:M1234567 ?relation commons:filename.jpg

I think triples of this form will need to be systematically added to the database.

Unfortunately, the one relation that is being provided that is similar to this, schema:contentUrl does not produce the right kind of url, and it is not straightforward to go from the first sort of url to the second.

It is possible to go from the second sort of url to the first sort, hence the workaround that makes the image grid possible.

A handful of thoughts:

• If you can define a limited set of files through some Commons query that might include the images that are the objects of the wd properties you are interested, then you could use the workaround to construct urls of the image-property value sort for each of the files in your limited set, and then match them.
• Even if such a limited "candidate set" cannot be defined through Commons statements, such a set might sometimes be possible to construct via eg an API call for members of a particular category or categories.
• It might be possible to go from a url of the image-property value sort to an M-ID or to an url of the second sort via an appropriate API call, but I don't know enough about it.

But rather than put too much effort into any of these, the best solution seems to me probably to be just to try to get the triples with that missing relation added to the database. Jheald (talk) 21:23, 23 July 2020 (UTC)

Help needed with a query

I am trying to write a query with instance of (P31) statements of items linked from Commons files using digital representation of (P6243 ). The current query does not work. Anyway to fix it? --Jarekt (talk) 15:24, 23 July 2020 (UTC)

SELECT ?type (COUNT(?file) AS ?count) 
WHERE {
     ?file wdt:P6243 ?item . 
     service <https://query.wikidata.org/sparql> { ?item wdt:P31 ?type . } 
} 
GROUP BY ?type
ORDER BY DESC(?count)
LIMIT 100

Try it!

@Jarekt: This query on the query page does what I think you are wanting.

A couple of things: that query has a limit on the numbers of ?item, otherwise there seem to be too many for the federated service to cope with. Secondly, splitting out the WITH block can be helpful, to control the order in which the parts of the query are executed. Jheald (talk) 20:53, 23 July 2020 (UTC)

Thanks for the tip about the ?item and thanks for a query. Your query does about 50% of what I was hoping. Finding the most used item types is great, but I was hoping to use it for finding odd links, like paintings linking to people or places, which in most cases are wrong. My {{Artwork}} template does it already a bit, but I can only hardwire a few "bad" items. I was hoping to be looking for artworks which are linking to items which are people or types which are instances of people, for example. --Jarekt (talk) 00:56, 24 July 2020 (UTC)

@Jarekt: Work in progress: Here's a query that tries find the most common classes of things that are objects of digital representation of (P6243), other than 2D single artworks : tinyurl.com/yxfvusgo

It may still have some rough edges, but it would be trivial to adapt it to list the artworks themselves, rather than the strange classes

Is this closer to what you were looking for ? Jheald (talk) 14:33, 24 July 2020 (UTC)

@Jarekt: Also, display of examples in an ImageGrid tinyurl.com/yx9fs38h, which makes it easier to view the sorts of things which are getting questionable P6243 statements.

Note that the query is written so that if any of the classes of the object item appears to be okay, then the item (and the image) are whitelisted. Jheald (talk) 14:49, 24 July 2020 (UTC)

It's interesting that Sadads manages to write a federated query that pulls back 100,000 examples of churches, but we seem to suffer if we push over a much smaller number of items.

Possibly, because there is a LIMIT on the Sadads query, the examples of churches may only being consumed as needed, and fewer than 100,000 are actually copied across. Or it could be that the federated data transfer isn't actually the pinch point. Jheald (talk) 15:35, 24 July 2020 (UTC)

All_images_depicting_Van_Gogh_artworks

@Jheald: I especially like the Commons:SPARQL_query_service/queries/examples#All_images_depicting_Van_Gogh_artworks query. It is a good use of apparently limited access to wikidata database. Thanks --Jarekt (talk) 12:29, 24 July 2020 (UTC)

That was by User:Husky. You're right, it's a nice query. Jheald (talk) 13:02, 24 July 2020 (UTC)

@Jarekt: Thanks! That wouldn't have been possible without all the other existing queries using federation though because i had no experience using federation whatsoever :) Husky ^{(talk to me)} 14:31, 25 July 2020 (UTC)

OTRS anomaly queries

We have over 50% of Wikimedia VRTS ticket number (P6305) statements already added, so I was thinking about developing some queries aimed at finding anomalies, or files using one OTRS number which were uploaded years apart, or listing different authors, with large number of people uploading, using different sources, etc. There might be reasonable explanations for all those cases but I was thinking about checking some of them to see if I can spot incorrect OTRS use. Is there a way to access upload date or the first uploding user using SPARQL queries, the way we can access width height and file type? --Jarekt (talk) 16:23, 27 July 2020 (UTC)

Missing elements from results set

UPDATE (in case someone eventually reads this section) - I Tested the query again today, 20 days later and it returned expected results. I does seem to be data freshness issue. --Nikola Tulechki (talk) 07:28, 29 May 2021 (UTC)

Hello I'm trying to use WCQS to get images of centennial trees in Bulgaria with this query:

#Images of remarkable trees in bulgaria
#defaultView:ImageGrid{"hide":["?img2"]}
select *
with {
  select * {
    # SELECT all remarkable trees in bulgaria
    service <https://query.wikidata.org/sparql> {
      ?tree wdt:P31 wd:Q811534 ; wdt:P17 wd:Q219 .
    }
  }
} as %items
where {
  include %items .
  #select images depicting thoose trees
  ?file wdt:P180 ?tree; 
     schema:contentUrl ?img ;
  # workaround to show the images in an image grid
  BIND(IRI(concat("http://commons.wikimedia.org/wiki/Special:FilePath/", wikibase:decodeUri(substr(str(?img),53)))) AS ?img2)
}

Try it!

However I am only getting 9 results, where there should be many more.

This image for example depicts (P180) Oak tree in Lyaskovec (Q106625895), I added it on April 26, but it is still missing from the result set today!

My question is am I doing something not in the right way or is this a data freshness issue?

If it is the latter, what is the update frequency of the data querable by WCQS?

Thanks --Nikola Tulechki (talk) 08:39, 8 May 2021 (UTC)

I was interested in the refresh frequency of WCQS as well. I monitored it for a month. I think currently every Monday a snapshot (or so) is taken, but it is available between 9 and 15 days later. So results could be minumum 9 days old but maximum about 15+7=22 days. In the example of the tree of 26 April a snapshot was probably taken at 3 May and was available in WCQS on 12 May. HenkvD (talk) 11:26, 29 May 2021 (UTC)

Using `schema:url` for showing the images

Hey, thanks to Phabricator task T277665 it's now possible to display images in a grid without using the old awful hack of getting it out of the file URL. I rewrote the first half of all queries using that old hack. So now, to get all images depicting Douglas Adams you can just do:

#defaultView:ImageGrid
select ?file ?image where {
  ?file wdt:P180 wd:Q42.
  ?file schema:url ?image.
}

Try it! Husky ^{(talk to me)} 09:38, 13 August 2021 (UTC)

Douglas Adams image grid -- two queries bc of inline syntax

The inline syntax at #Depictions of Douglas Adams shown as image grid generates a query in the Examples modal at https://wcqs-beta.wmflabs.org/ that is just ?file schema:url ?image.

I think probably the code behind the Examples modal needs to be modified to take only query syntax from the {{SPARQL}} template, but I guess for now the easier thing would be to change the <syntaxhighlight lang="SPARQL" inline> to just <code>? — Hugh (talk) 01:02, 17 September 2021 (UTC)

broken link https://wcqs-beta.wmflabs.org/

First link 'web interface of the query service' leads to :

HTTP ERROR 500
Problem accessing /oauth/check_login. Reason:
    Request failed.
Powered by Jetty:// 9.4.12.v20180830

This makes all 'TryIt' to fail; can someone correct please ? Thanks. Christian 🇫🇷 FR (talk) 13:24, 30 December 2021 (UTC) Christian 🇫🇷 FR (talk) 13:27, 30 December 2021 (UTC)

recent changes

Is it possible to display as a timestamped list the newest objects of inception. Means after a change of structured object, list gives out Filename and new date and timestamp of change. Thank you for help, regards, Conny (talk) 20:15, 30 June 2022 (UTC).

Images without Wikidata Items

How can I get the images that do not have Wikidata items? For example :I would like to get all mosques names from the Wikimedia Commons category (Category:Mosques in Dubai) to create items in Wikidata. Akbarali (talk) 09:54, 14 August 2022 (UTC)

@Akbarali: First up, it's worth looking with m:PetScan to check whether the subcategories have Wikidata items linked to them. Looking at this run it seems that four currently do, but two don't.

When it come to the images themselves, this query may be the closest I can get to what you have asked for: for each file in the category, it gives a list of depicts values if the file has them, coordinates if the file has them, and finally a flag 'M' if one of those depicts values has a instance of (P31) on Wikidata that says it's some kind of mosque.

If you switch to looking at results in map view, the rows with an 'M' should show up with dots in a different colour.

Being able to see the results plotted on a map may help you to identify groups of images that may all be of the same mosque; and which mosque the image(s) may actually be of. (You should also be able to right-click to a map for a particular image from coordinates on the image information page).

Also be aware that just because the image doesn't have a 'depicts' statement, that doesn't necessarily mean for sure that Wikidata doesn't have an item for the mosque. Here's a query https://w.wiki/5aLG for the items that wikidata currently does identify as mosques in Dubai -- but that may also be incomplete, if there are any mosques that don't have a instance of (P31) and either a country (P17) or a located in the administrative territorial entity (P131).

Good luck! It seems a very worthwhile project. Jheald (talk) 13:48, 15 August 2022 (UTC)

Thank you so much for this great help. Akbarali (talk) 18:32, 18 August 2022 (UTC)