#Import the necessary packages
from SPARQLWrapper import SPARQLWrapper, JSON

#Add some helper functions
def runQuery(endpoint,prefix,q):
    ''' Run a SPARQL query with a declared prefix over a specified endpoint '''
    sparql = SPARQLWrapper(endpoint)
    sparql.setQuery(prefix+q)
    sparql.setReturnFormat(JSON)
    return sparql.query().convert()

import pandas as pd

def dict2df(results):
    ''' Hack a function to flatten the SPARQL query results and return the column values '''
    data=[]
    for result in results["results"]["bindings"]:
        tmp={}
        for el in result:
            tmp[el]=result[el]['value']
        data.append(tmp)

    df = pd.DataFrame(data)
    return df

def dfResults(endpoint,prefix,q):
    ''' Generate a data frame containing the results of running
        a SPARQL query with a declared prefix over a specified endpoint '''
    return dict2df( runQuery( endpoint, prefix, q ) )
        
def printQuery(results,limit=''):
    ''' Print the results from the SPARQL query '''
    resdata=results["results"]["bindings"]
    if limit!='': resdata=results["results"]["bindings"][:limit]
    for result in resdata:
        for ans in result:
            print('{0}: {1}'.format(ans,result[ans]['value']))
        print()

def printRunQuery(endpoint,prefix,q,limit=''):
    ''' Print the results from the SPARQL query '''
    results=runQuery(endpoint,prefix,q)
    printQuery(results,limit)

endpoint_envAgency='http://environment.data.gov.uk/sparql/bwq/query'

prefix='''
'''

q='''
SELECT ?x ?name ?district
WHERE {
?x a <http://environment.data.gov.uk/def/bathing-water/BathingWater> .
?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
?x <http://statistics.data.gov.uk/def/administrative-geography/district> ?district .}
'''

df=dfResults(endpoint_envAgency,prefix,q)
df[:5]

prefix='''
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX admingeo: <http://statistics.data.gov.uk/def/administrative-geography/>
'''

q='''
SELECT ?x ?name ?district
WHERE {
    #?x rdfs:label "Selsey" .

    ?x a <http://environment.data.gov.uk/def/bathing-water/BathingWater> .
    ?x rdfs:label ?name .
    ?x admingeo:district ?district .
}
'''

printRunQuery(endpoint_envAgency,prefix,q,3)

#endpoint_os='http://data.ordnancesurvey.co.uk/datasets/os-linked-data/apis/sparql'
endpoint_os='http://data.ordnancesurvey.co.uk/datasets/boundary-line/apis/sparql'

prefix='''
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX admingeo: <http://statistics.data.gov.uk/def/administrative-geography/>
PREFIX ossr: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
'''

q='''
SELECT ?district ?districtname
WHERE {
    ?district ossr:within <http://data.ordnancesurvey.co.uk/id/7000000000041421> .
    ?district rdfs:label ?districtname .
}
'''

df=dfResults(endpoint_os,prefix,q)
df[:5]

#What's the code for the Isle of Wight?
for i in df[df['districtname']=='Isle of Wight']['district']: print(i)

#This suggests we could have asked a more direct question of the Ordnance Survey
prefix='''
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX admingeo: <http://statistics.data.gov.uk/def/administrative-geography/>
PREFIX ossr: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
PREFIX osadmingeo: <http://data.ordnancesurvey.co.uk/ontology/admingeo/>
'''

q='''
SELECT ?district ?districtname ?type
WHERE {
    ?district rdfs:label "Isle of Wight" ;
    rdf:type osadmingeo:UnitaryAuthority .
}
'''

printRunQuery(endpoint_os,prefix,q)

#7000000000002625
q='''
SELECT ?district
WHERE {
    ?district rdfs:label "East Sussex" .
}
'''

printRunQuery(endpoint_os,prefix,q)

q='''
SELECT ?x ?name ?districtname 
WHERE {

    ?x <http://statistics.data.gov.uk/def/administrative-geography/district> <http://data.ordnancesurvey.co.uk/id/7000000000002625> .

    ?x a <http://environment.data.gov.uk/def/bathing-water/BathingWater> .
    ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
    ?x <http://statistics.data.gov.uk/def/administrative-geography/district> ?district .
    
    ?district <http://www.w3.org/2000/01/rdf-schema#label> ?districtname .
}
ORDER BY ?districtname
'''

runQuery(endpoint_envAgency,prefix,q)

q='''
SELECT ?x ?name ?districtname 
WHERE {

    ?district <http://www.w3.org/2000/01/rdf-schema#label> 'Isle of Wight' .
    
    ?x a <http://environment.data.gov.uk/def/bathing-water/BathingWater> .
    ?x <http://www.w3.org/2000/01/rdf-schema#label> ?name .
    ?x <http://statistics.data.gov.uk/def/administrative-geography/district> ?district .
    ?district <http://www.w3.org/2000/01/rdf-schema#label> ?districtname.   
}
ORDER BY ?districtname
'''

printRunQuery(endpoint_envAgency,prefix,q,3)

q='''
SELECT ?location ?districtname ?name ?sedimentname ?lat ?long
WHERE {

    SERVICE <http://data.ordnancesurvey.co.uk/datasets/boundary-line/apis/sparql> { 

        ?area rdfs:label "East Sussex".
    
        ?district ossr:within ?area.
        ?district rdfs:label ?districtname.
    }
    
    ?location a <http://environment.data.gov.uk/def/bathing-water/BathingWater> .
    ?location <http://environment.data.gov.uk/def/bathing-water/sedimentTypesPresent> ?sediment .
    ?location <http://statistics.data.gov.uk/def/administrative-geography/district> ?district .
    ?location rdfs:label ?name.
    ?sediment rdfs:label ?sedimentname.
    
    ?location <http://location.data.gov.uk/def/ef/SamplingPoint/samplingPoint> ?samplingpoint.
    ?samplingpoint <http://www.w3.org/2003/01/geo/wgs84_pos#lat> ?lat.
    ?samplingpoint <http://www.w3.org/2003/01/geo/wgs84_pos#long> ?long.

     
    FILTER(LANG(?sedimentname) = "" || LANGMATCHES(LANG(?sedimentname), "en"))
    
}
ORDER BY ?districtname
'''

