SPARQL query for finding films originating from and released in the United States

Question

I have the following SPARQL query that appears to correctly produce the films produced in the US (country of origin) and released in the US (place of publication) in 2018. The issue I'm having is that one row is produced for each release even though the other releases are outside of the US. I've added a limit to reduce the size of the response.

Here is the query:

SELECT ?item ?name ?publication_date ?placeLabel WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item rdfs:label ?name;
        wdt:P31 wd:Q11424;
        wdt:P495 wd:Q30; # -> country of origin US
        wdt:P577 ?publication_date.

  ?item p:P577 ?publication_statement.
  ?publication_statement pq:P291 ?place.

  FILTER(xsd:date(?publication_date) > "2018-01-01"^^xsd:date)
  FILTER(
     (LANG(?name)) = "en" 
        && ?place=wd:Q30) # -> place of publication
}
ORDER BY ?name
LIMIT 10

I would like to change it so that it produces one row per movie IF it had a release in the US in 2018.

Thanks for your help. Comments on the use of FILTER or other non idiomatic SPARQL are also welcome.

Alexan · Accepted Answer · 2018-12-20T16:19:13.783

0

You can use GROUP BY:

SELECT ?item  (SAMPLE(?name) as ?Name) (SAMPLE(?publication_date) as ?Date) WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  ?item rdfs:label ?name;
        wdt:P31 wd:Q11424;
        wdt:P495 wd:Q30; # -> country of origin US
        wdt:P577 ?publication_date.

  ?item p:P577 ?publication_statement.
  ?publication_statement pq:P291 ?place.

  FILTER(xsd:date(?publication_date) > "2018-01-01"^^xsd:date)
  FILTER(
     (LANG(?name)) = "en" 
        && ?place=wd:Q30) # -> place of publication
}
GROUP BY ?item
ORDER BY ?Name
LIMIT 10

See this query on Wikidata.

And you need to fix the SELECT line as you can't pass out the indeterminate non-group keys without explicitly saying. See similar question.

edited Dec 20 '18 at 16:19

answered Dec 20 '18 at 16:08

Alexan

8,165
14
74
101

Ah, Duh. That makes sense. What does the `SAMPLE` do in this case? – Damon Snyder Dec 20 '18 at 16:12
1

@drsnyder `SAMPLE` does pick an random value from the set of values bound to the variable per group. For example, there might be multiple names, it picks just a random one. For a more deterministic result, you could also use `MIN` or `MAX` – UninformedUser Dec 20 '18 at 18:18
1

I see. This may not result in exactly what I was looking for but it get's me a lot closer. After looking at this a little more what I want to drill down on is extracting the release date in the US from a given film. I went ahead and accepted this answer because it helped me understand more about how SPARQL works. – Damon Snyder Dec 21 '18 at 04:49

SPARQL query for finding films originating from and released in the United States

1 Answers1