Last week I mentioned that the Linked Movie Database SPARQL endpoint would be fun to play with, and it has been. I used my spreadsheetSPARQL interface to send the following queries to their http://sparql.linkedmdb.org:2020/linkedmdb SPARQL endpoint.

One degree of Kevin Bacon

The following lists all the actors who have appeared in a movie with Kevin Bacon. Or, in more SPARQLy terms, it says “show me ?actorName (with no repeats) where ?kb is the ID for Kevin Bacon, and a given movie has ?kb and ?actor in it, and ?actor has the name ?actorName, but don’t show me ?actorName if the actor is ?kb” (that is, don’t list Kevin himself):

SELECT DISTINCT ?actorName WHERE {


  ?kb    <http://data.linkedmdb.org/resource/movie/actor_name> "Kevin Bacon".


  ?movie <http://data.linkedmdb.org/resource/movie/actor> ?kb;
         <http://data.linkedmdb.org/resource/movie/actor> ?actor.


  ?actor <http://data.linkedmdb.org/resource/movie/actor_name> ?actorName.


  FILTER (?kb != ?actor).
}

I won’t show you all of the 240 names that get returned, but here are the first few:

Eve
Vincent D'Onofrio
Daniel Stern
John C. Reilly
J. T. Walsh
Michael Gross
William Windom
Michael Tucker
Stephen Lang

Versatile actor(s)

Which actors have appeared in both a John Waters movie and a Steven Spielberg movie? (Assign the URI for each director to a variable, find the URI for any actors who worked with both directors, and get the actors’ names.)

SELECT DISTINCT ?actorName WHERE {


  ?dir1      <http://data.linkedmdb.org/resource/movie/director_name> "John Waters".


  ?dir2      <http://data.linkedmdb.org/resource/movie/director_name> "Steven Spielberg".


  ?dir1movie <http://data.linkedmdb.org/resource/movie/director> ?dir1;
             <http://data.linkedmdb.org/resource/movie/actor> ?actor.


  ?dir2movie <http://data.linkedmdb.org/resource/movie/actor> ?actor;
             <http://data.linkedmdb.org/resource/movie/director> ?dir2.


  ?actor     <http://data.linkedmdb.org/resource/movie/actor_name> ?actorName.
}

This query returns only one result: Darren E. Burrows, who was in both Amistad and Cry-Baby. (Fans of the old television show “Northern Exposure” might remember him as Ed Chigliak.)

Woody Allen’s favorite actors

To list everyone who had ever been in a Woody Allen movie, I might use the DISTINCT keyword so that each was only listed once, but I wanted the repetition so that I could see who had been in how many of these movies:

SELECT ?actorName WHERE {


  ?woody  <http://data.linkedmdb.org/resource/movie/director_name> "Woody Allen".


  ?movie  <http://data.linkedmdb.org/resource/movie/director> ?woody;
          <http://data.linkedmdb.org/resource/movie/actor> ?actor.


  ?actor <http://data.linkedmdb.org/resource/movie/actor_name> ?actorName.
}

Adding count(?actorName) after ?actorName caused an error, so either this SPARQL extension isn’t supported by the Linked Movie Database’s SPARQL implementation or I was doing something wrong. Either way, I got what I wanted by copying the output of the query above to a file called waactors.txt and piping that through the following at a Windows command prompt:

type waactors.txt | sort | uniq -c | sort /r

This sorts the names, reduces the list to show each name once with a count of how many times it occurred, and then does a reverse sort to put the names that occurred the most at the top of the list. (The Linux equivalent would be “cat waactors.txt | sort | uniq -c | sort -r”.) The resulting list began with these names:

     26 Woody Allen
     12 Mia Farrow
      7 Diane Keaton
      6 Julie Kavner
      5 Dianne Wiest
      4 Louise Lasser
      3 Tony Roberts
      3 Scarlett Johansson
      3 Judy Davis
      3 Alan Alda

Now we have a clear picture of who his favorite actor is. When I saw that the voice of Marge Simpson beat everyone but Allen himself and his two most famous leading ladies, I wondered what six movies she appeared in. The answer was easy to find out:

SELECT  ?movieName WHERE {


  ?woody  <http://data.linkedmdb.org/resource/movie/director_name> "Woody Allen".


  ?actor <http://data.linkedmdb.org/resource/movie/actor_name> "Julie Kavner".


  ?movie  <http://data.linkedmdb.org/resource/movie/director> ?woody;
          <http://data.linkedmdb.org/resource/movie/actor> ?actor;
          <http://purl.org/dc/terms/title> ?movieName.
}

The answer:

Don't Drink the Water
Deconstructing Harry
Hannah and Her Sisters
New York Stories
Radio Days
Shadows and Fog

Moving further down the list of Allen’s favorite actors, I’m going to go out on a limb and predict that Scarlett Johansson moves ahead of Tony Roberts before Alan Alda does.

6 Comments

By John Cowan on November 7, 2008 9:47 AM

Actually, you have to use “sort -n -r” on Posix systems to force the first field to be interpreted numerically. (I do a lot of this sort of thing.)

By Bob DuCharme on November 7, 2008 9:57 AM

Hi John,

I didn’t need the -n, I assume because the uniq -c output right-justifies the numbers with leading spaces that make the numeric sort come out the way I want it.

By glenn mcdonald on November 7, 2008 3:04 PM

Very cool that you actually managed to get computed answers to these questions.

Not so cool, it seems to me, that the questions are so cumbersome to frame.

I will now tantalize (appall? annoy?) you with what these queries would look like in Thread, the path-based query-language I’m working on at ITA:

“actors who have appeared in a movie with Kevin Bacon”:

Actor:=Kevin Bacon.Movie.Actor

“actors who have appeared in both a John Waters movie and a Steven Spielberg movie”:

Actor:(.Movie.Director:=John Waters:=Steven Spielberg)

“actors who have appeared in Woody Allen movies”:

Director:=Woody Allen.Movie.Actor

“actors who have appeared in Woody Allen movies, with counts”

Actor|Appearances=(.Movie:(.Director:=Woody Allen)._Count):Appearances>0#Appearances

or, more cleverly:

Director:=Woody Allen.Movie/Actor|Movies=(.Nodes._Count)#Movies

You can probably get the gist of most of these without a very detailed explanation of syntax. Except the last one, which is the most fun:

Director - get all nodes of type Director
:=Woody Allen - narrow this list down to the one named Woody Allen
.Movie - get all this director’s movies
/Actor - group these movies by actor (each movie will appear in multiple groups…)
|Movies=(.Nodes._Count) - calculate the number of grouped movies in each actor’s group, and call this “Movies”
#Movies - and sort the set of actor/movie-groups by these counts

No real points until it’s publicly available, obviously. But intriguing, maybe?

By glenn mcdonald on November 7, 2008 3:16 PM

Oh, sorry, the Waters/Spielberg example would be:

Actor:(.Movie.Director:=John Waters,=Steven Spielberg._Count:=2)

or

Actor:(.Movie.Director:=John Waters):(.Movie.Director:=Steven Spielberg)

or, for that matter

Director:=John Waters,=Steven Spielberg/(.Movie.Actor):(.nodes._Count:=2)

By Shahan Khatchadourian on November 23, 2008 11:48 AM

Thanks for the interesting post. The SPARQL endpoint has been shifted back to the D2RQ server:
http://data.linkedmdb.org/sparql

By Fozia on December 4, 2008 1:07 AM

well… i visit your website first time and found this site very usefull and intresting !

well… you guys doing nice work and i just want to say that keep rocking and keep it up !!!!

Regards
Fozia\