Notebook

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)
    
!cat addpointFromLatLon.sql

BEGIN;
SELECT AddGeometryColumn('foodratings', 'Geometry', 4326, 'POINT', 2);
UPDATE foodratings SET Geometry = GeomFromText('POINT('||"Longitude"||' '||"Latitude"||')',4326);
COMMIT;

In [32]:

! spatialite adminboundaries.db ".read addpointFromLatLon.sql utf-8"

SpatiaLite version ..: 4.3.0a	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualXL'		[direct XLS access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualElementary'	[ElemGeoms metahandler]
	- 'VirtualXPath'	[XML Path Language - XPath]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'VirtualGPKG'	[OGC GeoPackage interoperability]
	- 'VirtualBBox'		[BoundingBox tables]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 5.1.0, June 1st, 2018
GEOS version ........: 3.6.2-CAPI-1.10.2 4d2925d6
TARGET CPU ..........: x86_64-apple-darwin17.3.0
1

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)
!cat osgbfromlatlon.sql

BEGIN;
--ALTER TABLE foodratings ADD COLUMN osgb geometry(Geometry, 27700);
SELECT AddGeometryColumn('foodratings', 'osgb', 27700, 'POINT', 2);
UPDATE foodratings SET osgb = ST_Transform(Geometry, 27700);
COMMIT;

In [68]:

! spatialite adminboundaries.db ".read osgbfromlatlon.sql utf-8"

SpatiaLite version ..: 4.3.0a	Supported Extensions:
	- 'VirtualShape'	[direct Shapefile access]
	- 'VirtualDbf'		[direct DBF access]
	- 'VirtualXL'		[direct XLS access]
	- 'VirtualText'		[direct CSV/TXT access]
	- 'VirtualNetwork'	[Dijkstra shortest path]
	- 'RTree'		[Spatial Index - R*Tree]
	- 'MbrCache'		[Spatial Index - MBR cache]
	- 'VirtualSpatialIndex'	[R*Tree metahandler]
	- 'VirtualElementary'	[ElemGeoms metahandler]
	- 'VirtualXPath'	[XML Path Language - XPath]
	- 'VirtualFDO'		[FDO-OGR interoperability]
	- 'VirtualGPKG'	[OGC GeoPackage interoperability]
	- 'VirtualBBox'		[BoundingBox tables]
	- 'SpatiaLite'		[Spatial SQL - OGC]
PROJ.4 version ......: Rel. 5.1.0, June 1st, 2018
GEOS version ........: 3.6.2-CAPI-1.10.2 4d2925d6
TARGET CPU ..........: x86_64-apple-darwin17.3.0
1

In [48]:

! 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

Out[75]:

{'database': 'adminboundaries',
 'rows': [['Amazon World',
   'Restaurant/Cafe/Canteen',
   '{"type":"Point","coordinates":[-1.21882903575897,50.6552581787109]}'],
  ['Arreton Community Centre',
   'Other catering premises',
   '{"type":"Point","coordinates":[-1.24153304100037,50.67551422119139]}'],
  ['Arreton Manor Guest House',
   'Hotel/bed & breakfast/guest house',
   '{"type":"Point","coordinates":[-1.24636101722717,50.6780624389648]}'],
  ['Arreton Post Office',
   'Retailers - other',
   '{"type":"Point","coordinates":[-1.23434901237488,50.6716728210449]}'],
  ['Blackwater Mill Residential Home',
   'Hospitals/Childcare/Caring Premises',
   '{"type":"Point","coordinates":[-1.285486,50.673022]}'],
  ['Caterlink - Arreton St Georges CofE Primary School',
   'School/college/university',
   '{"type":"Point","coordinates":[-1.24153304100037,50.67551422119139]}'],
  ['Egg Packing Station',
   'Manufacturers/packers',
   '{"type":"Point","coordinates":[-1.23272597789764,50.65320968627929]}'],
  ["Farmer Jack's Farm Shop",
   'Retailers - other',
   '{"type":"Point","coordinates":[-1.246361,50.67806399999999]}'],
  ['Naturezones Wildlife Education Trust',
   'Restaurant/Cafe/Canteen',
   '{"type":"Point","coordinates":[-1.28188856496417,50.66936417171749]}'],
  ['NCCG Trading Ltd',
   'Restaurant/Cafe/Canteen',
   '{"type":"Point","coordinates":[-1.28424096107483,50.67769241333009]}'],
  ['Newstone Farm Shop',
   'Retailers - other',
   '{"type":"Point","coordinates":[-1.28206,50.673136]}'],
  ["Pink's",
   'Distributors/Transporters',
   '{"type":"Point","coordinates":[-1.24723601341248,50.66578674316409]}'],
  ['Redway Farm Bed & Breakfast',
   'Hotel/bed & breakfast/guest house',
   '{"type":"Point","coordinates":[-1.24533402919769,50.66086196899409]}'],
  ['St Georges Pre-School & Nursery',
   'Hospitals/Childcare/Caring Premises',
   '{"type":"Point","coordinates":[-1.24153304100037,50.67551422119139]}'],
  ['Sweet Memories',
   'Retailers - other',
   '{"type":"Point","coordinates":[-1.24636101722717,50.6780624389648]}'],
  ["The Dairyman's Daughter",
   'Pub/bar/nightclub',
   '{"type":"Point","coordinates":[-1.24636101722717,50.6780624389648]}'],
  ['The Fighting Cocks',
   'Pub/bar/nightclub',
   '{"type":"Point","coordinates":[-1.22725796699524,50.6580009460449]}'],
  ['The Garden Cafe',
   'Restaurant/Cafe/Canteen',
   '{"type":"Point","coordinates":[-1.218829,50.65525699999999]}'],
  ['The White Lion',
   'Pub/bar/nightclub',
   '{"type":"Point","coordinates":[-1.24636101722717,50.6780624389648]}'],
  ['Wight Salads Group Ltd',
   'Farmers/growers',
   '{"type":"Point","coordinates":[-1.22725796699524,50.6580009460449]}']],
 'truncated': False,
 'columns': ['BusinessName', 'BusinessType', 'AsGeoJSON(Geometry)'],
 'query': {'sql': "\nSELECT BusinessName, BusinessType, AsGeoJSON(Geometry) FROM \n    (SELECT FHRSID, p.wgs84, f.Geometry, f.BusinessName, f.BusinessType FROM \n         (SELECT FHRSID, Geometry, X(osgb) x, Y(osgb) y, BusinessName, BusinessType\n              FROM foodratings WHERE Geometry NOT NULL) f ,\n         parish_region p, idx_parish_region_geometry i\n         WHERE p.NAME LIKE '%Arreton%' AND p.pk_uid= i.pkid AND \n             i.xmin < f.x and i.xmax > f.x and i.ymin < f.y and i.ymax > f.y) fr\n   WHERE within(Geometry, wgs84) ;\n",
  'params': {}},
 'query_ms': 10.701179504394531}

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

Out[110]:

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

Out[124]:

In [ ]: