Talk:Q48557

Query

Map

• Items used: Eisenbahn-Romantik (Q48557)   

• Properties used: part of the series (P179)   , publication date (P577)   , main subject (P921)   , coordinate location (P625)   , terminus (P559)   , located in/on physical feature (P706)   , location (P276)   , located in the administrative territorial entity (P131)   , headquarters location (P159)   , operator (P137)   , country (P17)   , image (P18)   , reference URL (P854)   , series ordinal (P1545)   

• Features used: map (Q24515275)   

#defaultView:Map{"hide":["?coor_sample", "?decade_half", "?epDescription", "?countryLabel"], "layer": "?decade_half"}
SELECT
  ?ep_number
  ?ep ?epLabel ?epDescription
  ?year ?decade_half
  ?topic ?topicLabel ?topicDescription
  ?img ?url 
  ?countryLabel
  (SAMPLE(?coor) as ?coor_sample)
WHERE
{
	?ep wdt:P179 wd:Q48557.
	OPTIONAL{?ep p:P179 [ps:P179 wd:Q48557;prov:wasDerivedFrom/pr:P854 ?url].FILTER(contains(str(?url),"swrfernsehen"))}
	OPTIONAL{?ep wdt:P577 ?d}
	FILTER NOT EXISTS {?ep wdt:P577 ?d0.FILTER( ?d > ?d0)}
	OPTIONAL{?ep p:P179 [ps:P179 wd:Q48557; pq:P1545 ?ep_number]}
	BIND(YEAR(?d) as ?year)
	BIND(FLOOR(YEAR(?d)/5)*5 as ?decade_half)
	OPTIONAL
	{
		?ep p:P921/ps:P921 ?topic    # normal and preferred rank
        # ?ep wdt:P921 ?topic        # preferred rank only
		OPTIONAL{?topic wdt:P625 ?c0}
		OPTIONAL{?topic wdt:P559/wdt:P625 ?c1}
		OPTIONAL{?topic wdt:P706/wdt:P625 ?c2}
		OPTIONAL{?topic wdt:P276/wdt:P625 ?c3}
		OPTIONAL{?topic wdt:P131/wdt:P625 ?c4}
		OPTIONAL{?topic wdt:P159/wdt:P625 ?c5}
		OPTIONAL{?topic wdt:P137/wdt:P159/wdt:P625 ?c6}
		OPTIONAL{?topic wdt:P17/wdt:P625 ?c7}      
		BIND(coalesce(?c0, ?c1, ?c2, ?c3, ?c4, ?c5, ?c6, ?c7) as ?coor)  # use ?c0 if present, otherwise ?c1, ?c2 ..
		OPTIONAL{?topic wdt:P18 ?img}
        OPTIONAL{?topic wdt:P17 ?country}
	}
	SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de"}
}
GROUP BY ?ep_number ?ep ?epLabel ?epDescription ?year ?topic ?topicLabel ?topicDescription ?decade_half ?img ?url ?countryLabel
ORDER BY DESC(xsd:integer(?ep_number))

Try it!

Lines with termini/terminuses (P559)

• Items used: Eisenbahn-Romantik (Q48557)   

• Properties used: part of the series (P179)   , terminus (P559)   , coordinate location (P625)   , image (P18)   , publication date (P577)   , main subject (P921)   , series ordinal (P1545)   

• Features used: map (Q24515275)   

# topics (P921) with a terminus (P559)
#defaultView:Map{"hide":["?coorpoint","?coorline","?rgb","?decade_half"], "layer": "?decade_half"}
SELECT DISTINCT 
  ?coorpoint ?coorline ?rgb
  ?terminus ?terminusLabel 
  ?topic ?topicLabel 
  ?img
  ?episode ?episodeLabel ?episode_number ?episode_year ?decade_half # ?ct_episodes
WITH
{
  SELECT ?coorpoint ?terminus (SAMPLE(?topic0) AS ?topic)  (SAMPLE(?ep) AS ?episode)  # (COUNT(DISTINCT ?ep) as ?ct_episodes)
  WHERE
  {
	?ep wdt:P179 wd:Q48557.
	?ep p:P921/ps:P921 ?topic0 .
    ?topic0 wdt:P559 ?terminus . 
    ?terminus wdt:P625 ?coorpoint .
  }
  GROUP BY ?terminus ?coorpoint
} AS %terminuslocations
WITH
{
  SELECT DISTINCT ?coorline ?topic ?episode
  WHERE
  {
  	?episode wdt:P179 wd:Q48557.
	?episode p:P921/ps:P921 ?topic .
    ?topic wdt:P559 ?terminus1 , ?terminus2 . FILTER(STR(?terminus1) < STR(?terminus2))
    ?terminus1 (p:P625/psv:P625) [ wikibase:geoLatitude ?lat1;    wikibase:geoLongitude ?lon1 ].
    ?terminus2 (p:P625/psv:P625) [ wikibase:geoLatitude ?lat2;    wikibase:geoLongitude ?lon2 ].
    BIND(STRDT(CONCAT("LINESTRING(", STR(?lon1), " ", STR(?lat1), ",", STR(?lon2), " ", STR(?lat2), ")"), geo:wktLiteral) AS ?coorline)
  }
} AS %lines
WHERE
{
  { 
    INCLUDE %terminuslocations
    OPTIONAL { ?terminus wdt:P18 ?img }  
  }
  UNION
  { 
    INCLUDE %lines 
    OPTIONAL { ?topic wdt:P18 ?img }  
  }
  BIND(IF(BOUND(?coorline), IF(BOUND(?img), "00f", "f00"), "007f00") AS ?rgb)
  OPTIONAL{?episode wdt:P577 ?d} FILTER NOT EXISTS {?episode wdt:P577 ?d0. FILTER( ?d > ?d0) }
  BIND(YEAR(?d) as ?episode_year)
  BIND(FLOOR(YEAR(?d)/5)*5 as ?decade_half)  
  OPTIONAL { ?episode p:P179 [ps:P179 wd:Q48557; pq:P1545 ?episode_number] }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de" }
}

Try it!

Thanks

Just wanted to thank you. It is an even greater version!--Geogast 🤲 (talk) 14:56, 14 April 2021 (UTC)

@Geogast: good to hear you like it. Above one focusing on topics with terminus (P559). --- Jura 16:55, 17 April 2021 (UTC)