Talk:SPARQL examples

Cross check elevations

For some reason there's some pretty inconsistent data out there: https://www.wikidata.org/wiki/Q700658 vs. https://www.wikidata.org/wiki/Q21863565

or: https://www.wikidata.org/wiki/Q3905950 vs. https://www.openstreetmap.org/node/26864291 vs. https://www.wikidata.org/wiki/Q31652986

SELECT ?osmId ?ele ?ele_wikidata ?delta_ele ?loc ?wd ?distance WHERE {
  
  BIND (0.01 as ?radius)

  ?osmId osmm:type ?osmType .
  ?osmId osmt:ele ?ele .
  ?osmId osmt:natural "peak" .

  ?osmId osmm:loc ?center .

  # find all wikidata items in the close proximity
  SERVICE wikibase:around {
    ?wd wdt:P625 ?loc .
    bd:serviceParam wikibase:center ?center .
    bd:serviceParam wikibase:radius ?radius .
  }
  
  ?wd wdt:P2044 ?ele_wikidata.

  # Calculate distance between OSM's and WD's
  BIND(geof:distance(?center, ?loc) as ?distance).
  
  BIND( ABS(xsd:float(?ele) - xsd:float(?ele_wikidata)) AS ?delta_ele).
  
  FILTER (xsd:integer(?ele) > 0).
  
  FILTER (?delta_ele > 0).
  
}

Run it (edit query)

mmd, the first example seems like a duplicate - first is properly maintained, and the second one only links to sv and ceb wikis - both known as "botopedia" rather than "wikipedia". They need to be merged, keeping the bigger ones. I would ignore the ceb & sv values outright - likely junk pulled from some weird source, but you never know, it is possible in theory that they have better data. The second one is the same thing, and even ceb wiki has the right elevation - 2240, but for some reason the wikidata for it was incorrect. Also - need to merge with the italian one, making the italian default. --Yurik (talk) 09:35, 1 October 2017 (UTC)

Half-baked ideas

Some ideas for queries that I haven't gotten to work quite yet:

• Anagram place names – keeps timing out [1]
• Ordinal-numbered streets, even if the ordinal number is written out in full, in multiple languages, using Wikidata lexemes – unfortunately, Wikidata has no English ordinal number words yet [2]
• Chicago skyline (building heights by latitude) – rounding isn't quite working out [3]
• Places in the news: cities near coordinates of Wikidata items whose linked Wikipedia articles have been viewed at a higher rate than normal over the past 24 hours and thus should be given extra micromapping and countervandalism attention – unfortunately, the pageviews: data hasn't been updated in two years, it only tracks all-time pageviews, and a similar proposal for the Wikidata Query Service was declined
• Human-readable names and abbreviations of numbered routes – first, Wikidata needs road name formatter and road number formatter properties so it wouldn't be so reliant on wikidata=* and road number (P1824) tagging
• Features last edited by politicians/OSMF board members/other notable people – first, Wikidata needs an OpenStreetMap user name property
• Parts of the map shown in Wikipedia infoboxes, a potential vandalism vector – unfortunately, a category tree service tracks subcategories but not individual page membership, and MW2SPARQL does but keeps timing out on w:Category:Pages using the Kartographer extension [4]
• Highest ordinal-numbered streets – keeps timing out [5]
• Squarish brand icons – based on this query, cross-check the image's width and height in the Wikimedia Commons Query Service [6] and filter out awkward aspect ratios – unfortunately, the https://wcqs-beta.wmflabs.org/sparql endpoint is blocked by the Wikidata Query Service, and Sophox is unable to connect because it's behind a login wall (probably due to being in beta)

 – Minh Nguyễn ^💬 00:51, 10 July 2020 (UTC)

Adding another simple example

Looking for an example which is a equivalent of this overpass-turbo query (collecting results of 2 tags in one query):

nwr["office"="education"]({{bbox}});
nwr["sport"="pilates"]({{bbox}});
out body; >; out skel qt;

https://overpass-turbo.eu/s/WP4 --MalgiK (talk) 19:40, 6 August 2020 (UTC)

@MalgiK: Either ?osm osmt:office "education"; osmt:sport "pilates". or ?osm osmt:office "education". ?osm osmt:sport "pilates".. – Minh Nguyễn ^💬 01:20, 9 August 2020 (UTC)

