Country listing page

[timgdavies]

(Re #135)

I've just had a go at compressing the queries required for a country page into a single query.

The query below fetches a list of countries, their names and then counts the total number of projects, as well as the total number of Oil and Gas projects, and the total number of Mining projects.

The revised data should only ever have commodityType of "Oil and Gas" or "Mining", so we don't need columns for 'oil' or 'gas' on their own.

I've also added in the countrycode, as we may want to output this in the JSON representation.

I would suggest we add a column to the RHS of each row in the listing table with an 'Explore projects' link of some sort (could be an icon).

prefix rp: <http://resourceprojects.org/def/>

SELECT ?country ?countryName ?countryCode count(distinct ?project) as ?projectCount count(distinct ?oilProject) as ?oilAndGasProjectCount count(distinct ?miningProject) as ?miningProjectCount  WHERE {

 ?country a rp:Country.
 ?country skos:prefLabel ?countryName.
 ?country rp:identifier ?countryCode.

 OPTIONAL {
   ?project a rp:Project.
   ?project rp:hasLocation ?country.
 }

 OPTIONAL {
   ?oilProject a rp:Project.
   ?oilProject rp:hasLocation ?country.
   ?oilProject rp:hasCommodity ?oil.
   ?oil rp:commodityType "Oil and Gas".
   FILTER(?oil != <http://resourceprojects.org/commodity/>)
  }

 OPTIONAL {
   ?miningProject a rp:Project.
   ?miningProject rp:hasLocation ?country.
   ?miningProject rp:hasCommodity ?mine.
   ?mine rp:commodityType "Mining".
   FILTER(?mine != <http://resourceprojects.org/commodity/>)
  }

} GROUP BY ?country ?countryName ?countryCode ORDER BY ASC(?countryName)

[caprenter]

This is for a Countries list page? e.g. http://lodspeakr-live.nrgi-dev2.default.opendataservices.uk0.bigv.io/country

You want me to plug this into my branch and see what happens?

[timgdavies]

That's right yep. It's quite a bit change to the queries (i.e. replaces the three queries with one) so would also need the page template updated - hence not putting it direct into a commit.

[timgdavies]

I've also spent a few minutes exploring how to get a nice project map on that page. The following is mainly a braindump as I'll need to move on - but maybe come back to this later.

Potentially we could:

• Add a geoJSON layer of all the countries on earth to a large map at the top of the page (from a static file - I just did this with http://data.okfn.org/data/datasets/geo-boundaries-world-110m but it's a bit of a big file)
• Use the ability to style geoJson objects based on their properties to use ISO codes to colour countries according to their number of projects.

Alternatively, we could load the boundary data for each country into the data store, and then get back geoJson through the JSON template for the query, with the project counts etc. embedded in the geoJson.

[caprenter]

ok - I've altered the query and the template: https://github.com/NRGI/resourceprojects.org-frontend/commit/ac6013947378310aa9f58c784ca5f2222a4564d6

Checkout that branch to see what it looks like

[caprenter]

We've gone back to a previous query on this to drop out the lines with zero projects and to deal with load speed. That has also meant dropping the counts