printRunQuery(endpoint_envAgency,prefix,q,5)

#The master version of folium currently has an issue running on Python3
#!pip3 install git+https://github.com/tbicr/folium.git@fixed#folium
import folium
folium.initialize_notebook()

df=dfResults(endpoint_envAgency,prefix,q)
df[:3]

#Check that the lat and long values are numbers
df.dtypes

#Cast the lat and long values to floats
df['lat']=df['lat'].astype(float)
df['long']=df['long'].astype(float)

#Find their mean values to centre the map
latMean=df['lat'].mean()
longMean=df['long'].mean()

#Create the map with an appropriate zoom level, and centre it
bathingwater = folium.Map(location=[latMean, longMean], zoom_start=10)

#Iterate through the dataframe, adding each sample point as a marker on the map
for ix,row in df[['name','lat','long','sedimentname']].iterrows():
    bathingwater.simple_marker( location=[row['lat'],row['long']], popup=row['name'] )

#Render the map
bathingwater

q='''
SELECT ?districtname
WHERE {

    ?iw rdfs:label "Isle of Wight" ;
        rdf:type osadmingeo:UnitaryAuthority .

    ?district ossr:within ?iw .
    ?district rdfs:label ?districtname.
}
'''

printRunQuery(endpoint_os,prefix,q,5)

#Open Data Communities
endpoint_odc='http://opendatacommunities.org/sparql'

q='''
SELECT ?councilwebsite ?imdrank ?authority ?authorityname 
WHERE {

    ?iw rdfs:label "Isle of Wight" ;
        rdf:type osadmingeo:UnitaryAuthority .
    ?s <http://purl.org/linked-data/sdmx/2009/dimension#refArea> ?iw .
    ?s <http://opendatacommunities.org/def/IMD#IMD-rank> ?imdrank . 


    ?authority <http://opendatacommunities.org/def/local-government/governs> ?iw .
    ?authority <http://xmlns.com/foaf/0.1/page> ?councilwebsite .
    ?authority rdfs:label ?authorityname.
}
'''

printRunQuery(endpoint_odc,prefix,q,5)

q='''
SELECT ?districtname ?councilwebsite ?imdrank ?authority ?authorityname 
WHERE {

    ?iw rdfs:label "Isle of Wight" ;
        rdf:type osadmingeo:UnitaryAuthority .

    ?district ossr:within ?iw .
    ?district rdfs:label ?districtname.
    
    SERVICE <http://opendatacommunities.org/sparql> {

        ?s <http://purl.org/linked-data/sdmx/2009/dimension#refArea> ?iw .
        ?s <http://opendatacommunities.org/def/IMD#IMD-rank> ?imdrank . 


        ?authority <http://opendatacommunities.org/def/local-government/governs> ?iw .
        ?authority <http://xmlns.com/foaf/0.1/page> ?councilwebsite .
        ?authority rdfs:label ?authorityname.
    
    }
}
'''

printRunQuery(endpoint_os,prefix,q,5)

q='''
SELECT ?districtname ?onsdist 
WHERE {

    ?iw rdfs:label "Isle of Wight" ;
        rdf:type osadmingeo:UnitaryAuthority .

    ?district ossr:within ?iw .
    ?district rdfs:label ?districtname.
    ?district <http://www.w3.org/2002/07/owl#sameAs> ?onsdist .
}
'''
printRunQuery(endpoint_os,prefix,q,5)

endpoint_ons='http://statistics.data.gov.uk/sparql'

q='''
SELECT ?districtname ?x ?y
WHERE {
    
    <http://statistics.data.gov.uk/id/statistical-geography/E04001302> ?x ?y ;
        rdfs:label ?districtname
}
'''

runQuery(endpoint_ons,prefix,q)

q='''
SELECT ?districtname ?councilwebsite ?imdrank ?authority ?authorityname ?changeorder ?onsdist ?onscode
WHERE {

    ?iw rdfs:label "Isle of Wight" ;
        rdf:type osadmingeo:UnitaryAuthority .

    ?district ossr:within ?iw .
    ?district rdfs:label ?districtname.
    
    SERVICE <http://opendatacommunities.org/sparql> {

        ?s <http://purl.org/linked-data/sdmx/2009/dimension#refArea> ?iw .
        ?s <http://opendatacommunities.org/def/IMD#IMD-rank> ?imdrank . 


        ?authority <http://opendatacommunities.org/def/local-government/governs> ?iw .
        ?authority <http://xmlns.com/foaf/0.1/page> ?councilwebsite .
        ?authority rdfs:label ?authorityname.
    
    }
    
    ?district <http://www.w3.org/2002/07/owl#sameAs> ?onsdist

    SERVICE <http://statistics.data.gov.uk/sparql> {
        ?onsdist <http://statistics.data.gov.uk/def/boundary-change/originatingChangeOrder> ?changeorder .
        ?onsdist <http://statistics.data.gov.uk/def/boundary-change/operativedate> ?opdate ;
            <http://www.w3.org/2004/02/skos/core#notation> ?onscode.
        FILTER (isURI(?changeorder))
    }
 
}
'''

printRunQuery(endpoint_os,prefix,q,5)