Malline:Helsingin suojellut rakennukset -SPARQL

SELECT

 ?item
 ?itemLabel
 ?type
 ?long2
 ?lat2
 ?p18_old_s
 ?p18_any_s
 (IF (BOUND(?p18_old_s), ?p18_new_s, COALESCE(?p18_new_s,?p18_any_s)) as ?p18_new_with_failback)
 (CONCAT('{','{safesubst:Wikidata-lista/apu/tunnisteetcol',
   '|', 'p4009=', ?p4009g,
   '|', 'p4106=', ?p4106g,
   '|', 'p5310=', ?p5310g,
   '|', 'p5313=', ?p5313g,
   '|', 'p8355=', ?p8355g,
   '}','}') as ?links)
 (CONCAT('{','{safesubst:Wikidata-lista/apu/titlecol',
   '|','item=',REPLACE(STR(?item), 'http://www.wikidata.org/entity/', ),
   '|','parts=',REPLACE(STR(?l1_parts), 'http://www.wikidata.org/entity/', ),
   '}','}') as ?titlecol)
 (CONCAT('[','[d:',REPLACE(STR(?item), 'http://www.wikidata.org/entity/', ),'{','{subst:!}','}','Muokkaa]',']') as ?wdlink)
 (CONCAT('{', '{Paikkalinkki',
   '{','{subst:!}','}', STR(?lat2),
   '{','{subst:!}','}', STR(?long2),
   '{','{subst:!}','}','pyöristys=4',
   '{','{subst:!}','}','nimi=',COALESCE(?itemLabel, REPLACE(STR(?item), 'http://www.wikidata.org/entity/', )),
   '}','}') as ?geohackurl)
 (CONCAT('{', '{WLM-upload-button',
   '{','{subst:!}','}', 'luokat=', REPLACE(?commonscat, ' ', '_'),
   '{','{subst:!}','}', 'nimi=', COALESCE(?itemLabel, REPLACE(STR(?item), 'http://www.wikidata.org/entity/', )),
   '{','{subst:!}','}', 'kuvaus=', COALESCE(?itemDescription, '%20'),
   '{','{subst:!}','}', 'wikidata=', REPLACE(STR(?item), 'http://www.wikidata.org/entity/', ),
   '{','{subst:!}','}', 'lat=', STR(COALESCE(?lat2,)),
   '{','{subst:!}','}', 'long=', STR(COALESCE(?long2,)),
   '{','{subst:!}','}', 'kampanja=wlm-fi',
   '{','{subst:!}','}', 'otsikko=Tallenna kuva',
   '}','}'
  ) as ?wlmbutton )

