#!/usr/bin/env python
# coding: utf-8

# In[50]:


import oifsa, sqlite3


# In[51]:


dbname='adminboundaries.db'
conn = sqlite3.connect(dbname)


# In[18]:


#Handy utility function
def droptable(conn,table):
    cursor = conn.cursor()
    cursor.execute('''DROP TABLE IF EXISTS {}'''.format(table))
    conn.commit()


# In[54]:


table = 'foodratings'
#droptable(conn,table)


# In[30]:


#wight /via http://ratings.food.gov.uk/open-data/en-GB
url='http://ratings.food.gov.uk/OpenDataFiles/FHRS867en-GB.xml'

oifsa.save_fsa_data(url, conn, table, delay=1)


# In[31]:


addPointFromLatLon ='''
BEGIN;
SELECT AddGeometryColumn('{table}', 'Geometry', 4326, 'POINT', 2);
UPDATE {table} SET Geometry = GeomFromText('POINT('||"Longitude"||' '||"Latitude"||')',4326);
COMMIT;
'''.format(table=table)

with open("addpointFromLatLon.sql", "w") as sqlfile:
    sqlfile.write(addPointFromLatLon)
    
get_ipython().system('cat addpointFromLatLon.sql')


# In[32]:


get_ipython().system(' spatialite adminboundaries.db ".read addpointFromLatLon.sql utf-8"')


# In[67]:


osgbfromlatlon='''
BEGIN;
SELECT AddGeometryColumn('{table}', 'osgb', 27700, 'POINT', 2);
UPDATE {table} SET osgb = ST_Transform(Geometry, 27700);
COMMIT;
'''.format(table=table)

with open("osgbfromlatlon.sql", "w") as sqlfile:
    sqlfile.write(osgbfromlatlon)
get_ipython().system('cat osgbfromlatlon.sql')


# In[68]:


get_ipython().system(' spatialite adminboundaries.db ".read osgbfromlatlon.sql utf-8"')


# In[48]:


get_ipython().system(' spatialite adminboundaries.db "SELECT CreateSpatialIndex(\'parish_region\', \'Geometry\');"')


# In[ ]:


import requests, json

def get_geojson_q( q=None,json_url = "http://localhost:8001/adminboundaries.json"):      
    params = {'sql': q}
    
    r = requests.get(json_url, params=params)
    results = r.json()

    tmp = {r[0]:[r[1:-1]+[json.loads(r[-1])]] for r in results['rows']}
    return tmp

q='SELECT FHRSID, BusinessName, BusinessType, BusinessTypeID, RatingValue, AsGeoJSON(Geometry) FROM {} WHERE Geometry IS NOT NULL;'.format(table)

gj=get_geojson_q(q, "http://localhost:8003/adminboundaries.json")

select BusinessName, AsGeoJSON(foodratings.Geometry) from foodratings, parish_region WHERE parish_region.NAME LIKE '%Arreton%' AND  foodratings.Geometry NOT NULL  AND within(foodratings.Geometry, parish_region.wgs84) ;#do a quick text against the index
SELECT * FROM 
    (SELECT FHRSID, p.wgs84, f.Geometry FROM 
         (SELECT FHRSID, Geometry, X(osgb) x, Y(osgb) y 
              FROM foodratings WHERE Geometry NOT NULL) f ,
         parish_region p, idx_parish_region_geometry i
         WHERE p.NAME LIKE '%Arreton%' AND p.pk_uid= i.pkid AND 
             i.xmin < f.x and i.xmax > f.x and i.ymin < f.y and i.ymax > f.y) fr ;
# In[ ]:


#now use that inside a query to find within the actual shape


# In[73]:


q='''
SELECT BusinessName, BusinessType, AsGeoJSON(Geometry) FROM 
    (SELECT FHRSID, p.wgs84, f.Geometry, f.BusinessName, f.BusinessType FROM 
         (SELECT FHRSID, Geometry, X(osgb) x, Y(osgb) y, BusinessName, BusinessType
              FROM foodratings WHERE Geometry NOT NULL) f ,
         parish_region p, idx_parish_region_geometry i
         WHERE p.NAME LIKE '%Arreton%' AND p.pk_uid= i.pkid AND 
             i.xmin < f.x and i.xmax > f.x and i.ymin < f.y and i.ymax > f.y) fr
   WHERE within(Geometry, wgs84) ;
'''


# In[74]:


import requests

params = {'sql': q}
json_url = "http://localhost:8003/adminboundaries.json"

r = requests.get(json_url, params=params)
results = r.json()


# In[75]:


results


# In[110]:


import json
import folium
import html
from folium.plugins import MarkerCluster

center=json.loads(results['rows'][0][2])['coordinates']
m=folium.Map(location=center[::-1], zoom_start=12)

mc = MarkerCluster()

for result in results['rows']:
    name=result[0]
    typ=result[1]
    loc=json.loads(result[2])['coordinates'][::-1]
    #folium.Marker(loc, popup='{}<br/>{}'.format(html.escape(name),html.escape(typ) )).add_to(m)
    mc.add_child(folium.Marker(location=loc,
                               popup='{}<br/>{}'.format(html.escape(name),html.escape(typ))))

m.add_child(mc)
m


# In[99]:


def get_geojson(region_type, region, county=None, code=None,
                col='wgs84',json_url = "http://localhost:8001/adminboundaries.json"):
    
    q='SELECT AsGeoJSON({col}) FROM {table} WHERE name LIKE "%{region}%"'.format(col=col,table=region_type,region=region)
    
    if county is not None: q='{} AND FILE_NAME LIKE "%{}%"'.format(q, county)
    if code is not None: q='{} AND CODE LIKE "%{}%"'.format(q, code)
        
    params = {'sql': q}
    #for example: SELECT AsGeoJSON(wgs84) FROM district_borough_unitary_region WHERE name LIKE "%wight%" 
    r = requests.get(json_url, params=params)
    results = r.json()
    return json.loads(results['rows'][0][0])


# In[124]:


m=folium.Map(location=center[::-1], zoom_start=12)

import numpy as np

for result in results['rows']:
    name=result[0]
    typ=result[1]
    loc=json.loads(result[2])['coordinates'][::-1]
    loc[0]=loc[0]+np.random.uniform(0.001, 10**(-20))-0.0005
    loc[1]=loc[1]+np.random.uniform(0.001, 10**(-20))-0.0005
    folium.Marker(loc, popup='{}<br/>{}'.format(html.escape(name),html.escape(typ) )).add_to(m)

gj=get_geojson('parish_region', 'arreton', json_url = "http://localhost:8003/adminboundaries.json")
folium.GeoJson(gj, name='geojson').add_to(m)
m


# In[ ]: