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) 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; 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 FILTER(?name = "West Flanders, Flanders, Belgium, Europe, World") } ORDER BY ASC (?lon) 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 http://vocab.getty.edu/tgn/1026705 | 3.394156 | 51.040392 http://vocab.getty.edu/tgn/7259441 | 3.397425 | 50.933036 http://vocab.getty.edu/tgn/7252418 | 3.4 | 50.933333 http://vocab.getty.edu/tgn/7244225 | 3.4 | 50.966667 http://vocab.getty.edu/tgn/7254794 | 3.4 | 50.85 http://vocab.getty.edu/tgn/7238735 | 3.4 | 50.883333 http://vocab.getty.edu/tgn/7243404 | 3.4 | 50.816667 http://vocab.getty.edu/tgn/7251715 | 3.400297 | 50.946335 http://vocab.getty.edu/tgn/7254795 | 3.401288 | 50.838681 http://vocab.getty.edu/tgn/7244196 | 3.401882 | 51.012903 http://vocab.getty.edu/tgn/7249454 | 3.402476 | 50.863894 http://vocab.getty.edu/tgn/7259802 | 3.412111 | 51.034224 http://vocab.getty.edu/tgn/7245335 | 3.413569 | 51.058347 http://vocab.getty.edu/tgn/7256042 | 3.415451 | 50.909112 http://vocab.getty.edu/tgn/7245720 | 3.416172 | 50.96429 http://vocab.getty.edu/tgn/7249060 | 3.416667 | 51 http://vocab.getty.edu/tgn/7251467 | 3.416667 | 51.05 http://vocab.getty.edu/tgn/7251057 | 3.416667 | 51.033333 http://vocab.getty.edu/tgn/7241847 | 3.416667 | 50.85 http://vocab.getty.edu/tgn/7253626 | 3.416667 | 50.883333 http://vocab.getty.edu/tgn/7243766 | 3.416667 | 50.833333 http://vocab.getty.edu/tgn/7252741 | 3.416667 | 50.916667 http://vocab.getty.edu/tgn/7254082 | 3.416667 | 50.8 http://vocab.getty.edu/tgn/7259328 | 3.417234 | 50.824914 http://vocab.getty.edu/tgn/7245179 | 3.41954 | 50.996632 http://vocab.getty.edu/tgn/7252554 | 3.420431 | 50.806933 http://vocab.getty.edu/tgn/7251668 | 3.421124 | 50.917287 http://vocab.getty.edu/tgn/7253670 | 3.422087 | 50.879741 http://vocab.getty.edu/tgn/7254576 | 3.424195 | 50.992445 http://vocab.getty.edu/tgn/7240363 | 3.425607 | 50.842148 http://vocab.getty.edu/tgn/7241823 | 3.426499 | 50.808221 http://vocab.getty.edu/tgn/1026794 | 3.427562 | 50.888978 http://vocab.getty.edu/tgn/7250327 | 3.431847 | 50.937889 http://vocab.getty.edu/tgn/7245401 | 3.433333 | 51 http://vocab.getty.edu/tgn/7245713 | 3.433333 | 50.95 http://vocab.getty.edu/tgn/7251920 | 3.433333 | 50.85 http://vocab.getty.edu/tgn/7241693 | 3.433333 | 50.866667 http://vocab.getty.edu/tgn/7242017 | 3.433333 | 50.866667 http://vocab.getty.edu/tgn/7247495 | 3.433333 | 50.883333 http://vocab.getty.edu/tgn/7243848 | 3.433729 | 50.820134 http://vocab.getty.edu/tgn/7253725 | 3.434125 | 50.823303 http://vocab.getty.edu/tgn/7249744 | 3.436056 | 50.864364 http://vocab.getty.edu/tgn/7241983 | 3.440068 | 50.797029 http://vocab.getty.edu/tgn/7251353 | 3.443138 | 50.932441 http://vocab.getty.edu/tgn/7239946 | 3.446831 | 50.847425 http://vocab.getty.edu/tgn/7259585 | 3.45 | 50.883333 http://vocab.getty.edu/tgn/7240315 | 3.45 | 50.883333 http://vocab.getty.edu/tgn/7256022 | 3.45 | 50.816667 http://vocab.getty.edu/tgn/7241570 | 3.45 | 50.816667 http://vocab.getty.edu/tgn/7248375 | 3.45 | 50.816667 http://vocab.getty.edu/tgn/7252182 | 3.45 | 50.8 http://vocab.getty.edu/tgn/7250499 | 3.45 | 50.8 http://vocab.getty.edu/tgn/7244158 | 3.450666 | 50.788186 http://vocab.getty.edu/tgn/7249577 | 3.463173 | 50.944057 http://vocab.getty.edu/tgn/7248381 | 3.466667 | 50.833333 http://vocab.getty.edu/tgn/7251904 | 3.466667 | 50.816667 http://vocab.getty.edu/tgn/7259438 | 3.466667 | 50.8 http://vocab.getty.edu/tgn/7244415 | 3.468621 | 50.843237 http://vocab.getty.edu/tgn/7239221 | 3.468621 | 50.801783 http://vocab.getty.edu/tgn/7254662 | 3.470106 | 50.851981 http://vocab.getty.edu/tgn/7254736 | 3.474789 | 50.809013 http://vocab.getty.edu/tgn/7244717 | 3.47786 | 50.836998 http://vocab.getty.edu/tgn/7246518 | 3.483333 | 50.833333 http://vocab.getty.edu/tgn/7239553 | 3.483333 | 50.8 http://vocab.getty.edu/tgn/7259356 | 3.486601 | 50.789969 http://vocab.getty.edu/tgn/7244238 | 3.495912 | 50.8133 http://vocab.getty.edu/tgn/7253514 | 3.5 | 50.816667 http://vocab.getty.edu/tgn/7239729 | 3.501387 | 50.826796 http://vocab.getty.edu/tgn/1026531 | 3.502674 | 50.798514 http://vocab.getty.edu/tgn/7253914 | 3.508419 | 50.808023 http://vocab.getty.edu/tgn/7242726 | 3.588978 | 51.122016 (74 rows) SELECT * FROM ( SELECT DISTINCT ON (name) name, lon, lat FROM getty_places WHERE lat BETWEEN 52.5 AND 53.0) j ORDER BY lat; 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 FILTER(?lat >= 52.5) FILTER(?lat <= 53.0) } ORDER BY ASC (?name) name | lon | lat -----------------------------------------------+----------+----------- South Holland, Netherlands, Europe, World | 4.8333 | 52.5167 Gelderland, Netherlands, Europe, World | 5.65 | 52.6 Europe, World | 6.766 | 52.65 Netherlands, Europe, World | 6.2 | 52.7 Lower Saxony, Germany, Europe, World | 7.15 | 52.7 Drenthe, Netherlands, Europe, World | 6.333333 | 52.716667 Flevoland, Netherlands, Europe, World | 5.75 | 52.716667 North Holland, Netherlands, Europe, World | 5.266667 | 52.716667 Overijssel, Netherlands, Europe, World | 6.133333 | 52.716667 Friesland, Netherlands, Europe, World | 5.433333 | 52.733333 Gwynedd, Wales, United Kingdom, Europe, World | 4.4586 | 52.9756 Groningen, Netherlands, Europe, World | 6.983333 | 53 (12 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}} 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