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: PREFIX geom: PREFIX bif: SELECT ?atm ?geometry ?bank ?bankLabel WHERE { hint:Query hint:optimizer "None". SERVICE { {?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: PREFIX geom: PREFIX bif: SELECT ?atm ?geometry ?bank ?bankLabel WHERE { hint:Query hint:optimizer "None". SERVICE { {?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