Malline:Wikidata-galleria/Helsinki-sparql

SELECT ?item ?fileTitle ?image ?page_title

 (GROUP_CONCAT(DISTINCT(REPLACE(REPLACE(STR(?category1),"Category: *?([0-9]{4}) in Helsinki", "$1"), "Category: *?Helsinki in the ([0-9]{4}s)", "$1"))) as ?year)
 (IF(REGEX(?content, ?fileTitleMatch), 1,0) as ?c)
 (REPLACE(STR(?item), 'http://www.wikidata.org/entity/', ) as ?qid)

WHERE {

 BIND(wd:Q1386673 AS ?target_item)
 ?item (wdt:P276|wdt:P131|wdt:P669)* ?target_item .
 FILTER EXISTS { ?item wdt:P31/wdt:P279* wd:Q27096213 }

 ?article schema:about ?target_item .
 ?article schema:isPartOf <https://fi.wikipedia.org/> .
 ?item wdt:P18 ?image.
 
 BIND(STRAFTER(wikibase:decodeUri(STR(?article)), "https://fi.wikipedia.org/wiki/") as ?page_title)
 BIND(CONCAT("File:", STRAFTER(wikibase:decodeUri(STR(?image)), "http://commons.wikimedia.org/wiki/Special:FilePath/")) AS ?fileTitle)
 BIND(REPLACE(STRAFTER(wikibase:decodeUri(STR(?image)), "http://commons.wikimedia.org/wiki/Special:FilePath/"), "[ _]", ".") AS ?fileTitleMatch)

 SERVICE wikibase:mwapi {
   bd:serviceParam wikibase:endpoint "fi.wikipedia.org";
                   wikibase:api "Generator";
                   wikibase:limit "once";
                   mwapi:generator "allpages";
                   mwapi:gapfrom ?page_title;
                   mwapi:gapnamespace 0 ; # NS_FILE
                   mwapi:gaplimit 1;
                   mwapi:prop "revisions";
                   mwapi:rvprop "content".
   ?contentmodel wikibase:apiOutput 'revisions/rev/@contentmodel'.
   ?contentformat wikibase:apiOutput 'revisions/rev/@contentformat'.
   ?content wikibase:apiOutput 'revisions/rev/text()' .
 }
 
 OPTIONAL {
   SERVICE wikibase:mwapi {
     # Categories that contain these pages
     bd:serviceParam wikibase:api "Categories";
                     wikibase:endpoint "commons.wikimedia.org";
                     wikibase:limit 500;
                     mwapi:clshow "!hidden";
                     mwapi:titles  ?fileTitle.
     # Output the page title and category
     ?title wikibase:apiOutput mwapi:title.
     ?category1 wikibase:apiOutput mwapi:category .  
   }
   FILTER (
      REGEX(?category1, "Category: *?[0-9]{4} in Helsinki") || REGEX(?category1, "Category: *?Helsinki in the [0-9]{4}s") 
   )
 }  

} GROUP BY ?item ?fileTitle ?fileTitleMatch ?year ?c ?page_title ?content ?image ?category1 LIMIT 30