Thanks @Minh Nguyen: - had the same idea before, but couldn't get it running and both queries still response with an error. I wonder if the top line (SELECT * WHERE) needs to get minor modification (e.g. adding the ?osm variable or DISTINCT function)? For clarification, the query should show results of objects which have a tag office=education or which have a tag sport=pilates. --MalgiK (talk) 15:54, 9 August 2020 (UTC)

#defaultView:Map SELECT * WHERE { ?osmId osmm:type ?osmType ; ?osm osmt:office "education" ; osmt:sport "pilates" . osmm:loc ?loc . } Run it (edit query)

#defaultView:Map SELECT * WHERE { ?osmId osmm:type ?osmType ; ?osm osmt:office "education" ; ?osm osmt:sport "pilates" ; osmm:loc ?loc . } Run it (edit query)

@MalgiK: Ah, sorry I misread. Overpass's (…) syntax corresponds to SPARQL's UNION statement. – Minh Nguyễn ^💬 22:40, 9 August 2020 (UTC)

@Minh Nguyen: Interesting, added the UNION statement to the example, see here: SPARQL_examples#Show_all_OSM_objects_with_a_tag_office.3Deducation_or_tag_sport.3Dpilates but it still seems there is a syntax error included (there will be no map displayed). Could you please double check and correct the query by chance, e.g. right there. --MalgiK (talk) 11:28, 11 August 2020 (UTC)

@MalgiK: All set. The issue was probably the first line that bound the ?osmId variable, whereas the other lines bound the ?osm variable instead. The query doesn't seem to use the geometry type anywhere, so I removed it. – Minh Nguyễn ^💬 17:37, 11 August 2020 (UTC)

@Minh Nguyen: This (The query doesn't seem to use the geometry type anywhere) is really interesting, and i tried same syntax (removing type statment) for one of the very simple query (only ask for one tag (office=education)), see table for comparison:

a	b
#defaultView:Map SELECT * WHERE { ?osmId osmm:type ?osmType ; osmt:office "education" ; osmm:loc ?loc . } Run it (edit query)	#defaultView:Map SELECT * WHERE { ?osm osmt:office "education" . ?osm osmm:loc ?loc . } Run it (edit query)

Both are running fine.& it seems both are showing the same results (currently 154 items). So what is the difference, and which version/variant should be preferably used? Btw., query (a) takes 886 in average ~200 ms & query (b) takes also av. ~200 ms --MalgiK (talk) 11:23, 14 August 2020 (UTC)

@MalgiK: The two queries are functionally equivalent. (a) happens to require each result to have a type and returns that type in an additional column (since the query uses the * wildcard). Unlike Overpass, Sophox doesn't require you to specify any types if you don't care about the types. Since all elements have a type, capturing osmm:type shouldn't have a noticeable performance impact. On the other hand, if you had captured something else like osmt:building that appears on a great many features but not all of them, that clause could make the query run much more slowly or even time out, because the query would have join the results with the list of triples that involve building=*. – Minh Nguyễn ^💬 16:46, 14 August 2020 (UTC)

Adding an example which finds relations which have members with specific role name

Lets say you want find the Relation:multipolygon which have members of role outline instead of outer, see table: https://taginfo.openstreetmap.org/relations/multipolygon#roles (there are currently 39 such units) --MalgiK (talk) 10:12, 23 September 2020 (UTC)

Dead links

This page is linked from multiple places on wiki, but SPARQL server is dead. Is there some real chance that it will change? Mateusz Konieczny (talk) 19:18, 23 March 2021 (UTC)

@Mateusz Konieczny: At https://github.com/Sophox/sophox/issues/24 nyurik expressed that he still plans to do so, 12 days ago. --CamelCaseNick (talk) 23:05, 23 March 2021 (UTC)

Then I guess that waiting till 6 months or one year from initial outage report may make sense Mateusz Konieczny (talk) 08:47, 24 March 2021 (UTC)

Also, the Sophox source code is open source, so in principle anyone can set up their own instance and perform these queries. Sophox does have more intensive resource requirements than an Overpass instance, however. – Minh Nguyễn ^💬 19:24, 7 April 2021 (UTC)