Complex questions

Complex questions¶

Queries¶

Info

It could be that data has been added to the database and that the here presented counts and results therefore do not reflect the current state of the dataset.

The art of federated querying

Not every endpoint deals with your federated query in the same manner. Filters and joins might be processed differently, leading to long query times or even timeouts. Writing your query as concise and efficient as possible can shorten the query time dramatically and will put less stress on an external triplestore (e.g. Wikidata). If this does not work out, then splitting your query into several smaller queries might help.

1. Which films directed by Ernst Lubitsch, and starring Pola Negri, were shown in Groningen?¶

Query

SPARQL

PREFIX sem: <http://semanticweb.cs.vu.nl/2009/11/sem/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX schema: <http://schema.org/>

SELECT DISTINCT ?cc ?title WHERE {

  SERVICE <https://query.wikidata.org/sparql> {
            ?wdMovie wdt:P57 wd:Q51562 ; # director Ernst Lubitsch
                    wdt:P161 wd:Q230633 ; # cast member Pola Negri
                    wdt:P345 ?imdb ; # imdb code
                    wdt:P8296 ?cinemacontext . # cc code
    BIND(URI(CONCAT("http://www.cinemacontext.nl/id/", ?cinemacontext)) AS ?cc)
  }

  ?cc schema:name ?title .

  ?programme a schema:Event ;
            sem:hasEarliestBeginTimeStamp ?date ;
            schema:subEvent [ a schema:ScreeningEvent ;
                                schema:workPresented ?cc ] ;
            schema:location{2} ?place . 

  ?place a schema:Place ;
        schema:address [ a schema:PostalAddress ;
                          schema:addressLocality ?city ] .

  FILTER(?city = "Groningen")

} ORDER BY ?date

Result (29-10-2020)

1	http://www.cinemacontext.nl/id/F006163	Sumurun (1920)
2	http://www.cinemacontext.nl/id/F003478	Madame Dubarry (1919)
3	http://www.cinemacontext.nl/id/F006586	Bergkatze, Die (1921)
4	http://www.cinemacontext.nl/id/F006003	Carmen (1918)
5	http://www.cinemacontext.nl/id/F006527	Flamme, Die (1922)
6	http://www.cinemacontext.nl/id/F005279	Forbidden Paradise (1924)

2. How many French films were shown in Rotterdam in every year between 1942 and 1945?¶

3. How many long documentary films were shown in the Netherlands in 1934, and in which countries were those films produced?¶

4. How many cinemas were active in Rotterdam in every year between 1910 and 1930?¶

5. Which cinema owned by Abraham Tuschinski has shown the film De Jantjes (1934) most often?¶

6. Which films were screened by the Netherlands Film League in Utrecht between 1927 and 1933?¶

7. How many French films were shown in Utrecht and in Groningen in each year between 1922 and 1925?¶

8. Give a number of screenings per city per film by German film director Paul Verhoeven (1901).¶

Query

Two subqueries are issued:

Wikidata is asked to return all entities that have a specific director (Q72302) and have an imdb id. This is a result set of 31 (as of 2020).
The local triplestore is asked to give information for programmes in a particular city. Every program should have a screeningEvent that presents a movie. This movie has a schema:sameAs property that points to an imdb url. This url is stripped, after which we have the imdb id in the same way that it is listed on Wikidata. Since this is bound to the ?imdb variable, this serves as join in the result set.

SPARQL

PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX schema: <http://schema.org/>

SELECT (COUNT(?screening) AS ?count) ?cc ?title ?city WHERE {

  SERVICE <https://query.wikidata.org/sparql> {
            ?wdMovie wdt:P57 wd:Q72302 ; # director Paul Verhoeven
                    wdt:P345 ?imdb . # imdb code
        BIND(URI(CONCAT("https://www.imdb.com/title/", ?imdb)) AS ?imdbURL)
  }

  {
    SELECT * WHERE {
      GRAPH <https://data.create.humanities.uva.nl/id/cinemacontext/> {
        ?cc a schema:Movie ;
                  schema:name ?title ;
                  schema:sameAs ?imdbURL ;
                  ^schema:workPresented ?screening .

        ?screening ^schema:subEvent/schema:location ?cinema .

        ?cinema schema:location/schema:address/schema:addressLocality ?city .  

      }
    }
  }
} GROUP BY ?cc ?title ?city ORDER BY DESC(?count) ?city ?title

Result (per 17 Sept 2020):

count	cc	title	city
14	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Amsterdam
12	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Den Haag
11	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Amsterdam
8	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Den Haag
7	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Rotterdam
6	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Amsterdam
6	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Rotterdam
5	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Den Haag
5	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Utrecht
3	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Amsterdam
3	http://www.cinemacontext.nl/id/F000204	Renate im Quartett (1939)	Amsterdam
3	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Rotterdam
3	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Utrecht
3	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Utrecht
2	http://www.cinemacontext.nl/id/F000877	Salonwagen E417 (1939)	Amsterdam
2	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Den Haag
2	http://www.cinemacontext.nl/id/F000877	Salonwagen E417 (1939)	Den Haag
2	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Groningen
2	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Groningen
2	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Groningen
2	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Leiden
2	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Rotterdam
2	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Utrecht
1	http://www.cinemacontext.nl/id/F000204	Renate im Quartett (1939)	Den Haag
1	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Groningen
1	http://www.cinemacontext.nl/id/F000204	Renate im Quartett (1939)	Groningen
1	http://www.cinemacontext.nl/id/F000877	Salonwagen E417 (1939)	Groningen
1	http://www.cinemacontext.nl/id/F000481	Gold in New Frisco (1939)	Leiden
1	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Leiden
1	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Leiden
1	http://www.cinemacontext.nl/id/F000204	Renate im Quartett (1939)	Leiden
1	http://www.cinemacontext.nl/id/F000877	Salonwagen E417 (1939)	Rotterdam
1	http://www.cinemacontext.nl/id/F000544	Fledermaus, Die (1937)	Schiedam
1	http://www.cinemacontext.nl/id/F000125	Grosse Schatten, Der (1942)	Schiedam
1	http://www.cinemacontext.nl/id/F000062	Nacht in Venedig, Die (1942)	Schiedam
1	http://www.cinemacontext.nl/id/F000204	Renate im Quartett (1939)	Utrecht
1	http://www.cinemacontext.nl/id/F000877	Salonwagen E417 (1939)	Utrecht

Last update: January 4, 2021