E-PRTR Example Queries

From Enipedia
(Redirected from EPRTR Example Queries)
Jump to: navigation, search

This shows example queries for the European Pollutant Release and Transfer Register dataset. The data is available as RDF from here, and can be queried also via a SPARQL endpoint. The data structure available in the RDF files seems to be somewhat different from the data structure on the SPARQL endpoint, probably due to development work and refactoring.

We also host a copy of the data (sourced from the RDF files) on the Enipedia SPARQL endpoint using a named graph of http://enipedia.tudelft.nl/data/EPRTR. The data can be browsed via a Pubby interface. In building queries from this, What is tricky is that although you may see a link like http://enipedia.tudelft.nl/data/page/rdf/facility/7974, when you query the data, the URL in RDF is actually http://prtr.ec.europa.eu/rdf/facility/7974. Ideally if you visit http://prtr.ec.europa.eu/rdf/facility/7974, you would see a linked data browser or some other representation that would allow you to explore the data, but currently you get a 404 error.

Email me if there is some question, b.zhu@tudelft.nl.

Contents

[edit] Data Requirements

The E-PRTR data in RDF contains several owl:sameAs links. To include data linked via this, we also need to load in other datatsets.

In the http://enipedia.tudelft.nl/data/EPRTR named graph, the following RDF files are currently included:

There are probably a few more sources that we missed. The links below should be useful for finding more of them.

[edit] Schema

This picture shows the schema that is in use by the E-PRTR, and shows the patterns that you can query via SPARQL. The boxes correspond to values of rdf:type, and properties and their amount of usage is indicated as well. This image has automatically been generated via a series of queries that extract the structure of the data.

EUETS RDF Schema.png


[edit] Prefixes

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>

PREFIX: void: <http://rdfs.org/ns/void#> 
PREFIX: foaf: <http://xmlns.com/foaf/0.1/>
PREFIX: dc: <http://purl.org/dc/terms/> 
PREFIX: rdfs: <http://www.w3.org/2000/01/rdf-schema#> 
PREFIX: owl: <http://www.w3.org/2002/07/owl#> 
PREFIX: skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX: ramonGeographic: <http://ec.europa.eu/eurostat/ramon/ontologies/geographic.rdf#> 
PREFIX: wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#">

[edit] Information about a facility

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select * from <http://enipedia.tudelft.nl/data/EPRTR> {
<http://prtr.ec.europa.eu/rdf/facilityReport/373846> ?x ?y . 
} 

Example below is about the Corus Staal facility outside of Amsterdam:

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?pollutantName ?totalQuantity ?year from <http://enipedia.tudelft.nl/data/EPRTR> {
?fr rdf:type eprtr:FacilityReport .
?fr eprtr:reportingYear ?year . 
?fr eprtr:forFacility facility:7974 . 
?pr eprtr:facilityReport ?fr . 
?pr rdf:type eprtr:PollutantRelease . 
?pr eprtr:forPollutant ?pollutant . 
?pollutant rdfs:label ?pollutantName . 
?pr eprtr:totalQuantity ?totalQuantity .
} 

[edit] Waste transfer values

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select * from <http://enipedia.tudelft.nl/data/EPRTR> {
   ?facility eprtr:latestReport ?y .
   ?facility rdf:type eprtr:Facility . 
   ?x eprtr:facilityReport ?y .
   ?x rdf:type eprtr:WasteTransfer .
   ?x ?properties ?value .
} limit 10

[edit] facilities and their pollutant releases

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?name ?pollutantName ?totalQuantity from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:latestReport ?lr .
?facility eprtr:facilityName ?name .
?pr eprtr:facilityReport ?lr . 
?pr rdf:type eprtr:PollutantRelease . 
?pr eprtr:forPollutant ?pollutant . 
?pollutant rdfs:label ?pollutantName . 
?pr eprtr:totalQuantity ?totalQuantity .
} 

[edit] Mapping waste transfer within EU

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>
select ?name ?lat ?long  ?facilitycountry ?countryname ?SITEADDRESSCITY ?streetname ?POSTALCODE  from <http://enipedia.tudelft.nl/data/EPRTR> {
   ?facility eprtr:latestReport ?y .
   ?facility eprtr:facilityName ?name .
   ?facility rdf:type eprtr:Facility .
   ?facility eprtr:inCountry ?country .
   ?facility wgs84:lat ?lat .
   ?facility wgs84:long ?long .
   ?country rdfs:label ?facilitycountry .
   ?x eprtr:facilityReport ?y .
   ?x rdf:type eprtr:WasteTransfer .
   ?x eprtr:confidentialIndicator 0 .
   ?x eprtr:wasteHandlerPartySiteAddressCountryName ?countryname .
   OPTIONAL{?x eprtr:wasteHandlerPartySiteAddressStreetName ?streetname } .
   OPTIONAL{?x eprtr:wasteHandlerPartySiteAddressPostalCode ?POSTALCODE } .
   OPTIONAL{?x eprtr:wasteHandlerPartySiteAddressCity ?SITEADDRESSCITY } .
   OPTIONAL{?x eprtr:wasteHandlerPartySiteAddressPostalCode ?POSTALCODE } .

}

[edit] Facilities and CO2 emission

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?facilityName "CO2" ?quantity from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label "CO2, AIR" .
?pollutantRelease eprtr:totalQuantity ?quantity
}

[edit] CO2 emission per European countries

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?countryName sum(?quantity) as ?totalQuantity ?pollutantName from 
<http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label ?pollutantName . 
filter(regex(?pollutantName, "CO2, AIR")) . 
?latestReport eprtr:inCountry ?country . 
?country rdfs:label ?countryName .
?pollutantRelease eprtr:totalQuantity ?quantity .
?latestReport eprtr:forNUTS ?nuts . 
} group by ?countryName ?pollutantName order by DESC(?totalQuantity)

[edit] CO2 emission per NUTS2 region

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?regionName sum(?quantity) as ?totalQuantity ?pollutantName from 
<http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label ?pollutantName . 
filter(regex(?pollutantName, "CO2, AIR")) . 
?pollutantRelease eprtr:totalQuantity ?quantity .
?latestReport eprtr:forNUTS ?nuts . 
?nuts rdfs:label ?regionName .
} group by ?regionName ?pollutantName order by DESC(?totalQuantity)

[edit] Display CO2 emissions of countries and regions in Europe with shiny

We need two files, ui.r and server.r. Shiny.

This link takes you to the interface: enCore project CO2 emissions.

ui.r
library(reshape2)
countryEmission = read.csv("country.csv")
regionEmission = read.csv("region.csv")

tmp = colsplit(countryEmission$countryName, " - ", names=c("CountryAbbrev", "CountryFullName"))
countryEmission = cbind(countryEmission, tmp)
countryEmission$countryName = countryEmission$CountryFullName
countryEmission$CountryFullName = NULL

col = colsplit(regionEmission$regionName, " - ", names=c("regionAbbrev", "regionFullName"))
regionEmission <- cbind(regionEmission, col)
regionEmission$regionName = regionEmission$regionFullName
regionEmission$regionFullName = NULL
regionEmission = subset(regionEmission, regionName!= "N/A")


shinyUI(fluidPage(
  titlePanel("enCore projects CO2 per country"),
  
  sidebarLayout(
    sidebarPanel(
            
      selectInput("var1", 
                  label = "Choose a country to display",
                  choices = countryEmission$countryName,
                  selected = countryEmission$countryName[1]),
    
                  
               
      selectInput("var2", 
                  label = "Choose a region to display",
                  choices = regionEmission$regionName,
                  selected = regionEmission$regionName[1])
    
    ),
    
    mainPanel(
      textOutput("country"),
      textOutput("region")
      )
 )
))
server.r
shinyServer(
  function(input, output) {
    
    output$country <- renderText({ 
      locs = which(countryEmission$countryName == input$var1)
      data <- countryEmission$totalQuantity[locs]
      paste("The CO2 emission of", input$var1, "is", data, "kg")
    })
    
    output$region <- renderText({ 
      locs2 = which(regionEmission$regionName == input$var2)
      data <- regionEmission$totalQuantity[locs2]
      paste("The CO2 emission of", input$var2, "is", data, "kg")
      
      })
    
    
  }
)

[edit] Making choropleth map of CO2 difference per country using R

The shape-file data can be downloaded from here.

Inspired by Maps in R: choropleth maps.

library(maptools)
library(ggplot2)
library(ggmap)
library(rgeos)
library(reshape2)
# read administrative boundaries (change folder appropriately)
eurMap <- readShapePoly(fn="NUTS_2010_60M_SH/data/NUTS_RG_60M_2010")
#plot(eurMap)
# read downloaded data (change folder appropriately)
eurEdu <- read.csv("country.csv", stringsAsFactors = F)
eurEdu$totalQuantity <- as.double(eurEdu$totalQuantity) #format as numeric

# split the column countryName into the abbrev and full name, the abbre is also the NUTS0 code 
tmp = colsplit(eurEdu$countryName, " - ", names=c("CountryAbbrev", "CountryFullName"))
eurEdu = cbind(eurEdu, tmp)
eurEdu$countryName = eurEdu$CountryFullName
eurEdu$CountryFullName = NULL

#show the data framework

map_nuts2.fort <- fortify(eurMap, region='NUTS_ID') 
map_nuts2.fort<-map_nuts2.fort[order(map_nuts2.fort$order), ] 
head(as.data.frame(map_nuts2.fort))

#map_nuts2 <- subset(eurMap, STAT_LEVL_ <= 0)


# merge map and data
eurEduMapDf <- merge(map_nuts2.fort, eurEdu, by.x="id", by.y="CountryAbbrev")
eurEduMapDf <- eurEduMapDf[order(eurEduMapDf$order),]

#limit data to main Europe
europe.limits <- geocode(c("Cape Fligely, Rudolf Island, Franz Josef Land, Russia", "Gavdos, Greece", "Faja Grande, Azores", "Severny Island, Novaya Zemlya, Russia"))

eurEduMapDf <- subset(eurEduMapDf, long > min(europe.limits$lon) & long < max(europe.limits$lon) & lat > min(europe.limits$lat) & lat < max(europe.limits$lat))

# ggplot mapping
# data layer

m0 <- ggplot(data=eurEduMapDf)
# empty map (only borders)
m1 <- m0 + geom_path(aes(x=long, y=lat, group=group), color='gray') + coord_equal()

# fill with CO2 data
m2 <- m1 + geom_polygon(aes(x=long, y=lat, group=group, fill=totalQuantity))

# optional, add text on countries
library(doBy)
txtVal <- summaryBy(long + lat + totalQuantity ~ id, data=eurEduMapDf, FUN=mean, keep.names=T)
m3 <- m2 + geom_text(aes(x=long, y=lat, label=totalQuantity), data=txtVal, col="yellow", cex=3)
server.r
shinyServer(
  function(input, output) {
    
    output$country <- renderText({ 
      locs = which(countryEmission$countryName == input$var1)
      data <- countryEmission$totalQuantity[locs]
      paste("The CO2 emission of", input$var1, "is", data, "kg")
    })
    
    output$region <- renderText({ 
      locs2 = which(regionEmission$regionName == input$var2)
      data <- regionEmission$totalQuantity[locs2]
      paste("The CO2 emission of", input$var2, "is", data, "kg")
      
      })
    
    
  }
)

[edit] Making choropleth map of CO2 difference per NUTS2 region using R

library(maptools)
library(ggplot2)
library(ggmap)
library(rgeos)
library(reshape2)

# read administrative boundaries (change folder appropriately)
eurMap <- readShapePoly(fn="NUTS_2010_60M_SH/data/NUTS_RG_60M_2010")

#plot(eurMap)

# read downloaded data (change folder appropriately)
regionEmission <- read.csv("region.csv", stringsAsFactors = F)
regionEmission$totalQuantity <- as.double(eurEdu$totalQuantity) #format as numeric

# split the column regionName into the abbrev and full name, the abbre is also the NUTS2 code 
col = colsplit(regionEmission$regionName, " - ", names=c("regionAbbrev", "regionFullName"))
regionEmission <- cbind(regionEmission, col)
regionEmission$regionName = regionEmission$regionFullName
regionEmission$regionFullName = NULL

#removing a row called N/A
regionEmission = subset(regionEmission, regionName!= "N/A")

#show the data framework
head(as.data.frame(eurMap))

# formate the dataframe work of eurMap, so that ggplot can read
map_nuts2.fort <- fortify(eurMap, region='NUTS_ID') 
head(as.data.frame(map_nuts2.fort))
map_nuts2.fort<-map_nuts2.fort[order(map_nuts2.fort$order), ] 

#check the level and get a subset
#map_nuts2 <- subset(eurMap, STAT_LEVL_ <= 2)
#head(as.data.frame(map_nuts2))
#map_nuts2.fort <- fortify(map_nuts2, region='NUTS_ID') 
#head(as.data.frame(map_nuts2.fort))

# merge map and data
eurEduMapDf <- merge(map_nuts2.fort, regionEmission, by.x="id", by.y="regionAbbrev")
eurEduMapDf <- eurEduMapDf[order(eurEduMapDf$order),]

#limit data to main Europe
europe.limits <- geocode(c("Cape Fligely, Rudolf Island, Franz Josef Land, Russia", "Gavdos, Greece", "Faja Grande, Azores", "Severny Island, Novaya Zemlya, Russia"))

eurEduMapDf <- subset(eurEduMapDf, long > min(europe.limits$lon) & long < max(europe.limits$lon) & lat > min(europe.limits$lat) & lat < max(europe.limits$lat))

# ggplot mapping
# data layer

m0 <- ggplot(data=eurEduMapDf)
# empty map (only borders)
m1 <- m0 + geom_path(aes(x=long, y=lat, group=group), color='gray') + coord_equal()

#fill with reginon CO2 pollution data
m2 <- m1 + geom_polygon(aes(x=long, y=lat, group=group, fill=totalQuantity)) + scale_fill_gradient(low='white', high='grey20')

#add the emission text on top of regions
library(doBy)
txtVal <- summaryBy(long + lat + totalQuantity ~ id, data=eurEduMapDf, FUN=mean, keep.names=T)
m3 <- m2 + geom_text(aes(x=long, y=lat, label=totalQuantity), data=txtVal, col="yellow", cex=3)

#to have visible borders
m0 <- ggplot(data=eurEduMapDf)
m1 <- m0 + geom_polygon(aes(x=long, y=lat, group=group, fill=totalQuantity)) + scale_fill_gradient(low='white', high='grey20')
m2 <- m1 + geom_path(aes(x=long, y=lat, group=group), color='gray') + coord_equal()

#over a Googlemap
map <- get_map(location = 'Europe', zoom=4)
m0 <- ggmap(map)
m1 <- m0 + geom_polygon(aes(x=long, y=lat, group=group, fill=totalQuantity), data=eurEduMapDf, alpha=.7) + scale_fill_gradient(low='white', high='grey20')
m2 <- m1 + geom_path(aes(x=long, y=lat, group=group), data=eurEduMapDf, color='black')

[edit] CO2 per sector in EU by NACE codes

PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
select ?NACE_code sum(?quantity) as ?totalQuantity ?pollutantName from 
<http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label ?pollutantName . 
filter(regex(?pollutantName, "CO2, AIR")) . 
?latestReport eprtr:nACEActivity ?NACE. 
?NACE rdfs:label ?NACE_code .
?pollutantRelease eprtr:totalQuantity ?quantity .

} group by ?NACE_code ?pollutantName order by DESC(?totalQuantity)

[edit] Visualization of CO2 per sector in EU by NACE codes with R

Color in R can be found: here.

The resulting bar-chart:

CO2 per sector NACE.png

library(maptools)
library(ggplot2)
library(ggmap)
library(rgeos)
library(reshape2)
library(sqldf)

NACE <- read.csv("NACE_CO2.csv", stringsAsFactors = F)
NACE$totalQuantity <- as.double(NACE$totalQuantity) #format as numeric

# split the column countryName into codes and industrial activites
tmp = colsplit(NACE$NACE_code, " - ", names=c("Codes", "Activity"))
NACE = cbind(NACE, tmp)
NACE$NACE_code = NACE$Activity
NACE$Codes = NULL

NACE = sqldf("select * from NACE order by totalQuantity ASC")

# reorder to get a barchart from highest to lowest
NACE$NACE_code = factor(NACE$NACE_code, levels=NACE$NACE_code)

#data layer
gg <- ggplot(NACE, aes(x = NACE_code, y = totalQuantity, fill = totalQuantity))

# plot bar
gg<- gg + geom_bar(stat = "identity")

# turn x, y axis around, make the colour gradient
gg<- gg + coord_flip()+ scale_fill_gradient(low='grey65', high='grey20')

gg

ggsave("CO2_per_sector_NACE.pdf", plot = gg, scale = 2)

[edit] Facilities with CO2 emissions, map them per sector

Production of electricity
Manufacture of refined petroleum products
Manufacture of basic iron and steel and of ferro-alloys
options(stringsAsFactors = FALSE)
library(SPARQL)
library(maptools)
library(ggplot2)
library(ggmap)


queryString = "PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

select ?facilityName ?NACE_code ?quantity ?lat ?long from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label \"CO2, AIR\" .
?pollutantRelease eprtr:totalQuantity ?quantity .
?facility wgs84:lat ?lat .
?facility wgs84:long ?long .
?latestReport eprtr:nACEActivity ?NACE . 
?NACE rdfs:label ?NACE_code .
}"

endpoint = "http://enipedia.tudelft.nl/sparql"

queryResults = SPARQL(url=endpoint, query=queryString, format='csv', extra=list(format='text/csv'))
NACE = queryResults$results

NACE$lat <- as.numeric(NACE$lat)
NACE$long <- as.numeric(NACE$long)
tmp = colsplit(NACE$NACE_code, " - ", names=c("Codes", "Activity"))
NACE = cbind(NACE, tmp)
NACE$NACE_code = NACE$Activity
NACE$Codes = NULL

#get the facet of individual sector
#NACE <- subset(NACE, NACE_code == "Manufacture of basic iron and steel and of ferro-alloys") 
  
map <- get_map(location = c(left = -10, bottom = 30, right = 20, top = 80), zoom= 4)
m0 <- ggmap(map)
m1 <- m0 + geom_point(aes(x=long, y=lat, size=quantity), data = NACE, alpha=.7)
m1


[edit] Show CO2 providers on a static map using googleVis

options(stringsAsFactors = FALSE)
library(XML)
library(googleVis)
map <- read.csv("CO2_facilities.csv")
map$loc=paste(map$lat, map$long, sep=":")

G <- gvisGeoChart(map, locationvar = "loc", sizevar = "quantity", hovervar = "facilityName",
                  options=list(region = "150", displayMode="Markers", backgroundColor="lightblue", width=1600, height=1200), chartid="CO2_facility")
plot(G)

[edit] Making an interactive map of CO2 providers in Europe, data from EPRTR

options(stringsAsFactors = FALSE)
library(leafletR)
library(XML)
library(SPARQL)

#load data from querying the sparql endpoint
queryString = "PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

select ?facilityName ?quantity ?lat ?long from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label \"CO2, AIR\" .
?pollutantRelease eprtr:totalQuantity ?quantity .
?facility wgs84:lat ?lat .
?facility wgs84:long ?long .
}"

endpoint = "http://enipedia.tudelft.nl/sparql"

queryResults = SPARQL(url=endpoint, query=queryString, format='csv', extra=list(format='text/csv'))
map = queryResults$results

map$facilityName = NULL
map <- map[c(2, 3, 1)]

# store data in GeoJSON file
q.dat <- toGeoJSON(data= map,name="coordinates")
# make style based on quake magnitude
q.style <- styleGrad(prop="quantity", breaks=seq(1e+8, 3e+10, by = 5e+9), style.val=rev(heat.colors(3)), leg="CO2 quantity",
                     fill.alpha=0.7, rad=5)
                     
                     # create map
