From bff4b4af7014fbf325b36e5ee149debe8e5dfd47 Mon Sep 17 00:00:00 2001 From: lltommy Date: Fri, 13 Nov 2020 18:41:08 +0100 Subject: Initial commit: SPARQL playground, which replaces the demo page. Some queries have to be adjusted once changed metadata schema is live --- bh20simplewebuploader/main.py | 211 +++++++++++++++++++++++++++++- bh20simplewebuploader/static/main.css | 18 +++ bh20simplewebuploader/static/main.js | 84 +++++++++++- bh20simplewebuploader/templates/demo.html | 22 +++- bh20simplewebuploader/templates/menu.html | 2 +- 5 files changed, 325 insertions(+), 12 deletions(-) (limited to 'bh20simplewebuploader') diff --git a/bh20simplewebuploader/main.py b/bh20simplewebuploader/main.py index 0495613..27dcd4b 100644 --- a/bh20simplewebuploader/main.py +++ b/bh20simplewebuploader/main.py @@ -949,8 +949,8 @@ def getSEQCountbyLocation(): @app.route('/api/getSEQCountbyContinent', methods=['GET']) def getSEQCountbyContinent(): query="""SELECT DISTINCT ?continent ?continent_label (count(?fasta) as ?fastaCount) WHERE { - ?fasta ?x[ ?location] . - ?location ?country . + ?fasta ?x [ ?location] . + ?location ?country . ?country ?continent . OPTIONAL { ?continent rdfs:label ?key_tmp_label } BIND(IF(BOUND(?key_tmp_label),?key_tmp_label, ?location) as ?continent_label) @@ -1064,3 +1064,210 @@ def getSEQbyLocationAndSpecimenSource(): r = requests.get(sparqlURL, params=payload) result = r.json()['results']['bindings'] return str(result) + + +################## SPARQL PLAYGORUND API function ################ + +@app.route('/api/demoGetSEQCountbySpecimenSource', methods=['GET']) +def demoGetSEQCountbySpecimenSource(): + prefix="""PREFIX obo: +PREFIX rdfs: """ + + query="""SELECT ?specimen_source ?specimen_source_label (count(?seq) as ?seqCount) WHERE + { + ?seq ?x [obo:OBI_0001479 ?specimen_source] . + ?specimen_source rdfs:label ?specimen_source_label + } + GROUP BY ?specimen_source ?specimen_source_label + ORDER BY DESC (?seqCount) + """ + + description="Get the count of all sequences, grouped by specimen source and specimen label (This is a 1-to-1 relationship). In addition we want to order by the sequence count descending." + payload = {'query': prefix+query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + + return jsonify([{'description' : description},{'prefix' : prefix}, {'query': query}],[{'count': x['seqCount']['value'], + 'key': x['specimen_source']['value'], + 'label': x['specimen_source_label']['value']} for x in result]) + + +@app.route('/api/demoGetSEQCountbyLocation', methods=['GET']) +def demoGetSEQCountbyLocation(): + prefix="""PREFIX obo: +PREFIX rdfs: """ + + query=""" + SELECT ?geoLocation ?geoLocation_label (count(?seq) as ?seqCount) WHERE + { + ?seq ?x [obo:GAZ_00000448 ?geoLocation] . + ?geoLocation rdfs:label ?geoLocation_label + } + GROUP BY ?geoLocation ?geoLocation_label + """ + description = "Get count of all sequences grouped by geoLocation and geoLocation_label (1-to-1 relationship)" + payload = {'query': prefix+query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description' : description},{'prefix' : prefix}, {'query': query}],[{'count': x['seqCount']['value'], + 'key': x['geoLocation']['value'], + 'label': x['geoLocation_label']['value']} for x in result]) + + + +@app.route('/api/demoGetAuthors', methods=['GET']) +def demoGetAuthors(): + prefix="""PREFIX obo: +PREFIX rdfs: +PREFIX wiki: """ + + query = """SELECT DISTINCT ?author ?country_label ?continent_label WHERE { + ?fasta ?x [ obo:GAZ_00000448 ?location] . + ?fasta ?y [ obo:NCIT_C42781 ?author] . + + ?location wiki:P17 ?country . + ?country wiki:P30 ?continent . + ?country rdfs:label ?country_label . + ?continent rdfs:label ?continent_label + } + ORDER BY ?author + LIMIT 500 + """ + + description = "Get all autors (obo:NCIT_C42781) that are in the DB and the country/continent where their samples were taken. The result is limited to 500." + payload = {'query': prefix+query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description' : description},{'prefix' : prefix}, {'query': query}],[{'author': x['author']['value'], + 'country_label': x['country_label']['value'], + 'continent_label': x['continent_label']['value']} for x in result]) + + +@app.route('/api/demoInstitutesPublications', methods=['GET']) +def demoInstitutesPublications(): + prefix="PREFIX obo: " + query=""" + SELECT DISTINCT ?originating_lab ?publication WHERE { + ?seq ?x [ obo:NCIT_C37984 ?originating_lab] . + ?seq ?y [ obo:NCIT_C19026 ?publication] . + } + """ + + description = "List institutes (originating_lab, obo:NCIT_C37984) associated their publications in the DB" + payload = {'query': prefix+query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description' : description},{'prefix' : prefix}, {'query': query}],[{'originating_lab': x['originating_lab']['value'], + 'publication': x['publication']['value']} for x in result]) + + + +@app.route('/api/demoGetSEQCountbytechContinent', methods=['GET']) +def demoGetSEQCountbytechContinent(): + prefix="""PREFIX obo: +PREFIX wiki: +PREFIX rdfs: """ + + query="""SELECT DISTINCT ?continent_label ?tech_label (count(?seq) as ?seqCount) WHERE + { + ?seq ?x [ obo:OBI_0600047 ?tech] . + ?seq ?y [ obo:GAZ_00000448 ?location] . + + ?tech rdfs:label ?tech_label . + + ?location wiki:P17 ?country . + ?country wiki:P30 ?continent . + ?continent rdfs:label ?continent_label + } + + GROUP BY ?tech_label ?continent_label + ORDER BY ?continent_label ?seqCount + """ + + description = "List institutes (originating_lab, obo:NCIT_C37984) and their associated publications in the DB" + payload = {'query': prefix+query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description' : description},{'prefix' : prefix}, {'query': query}],[{'continent_label': x['continent_label']['value'], + 'tech_label': x['tech_label']['value'], 'seqCount': x['seqCount']['value']} for x in result]) + + +@app.route('/api/demoGetSEQCountbytech', methods=['GET']) +def dempGetSEQCountbytech(): + prefix="""PREFIX obo: +PREFIX rdfs: """ + + query="""SELECT ?tech ?tech_label (count(?seq) as ?seqCount) WHERE + { + ?seq ?x [obo:OBI_0600047 ?tech] . + ?tech rdfs:label ?tech_label + } + GROUP BY ?tech ?tech_label ORDER BY DESC (?seqCount) + """ + description = "Show count per sequence technology" + payload = {'query': prefix + query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description': description}, {'prefix': prefix}, {'query': query}], + [{'tech_label': x['tech_label']['value'], + 'tech': x['tech']['value'], 'seqCount': x['seqCount']['value']} for x in result]) + + +@app.route('/api/demoGetSequencePerDate', methods=['GET']) +def demoGetSequencePerDate(): + prefix="" + query="""SELECT ?seq ?date WHERE { + ?seq ?a [ ?date] + FILTER ( xsd:date(?date) < xsd:date("2020-03-01") ) + } + ORDER BY ?date""" + description = "Show all sequences with a submission date before 2020-03-01! To accomplish this a FILTER expression is used. Since date is a string, we cast xsd:date(...)" + payload = {'query': prefix + query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description': description}, {'prefix': prefix}, {'query': query}], + [{'seq': x['seq']['value'], + 'date': x['date']['value']} for x in result]) + +@app.route('/api/demoLocationGps', methods=['GET']) +def demoLocationGps(): + prefix="""PREFIX obo: +PREFIX wiki: """ + + query="""SELECT distinct ?location ?GPS WHERE { + ?seq ?a [ obo:GAZ_00000448 ?location] . + ?location wiki:P625 ?GPS + } + """ + description = "Show all locations with their GPS coordinates that we have in the database. GPS coordinates are encoded as Point tuple." + payload = {'query': prefix + query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description': description}, {'prefix': prefix}, {'query': query}], + [{'location': x['location']['value'], + 'GPS': x['GPS']['value']} for x in result]) + +@app.route('/api/getNYsamples', methods=['GET']) +def getNYsamples(): + prefix="""PREFIX obo: +PREFIX wikiE: """ + + query="""SELECT DISTINCT ?seq ?key_label ?key ?value_label ?value WHERE { + ?seq ?x [ obo:GAZ_00000448 wikiE:Q1384] . + ?seq ?y [?key ?value] . + + ?key rdfs:label ?key_label . + ?value rdfs:label ?value_label + } + ORDER BY ?seq""" + + description = "Get all samples and their information (key, values) that were taken in New York (Q1384)!" + payload = {'query': prefix + query, 'format': 'json'} + r = requests.get(sparqlURL, params=payload) + result = r.json()['results']['bindings'] + return jsonify([{'description': description}, {'prefix': prefix}, {'query': query}], + [{'seq': x['seq']['value'], + 'key_label': x['key_label']['value'], + 'key': x['key']['value'], + 'value_label': x['value_label']['value'], + 'value': x['value']['value']} for x in result]) \ No newline at end of file diff --git a/bh20simplewebuploader/static/main.css b/bh20simplewebuploader/static/main.css index 76a1755..36c1a33 100644 --- a/bh20simplewebuploader/static/main.css +++ b/bh20simplewebuploader/static/main.css @@ -533,3 +533,21 @@ div.status { border-bottom: 2px solid white; padding-bottom: 20px; } + + + +/* SPARQL playground CSS*/ +#playground { +margin-left: 20px; +} + +#playground table{ +border: 1px solid black; +min-height: 400px; +} + +#playground td{ +border: 1px solid black; +padding-left:10px; +padding-right:10px; +} diff --git a/bh20simplewebuploader/static/main.js b/bh20simplewebuploader/static/main.js index 6cc0d9f..0e642c1 100644 --- a/bh20simplewebuploader/static/main.js +++ b/bh20simplewebuploader/static/main.js @@ -83,6 +83,48 @@ function fetchHTMLTable(apiEndPoint) { }); } + + +// changing access for Demo page +function demofetchHTMLTable(apiEndPoint) { + fetch(scriptRoot + apiEndPoint) + .then(response => { + return response.json(); + }) + .then(data => { + htmlString="

Description

"+data[0][0]["description"]+"

" + prefix=data[0][1]["prefix"].replaceAll("<","<") + htmlString+="

Namespace

"+prefix+"
"//prefix to construct correct query @data[0][1]["prefix"] + htmlString+="

SPARQL query

"+data[0][2]["query"]+"
" + htmlString+="

SPARQL results table

" + + keys=Object.keys(data[1][0]) + // Add keys as table headers + htmlString+="" + for (var j=0; j" + } + + //Go through the results set given the keys and fill the tail of the table + for (var i=0; i" + } + htmlString+="" + } + htmlString=htmlString+"
" + + //Something like this would be nice, hm + //htmlString+="Execute this query here" + + document.getElementById("playground").innerHTML = htmlString + }); +} + + + + /* Fetch record info using a 'global search'. Returns for example [ @@ -122,14 +164,50 @@ let fetchCountDB = () => { fetchAPI("/api/getCountDB"); } -let fetchSEQCountBySpecimen = () => { - fetchHTMLTable("/api/getSEQCountbySpecimenSource"); + +//****** SPARQL playground functions // keep old functionality as comments for now, might be transfered elsewhere +let fetchSEQCountBySpecimen = (toHTML) => { + //fetchHTMLTable("/api/getSEQCountbySpecimenSource"); + demofetchHTMLTable("/api/demoGetSEQCountbySpecimenSource") } let fetchSEQCountByLocation = () => { - fetchHTMLTable("/api/getSEQCountbyLocation"); + //fetchHTMLTable("/api/getSEQCountbyLocation"); + demofetchHTMLTable("/api/demoGetSEQCountbyLocation") +} + +//Get authors and there country/contitent where they come from +let fetchAuthors = () => { + demofetchHTMLTable("/api/demoGetAuthors") +} + +// Fetch all institutes/originating labs and their associeted publications +let fetchInstitutesPublications = () => { + demofetchHTMLTable("/api/demoInstitutesPublications") +} + +//Fetch seqeenctechnologies used by continent +let demoGetSEQCountbytechContinent = () => { + demofetchHTMLTable("/api/demoGetSEQCountbytechContinent") +} + +let demoGetSEQCountbytech = () => { + demofetchHTMLTable("/api/demoGetSEQCountbytech") +} + +let demoGetSequencePerDate = () => { + demofetchHTMLTable('/api/demoGetSequencePerDate') +} + +let demoLocationGps = () => { + demofetchHTMLTable("/api/demoLocationGps") +} + +let getNYsamples = () => { + demofetchHTMLTable("/api/getNYsamples") } +//old/unused functions let fetchSEQCountByTech = () => { fetchHTMLTable("/api/getSEQCountbytech"); } diff --git a/bh20simplewebuploader/templates/demo.html b/bh20simplewebuploader/templates/demo.html index 1edca00..ffebc91 100644 --- a/bh20simplewebuploader/templates/demo.html +++ b/bh20simplewebuploader/templates/demo.html @@ -5,7 +5,7 @@ {% include 'banner.html' %} {% include 'menu.html' %} -

The Virtuoso database contains public sequences! The examples here should provide a starting point to explore our data in our public SPARQL endpoint or via SIB COVID-19 Integrated Knowledgebase. See also our documentation here for more information!

+

The Virtuoso database contains public sequences! The examples here should provide a starting point to explore our data in our public SPARQL endpoint or via SIB COVID-19 Integrated Knowledgebase. See also our documentation here for more information!

- + + + + + + + + + + + + + @@ -36,7 +46,7 @@
-
+
{% include 'footer.html' %} diff --git a/bh20simplewebuploader/templates/menu.html b/bh20simplewebuploader/templates/menu.html index 8e6ef52..cf32fec 100644 --- a/bh20simplewebuploader/templates/menu.html +++ b/bh20simplewebuploader/templates/menu.html @@ -4,7 +4,7 @@ DOWNLOAD UPLOAD STATUS - DEMO + SPARQL-PLAYGROUND EXPORT DOCS ABOUT -- cgit v1.2.3