WHERE { SELECT DISTINCT ?item ?itemLabel (SAMPLE(?lat) as ?lat2) (SAMPLE(?long) as ?long2)

 (SAMPLE(?p18_any) as ?p18_any_s)
 (SAMPLE(?p18_old) as ?p18_old_s)
 (SAMPLE(?p18_new) as ?p18_new_s)
 (GROUP_CONCAT(DISTINCT(?l1_part)) AS ?l1_parts)
 (COALESCE(?p366, ?p31) as ?type)
 (COALESCE(?commonscat1,
  CONCAT(IF(BOUND(?commonscat2),CONCAT(?commonscat2, '%7C'),),
         IF(BOUND(?commonscat3),CONCAT(?commonscat3, '%7C'),),
         IF(BOUND(?commonscat4),CONCAT(?commonscat4, '%7C'),),
         IF(BOUND(?commonscat5),CONCAT(?commonscat5, '%7C'),),
         IF(BOUND(?commonscat6),CONCAT(?commonscat6, '%7C'),))
  ) as ?commonscat)
  (GROUP_CONCaT(DISTINCT(COALESCE(?p4009,?l2_p4009,))) as ?p4009g)
  (GROUP_CONCaT(DISTINCT(COALESCE(?p4106,?l2_p4106,))) as ?p4106g)
  (GROUP_CONCaT(DISTINCT(COALESCE(?p5310,?l2_p5310,))) as ?p5310g)
  (GROUP_CONCaT(DISTINCT(COALESCE(?p5313,?l2_p5313,))) as ?p5313g)
  (GROUP_CONCaT(DISTINCT(COALESCE(?p8355,?l2_p8355,))) as ?p8355g)

WHERE {

 values ?include_area_values {  } . 
 ?item (wdt:P276|wdt:P361|wdt:P131)* ?include_area_values.
 FILTER NOT EXISTS {
   values ?word { wd:Q99458251 wd:Q99454315 } 
   ?item (wdt:P276|wdt:P361|wdt:P131|wdt:P669)* ?word 
 }  
 ?item wdt:P1435 ?heritage .
 ?item wdt:P17 wd:Q33 .
 ?item wdt:P131 wd:Q1757 .
 ?item wdt:P131 ?p131 .
 OPTIONAL {
   ?item p:P625 ?coords .
   ?coords psv:P625 ?coordinate_node .
   ?coordinate_node wikibase:geoLatitude ?lat .
   ?coordinate_node wikibase:geoLongitude ?long .
 }
 OPTIONAL {
   ?item p:P18 ?p_p18 .
   ?p_p18 ps:P18 ?p18_any
   OPTIONAL {
     ?p_p18 pq:P585 ?p18_p585
     OPTIONAL {
       ?p_p18 ps:P18 ?p18_new
       FILTER( ?p18_p585 >= '2000-01-01T00:00:00'^^xsd:dateTime)
     }
     OPTIONAL {
       ?p_p18 ps:P18 ?p18_old
       FILTER( ?p18_p585 < '2000-01-01T00:00:00'^^xsd:dateTime)
     }
   }
 }
 OPTIONAL {
   ?item (wdt:P276|wdt:P361|wdt:P131)* ?l1_part .
   ?l1_part wdt:P1435 ?l1_part_p1435
   FILTER ( ?l1_part != ?item )
   FILTER ( ?l1_part != ?p276 )
 }
 OPTIONAL { ?item wdt:P373 ?commonscat1 }
 OPTIONAL { ?p131 wdt:P373 ?commonscat2 FILTER ( ?p131 != wd:Q1757 ) }
 OPTIONAL { ?item wdt:P276 ?p276 . ?p276 wdt:P373 ?commonscat3 }
 OPTIONAL { ?item wdt:P669 ?p669 . ?p669 wdt:P373 ?commonscat4 }
 OPTIONAL { ?item wdt:P361 ?p361 . ?p361 wdt:P373 ?commonscat5 }
 OPTIONAL { ?item wdt:P84 ?p84 . ?p84 wdt:P373 ?commonscat6 }
 OPTIONAL { ?item wdt:P31 ?p31 }
 OPTIONAL { ?item wdt:P366 ?p366 }
 OPTIONAL { ?item wdt:P4009 ?p4009 }
 OPTIONAL { ?item wdt:P5310 ?p5310 }
 OPTIONAL { ?item wdt:P5313 ?p5313 }
 OPTIONAL { ?item wdt:P4106 ?p4106 }
 OPTIONAL { ?item wdt:P8355 ?p8355 }
 OPTIONAL {
   ?item (wdt:P276|wdt:P361|wdt:P131)* ?l2_part .
   ?l2_part wdt:P1435 ?l2_part_p1435
   OPTIONAL { ?l2_part wdt:P4009 ?l2_p4009 }
   OPTIONAL { ?l2_part wdt:P5310 ?l2_p5310 }
   OPTIONAL { ?l2_part wdt:P5313 ?l2_p5313 }
   OPTIONAL { ?l2_part wdt:P4106 ?l2_p4106 }
   OPTIONAL { ?l2_part wdt:P8355 ?l2_p8355 }
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'fi'. }
 

} GROUP BY ?item ?itemLabel ?itemDescription ?commonscat1 ?commonscat2 ?commonscat3 ?commonscat4 ?commonscat5 ?commonscat6 ?commonscat2b ?links ?p18_old_s ?p18_new_s ?p18_any_s ?p366 ?p31 }