Initial commit: SPARQL playground, which replaces the demo page. Some queries have to be adjusted once changed metadata schema is live

5 files changed, 325 insertions, 12 deletions

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[ <http://purl.obolibrary.org/obo/GAZ_00000448> ?location] .
-   ?location <http://www.wikidata.org/prop/direct/P17> ?country .
+    ?fasta ?x [ <http://purl.obolibrary.org/obo/GAZ_00000448> ?location] .
+    ?location <http://www.wikidata.org/prop/direct/P17> ?country .
     ?country <http://www.wikidata.org/prop/direct/P30> ?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: <http://purl.obolibrary.org/obo/> 
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"""
+
+    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: <http://purl.obolibrary.org/obo/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"""
+
+    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: <http://purl.obolibrary.org/obo/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
+PREFIX wiki: <http://www.wikidata.org/prop/direct/>"""
+
+    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: <http://purl.obolibrary.org/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: <http://purl.obolibrary.org/obo/>
+PREFIX wiki: <http://www.wikidata.org/prop/direct/> 
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"""
+
+    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: <http://purl.obolibrary.org/obo/>
+PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>"""
+
+    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 [<http://ncicb.nci.nih.gov/xml/owl/EVS/Thesaurus.owl#C25164> ?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: <http://purl.obolibrary.org/obo/>
+PREFIX wiki: <http://www.wikidata.org/prop/direct/>"""
+
+    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: <http://purl.obolibrary.org/obo/>
+PREFIX wikiE: <http://www.wikidata.org/entity/>"""
+
+    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="<h3>Description</h3><p>"+data[0][0]["description"]+"</p>"
+            prefix=data[0][1]["prefix"].replaceAll("<","&lt;")
+            htmlString+="<h4>Namespace</h4><pre>"+prefix+"</pre>"//prefix to construct correct query @data[0][1]["prefix"]
+            htmlString+="<h3>SPARQL query</h3><pre>"+data[0][2]["query"]+"</pre>"
+            htmlString+="<h3>SPARQL results table</h3><table>"
+
+            keys=Object.keys(data[1][0])
+           // Add keys as table headers
+            htmlString+="<tr>"
+            for (var j=0; j<keys.length;j++){
+                    htmlString+="<td style='font-weight: bold;'>"+keys[j]+"</bold></td>"
+                }
+
+            //Go through the results set given the keys and fill the tail of the table
+            for (var i=0; i<data[1].length;i++) {
+                htmlString+="</tr><tr>"
+                for (var j=0; j<keys.length;j++){
+                    htmlString+="<td>"+data[1][i][keys[j]]+"</td>"
+                }
+                htmlString+="</tr>"
+            }
+            htmlString=htmlString+"</table>"
+
+            //Something like this would be nice, hm
+            //htmlString+="Execute this query <a href='http://sparql.genenetwork.org/sparql?query='"+encodeURIComponent(encodeURIComponent(data[0][1]["prefix"]+data[0][2]["query"]))+"'>here</a>"
+
+            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' %}
 
-    <p>The Virtuoso database contains <span id="CounterDB"></span> public sequences! The examples here should provide a starting point to explore our data in our public <a href="http://sparql.genenetwork.org/sparql">SPARQL endpoint</a> or via <a href="https://covid-19-sparql.expasy.org/">SIB COVID-19 Integrated Knowledgebase</a>. See also our documentation <a href="http://127.0.0.1:5000/blog?id=using-covid-19-pubseq-part1">here</a> for more information!</p>
+    <p>The Virtuoso database contains <span id="CounterDB"></span> public sequences! The examples here should provide a starting point to explore our data in our public <a href="http://sparql.genenetwork.org/sparql">SPARQL endpoint</a> or via <a href="https://covid-19-sparql.expasy.org/">SIB COVID-19 Integrated Knowledgebase</a>. See also our documentation <a href="http://covid-19.genenetwork.org/blog?id=using-covid-19-pubseq-part1">here</a> for more information!</p>
     <!--
     <div class="search">
       <input id="search-input" type="search" placeholder="FASTA uri" required>
@@ -21,11 +21,21 @@
     <section class="search-section">
       <div class="filter-options" action="#">
         <div>
-          <button class="button" onclick="fetchSEQCountBySpecimen()">Count by Specimen source</button>
           <button class="button" onclick="fetchSEQCountByLocation()">Count by Location</button>
-          <button class="button" onclick="fetchSEQCountByTech()">Count by Sequencer</button>
-          <!-- <button class="button" onclick="fetchAllaccessions()">Show All accessions</button> -->
-          <button class="button" onclick="fetchSEQCountbyContinent()">Count by Continent</button>
+          <button class="button" onclick="demoGetSEQCountbytech()">Count by Sequencer</button>
+          <button class="button" onclick="fetchInstitutesPublications()">Get list of publications</button>
+          <button class="button" onclick="fetchSEQCountBySpecimen()">Count by Specimen source</button>
+          <button class="button" onclick="demoGetSEQCountbytechContinent()">Sequence Technologies used by continent</button>
+          <button class="button" onclick="getNYsamples()">Get all NY samples</button>
+          <button class="button" onclick="fetchAuthors()">Get authors</button>
+          <button class="button" onclick="demoGetSequencePerDate()">Show Sequences before submission date</button>
+          <button class="button" onclick="demoLocationGps()">Locations and their GPS</button>
+
+
+
+
+          <!-- <button class="button" onclick="fetchAllaccessions()">Show All accessions</button>
+          <button class="button" onclick="fetchSEQCountbyContinent()">Count by Continent</button>-->
         </div>
 
       </div>
@@ -36,7 +46,7 @@
 
 
     <section>
-      <div id="table"></div>
+      <div id="playground"></div>
     </section>
 
  {% 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 @@
     <a href="/download" class="{{ 'active' if menu=='DOWNLOAD' }}">DOWNLOAD</a>
     <a href="/upload" class="{{ 'active' if menu=='UPLOAD' }}">UPLOAD</a>
     <a href="/status" class="{{ 'active' if menu=='STATUS' }}">STATUS</a>
-    <a href="/demo" class="{{ 'active' if menu=='DEMO' }}">DEMO</a>
+    <a href="/demo" class="{{ 'active' if menu=='DEMO' }}">SPARQL-PLAYGROUND</a>
     <a href="/export" class="{{ 'active' if menu=='EXPORT' }}">EXPORT</a>
     <a href="/blog" class="{{ 'active' if menu=='BLOG' }}">DOCS</a>
     <a href="/about" class="{{ 'active' if menu=='ABOUT' }}">ABOUT</a>