q.map <- leaflet(data=q.dat, title="CO2 quantity",
                 base.map="osm", style=q.style, popup="quantity")
# view map in browser
#q.map

[edit] Making an interactive map of CO2 providers in Europe, data from EPRTR(with updated rdf file from EPRTR)

options(stringsAsFactors = FALSE)
library(leafletR)
library(XML)
library(SPARQL)

#load data from querying the sparql endpoint
queryString = "PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

#get million ton
#get rid of language tag en
select (?facilityName as ?FacilityName) (xsd:float(?quantity)/1e+9 AS ?Quantity) ?lat ?long (str(?regionName) AS ?RegionName) (str(?industrialClassification) AS ?IndustrialClassification) from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?pollutantRelease rdfs:label \"CO2, AIR\" .
?pollutantRelease eprtr:totalQuantity ?quantity .
?facility wgs84:lat ?lat .
?facility wgs84:long ?long .
?latestReport eprtr:forNUTS ?nuts . 
?nuts owl:sameAs ?rdfdata .
?rdfdata rdfs:label ?regionName .
?latestReport eprtr:nACEActivity ?NACE. 
?NACE owl:sameAs ?rdfdata2 .
?rdfdata2 rdfs:label ?industrialClassification .

}"

endpoint = "http://enipedia.tudelft.nl/sparql"

queryResults = SPARQL(url=endpoint, query=queryString, format='csv', extra=list(format='text/csv'))
map = queryResults$results


#map$facilityName = NULL
#map <- map[c(2, 3, 1)]
#the code below is to clean the data, "facilityName": "AB "Akmenės cementas""
#which confuses the code since it thinks the facilityName is just "AB ", and then there's this other text after that (Akmenės cementas") which it can't make any sense out of.  
#The code below will generate this output:  "facilityName": "AB \"Akmenės cementas\""
#which escapes the quotes in the text so that they are interpreted correctly: https://en.wikipedia.org/wiki/Escape_character

map$facilityName = gsub('"', '\\\\"', map$facilityName)
#I can just change the sequence in the sparql, but i can also use c(1,2) below in the togeojson, 1 indicates the column number
map <- map[c(3, 4, 1, 2, 5, 6)]
#format(map$Quantity, digits = 4, nsmall = 3)

# store data in GeoJSON file
q.dat <- toGeoJSON(data= map, name="coordinates", overwrite=TRUE)
# make style based on quake magnitude
q.style <- styleGrad(prop="Quantity", breaks=seq(0, 4, by =0.5), out = 1, style.val=rev(heat.colors(9)), leg="CO2 quantity in million ton",
                     fill.alpha=0.7, rad=5)
                     
# create map
q.map <- leaflet(data=q.dat, title="CO2 source visulization",
                 base.map="osm", style=q.style, popup=("*"))
# view map in browser
#q.map

[edit] Check if the "CO2 EXCL BIOMASS, AIR facilities" are included in the "CO2, AIR" facilities

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

#get million ton
#get rid of language tag en
select * from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility rdf:type eprtr:Facility . 
?facility eprtr:latestReport ?latestReport .
?latestReport eprtr:reportingYear ?reportingyear .
filter(?reportingyear > 2004) .

?pollutantRelease1 eprtr:facilityReport ?latestReport .
?pollutantRelease1 rdfs:label ?pollutantName1 . 
filter(?pollutantName1, "CO2 EXCL BIOMASS, AIR") . 

FILTER NOT EXISTS {
?pollutantRelease2 eprtr:facilityReport ?latestReport .
?pollutantRelease2 rdfs:label ?pollutantName2 .
filter(?pollutantName2, "CO2, AIR") . 
}

}PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

#get million ton
#get rid of language tag en
select * from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility rdf:type eprtr:Facility . 
?facility eprtr:latestReport ?latestReport .
?latestReport eprtr:reportingYear ?reportingyear .
filter(?reportingyear > 2004) .

?pollutantRelease1 eprtr:facilityReport ?latestReport .
?pollutantRelease1 rdfs:label ?pollutantName1 . 
filter(?pollutantName1, "CO2 EXCL BIOMASS, AIR") . 

