Skip to content

Instantly share code, notes, and snippets.

@hughlilly

hughlilly/2019-62.sparql

Last active October 31, 2022 02:00
Show Gist options
  • Save hughlilly/f177b9c97d0aaff129770f773caf5258 to your computer and use it in GitHub Desktop.
Save hughlilly/f177b9c97d0aaff129770f773caf5258 to your computer and use it in GitHub Desktop.
Download ZIP
Queries made against the AM SPARQL endpoint at <http://api.aucklandmuseum.com/sparql>. When using the interface at https://yasgui.triply.cc, add an "Accept" header of "application/sparql-results+json" under Header Arguments in the cog menu. e.g. https://api.triplydb.com/s/3TDjGQmls
Raw
2019-62.sparql
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
#SELECT (COUNT(?num) AS ?count) WHERE {
SELECT ?record WHERE {
?record am:accessionNumber ?num.
FILTER strstarts(?num,'2019.62')
}
Raw
Access_Rights_DH.sparql
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
# Return statement and count thereof
?access_statement
(COUNT(?rights) AS ?count)
WHERE {
# Limit our search to just DH
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
# Un-comment one of these to see statements by collecting area
# ?record ecrm:P50_has_current_keeper/rdf:value "painting and drawings"^^xsd:string.
# ?record ecrm:P50_has_current_keeper/rdf:value "publication"^^xsd:string.
# ?record ecrm:P50_has_current_keeper/rdf:value "photography"^^xsd:string.
# ?record ecrm:P50_has_current_keeper/rdf:value "ephemera"^^xsd:string.
# ?record ecrm:P50_has_current_keeper/rdf:value "manuscripts and archives"^^xsd:string.
# Get rights, then limit to just Access Rights, and store in ?access_statement
?record ecrm:P75_possesses ?rights .
?rights ecrm:P2_has_type "Access Rights"^^xsd:string;
rdf:value ?access_statement.
}
# Group by statement and order by count, descending
GROUP BY ?access_statement
ORDER BY DESC(?count)
Raw
All_Ceno.sparql
PREFIX db: <http://dbpedia.org/ontology/>
PREFIX ecrm:<http://erlangen-crm.org/current/>
PREFIX am:<http://collections.aucklandmuseum.com/ontology/core/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT * where
{
# Person Basic data
?person am:givenName/foaf:familyName ?familyname.
?person am:givenName/foaf:givenName ?firstname.
?person am:war/rdf:value "World War II, 1939-1945".
?person am:serviceNumber/rdf:value ?serviceNumber.
?person am:ethnicity ?ethnicity.
?person am:ethnicityNotes ?ethnicityNotes.
optional {?person am:force ?force.}
?person am:hapu/rdf:value ?hapu.
?person am:ingoa ?ingoa. #gives duplicates
?person am:iwi/rdf:value ?iwi.
?person am:religion ?religion.
?person am:rohe ?rohe.
?person am:waka ?waka.
#Birth
optional {?person am:birth/am:placeOfBirth/am:displayValue ?placeOfBirth.}
optional {?person am:birth/am:dateOfBirth ?dateOfBirth.}
optional {?person am:birth/am:dateOfBirthSource ?dateOfBirthSource}
optional {?person am:birthNotes ?birthNotes.}
#Relationship Status
?person am:personalRelationship/am:relationshipDate ?relationshipDate.
?person am:personalRelationship/am:relationshipStatus ?relationshipStatus.
#Before Enlistment
?person am:beforeEnlistment/am:addressBeforeEnlistment ?addressBeforeEnlistment.
?person am:beforeEnlistment/am:dateForAddressBeforeEnlistment ?dateForAddressBeforeEnlistment.
?person am:beforeEnlistment/am:placeBeforeEnlistment/am:displayValue ?placeBeforeEnlistment.
#Enlistment
?person am:enlistment/am:ageAtEnlistment ?ageAtEnlistment.
?person am:enlistment/am:conflict ?enlistmentConflict.
?person am:enlistment/am:dateOfEnlistment ?dateOfEnlistment.
?person am:enlistment/am:jobAtEnlistment ?jobAtEnlistment.
?person am:enlistment/am:militaryDistrict ?militaryDistrict.
?person am:enlistment/am:militaryUnitOnEnlistment ?militaryUnitOnEnlistment.
#Embarkation
?person am:embarkation/am:conflict ?conflict.
?person am:embarkation/am:embarkationBody ?embarkationBody.
?person am:embarkation/am:embarkationDate ?embarkationDate.
?person am:embarkation/am:embarkationEvent ?embarkationEvent.
?person am:embarkation/am:embarkationVessel ?embarkationVessel.
?person am:embarkation/am:placeBeforeEmbarkation ?placeBeforeEmbarkation.
?person am:embarkation/am:rankOnEmbarkation ?rankOnEmbarkation.
?person am:embarkation/am:ageAtEmbarkation ?ageAtEmbarkation.
?person am:nextOfKinAtEmbarkation/am:conflict ?conflict.
?person am:nextOfKinAtEmbarkation/am:nextOfKin ?nextOfKin.
?person am:nextOfKinAtEmbarkation/am:nextOfKinLocation ?nextOfKinLocation.
#Medical Info
?person am:medicalInformation/am:medical ?medical.
?person am:medicalInformation/am:medicalDate ?medicalDate.
?person am:medicalInformation/am:notes ?notes.
#Discharge
?person am:discharge/am:dischargeDate ?dischargeDate.
?person am:discharge/am:rankLastHeld ?rankLastHeld.
?person am:discharge/am:unitLast ?unitLast.
?person am:discharge/am:conflict ?conflict.
?person am:discharge/am:causeOfDischarge ?causeOfDischarge.
#Post War Occupation
?person am:postWarOccupation/am:dateOfEmployment ?dateOfEmployment.
?person am:postWarOccupation/am:occupation ?occupation.
#Capture
?person am:capture/am:captureDuration ?captureDuration.
?person am:capture/am:captureDuration_earliest ?captureDuration_earliest.
?person am:capture/am:captureDuration_latest ?captureDuration_latest.
?person am:capture/am:captureEvent ?captureEvent.
?person am:capture/am:conflict ?CaptureConflict.
?person am:powDaysInterned ?powDaysInterned.
?person am:capture/am:capturePlace ?capturePlace.
?person am:capture/am:captureNotes ?captureNotes.
#Death
?person am:death/am:ageAtDeath ?ageAtDeath.
?person am:death/am:dateOfDeath ?dateOfDeath.
?person am:death/am:placeOfDeath ?placeOfDeath.
?person am:death/am:rankLastHeld ?rankLastHeld.
?person am:death/am:unitLast ?unitLast.
?person am:militaryperson/am:obituary ?obituary.
?person am:memorialNotes ?memorialNotes.
#cemetery
?person am:cemetery/am:graveReference ?graveReference.
?person am:cemetery/am:restingPlace ?restingPlace.
?person am:memorialNotes ?memorialNotes.
?person am:memorialReference ?memorialReference.
}
LIMIT 1
Raw
collectingArea_counts.sparql
# Record counts by department, aggregated by collecting area
# https://api.triplydb.com/s/ci7gH43rz
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT
(GROUP_CONCAT (DISTINCT ?dept; separator="; ") AS ?depts)
?collectingArea
(COUNT(?record) AS ?count)
WHERE {
?record ecrm:P50_has_current_keeper/rdf:value ?dept;
rdf:type ?type .
BIND (
IF (?type = ecrm:E20_Biological_Object, "Natural Sciences",
IF (?type = ecrm:E84_Information_Carrier, "Documentary Heritage",
IF (?type = ecrm:E22_Man-Made_Object, "Human History",
false
)
)
)
AS ?collectingArea
)
}
GROUP BY ?collectingArea
ORDER BY ?type
Raw
copyrightStatementsEPH.sparql
# Finds copyright statements in Ephemera.
# As written, finds statement used 6 times ("No KNown Copyright") that will have to be cleaned up.
# https://api.triplydb.com/s/TOvhBuSZh
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX rdf:<http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
# Show records; useful only when HAVING is used below, and then only for multiple records having the same statement:
#SELECT (COUNT(DISTINCT ?record) AS ?count) (GROUP_CONCAT(DISTINCT ?record; SEPARATOR = '|') AS ?records) ?statement
# Use " OR " as separator to get a list to paste into Presto:
SELECT ?statement (GROUP_CONCAT(DISTINCT ?ID; SEPARATOR = ' OR ') AS ?searchString) (COUNT(DISTINCT ?record) AS ?count)
# Show types and group statements:
#SELECT ?type (GROUP_CONCAT(DISTINCT ?statement; SEPARATOR = '|') AS ?statements)
# Show statements and count records:
#SELECT ?statement (COUNT(DISTINCT ?record) AS ?count)
# Show all:
#SELECT *
WHERE {
?record ecrm:P50_has_current_keeper/rdf:value "ephemera"^^xsd:string ;
rdf:type ecrm:E84_Information_Carrier ;
ecrm:P75_possesses [ecrm:P2_has_type ?type; rdf:value ?statement]
BIND (REPLACE (STR(?record), "http://api.aucklandmuseum.com/id/library/ephemera/", "Ephemera.ContentItemID=") AS ?ID).
}
GROUP BY ?statement
# The HAVING clause narrows an aggregate. It must follow GROUP BY and must precede ORDER BY.
# You may want to change the projection above to show records IDs
# Find those statements used only once by using the HAVING clause to limit to <2:
#HAVING (?count < 7000)
#HAVING (?count > 10)
# Combine logical arguments to find, for example, those statements used 5 times:
# HAVING ((?count > 4) && (?count < 6))
HAVING (?count = 6)
#ORDER BY ASC(?count)
#ORDER BY DESC(?count)
# Apply a limit to test if syntax works
#LIMIT 2
Raw
customPrefixes.sparql
#Custom prefixes for using with the VALUES keyword
# https://api.triplydb.com/s/qODeEC5Xk
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
#Custom prefixes
PREFIX hh: <http://api.aucklandmuseum.com/id/humanhistory/object/>
PREFIX ns: <http://api.aucklandmuseum.com/id/naturalsciences/object/>
PREFIX ph: <http://api.aucklandmuseum.com/id/library/photography/>
PREFIX ms: <http://api.aucklandmuseum.com/id/library/manuscriptsandarchives/>
PREFIX pd: <http://api.aucklandmuseum.com/id/library/paintinganddrawings/>
PREFIX pub: <http://api.aucklandmuseum.com/id/library/catalogq40/>
PREFIX eph: <http://api.aucklandmuseum.com/id/library/ephemera/>
#Presto documents
PREFIX doc: <http://api.aucklandmuseum.com/id/document/>
#Person/OC
PREFIX oc: <http://api.aucklandmuseum.com/id/person/>
SELECT ?desc ?name WHERE {
# Beteween these two braces, put, for example, "hh:10606 hh:10728"
VALUES ?record { hh:10606 hh:108 }
?record am:nameTitle/rdf:value ?name;
dc:description ?desc
}
LIMIT 1
Raw
depts_ECRMTypes.sparql
# Finds departments and record types; groups by dept.
# https://api.triplydb.com/s/12cnfp147
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT
(GROUP_CONCAT(DISTINCT ?dept; separator="; ") AS ?depts)
?type
WHERE {
?record ecrm:P50_has_current_keeper/rdf:value ?dept;
rdf:type ?type .
}
GROUP BY ?type
Raw
DH_depts.sparql
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
?dept
(COUNT(?dept) AS ?count)
WHERE {
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
?record ecrm:P50_has_current_keeper/rdf:value ?dept .
}
GROUP BY ?dept
ORDER BY DESC(?count)
Raw
DirChoice_tag.sparql
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?record (GROUP_CONCAT(DISTINCT ?tag; separator="; ") AS ?tags) WHERE {
?record am:museumTag "Director's Choice"^^xsd:string .
?record am:museumTag ?tag
}
GROUP BY ?record
Raw
EPH_desc.sparql
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?keywords ?id WHERE {
?id ecrm:P50_has_current_keeper/rdf:value "ephemera"^^xsd:string;
am:keyword ?keywords .
# Filter to just entries with pipestem
# FILTER CONTAINS (?keywords,'|')
}
#LIMIT 10
Raw
Flickr_upload.sparql
# Auckland Museum API
# Flickr account data export
# September 2019
PREFIX ref: <http://purl.org/vocab/relationship/>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX dct: <http://purl.org/dc/terms/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX schema: <http://schema.org/>
# For multi-value fields, group and concatenate
SELECT
( GROUP_CONCAT(DISTINCT ?cr ; separator = ' | ' ) AS ?creator )
( GROUP_CONCAT(DISTINCT ?ttl ; separator = ' ' ) AS ?title )
?desc
( GROUP_CONCAT(DISTINCT ?note ; separator = ' ' ) AS ?notes )
( GROUP_CONCAT(DISTINCT ?fmt ; separator = '; ' ) AS ?formats )
( GROUP_CONCAT(DISTINCT ?med ; separator = '; ' ) AS ?media )
( GROUP_CONCAT(DISTINCT ?physDesc ; separator = '; ' ) AS ?size )
?date ?citation ?tags ?img ?link
WHERE {
# Limit out sensitive records (could check ?isTaonga too)
OPTIONAL { ?id am:sensitive false }
# Mandatory values
?id ecrm:P50_has_current_keeper/rdf:value "painting and drawings"^^xsd:string;
am:primaryRepresentation ?imgraw;
ecrm:P75_possesses ?rights;
ecrm:P1_is_identified_by ?identifiers.
# Optional values
OPTIONAL { ?id ecrm:P108i_was_produced_by/ecrm:P14_carried_out_by/foaf:name ?cr. }
OPTIONAL { ?id dc:title ?ttl; }
OPTIONAL { ?id dc:description ?desc. }
OPTIONAL { ?id ecrm:P3_has_note ?note. }
OPTIONAL { ?id dc:format ?fmt. }
OPTIONAL { ?id dct:medium ?med. }
OPTIONAL { ?id am:physicalDescription ?physDesc. }
OPTIONAL { ?id ecrm:P86_falls_within/rdf:value ?date. }
# Create correct Collections Online links
BIND (REPLACE (STR (?id), "http://api.aucklandmuseum.com/id/library/paintinganddrawings/","https://www.aucklandmuseum.com/collections-research/collections/record/am_library-paintinganddrawings-") AS ?linkstring).
BIND (URI (?linkstring) AS ?link)
# Create correct image URLs
BIND (REPLACE (STR (?imgraw), "http://", "https://media.", "i") AS ?imagestring).
BIND (URI (?imagestring) AS ?img)
# Create tags
?identifiers ecrm:P2_has_type "Reference Number"^^xsd:string;
rdf:value ?ref.
BIND (REPLACE(STR(?ref), " ", "-") AS ?refnospaces).
BIND (REPLACE(STR(?refnospaces), "\\(", "-") AS ?refnoleftbrackets).
BIND (REPLACE(STR(?refnoleftbrackets), "\\)", "") AS ?refnobrackets).
BIND (CONCAT("no-known-copyright auckland-museum ", LCASE(?refnobrackets)) AS ?tags).
# Create citation
BIND (CONCAT(" Auckland Museum ref. ", ?ref, ".") AS ?citation).
# Filter rights statement for only those containing "known"
?rights ecrm:P2_has_type "Copyright"^^xsd:string;
rdf:value ?rightsStatement.
FILTER regex(?rightsStatement, "known", "i")
}
GROUP BY ?desc ?date ?citation ?tags ?img ?link
Raw
HillaryCollectionPH.sparql
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT (COUNT(DISTINCT ?record) AS ?count)
WHERE {
?record am:museumTag "Edmund Hillary"^^xsd:string .
}
Raw
HillaryPHNepal.sparql
# Hillary Collection Nepal photos for AppSheet app
# <https://api.triplydb.com/s/WjUpWUgy0>
PREFIX schema: <http://schema.org/>
PREFIX sc: <http://purl.org/science/owl/sciencecommons/>
PREFIX rfd: <http://com.intrinsec//ontology#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?contentID ?image ?title ?desc
(GROUP_CONCAT(DISTINCT ?keyword; separator=", ") AS ?keywords)
(GROUP_CONCAT(DISTINCT ?note; separator="; ") AS ?notes)
?date ?place ?refNum
WHERE {
?record am:childOf <http://api.aucklandmuseum.com/id/library/photography/66029>;
dc:title ?title;
am:primaryRepresentation ?image;
ecrm:P1_is_identified_by ?ident.
?ident ecrm:P2_has_type "Reference Number"^^xsd:string;
rdf:value ?refNum.
OPTIONAL {?record dc:description ?desc.}
OPTIONAL {?record am:keyword ?keyword .}
OPTIONAL {?record ecrm:P3_has_note ?note .}
OPTIONAL {?record ecrm:P86_falls_within/rdf:value ?date .}
OPTIONAL {?record ecrm:P108i_was_produced_by/ecrm:P7_took_place_at/rdf:value ?place .}
OPTIONAL {?record dc:subject/rdf:value ?subject.}
# Search fields for "nepal" (case-insensitive)
FILTER (
REGEX(?keyword, "nepal", "i") ||
REGEX(?note, "nepal", "i") ||
REGEX(?desc, "nepal", "i") ||
REGEX(?subject, "nepal", "i") ||
REGEX(?place, "nepal", "i") ||
REGEX(?title, "nepal", "i")
)
# BIND(STR(?imgURL) AS ?image)
BIND(REPLACE(STR(?record),"http://api.aucklandmuseum.com/id/library/photography/","") AS ?contentID) .
}
GROUP BY ?contentID ?title ?desc ?image ?date ?place ?refNum
ORDER BY ?contentID
Raw
HillaryTag.sparql
# https://api.triplydb.com/s/DzjbkELzC
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?tag (COUNT(DISTINCT ?record) AS ?count)
WHERE {
?record am:museumTag "Edmund Hillary"^^xsd:string .
?record am:museumTag ?tag
}
GROUP BY ?tag
Raw
imgData.sparql
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
*
WHERE {
<http://api.aucklandmuseum.com/id/media/v/545279> ?p ?o.
OPTIONAL { ?o ?p2 ?o2 }
}
Raw
JohnMercer.sparql
# John Mercer Springbok Tour photography for GAC <>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX schema: <http://schema.org/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
?itemid ?filetype ?subitemid ?filespec ?title_en ?creator_en ?type_0_en ?relation_url
(GROUP_CONCAT (DISTINCT ?note; separator=";") AS ?customtext_note_0_en)
?customtext_process_0_en ?description_en ?format_0_en ?art_support_0_en ?rights_0_en
(GROUP_CONCAT (DISTINCT ?subject; separator=";") AS ?subjects)
(GROUP_CONCAT (DISTINCT ?process; separator=";") AS ?customtext_process_0_en)
(GROUP_CONCAT (DISTINCT ?support; separator=";") AS ?art_support_0_en)
?loc
WHERE {
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
?record ecrm:P108i_was_produced_by/ecrm:P14_carried_out_by <http://api.aucklandmuseum.com/id/person/p/c3981c999a18b93afa6f159bd3e32cea36514f07>;
ecrm:P1_is_identified_by/rdf:value ?itemid;
dc:title ?title_en;
dc:creator ?creator_en;
schema:description ?description_en;
dc:format ?type_0_en;
am:physicalDescription ?format_0_en;
dc:subject/rdf:value ?subject;
ecrm:P138_has_representation/am:mediaLocation ?loc;
ecrm:P138_has_representation/ecrm:P75_possesses/rdf:value ?rights_0_en;
OPTIONAL{?record ecrm:P108i_was_produced_by/ecrm:P32_used_general_technique ?process; .}
OPTIONAL{?record ecrm:P108i_was_produced_by/am:hasSupport ?support; .}
OPTIONAL{?record ecrm:P3_has_note ?note .}
BIND ("image" AS ?filetype)
# Regex explanation: https://regex101.com/r/KukTZY/1
BIND (REPLACE(?loc, "^(.+\\/)*(.+)$", "$2") AS ?filespec)
BIND (REPLACE (STR (?record), "http://api.aucklandmuseum.com/id/library/photography/","https://www.aucklandmuseum.com/collection/object/am_library-photography-") AS ?relation_url)
}
GROUP BY ?itemid ?filetype ?subitemid ?filespec ?title_en ?creator_en ?type_0_en ?relation_url ?description_en ?format_0_en ?rights_0_en ?loc
#LIMIT 10
Raw
measurements_and_depts.sparql
PREFIX ecrm:<http://erlangen-crm.org/current/>
SELECT DISTINCT ?measure (GROUP_CONCAT(DISTINCT ?dept; separator="; ") AS ?depts)
WHERE {
?object ecrm:P43_has_dimension/ecrm:P2_has_type ?measure;
ecrm:P50_has_current_keeper/rdf:value ?dept
}
GROUP BY ?measure
Raw
mechanical_pencils.sparql
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX ecrm:<http://erlangen-crm.org/current/>
PREFIX am:<http://collections.aucklandmuseum.com/ontology/core/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
#(COUNT(DISTINCT ?measurement) AS ?measurementCount)
#(GROUP_CONCAT(?dimension; separator=";") AS ?dimensions)
SELECT ?object ?dept ?accessionnumber ?otherID ?image ?date ?place ?title ?classification (GROUP_CONCAT(DISTINCT ?measurement; separator=" × ") AS ?measurements) ?description WHERE
{
?object am:classification/rdf:value ?classification;
FILTER regex(str(?classification), '^pencil, mechanical', 'i')
{?object ecrm:P138_has_representation ?image}
optional {?object ecrm:P50_has_current_keeper/rdf:value ?dept}
optional {?object dc:title ?title}
optional {?object am:accessionNumber ?accessionnumber}
optional {?object am:otherIdentifier/rdf:value ?otherID}
optional {?object ecrm:E12_production ?place}
optional {?object ecrm:E52_has_time_span ?date}
optional {?object dc:description ?description}
optional {?object ecrm:P43_has_dimension/ecrm:P90_has_value ?measurementValue;
ecrm:P43_has_dimension/ecrm:P2_has_type ?measurementType;
BIND(CONCAT(str(?measurementValue), " (", str(?measurementType), ")") AS ?measurement)}
#FILTER ( ?measurements != ?object)
#optional {?object ecrm:P7_took_place_at/dc:title ?placename}
}
GROUP BY ?object ?dept ?accessionnumber ?otherID ?date ?image ?place ?title ?classification ?description
#HAVING(COUNT(?image) > 1)
#LIMIT 50
Raw
media_IDs.sparql
PREFIX ecrm: <http://erlangen-crm.org/current/>
SELECT * WHERE {
?record ecrm:P138_has_representation ?img
BIND(REPLACE(STR(?img),"http://api.aucklandmuseum.com/id/media/v/","") AS ?mediaID)
}
LIMIT 1
Raw
men_horses.sparql
PREFIX am:<http://collections.aucklandmuseum.com/ontology/core/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT * WHERE {
?a rdf:type am:Event.
?a am:transportShip/rdf:value ?ship.
?a am:grossTonnage ?Tonnage.
?a am:numberOfHorses ?horse.
optional { ?a am:numberOfMen ?men;}
optional { ?a am:numberOfOfficers ?officers;}
}
Raw
MS_PDFs_images.sparql
# https://api.triplydb.com/s/GtA1mc3bx
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX record: <https://www.aucklandmuseum.com/collection/object/am_library-manuscriptsandarchives->
SELECT DISTINCT ?link ?refNum
WHERE {
?record ecrm:P50_has_current_keeper/rdf:value "manuscripts and archives"^^xsd:string .
?record ecrm:P70_documents ?pdf;
ecrm:P138_has_representation ?img;
ecrm:P1_is_identified_by ?ident.
?ident ecrm:P2_has_type "Reference Number"^^xsd:string;
rdf:value ?refNum.
BIND (REPLACE (STR (?record), "http://api.aucklandmuseum.com/id/library/manuscriptsandarchives/", "https://www.aucklandmuseum.com/collection/object/am_library-manuscriptsandarchives-") AS ?linkstring).
BIND (URI(?linkstring) AS ?link)
}
GROUP BY ?link ?pdf ?refNum
ORDER BY ?refNum
Raw
MUS-2010-16_PDFs.SPARQL
# https://api.triplydb.com/s/QSvC6eHeK
PREFIX schema: <http://schema.org/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT *
WHERE {
?record am:childOf <http://api.aucklandmuseum.com/id/library/manuscriptsandarchives/16659>;
ecrm:P1_is_identified_by/rdf:value ?ref;
schema:name ?title;
am:pdfRepresentation ?image;
ecrm:P70_documents/am:mediaLocation ?loc;
am:lastModifiedOn ?date .
FILTER (?date > "2021-11-26T00:00:00Z"^^xsd:dateTime)
BIND (NOW () AS ?current_time)
BIND (REPLACE (STR (?record), "http://api.aucklandmuseum.com/id/library/manuscriptsandarchives/", "https://www.aucklandmuseum.com/collection/object/am_library-manuscriptsandarchives-") AS ?link)
BIND('''
<h3><a href="{{link}}">{{ref}}</a></h3>
<img src="{{image}}">
<p>{{title}}</p>
<p><small>
<a href="{{image}}?rendering=original.pdf"> {{loc}} </a>
</small></p>
<p><small>Last modified: {{date}}</small><br/>
<small>Current time: {{current_time}}</small>
'''^^rdf:HTML as ?widget)
}
#LIMIT 1
Raw
museumTag.sparql
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
# ?record am:museumTag "nature printing"^^xsd:string .
SELECT ?tag WHERE {
?record am:museumTag ?tag .
}
LIMIT 1
Raw
OC_DoD.sparql
PREFIX rfd: <http://com.intrinsec//ontology#>
PREFIX db: <http://dbpedia.org/ontology/>
PREFIX ecrm:<http://erlangen-crm.org/current/>
PREFIX am:<http://collections.aucklandmuseum.com/ontology/core/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX s: <http://schema.org/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?record ?name ?monthday ?year ?cause ?age
(GROUP_CONCAT(DISTINCT ?place ; separator = '; ') AS ?at )
?url
WHERE
{
?record rdf:type am:MilitaryPerson;
s:name ?firstname;
s:familyName ?lastname;
am:death ?death;
am:war/rdf:value "World War I, 1914-1918"^^xsd:string.
OPTIONAL {?record am:causeOfDeath ?cause . }
?death am:dateOfDeath_earliest ?date;
am:ageAtDeath ?age .
OPTIONAL {?death am:placeOfDeath/am:displayValue ?place . }
BIND(CONCAT(?firstname, " ", ?lastname) AS ?name) .
BIND(URI(REPLACE(STR(?record), "http://api.aucklandmuseum.com/id/person", "https://www.aucklandmuseum.com/war-memorial/online-cenotaph/record")) AS ?url) .
BIND(STR(YEAR(?date)) AS ?year) .
BIND(MONTH(?date) AS ?month) .
BIND(DAY(?date) AS ?day) .
BIND(CONCAT(STR(?month), "-" , STR(?day)) AS ?monthday) .
}
GROUP BY ?record ?name ?monthday ?year ?cause ?age ?url
ORDER BY ?monthday ASC(?year)
LIMIT 5
#LIMIT 50
#LIMIT 100
#LIMIT 10000
#OFFSET 10000
Raw
OC_IDs.sparql
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am:<http://collections.aucklandmuseum.com/ontology/core/>
SELECT ?id WHERE {
?record rdf:type am:MilitaryPerson .
BIND(REPLACE(STR(?record),"http://api.aucklandmuseum.com/id/person/","") AS ?id) .
}
# LIMIT 1
Raw
OC_URLs.sparql
# You'll need to run at least three queries, using OFFSET to
# get the second and third chunks (i.e. OFFSET 100000, then OFFSET 200000).
# LIMIT stays the same.
# See <https://www.w3.org/TR/rdf-sparql-query/#modOffset> for details.
#
# Short URL for this query: <https://api.triplydb.com/s/rVeYVTu2T>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT *
WHERE {
?record rdf:type am:MilitaryPerson
}
LIMIT 100000
#OFFSET 100000
Raw
OC_WWI.sparql
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT (COUNT(?record) AS ?count) WHERE {
?record rdf:type am:MilitaryPerson .
?record am:war/rdf:value "World War I, 1914-1918"^^xsd:string .
}
LIMIT 1
Raw
OCstories.sparql
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX schema: <http://schema.org/>
SELECT DISTINCT ?a ?firstNames ?lastName ?dob ?birthCity ?birthCountry ?dod (group_concat(distinct ?deathCity;separator='|') as ?deathCity1) ?deathCountry ?age ?cause (group_concat(distinct ?force;separator='|') as ?forces) ?displayNotes ?buriedAt
{
?a am:war/rdf:value "World War I, 1914-1918".
?a schema:name ?firstNames.
?a schema:familyName ?lastName.
?a am:force ?force.
?a am:birth ?birth.
?birth am:dateOfBirth ?dob.
# ; am:placeOfBirth/am:displayValue ?birthCity; am:placeOfBirth/am:childOf/am:displayValue ?birthCountry.
?a am:death ?death.
?death am:dateOfDeath ?dod.
# ; am:placeOfDeath/am:displayValue ?deathCity; am:placeOfDeath/am:childOf/am:displayValue ?deathCountry; am:ageAtDeath ?age.
optional {?death am:restingPlace ?buriedAt.}
optional {?a am:causeOfDeath ?cause.}
?a am:displayNotes ?displayNotes.
}
GROUP BY ?a ?firstNames ?lastName ?force ?dob ?birthCity ?birthCountry ?dod ?deathCity ?deathCountry ?age ?cause ?buriedAt ?displayNotes
LIMIT 1
Raw
PDFs.sparql
# Records with PDFs attached <https://api.triplydb.com/s/utEg7n154>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
?record
(COUNT(?pdf) AS ?pdfs)
WHERE {
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
# Just one collecting area, e.g. MS:
# ?record ecrm:P50_has_current_keeper/rdf:value "manuscripts and archives"^^xsd:string.
?record am:pdfRepresentation ?pdf .
}
GROUP BY (?record)
# The HAVING clause narrows an aggregate. It must follow GROUP BY and must precede ORDER BY.
# Find records with more than one PDF attached:
#HAVING (?pdfs > 5)
# Combine logical arguments to find, for example, records with at least two PDFs but less than 10:
#HAVING ((?pdfs > 1) && (?pdfs < 10))
ORDER BY DESC(?pdfs)
# Limit during testing
#LIMIT 5
Raw
people_places.sparql
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT DISTINCT ?a ?placeOfBirthP ?hLevelBirth ?placeOfDeathP ?hLevelDeath WHERE {
?a am:war/rdf:value ?war;
FILTER regex(str(?war), '^World War I, 1914-1918', 'i')
?a am:birth/am:placeOfBirth/am:childOf/am:displayValue ?placeOfBirthP.
?a am:birth/am:placeOfBirth/am:childOf/am:hierarchicalLevel ?hLevelBirth.
?a am:death/am:placeOfDeath/am:childOf/am:displayValue ?placeOfDeathP.
?a am:death/am:placeOfDeath/am:childOf/am:hierarchicalLevel ?hLevelDeath.
}
LIMIT 10
Raw
PH_IDs.sparql
# Get Photography IDs <https://api.triplydb.com/s/5OrT3C5U0>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
?id
WHERE {
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
?record ecrm:P50_has_current_keeper/rdf:value "photography"^^xsd:string.
BIND(REPLACE(STR(?record),"http://api.aucklandmuseum.com/id/library/photography/","") AS ?id) .
}
LIMIT 1
Raw
pocketwatches-GAC.sparql
# Pocketwatches for GAC <https://api.triplydb.com/s/BmJ2cwXMe>
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX schema: <http://schema.org/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
#Custom prefixes
PREFIX hh: <http://api.aucklandmuseum.com/id/humanhistory/object/>
PREFIX ns: <http://api.aucklandmuseum.com/id/naturalsciences/object/>
PREFIX ph: <http://api.aucklandmuseum.com/id/library/photography/>
PREFIX ms: <http://api.aucklandmuseum.com/id/library/manuscriptsandarchives/>
PREFIX pd: <http://api.aucklandmuseum.com/id/library/paintinganddrawings/>
PREFIX pub: <http://api.aucklandmuseum.com/id/library/catalogq40/>
PREFIX eph: <http://api.aucklandmuseum.com/id/library/ephemera/>
#Presto documents
PREFIX doc: <http://api.aucklandmuseum.com/id/document/>
#Person/OC
PREFIX oc: <http://api.aucklandmuseum.com/id/person/>
SELECT
?itemid ?filetype ?subitemid ?filespec ?title_en ?relation_url
# Separate these as "customtext:otherNum#X/en", where X is 0-2
(GROUP_CONCAT (DISTINCT ?other_id_entry; separator="|") AS ?other_IDs)
# Change to "customtext:note#0/en"
(GROUP_CONCAT (DISTINCT ?engravingNote; separator="; ") AS ?customtext_note_0_en)
?description_en
# Change to "rights#0/en"
?rights_0_en
# Cut this column off -- only for Powershell script
?loc
WHERE {
VALUES ?record { hh:1498 hh:1203 hh:7179 hh:11454 hh:11194 hh:16141 hh:10100 hh:13840 hh:15964 }
OPTIONAL { ?record ecrm:P1_is_identified_by/rdf:value ?itemid; }
?record dc:title ?title_en;
dc:description ?description_en;
ecrm:P65_shows_visual_item/rdf:value ?engravingNote .
?record am:otherIdentifier [ rdf:value ?other_id ;
ecrm:P2_has_type ?other_id_type ] .
# Get images
?record ecrm:P138_has_representation ?img_url .
?img_url am:publicationStatus "Full public access"^^xsd:string;
ecrm:P75_possesses/rdf:value ?rights_0_en;
am:mediaLocation ?loc .
# Regex from https://regex101.com/r/3R0loy/2 ; see https://stackoverflow.com/q/68931997/10267529
BIND (REPLACE(?loc, "(.+\\\\)*(.+)", "$2") AS ?filespec)
# Regex from https://regex101.com/r/ItHwgK/1 w another backslash before the full stop bc SPARQL requires it
BIND (REPLACE(?filespec, "^(.+?)(?:\\.[^.]*)$", "$1") AS ?subitemid)
BIND ("image" AS ?filetype)
BIND (CONCAT(?other_id, " (", ?other_id_type, ")") AS ?other_id_entry)
BIND (URI(REPLACE(STR(?record), "http://api.aucklandmuseum.com/id/humanhistory/object/","https://www.aucklandmuseum.com/collection/object/am_humanhistory-object-")) AS ?relation_url)
}
GROUP BY ?itemid ?subitemid ?filetype ?filespec ?title_en ?description_en ?rights_0_en ?relation_url ?img_url ?loc
ORDER BY ?vernonID
#LIMIT 1
Raw
pubs-online.sparql
# https://api.triplydb.com/s/yg9vdXUaP
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT ?ID
WHERE {
?record ecrm:P50_has_current_keeper/rdf:value "publication"^^xsd:string ;
BIND (REPLACE (STR(?record), "http://api.aucklandmuseum.com/id/library/catalogq40/", "") AS ?ID)
}
LIMIT 1
Raw
Restrictions-Vernon.sparql
#https://api.triplydb.com/s/nHqZkAWT6
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT
?restriction
# Show records -- when limiting to a string
#?record
(COUNT(?record) AS ?count)
WHERE {
# Search for an exact string with ' am:restriction"<somestringhere>"^^xsd:string .' in the next line
# instead of just getting the value.
?record am:restriction ?restriction .
}
GROUP BY ?restriction
#ORDER BY DESC(?count)
#LIMIT 10
Raw
Tags.sparql
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
#SELECT (COUNT(?num) AS ?count) WHERE {
SELECT DISTINCT ?tag WHERE {
# ?record am:museumTag "21st Battalion Association"^^xsd:string .
?record am:museumTag ?tag .
# Negate this for Vernon; leave it positive for Presto
FILTER EXISTS {?record rdf:type ecrm:E84_Information_Carrier .}
}
#LIMIT 10
Raw
tags_by_dept.SPARQL
# https://api.triplydb.com/s/4NmATWiLI
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
PREFIX search_tag: <https://www.aucklandmuseum.com/discover/collections/search?tag=>
SELECT DISTINCT ?tag
(COUNT(DISTINCT ?record) AS ?num_records)
(COUNT(DISTINCT ?dept) AS ?num_depts)
(GROUP_CONCAT (DISTINCT ?dept; separator="; ") AS ?depts)
?link
WHERE {
?record am:museumTag ?tag;
ecrm:P50_has_current_keeper/rdf:value ?dept .
BIND (CONCAT("https://www.aucklandmuseum.com/discover/collections/search?tag=", ?tag) AS ?linkstring).
BIND (URI (?linkstring) AS ?link)
}
GROUP BY ?tag ?link
Raw
TudorCollins.sparql
# Tudor Collins copyright statements: https://api.triplydb.com/s/2uA5x_M3B
PREFIX dc: <http://purl.org/dc/elements/1.1/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX ecrm: <http://erlangen-crm.org/current/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT ?copyrightStatement (COUNT(DISTINCT ?record) AS ?count)
WHERE {
?record ecrm:P108i_was_produced_by/ecrm:P14_carried_out_by <http://api.aucklandmuseum.com/id/person/p/b4190e440a1660cf9578d765b53ae0971fc4fe21>;
ecrm:P138_has_representation/ecrm:P75_possesses/rdf:value ?copyrightStatement.
}
GROUP BY ?copyrightStatement
Raw
userTags.sparql
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX am: <http://collections.aucklandmuseum.com/ontology/core/>
SELECT (GROUP_CONCAT(DISTINCT ?record; SEPARATOR = '|') AS ?records) ?tag
WHERE {
?record am:publicTags/rdf:value ?tag.
}
GROUP BY ?tag
#LIMIT 100
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment