Creating a Map of Project Lead Institutions in Programme

step by step guide

Creating a Map of Programme Institutions

1. Obtain Data from PROD (via Talis store)

2. Obtain Geo-data

3. Map Data

USING KML

Useful Resources

1. Obtain Data from PROD (via Talis store)

1) Go to the SparlProxy web service:

http://data-gov.tw.rpi.edu/ws/sparqlproxy.php

2) At the bottom of  the Box  - SPARQL service (URL) put

http://api.talis.com/stores/jisc-prod-dev1/services/sparql

3) Check CSV radio Button

4) We need to use a SPARQL Query to get the data. Here are some examples of some useful starter for 10 queries:

If you want a list of all Projects in PROD and their institutions:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

PREFIX owl: <http://www.w3.org/2002/07/owl#>

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

PREFIX foaf: <http://xmlns.com/foaf/0.1/>

PREFIX jisc: <http://www.rkbexplorer.com/ontologies/jisc#>

PREFIX doap: <http://usefulinc.com/ns/doap#>

PREFIX prod: <http://prod.cetis.ac.uk/vocab/>

PREFIX mu: <http://www.jiscmu.ac.uk/schema/muweb/>

PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>

PREFIX dc: <http://purl.org/dc/elements/1.1/>

SELECT DISTINCT ?projectID ?Project ?Programme ?Strand ?Lead_Inst_ID ?Lead_Inst ?Partner_ID ?Partner

WHERE {  

?projectID a doap:Project .

OPTIONAL { ?projectID prod:programme ?Programme } .

OPTIONAL { ?projectID prod:strand ?Strand } .

OPTIONAL { ?projectID jisc:short-name ?Project } .

?projectID doap:vendor ?Lead_Inst_ID .

?Lead_Inst_ID rdfs:label ?Lead_Inst .

OPTIONAL { ?projectID prod:partnerInstitution ?Partner_ID .

  ?Partner_ID rdfs:label ?Partner .

 } .

}

A SPARQL Query to get all projects and their partners

If you want a specific programme then use the query below. Change “Curriculum Delivery” to the Programme you want:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

PREFIX owl: <http://www.w3.org/2002/07/owl#>

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

PREFIX foaf: <http://xmlns.com/foaf/0.1/>

PREFIX jisc: <http://www.rkbexplorer.com/ontologies/jisc#>

PREFIX doap: <http://usefulinc.com/ns/doap#>

PREFIX prod: <http://prod.cetis.ac.uk/vocab/>

PREFIX mu: <http://www.jiscmu.ac.uk/schema/muweb/>

PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>

PREFIX dc: <http://purl.org/dc/elements/1.1/>

SELECT DISTINCT ?projectID ?Project ?Programme ?Strand ?Lead_Inst_ID ?Lead_Inst ?Partner_ID ?Partner

WHERE {  

?projectID a doap:Project .

?projectID prod:programme  "Curriculum Delivery" .

OPTIONAL { ?projectID prod:programme ?Programme } .

OPTIONAL { ?projectID prod:strand ?Strand } .

OPTIONAL { ?projectID jisc:short-name ?Project } .

?projectID doap:vendor ?Lead_Inst_ID .

?Lead_Inst_ID rdfs:label ?Lead_Inst .

OPTIONAL { ?projectID prod:partnerInstitution ?Partner_ID .

  ?Partner_ID rdfs:label ?Partner .

 } .

}

A SPARQL Query to get all Curriculum Delivery and their partners

Copy the code and paste in into the SPARQL Query box

5) Press the query button (you’ll get a page similar to the screenshot below).  Copy the URL of this results page

6) To make sense and display the query, open a new Google Spreadsheet

7) In Column A1 type the function =ImportData(“URL”) Swap URL for the URL you copied from you query. You should get a spreadsheet looking something like the screen shot below.

Name, and save your spreadsheet.  Remember, as this is a google doc you can share it too.

2. Obtaining Geo-data and creating maps

1) Our first query and spreadsheet doesn’t have any Geo Location data for our institutions. We are going to grab this data (currently just for lead institutions) using the method described in Section 1. This time use the query below, change “Curriculum Delivery” for whatever program you want to work with.  

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>

PREFIX owl: <http://www.w3.org/2002/07/owl#>

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

PREFIX foaf: <http://xmlns.com/foaf/0.1/>

PREFIX jisc: <http://www.rkbexplorer.com/ontologies/jisc#>

PREFIX doap: <http://usefulinc.com/ns/doap#>

PREFIX prod: <http://prod.cetis.ac.uk/vocab/>

PREFIX mu: <http://www.jiscmu.ac.uk/schema/muweb/>

PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>

PREFIX dc: <http://purl.org/dc/elements/1.1/>

SELECT DISTINCT ?projectID ?projectname ?desc  ?Lead_Inst_ID ?Lead_Inst xsd:float(?lat)  xsd:float(?long)

WHERE {  

?projectID doap:name ?projectname  .

?projectID doap:vendor ?Lead_Inst_ID .

?projectID prod:programme  "Curriculum Delivery" .

?Lead_Inst_ID rdfs:label ?Lead_Inst .

OPTIONAL {  

  ?Lead_Inst_ID owl:sameAs ?fbID .

  ?muID owl:sameAs ?fbID .

  ?muID geo:lat ?lat .

  ?muID geo:long ?long .

} .

OPTIONAL { ?projectID doap:shortdesc ?desc } .

}

ORDER BY ?Lead_Inst

Follow the steps 1- 6 as before and Save this query in a new sheet on your google spreadsheet using the =ImportData(“URL”) function as before.

This time you should get a spreadsheet that looks similar to the screengrab below.

If you are looking for geo-data for partner institutions, you may have to do some extra work for example, Martin Hawksey had to to do some additional searching to get the geo-locations for all partners involved in the OER programmes (see this blog post http://mashe.hawksey.info/2012/01/oer-visualisation-project-maps-maps-maps-maps-day-20/ )

3. Map Data

Now we’re going to map the Long and Lat in our second sheet. There are a number of ways to map, and we’re going to use an existing template and integration of google maps and KML.

*NB it is easier to use Chrome to get this to work

Using KML

1) Create a copy of the Google spreadsheet mapper by going to this link:

https://spreadsheets.google.com/ccc?key=p-zIWuNNsnGKqQ_V-SpUsRQ&newcopy

2) In your copy, open the second sheet (Placemark Data). Delete the example data (everything in the white cells). From the spreadsheet you created in Step 2, copy and paste the Long and Lat data. Make sure that there is a number in the template column (1-6) for each record) .

 

3) Click File and choose “Publish as web page”, from the “Get a Link to the Published Data” section, select TXT/Pain Text option, and copy the URL, which should look something like this

https://docs.google.com/spreadsheet/pub?key=0Ah6ebMDR0G5WdF9Sb19ISTM5YnlmTEk0c2JmWkVIUGc&output=txt

5) Go back to the Start Here sheet and Paste the URL into the white cell provided under Publish and View. Then click on view Place Marks in Google Maps - you should see a map looking something like this:

Useful Resources

Google Spreadsheets

docs.google.com

Martins Cheatsheet

https://docs.google.com/document/d/1atVIAjKoZl1dUzq7jYys0c7ObMVMULmuC_eZXlMhDh8/edit?hl=en_US

sparlproxy web service:

http://data-gov.tw.rpi.edu/ws/sparqlproxy.php

JISC CETIS PROD TALIS datastore:

Cup of tea and a couple of hob nobs - or a twix!