Query examples


#1

Some examples of queries for a tutorial/demo:

Count number of emails

PREFIX schema: <http://schema.org/>
SELECT (COUNT(DISTINCT ?m) AS ?c) WHERE { ?m a schema:EmailMessage }

When do I sent my email?

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT ?hour (COUNT(DISTINCT ?email) AS ?count) WHERE {
  ?email a schema:EmailMessage .
  ?email schema:sender/personal:sameAs*/schema:email <mailto:MY_ADDRESS> .
  ?email schema:dateSent ?dateEmail .
  BIND(HOURS(?dateEmail) AS ?hour)
} GROUP BY ?hour ORDER BY ?hour

Use of email address domain per number of emails per year

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT ?domain ?year (COUNT(DISTINCT ?email) AS ?count) WHERE {
  ?email a schema:EmailMessage .
  ?email schema:sender/schema:email/personal:domain ?domain .
  ?email schema:dateSent ?date .
  BIND(YEAR(?date) AS ?year)
} GROUP BY ?domain ?year ORDER BY DESC(COUNT(DISTINCT ?email))

Number of emails I have replied to

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT (COUNT(DISTINCT ?email) AS ?count) WHERE {
  ?email a schema:EmailMessage . 
  ?reply personal:inReplyTo ?email . 
  ?reply schema:sender/personal:sameAs*/schema:email <mailto:MY_EMAIL> .
}

Ratio of email sent directly to me I have replied to

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT ((?countIReply / ?count) * 100 AS ?c) WHERE {
  {
    SELECT (COUNT(?email) AS ?countIReply) WHERE {
      ?email a schema:EmailMessage . 
      ?reply personal:inReplyTo ?email .
      ?email personal:primaryRecipient/personal:sameAs*/schema:email <mailto:MY_EMAIL> .
      ?reply schema:sender/personal:sameAs*/schema:email <mailto:MY_EMAIL> .
    }
  }
  {
    SELECT (COUNT(?email) AS ?count) WHERE {
      ?email a schema:EmailMessage .
      ?email personal:primaryRecipient/personal:sameAs*/schema:email <mailto:MY_EMAIL> .
    }
  }
}

Events in June 2016 with their duration

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT ?event ?start ?end (personal:durationInMillis(?start, ?end) /3600000 AS ?duration) WHERE {
  ?event a schema:Event ;
             schema:startDate ?start ;
             schema:endDate ?end .
  FILTER(YEAR(?start) = 2016 && MONTH(?start) = 6)
 } ORDER BY ?start LIMIT 100

Events in June 2016 with their location(s)

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
SELECT ?eventName ?locName ?locUri WHERE {
      ?event a schema:Event ;
                  schema:location/personal:sameAs* ?location ;
                  schema:name ?eventName ;
                  schema:startDate ?start .
     FILTER(YEAR(?start) = 2016 && MONTH(?start) = 6) .
    ?location schema:name ?locName ;
                    schema:geo ?geo .
    ?geo schema:latitude ?eLat ; schema:longitude ?eLon .
    BIND(IRI(CONCAT("https://maps.google.com/maps?ll=", STR(?eLat), ",", STR(?eLon))) AS ?locUri)
} ORDER BY ?start LIMIT 100

Events with two different locations with coordinates beeing distant of more than 100m

With GeoSPARQL

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX units: <http://www.opengis.net/def/uom/OGC/1.0/>
SELECT DISTINCT ?eventName ?locationName1 ?locationName2 ?distance WHERE {
      ?event a schema:Event ;
                  schema:location/personal:sameAs* ?loc1 ;
                  schema:location/personal:sameAs* ?loc2 ;
                  schema:name ?eventName ;
                  schema:startDate ?start .
     FILTER(?loc1 != ?loc2) .
    ?loc1 schema:name ?locationName1 ;
              schema:geo/geo:asWKT ?wkt1 .
    ?loc2 schema:name ?locationName2 ;
              schema:geo/geo:asWKT ?wkt2 .
    BIND(geof:distance(?wkt1, ?wkt2, units:metre) AS ?distance)
    FILTER(?distance >= 100)
    FILTER(?locationName1 < ?locationName2) #Hack to avoid duplicates results (with ?loc1 and ?loc2) permutated
} ORDER BY DESC(?distance)

Full text search

With Sesame full text search feature

PREFIX search: <http://www.openrdf.org/contrib/lucenesail#>
SELECT ?subj ?prop ?score ?snippet WHERE {
  ?subj search:matches [
     search:query "XXX";
     search:property ?prop;
     search:score ?score;
     search:snippet ?snippet
  ]
} ORDER BY DESC(?score) LIMIT 10


#2

List Email Messages between two dates (e.g. mails in 2016)

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?message ?received ?headline ?senderEmail ?senderName WHERE {
  ?message a schema:EmailMessage ;
      schema:dateReceived ?received .
   OPTIONAL{ 
       ?message schema:headline ?headline . }
    OPTIONAL{ 
        ?message schema:sender/schema:email/schema:name ?senderEmail . 
            OPTIONAL { ?message schema:sender/schema:name ?senderName.  }
     }
  FILTER ( ?received <= "2017-01-01T00:00:00.000"^^xsd:dateTime  &&
                  ?received >= "2016-01-01T00:00:00.000"^^xsd:dateTime)
 } ORDER BY ?received LIMIT 100

(sub)Graph of who I send messages to

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

CONSTRUCT{
 ?sender schema:sender ?receiver .
 ?sender schema:name ?senderName .
 ?receiver schema:name ?receiverName .
} WHERE {
 ?receiver ^schema:recipient/schema:sender ?sender .
 ?sender schema:email <mailto:MY_ADDRESS> .
 OPTIONAL { ?sender personal:sameAs*/schema:name ?senderName . }
 OPTIONAL { ?receiver personal:sameAs*/schema:name ?receiverName . }
} LIMIT 100

Get my contacts

PREFIX schema: <http://schema.org/>
PREFIX personal: <http://thymeflow.com/personal#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT
?agent ?email ?name ?givenName ?familyName ?telephone ?organization  ?source 
WHERE {
GRAPH ?source {
  ?agent a personal:Agent ;
     schema:name ?name .
OPTIONAL {
   ?agent schema:email/schema:name ?email .
 }
OPTIONAL{
   ?agent schema:givenName ?givenName ;
               schema:familyName ?familyName .
  }
OPTIONAL{
   ?agent schema:memberOf/schema:name ?organization .
  }
  ?agent schema:telephone ?telephone .
}
} LIMIT 100

Insert data

PREFIX dc: <http://purl.org/dc/elements/1.1/>
INSERT DATA
{ 
  <http://example/book1> dc:title "A new book" ;
                         dc:creator "A.N.Other" .
}

Delete data

PREFIX dc: <http://purl.org/dc/elements/1.1/>
DELETE DATA
{ 
  <http://example/book1> dc:title "A new book" ;
                         dc:creator "A.N.Other" .
}