FILTER NOT EXISTS {
?pollutantRelease2 eprtr:facilityReport ?latestReport .
?pollutantRelease2 rdfs:label ?pollutantName2 .
filter(?pollutantName2, "CO2, AIR") . 
}

}

[edit] Check if all the the "CO2 EXCL BIOMASS, AIR facilities" emit less CO2 than the "CO2, AIR" facilities

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

#get million ton
#get rid of language tag en
select * from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility rdf:type eprtr:Facility . 
?facility eprtr:latestReport ?latestReport .
?latestReport eprtr:reportingYear ?reportingyear .
filter(?reportingyear > 2004) .

?pollutantRelease1 eprtr:facilityReport ?latestReport .
?pollutantRelease1 rdfs:label ?pollutantName1 . 
filter(?pollutantName1 = "CO2 EXCL BIOMASS, AIR") . 
?pollutantRelease1 eprtr:totalQuantity ?quantity1 . 

?pollutantRelease2 eprtr:facilityReport ?latestReport .
?pollutantRelease2 rdfs:label ?pollutantName2 .
filter(?pollutantName2 = "CO2, AIR") . 
?pollutantRelease2 eprtr:totalQuantity ?quantity2 . 

filter(?quantity1 > ?quantity2) . 

}

[edit] Something wrong with the information from Greece

In the rdf data, there is the country. ?facility eprtr:inCountry eprtr:rdf/country/EL

But the triple eprtr:rdf/country/EL owl:sameAs <http://eurostat.linked-statistics.org/dic/geo#GR>, links the country to external information. But in the rdf file from eurostat, the information of Greece is skos:Concept rdf:about="/dic/geo#EL">. geo#GR and geo#EL can't be linked.

What we did is to use another external rdf source: <http://ec.europa.eu/eurostat/ramon/rdfdata/countries/EL>

Same problems happened for the NUTS2 part.

We stopped using the <http://eurostat.linked-statistics.org/>, and use another one: <http://rdfdata.eionet.europa.eu/ramon/nuts/EL30>. we downloaded the ramon data-set. But the URL is different from <http://rdfdata.eionet.europa.eu>. The new rdf data-set in enipedia is replaced with the <http://rdfdata.eionet.europa.eu>.

The query below delivers the fixed result.

PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX eprtr: <http://prtr.ec.europa.eu/rdf/schema.rdf#>
PREFIX facility: <http://prtr.ec.europa.eu/rdf/facility/>
PREFIX wgs84: <http://www.w3.org/2003/01/geo/wgs84_pos#>

#get million ton
#get rid of language tag en
select (?facilityName as ?FacilityName) (xsd:float(?quantity)/1e+9 AS ?Quantity) 
?lat ?long (str(?regionName) AS ?RegionName) 
(str(?industrialClassification) AS ?IndustrialClassification) ?City ?Country ?ReportingYear
from <http://enipedia.tudelft.nl/data/EPRTR> {
?facility eprtr:facilityName ?facilityName .
?facility eprtr:latestReport ?latestReport .
?pollutantRelease eprtr:facilityReport ?latestReport .
?latestReport eprtr:reportingYear ?ReportingYear .
?pollutantRelease rdfs:label \"CO2, AIR\" .
?pollutantRelease eprtr:totalQuantity ?quantity .
?facility wgs84:lat ?lat .
?facility wgs84:long ?long .
?facility eprtr:city ?City .
?latestReport eprtr:forNUTS ?nuts . 
?nuts owl:sameAs ?rdfdata .
?rdfdata rdfs:label ?regionName .
?latestReport eprtr:inCountry ?country . 
?country owl:sameAs ?rdfdata3 .
?rdfdata3 rdfs:label ?Country .
?latestReport eprtr:nACEActivity ?NACE. 
?NACE owl:sameAs ?rdfdata2 .
?rdfdata2 rdfs:label ?industrialClassification .
}
Personal tools
Namespaces

Variants
Actions
Navigation
Portals
Advanced
Toolbox