CREATE SERVER wikidata
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (
  endpoint 'https://query.wikidata.org/sparql');
/*
 * this USER MAPPING must be ignored, as the triplestore does not require user authentication
 */
CREATE USER MAPPING FOR postgres SERVER wikidata OPTIONS (user 'foo', password 'bar');
CREATE FOREIGN TABLE atms_munich (
atmid text     OPTIONS (variable '?atm'),
atmwkt text    OPTIONS (variable '?geometry', literaltype 'geo:wktLiteral'),
bankid text    OPTIONS (variable '?bank'),
bankname text  OPTIONS (variable '?bankLabel', literaltype 'xsd:string')
)
SERVER wikidata OPTIONS (
  log_sparql 'true',
  sparql '
  PREFIX lgdo: <http://linkedgeodata.org/ontology/>
  PREFIX geom: <http://geovocab.org/geometry#>
  PREFIX bif: <bif:>
  
  SELECT ?atm ?geometry ?bank ?bankLabel 
  WHERE {
    hint:Query hint:optimizer "None".
    SERVICE <http://linkedgeodata.org/sparql> 
    {
      {?atm a lgdo:Bank; lgdo:atm true.}
      UNION 
      {?atm a lgdo:Atm.}    
      ?atm geom:geometry [geo:asWKT ?geometry];
         lgdo:operator ?operator.
      FILTER(bif:st_intersects(?geometry, bif:st_point(11.5746898, 48.1479876), 5)) # 5 km around Munich
    }
  BIND(STRLANG(?operator, "de") as ?bankLabel) 
  ?bank rdfs:label ?bankLabel.
  { ?bank wdt:P527 wd:Q806724. }
  UNION { ?bank wdt:P1454 wd:Q5349747. }
  MINUS { wd:Q806724 wdt:P3113 ?bank. }
}
'); 
SELECT atmid, bankname, atmwkt
FROM atms_munich
WHERE bankname = 'BBBank';
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


  PREFIX lgdo: <http://linkedgeodata.org/ontology/>
  PREFIX geom: <http://geovocab.org/geometry#>
  PREFIX bif: <bif:>
  
  SELECT ?atm ?geometry ?bank ?bankLabel 
  WHERE {
    hint:Query hint:optimizer "None".
    SERVICE <http://linkedgeodata.org/sparql> 
    {
      {?atm a lgdo:Bank; lgdo:atm true.}
      UNION 
      {?atm a lgdo:Atm.}    
      ?atm geom:geometry [geo:asWKT ?geometry];
         lgdo:operator ?operator.
      FILTER(bif:st_intersects(?geometry, bif:st_point(11.5746898, 48.1479876), 5)) # 5 km around Munich
    }
  BIND(STRLANG(?operator, "de") as ?bankLabel) 
  ?bank rdfs:label ?bankLabel.
  { ?bank wdt:P527 wd:Q806724. }
  UNION { ?bank wdt:P1454 wd:Q5349747. }
  MINUS { wd:Q806724 wdt:P3113 ?bank. }
}


                      atmid                      | bankname |            atmwkt            
-------------------------------------------------+----------+------------------------------
 http://linkedgeodata.org/triplify/node418977189 | BBBank   | POINT(11.5558547 48.1592738)
(1 row)

CREATE FOREIGN TABLE places_below_sea_level (
  wikidata_id text   OPTIONS (variable '?placeid', expression 'STR(?place)'),
  label text         OPTIONS (variable '?labelc', expression 'UCASE(?label)'),
  wkt text           OPTIONS (variable '?location', literaltype 'geo:wktLiteral'),
  elevation numeric  OPTIONS (variable '?elev')
)
SERVER wikidata OPTIONS (
  log_sparql 'true',
  sparql '
  SELECT *
  WHERE
  {
    ?place rdfs:label ?label .
    ?place p:P2044/psv:P2044 ?placeElev.
    ?placeElev wikibase:quantityAmount ?elev.
    ?placeElev wikibase:quantityUnit ?unit.
    bind(0.01 as ?km).
    FILTER( (?elev < ?km*1000 && ?unit = wd:Q11573)
        || (?elev < ?km*3281 && ?unit = wd:Q3710)
        || (?elev < ?km      && ?unit = wd:Q828224) ).
    ?place wdt:P625 ?location.    
    FILTER(LANG(?label)="en")
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
'); 
SELECT wikidata_id, label, wkt
FROM places_below_sea_level
WHERE wikidata_id = 'http://www.wikidata.org/entity/Q61308849'
FETCH FIRST 5 ROWS ONLY;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT (STR(?place) AS ?placeid) (UCASE(?label) AS ?labelc) ?location 
{
    ?place rdfs:label ?label .
    ?place p:P2044/psv:P2044 ?placeElev.
    ?placeElev wikibase:quantityAmount ?elev.
    ?placeElev wikibase:quantityUnit ?unit.
    bind(0.01 as ?km).
    FILTER( (?elev < ?km*1000 && ?unit = wd:Q11573)
        || (?elev < ?km*3281 && ?unit = wd:Q3710)
        || (?elev < ?km      && ?unit = wd:Q828224) ).
    ?place wdt:P625 ?location.    
    FILTER(LANG(?label)="en")
    SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
 FILTER(STR(?place) = "http://www.wikidata.org/entity/Q61308849")
}
LIMIT 5

               wikidata_id                |     label     |         wkt          
------------------------------------------+---------------+----------------------
 http://www.wikidata.org/entity/Q61308849 | TUKTOYAKTUK A | Point(-133.03 69.43)
(1 row)

/*
 * Expression pushdown tests with LCASE, UCASE, STRLEN, STRBEFORE, 
 * STRAFTER, CONCAT, STRSTARTS, STRENDS and LANG
 */
CREATE FOREIGN TABLE european_countries (
  uri text        OPTIONS (variable '?country', literaltype 'xsd:string'),
  label text      OPTIONS (variable '?label', literaltype '*'),
  nativename name OPTIONS (variable '?nativename', language '*'),
  len_label int   OPTIONS (variable '?1len2', expression 'STRLEN(?nativename)'),
  uname text      OPTIONS (variable '?ucase_nativename', expression 'UCASE(?nativename)'),
  lname text      OPTIONS (variable '?lcase_nativename', expression 'LCASE(?nativename)'),
  language text   OPTIONS (variable '?language', expression 'LANG(?nativename)', literaltype 'xsd:string'),
  base_url text   OPTIONS (variable '?b4se', expression 'STRBEFORE(STR(?country),"Q")', literaltype 'xsd:string'),
  qid text        OPTIONS (variable '?q1d', expression 'STRAFTER(STR(?country),"entity/")', literaltype 'xsd:string'),
  ctlang text     OPTIONS (variable '?ct', expression 'CONCAT(STR(?country),UCASE(?nativename))'),
  dt date         OPTIONS (variable '?det', expression '"2002-03-08"^^xsd:date', literaltype 'xsd:date'),
  ts timestamp    OPTIONS (variable '?ts', expression '"2002-03-08T14:33:42"^^xsd:dateTime', literaltype 'xsd:dateTime'),
  bt boolean      OPTIONS (variable '?but', expression 'STRSTARTS(STR(?country),"http")', literaltype 'xsd:boolean'),
  bf boolean      OPTIONS (variable '?buf', expression 'STRENDS(STR(?country),"http")', literaltype 'xsd:boolean')
)
SERVER wikidata OPTIONS (
  log_sparql 'true',
  sparql '
  SELECT *
  {
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
'); 
SELECT uri, label, language, bf, bt
FROM european_countries
WHERE 
  language = 'de' AND 
  language <> 'en' AND
  language IN ('de', 'en') AND
  language NOT IN ('es','pt') AND 
 
  len_label <= 10 AND
  len_label IN (8,9) AND
  len_label NOT IN (10,11) AND
  dt NOT IN ('2000-01-01','2000-01-02') AND
  dt IN ('2002-03-08','2000-01-02') AND
  dt = '2002-03-08' AND
  dt != '2002-03-10' AND
  ts NOT IN ('2002-03-08 12:00:00', '2002-03-08 13:00:00') AND
  ts IN ('2002-03-08T14:33:42', '2002-03-08 13:00:00') AND
  ts = '2002-03-08 14:33:42' AND
  ts <> '2002-03-08 11:30:00' AND
  qid IN ('Q32','Q35') AND
  qid NOT IN ('foo','bar') AND
  base_url = 'http://www.wikidata.org/entity/' AND
  ctlang = 'http://www.wikidata.org/entity/Q32LUXEMBURG'
ORDER by language;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?label (STRLEN(?nativename) AS ?1len2) (LANG(?nativename) AS ?language) (STRBEFORE(STR(?country),"Q") AS ?b4se) (STRAFTER(STR(?country),"entity/") AS ?q1d) (CONCAT(STR(?country),UCASE(?nativename)) AS ?ct) ("2002-03-08"^^xsd:date AS ?det) ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) (STRSTARTS(STR(?country),"http") AS ?but) (STRENDS(STR(?country),"http") AS ?buf) 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
   FILTER(LANG(?nativename) != "en"^^xsd:string)
 FILTER(LANG(?nativename) IN ("de"^^xsd:string, "en"^^xsd:string))
 FILTER(LANG(?nativename) NOT IN ("es"^^xsd:string, "pt"^^xsd:string))
 FILTER(STRLEN(?nativename) <= 10)
 FILTER(STRLEN(?nativename) IN (8, 9))
 FILTER(STRLEN(?nativename) NOT IN (10, 11))
 FILTER("2002-03-08"^^xsd:date NOT IN ("2000-01-01"^^xsd:date, "2000-01-02"^^xsd:date))
 FILTER("2002-03-08"^^xsd:date IN ("2002-03-08"^^xsd:date, "2000-01-02"^^xsd:date))
 FILTER("2002-03-08"^^xsd:date != "2002-03-10"^^xsd:date)
 FILTER("2002-03-08T14:33:42"^^xsd:dateTime NOT IN ("2002-03-08T12:00:00.000000"^^xsd:dateTime, "2002-03-08T13:00:00.000000"^^xsd:dateTime))
 FILTER("2002-03-08T14:33:42"^^xsd:dateTime IN ("2002-03-08T14:33:42.000000"^^xsd:dateTime, "2002-03-08T13:00:00.000000"^^xsd:dateTime))
 FILTER("2002-03-08T14:33:42"^^xsd:dateTime != "2002-03-08T11:30:00.000000"^^xsd:dateTime)
 FILTER(STRAFTER(STR(?country),"entity/") IN ("Q32"^^xsd:string, "Q35"^^xsd:string))
 FILTER(STRAFTER(STR(?country),"entity/") NOT IN ("foo"^^xsd:string, "bar"^^xsd:string))
 FILTER(LANG(?nativename) = "de"^^xsd:string)
 FILTER("2002-03-08"^^xsd:date = "2002-03-08"^^xsd:date)
 FILTER("2002-03-08T14:33:42"^^xsd:dateTime = "2002-03-08T14:33:42.000000"^^xsd:dateTime)
 FILTER(STRBEFORE(STR(?country),"Q") = "http://www.wikidata.org/entity/"^^xsd:string)
 FILTER(CONCAT(STR(?country),UCASE(?nativename)) = "http://www.wikidata.org/entity/Q32LUXEMBURG")
}

                uri                 | label | language | bf | bt 
------------------------------------+-------+----------+----+----
 http://www.wikidata.org/entity/Q32 |       | de       | f  | t
(1 row)

/*
 * Test WHERE conditions with boolean columns using IS and IS NOT
 */
SELECT uri, nativename
FROM european_countries
WHERE
  nativename = 'Luxembourg' AND
  bf IS false AND
  bf IS NOT true AND
  bt IS true AND
  bt IS NOT false;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename (STRSTARTS(STR(?country),"http") AS ?but) (STRENDS(STR(?country),"http") AS ?buf) 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
   FILTER(STRENDS(STR(?country),"http") = "false"^^xsd:boolean)
 FILTER(STRENDS(STR(?country),"http") != "true"^^xsd:boolean)
 FILTER(STRSTARTS(STR(?country),"http") = "true"^^xsd:boolean)
 FILTER(STRSTARTS(STR(?country),"http") != "false"^^xsd:boolean)
 FILTER(STR(?nativename) = "Luxembourg")
}

                uri                 | nativename 
------------------------------------+------------
 http://www.wikidata.org/entity/Q32 | Luxembourg
(1 row)

SELECT uri, nativename 
FROM european_countries
WHERE
  nativename = 'Luxembourg' AND
  bf IS false AND
  NOT bf IS true AND
  bt IS true AND
  NOT bt IS false;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename (STRSTARTS(STR(?country),"http") AS ?but) (STRENDS(STR(?country),"http") AS ?buf) 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
   FILTER(STRENDS(STR(?country),"http") = "false"^^xsd:boolean)
 FILTER(STRENDS(STR(?country),"http") != "true"^^xsd:boolean)
 FILTER(STRSTARTS(STR(?country),"http") = "true"^^xsd:boolean)
 FILTER(STRSTARTS(STR(?country),"http") != "false"^^xsd:boolean)
 FILTER(STR(?nativename) = "Luxembourg")
}

                uri                 | nativename 
------------------------------------+------------
 http://www.wikidata.org/entity/Q32 | Luxembourg
(1 row)

/*
 * These boolean expressions won't be pushed down
 */
SELECT uri, nativename 
FROM european_countries
WHERE
  nativename = 'Luxembourg' AND
  bf = false AND
  bf != true AND
  bt = true AND
  bt != false;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename (STRSTARTS(STR(?country),"http") AS ?but) (STRENDS(STR(?country),"http") AS ?buf) 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
   FILTER(STR(?nativename) = "Luxembourg")
}

                uri                 | nativename 
