CREATE SERVER getty FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'http://vocab.getty.edu/sparql.xml', format 'application/sparql-results+xml', query_param 'query' ); CREATE FOREIGN TABLE getty_places ( uri text OPTIONS (variable '?place'), name text OPTIONS (variable '?name'), namec text OPTIONS (variable '?namecapital', expression 'UCASE(?name)'), lon numeric OPTIONS (variable '?lon'), lat numeric OPTIONS (variable '?lat') ) SERVER getty OPTIONS ( log_sparql 'true', sparql ' PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: SELECT * WHERE { ?place skos:inScheme tgn: ; foaf:focus ?geouri ; foaf:focus [ontogeo:within(50.787185 3.389722 53.542265 7.169019)] ; gvp:parentString ?name . ?geouri a schema:Place ; geo:lat ?lat ; geo:long ?lon } '); SELECT DISTINCT name, lon, lat FROM getty_places ORDER BY lat LIMIT 10; INFO: SPARQL query sent to 'http://vocab.getty.edu/sparql.xml': PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: SELECT DISTINCT ?name ?lon ?lat { ?place skos:inScheme tgn: ; foaf:focus ?geouri ; foaf:focus [ontogeo:within(50.787185 3.389722 53.542265 7.169019)] ; gvp:parentString ?name . ?geouri a schema:Place ; geo:lat ?lat ; geo:long ?lon } ORDER BY ASC (?lat) ASC (?name) ASC (?lon) INFO: SPARQL returned 19666 records. name | lon | lat --------------------------------------------------+----------+----------- Limburg, Flanders, Belgium, Europe, World | 5.44198 | 50.787231 Limburg, Flanders, Belgium, Europe, World | 5.378992 | 50.787296 Limburg, Flanders, Belgium, Europe, World | 5.175732 | 50.787592 East Flanders, Flanders, Belgium, Europe, World | 3.986098 | 50.787634 East Flanders, Flanders, Belgium, Europe, World | 3.816052 | 50.787787 Vlaams-Brabant, Flanders, Belgium, Europe, World | 4.197696 | 50.787941 Vlaams-Brabant, Flanders, Belgium, Europe, World | 4.837764 | 50.788137 West Flanders, Flanders, Belgium, Europe, World | 3.450666 | 50.788186 East Flanders, Flanders, Belgium, Europe, World | 3.960753 | 50.788248 East Flanders, Flanders, Belgium, Europe, World | 3.753994 | 50.788325 (10 rows) SELECT uri, lon, lat FROM getty_places WHERE name = 'West Flanders, Flanders, Belgium, Europe, World' ORDER BY lon LIMIT 3; INFO: SPARQL query sent to 'http://vocab.getty.edu/sparql.xml': PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: SELECT ?place ?name ?lon ?lat { ?place skos:inScheme tgn: ; foaf:focus ?geouri ; foaf:focus [ontogeo:within(50.787185 3.389722 53.542265 7.169019)] ; gvp:parentString ?name . ?geouri a schema:Place ; geo:lat ?lat ; geo:long ?lon ## rdf_fdw pushdown conditions ## FILTER(?name = "West Flanders, Flanders, Belgium, Europe, World") } ORDER BY ASC (?lon) LIMIT 3 INFO: SPARQL returned 3 records. uri | lon | lat ------------------------------------+----------+----------- http://vocab.getty.edu/tgn/7241965 | 3.390464 | 50.899208 http://vocab.getty.edu/tgn/7250496 | 3.39086 | 50.839573 http://vocab.getty.edu/tgn/7251247 | 3.391555 | 51.161424 (3 rows) SELECT * FROM ( SELECT DISTINCT ON (name) name, lon, lat FROM getty_places WHERE lat BETWEEN 52.50 AND 52.60 AND lon BETWEEN 4.81 AND 4.82) j ORDER BY lat LIMIT 3; INFO: SPARQL query sent to 'http://vocab.getty.edu/sparql.xml': PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: SELECT ?name ?lon ?lat { ?place skos:inScheme tgn: ; foaf:focus ?geouri ; foaf:focus [ontogeo:within(50.787185 3.389722 53.542265 7.169019)] ; gvp:parentString ?name . ?geouri a schema:Place ; geo:lat ?lat ; geo:long ?lon ## rdf_fdw pushdown conditions ## FILTER(?lat >= 52.50) FILTER(?lat <= 52.60) FILTER(?lon >= 4.81) FILTER(?lon <= 4.82) } ORDER BY ASC (?name) INFO: SPARQL returned 6 records. name | lon | lat -------------------------------------------+----------+------ Netherlands, Europe, World | 4.8167 | 52.5 North Holland, Netherlands, Europe, World | 4.816667 | 52.5 (2 rows) /* * == Non-Italians Who Worked in Italy == * Find non-Italians who worked in Italy and lived during a given time range * Having event that took place in tgn:1000080 Italy or any of its descendants * Birth date between 1250 and 1780 * Just for variety, we look for artists as descendants of facets ulan:500000003 "Corporate bodies" or ulan:500000002 "Persons, Artists", rather than having type "artist" as we did in previous queries. In the previous query we used values{..} but we here use filter(in(..)). * Not having nationality aat:300111198 Italian or any of its descendants */ CREATE FOREIGN TABLE getty_non_italians ( uri text OPTIONS (variable '?x'), name text OPTIONS (variable '?name'), bio text OPTIONS (variable '?bio'), birth int OPTIONS (variable '?birth') ) SERVER getty OPTIONS ( log_sparql 'true', sparql ' PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: PREFIX foaf: SELECT ?x ?name ?bio ?birth { {SELECT DISTINCT ?x {?x foaf:focus/bio:event/(schema:location|(schema:location/gvp:broaderExtended)) tgn:1000080-place}} ?x gvp:prefLabelGVP/xl:literalForm ?name; foaf:focus/gvp:biographyPreferred [ schema:description ?bio; gvp:estStart ?birth]. FILTER ("1250"^^xsd:gYear <= ?birth && ?birth <= "1780"^^xsd:gYear) FILTER EXISTS {?x gvp:broaderExtended ?facet. FILTER(?facet in (ulan:500000003, ulan:500000002))} FILTER NOT EXISTS {?x foaf:focus/(schema:nationality|(schema:nationality/gvp:broaderExtended)) aat:300111198}} '); SELECT name, bio, birth FROM getty_non_italians WHERE bio ~~* '%artist%' ORDER BY birth LIMIT 10; INFO: SPARQL query sent to 'http://vocab.getty.edu/sparql.xml': PREFIX ontogeo: PREFIX geo: PREFIX gvp: PREFIX skos: PREFIX schema: PREFIX foaf: SELECT ?x ?name ?bio ?birth { {SELECT DISTINCT ?x {?x foaf:focus/bio:event/(schema:location|(schema:location/gvp:broaderExtended)) tgn:1000080-place}} ?x gvp:prefLabelGVP/xl:literalForm ?name; foaf:focus/gvp:biographyPreferred [ schema:description ?bio; gvp:estStart ?birth]. FILTER ("1250"^^xsd:gYear <= ?birth && ?birth <= "1780"^^xsd:gYear) FILTER EXISTS {?x gvp:broaderExtended ?facet. FILTER(?facet in (ulan:500000003, ulan:500000002))} FILTER NOT EXISTS {?x foaf:focus/(schema:nationality|(schema:nationality/gvp:broaderExtended)) aat:300111198}} INFO: SPARQL returned 174 records. name | bio | birth -------------------------------------+-----------------------------------------------------------------------+------- Juán de España | Spanish artist and goldsmith, active 1455 | 1415 Coecke van Aelst, Pieter, the elder | Flemish artist, architect, and author, 1502-1550 | 1502 Worst, Jan | Dutch artist, active ca. 1645-1655 | 1605 Mander, Karel van, III | Dutch portraitist and decorative artist, 1608-1670, active in Denmark | 1608 Ulft, Jacob van der | Dutch artist, 1627-1689 | 1627 Fiammingo, Giacomo | Flemish artist, fl. 1655 | 1635 Marotte, Charles | French artist, fl. ca.1719-1743 | 1699 Troll, Johann Heinrich | Swiss artist, 1756-1824 | 1756 Beys, G. | French artist, fl. ca.1786-1800 | 1766 Vaucher, Gabriel Constant | Swiss artist, 1768-1814 | 1768 (10 rows) DROP SERVER getty CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to foreign table getty_places drop cascades to foreign table getty_non_italians