How to reverse the order of concatenated values in a SPARQL query of Wikidata?

Question

I would like to get cities in Canada with a location concatenation as cross-feature that is sortable. This works :

SELECT 
(GROUP_CONCAT(?locationLabel; separator = ' < ') as ?locationLabels)
?city ?cityLabel
#?locationLabel
WHERE 
{
  ?city wdt:P17 wd:Q16. # country Canada.
  ?city wdt:P131* ?location. #located in the administrative territorial entity (P131)
  #?city wdt:P31 wd:Q515. # city (Q515)
  ?city wdt:P31 wd:Q21507383. #provincial or territorial capital city in Canada (Q21507383)

  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en". 
    ?city rdfs:label ?cityLabel. 
    ?location rdfs:label ?locationLabel . 
  }
}
GROUP BY ?city ?cityLabel

Some results:

<html><head><meta charset="utf-8"></head><body><table><thead><tr><th>locationLabels</th><th>city</th><th>cityLabel</th></tr></thead><tbody><tr><td>Whitehorse &lt; Yukon &lt; Canada</td><td>http://www.wikidata.org/entity/Q2058</td><td>Whitehorse</td></tr><tr><td>Yellowknife &lt; Northwest Territories &lt; Canada</td><td>http://www.wikidata.org/entity/Q2061</td><td>Yellowknife</td></tr><tr><td>St. John's &lt; Newfoundland and Labrador &lt; Dominion of Newfoundland &lt; Newfoundland Colony &lt; Canada</td><td>http://www.wikidata.org/entity/Q2082</td><td>St. John's</td></tr><tr><td>Edmonton &lt; Alberta &lt; Canada</td><td>http://www.wikidata.org/entity/Q2096</td><td>Edmonton</td></tr><tr><td>Regina &lt; Division No. 6 &lt; Saskatchewan &lt; Canada</td><td>http://www.wikidata.org/entity/Q2123</td><td>Regina</td></tr><tr><td>Charlottetown &lt; Queens County &lt; Prince Edward Island &lt; Canada</td><td>http://www.wikidata.org/entity/Q2127</td><td>Charlottetown</td></tr><tr><td>Winnipeg &lt; Winnipeg Metropolitan Region &lt; Manitoba &lt; Canada</td><td>http://www.wikidata.org/entity/Q2135</td><td>Winnipeg</td></tr><tr><td>Victoria &lt; British Columbia &lt; Capital Regional District &lt; Canada</td><td>http://www.wikidata.org/entity/Q2132</td><td>Victoria</td></tr><tr><td>Fredericton &lt; York County &lt; New Brunswick &lt; Canada</td><td>http://www.wikidata.org/entity/Q2138</td><td>Fredericton</td></tr><tr><td>Halifax &lt; Halifax County &lt; Nova Scotia &lt; Canada</td><td>http://www.wikidata.org/entity/Q2141</td><td>Halifax</td></tr><tr><td>Quebec City &lt; Quebec &lt; Capitale-Nationale &lt; Quebec &lt; Canada</td><td>http://www.wikidata.org/entity/Q2145</td><td>Quebec City</td></tr><tr><td>Toronto &lt; Ontario &lt; Canada</td><td>http://www.wikidata.org/entity/Q172</td><td>Toronto</td></tr></tbody></table></body></html>

Nevertheless, I would like to reverse the order in which the location labels appear. For instance, I would prefer to have "Canada > Alberta > Edmonton" than "Edmonton < Alberta < Canada".

Thank you very much for your help !

EDIT

I did another attempt from provinces in Canada and a kind of inverse property 'contains administrative territorial entity (P150)' but the returned results are not convincing:

SELECT 
(GROUP_CONCAT(?locationLabel; separator = ' > ') as ?locationLabels)
?province ?provinceLabel
WHERE 
{
  ?province wdt:P31 wd:Q11828004. #province of Canada (Q11828004)  
  ?province wdt:P150* ?location.
 
  SERVICE wikibase:label { 
    bd:serviceParam wikibase:language "en". 
    ?province rdfs:label ?provinceLabel. 
    ?location rdfs:label ?locationLabel . 
  }
}
GROUP BY ?province ?provinceLabel

for grouimpossible as there is no order at all defined in this aggregate function by the SPARQL 1.1 specs. And to get the intended order you'd have to know the geospatial containment in your query already, i.e. you would have to keep track of it. But even then ... — UninformedUser, Aug 18 '21 at 03:22
Maybe a solution from here ? : https://stackoverflow.com/questions/56569336/reversing-a-string-in-sparql — Ludovic Bocken, Aug 18 '21 at 13:36
I am very interested in a solution in SPARQL but here is a way in Python to reverse values: string = "Halifax < Halifax County < Nova Scotia < Canada" new_string = string.replace(' ', '_') new_string = "_<_" + new_string new_string = new_string.replace('_<_', ' |> ') words = new_string.split('|') reverse_string = ' '.join(reversed(words)) clean_new_string = ''.join(reverse_string.split()) clean_new_string = clean_new_string.replace('>', '', 1) clean_new_string = clean_new_string.replace('>', ' > ') clean_new_string 'Canada > Nova_Scotia > Halifax_County > Halifax' — Ludovic Bocken, Aug 18 '21 at 15:02
I think you misunderstand the problem, it's not about reversing things, it's more about how to keep track of the geospatial relationship when using a SPARQL property path operator `*` - how to get to `Halifax < Halifax County < Nova Scotia < Canada` ... all you know is that all locations `l_1, l_2, ..., l_n` are contained in Canada, but you don't know the relations among the `l_i`s anymore. There are ways to get some kind of index in the paths, but even then the order is being lost when using the result in a `group_concat` function which is the only way to concatenate `n` items — UninformedUser, Aug 19 '21 at 06:26
and don't understand me wrong, in Wikidata right now it looks like they keep track of the order of the locations, but this is more by accident resp. their implementation. In SPARQL there is no such guarantee — UninformedUser, Aug 19 '21 at 06:30

How to reverse the order of concatenated values in a SPARQL query of Wikidata?

0 Answers0