------------------------------------+------------
 http://www.wikidata.org/entity/Q32 | Luxembourg
(1 row)

DO $$
BEGIN    
  CREATE TABLE tmp_eu_countries AS
  SELECT uri, nativename 
  FROM european_countries
  ORDER BY nativename
  OFFSET 0 LIMIT 5;  
END; $$;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
ORDER BY  ASC (?nativename)
LIMIT 5

SELECT * FROM tmp_eu_countries;
                 uri                 |     nativename     
-------------------------------------+--------------------
 http://www.wikidata.org/entity/Q35  | Danmark
 http://www.wikidata.org/entity/Q35  | Danmarks Rige
 http://www.wikidata.org/entity/Q35  | Kongeriget Danmark
 http://www.wikidata.org/entity/Q213 | Česká republika
 http://www.wikidata.org/entity/Q219 | България
(5 rows)

DROP TABLE tmp_eu_countries;
/* Pagination with OFFSET + LIMIT */
DO $$
DECLARE 
 chunk_size int := 5;
 max int := 15;
BEGIN

  CREATE TEMPORARY TABLE local (
    id text DEFAULT '',
    name text DEFAULT ''
  );

  /* Select records from the foreign table in chunks
   * in the size of 'chunk_size' with a maximum of
   * 'max' records.
   */
  FOR i IN 0..max-chunk_size BY chunk_size LOOP
    INSERT INTO local
    SELECT uri, nativename 
    FROM european_countries
    ORDER BY uri 
    OFFSET i LIMIT chunk_size;
  END LOOP;

END; $$;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
ORDER BY  ASC (?country)
LIMIT 5

INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
ORDER BY  ASC (?country)
LIMIT 10

INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
ORDER BY  ASC (?country)
LIMIT 15

/* Compare the stored records from the loop with a
 * single query with a LIMIT 'max' */
WITH j AS (
SELECT uri, nativename 
  FROM european_countries
  ORDER BY uri 
  LIMIT 15
)
SELECT * FROM local EXCEPT SELECT * FROM j;
INFO:  SPARQL query sent to 'https://query.wikidata.org/sparql':


SELECT ?country ?nativename 
{
    wd:Q458 wdt:P150 ?country.
    OPTIONAL { ?country wdt:P1705 ?nativename }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  }
ORDER BY  ASC (?country)
LIMIT 15

 id | name 
----+------
(0 rows)

DROP SERVER wikidata CASCADE;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to user mapping for postgres on server wikidata
drop cascades to foreign table atms_munich
drop cascades to foreign table places_below_sea_level
drop cascades to foreign table european_countries