def _create_cluster(name, cluster):
def _get_details_from_point(point, properties):
# get details of a point
conn = psycopg2.connect("dbname={name} user={user} port={port} password={password}".format(**_config(section='db')))
cur = conn.cursor()
cur.execute("SELECT positionable_id, positionable_type, year FROM position_infos WHERE location = ST_GeomFromText('POINT(%s %s)')", point)
entry = cur.fetchone()
properties['id'] = entry[0]
properties['type'] = entry[1]
if entry[2] == None:
properties['year'] = 'unknown'
else:
properties['year'] = entry[2]
cur.close()
conn.close()
filters = ('', 'Plant')
for filter in filters:
filter_str = ''
if len(filter) > 0:
filter_str = "WHERE positionable_type = '{0}'".format(filter)
file_name = "{0}{1}cluster{2}.json".format(name, filter, cluster)
print file_name
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={1} user={2} password={3} port={4}" -sql "SELECT count(*), ST_Centroid(ST_Collect(location::geometry)) AS geom FROM ( SELECT kmeans(ARRAY[ST_X(location::geometry), ST_Y(location::geometry)], {5}) OVER (), location FROM position_infos {6}) AS ksub GROUP BY kmeans ORDER BY kmeans"'.format(
file_name,
_config('name', section='db'),
_config('user', section='db'),
_config('password', section='db'),
_config('port', section='db'),
cluster,
filter_str)
local(cmd)
# check if any clusters have a count of 1
has_changed = None
features = None
with open(file_name, 'rw') as f:
features = json.load(f)['features']
for feature in features:
if feature['properties']['count'] == 1:
# cluster has a count of 1, get more details
has_changed = True
_get_details_from_point(
feature['geometry']['coordinates'],
feature['properties'])
if has_changed:
with open(file_name, 'w') as f:
out = {
'type': 'FeatureCollection',
'features': features
}
json.dump(out, f)
def _create_cluster(name, cluster):
def _get_details_from_point(point, properties):
# get details of a point
conn = psycopg2.connect(
"dbname={name} user={user} port={port} password={password}".format(
**_config(section='db')))
cur = conn.cursor()
cur.execute(
"SELECT positionable_id, positionable_type, year FROM position_infos WHERE location = ST_GeomFromText('POINT(%s %s)')",
point)
entry = cur.fetchone()
properties['id'] = entry[0]
properties['type'] = entry[1]
if entry[2] == None:
properties['year'] = 'unknown'
else:
properties['year'] = entry[2]
cur.close()
conn.close()
filters = ('', 'Plant')
for filter in filters:
filter_str = ''
if len(filter) > 0:
filter_str = "WHERE positionable_type = '{0}'".format(filter)
file_name = "{0}{1}cluster{2}.json".format(name, filter, cluster)
print file_name
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={1} user={2} password={3} port={4}" -sql "SELECT count(*), ST_Centroid(ST_Collect(location::geometry)) AS geom FROM ( SELECT kmeans(ARRAY[ST_X(location::geometry), ST_Y(location::geometry)], {5}) OVER (), location FROM position_infos {6}) AS ksub GROUP BY kmeans ORDER BY kmeans"'.format(
file_name, _config('name', section='db'),
_config('user', section='db'), _config('password', section='db'),
_config('port', section='db'), cluster, filter_str)
local(cmd)
# check if any clusters have a count of 1
has_changed = None
features = None
with open(file_name, 'rw') as f:
features = json.load(f)['features']
for feature in features:
if feature['properties']['count'] == 1:
# cluster has a count of 1, get more details
has_changed = True
_get_details_from_point(feature['geometry']['coordinates'],
feature['properties'])
if has_changed:
with open(file_name, 'w') as f:
out = {'type': 'FeatureCollection', 'features': features}
json.dump(out, f)
def convert_db_json():
"""
Convert database to geojson
"""
file_name = 'out.json'
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" -sql "select id, location from position_infos"'.format(
file_name, **_config(section='db'))
local(cmd)
def create_clusters():
"""
Create clusters for groups of zoom levels. The number of clusters for each zoom level needs to be added to the config.ini
e.g.
clusters = {"0-6": 1, "7-9": 5, "9-12": 10, "12-18": 0}
"""
data_dir = _path_join(_get_source()[2], 'www', 'data', '')
layers = ast.literal_eval(_config('clusters', section='app'))
clusters = set(layers.values())
for cluster in clusters:
_create_cluster('{0}'.format(data_dir), cluster)
# create gardens geojson (non clustered)
file_name = os.path.join(data_dir, 'Gardens.json')
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" -sql "SELECT positionable_id AS id, positionable_type AS type, location FROM position_infos WHERE positionable_type = \'Garden\'"'.format(
file_name, **_config(section='db'))
local(cmd)
def convert_db_json():
"""
Convert database to geojson
"""
file_name = 'out.json'
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" -sql "select id, location from position_infos"'.format(
file_name,
**_config(section='db')
)
local(cmd)
def convert_db_sqlite():
"""
Convert database to sqlite database
"""
file_name = os.path.join(_get_source()[1], 'platforms', 'android',
'assets', 'db', 'botanitours.sqlite')
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -preserve_fid -f SQLite -dsco SPATIALITE=yes {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" gardens plant_common_names plant_images plants position_infos'.format(
file_name, **_config(section='db'))
local(cmd)
update_app()
def create_clusters():
"""
Create clusters for groups of zoom levels. The number of clusters for each zoom level needs to be added to the config.ini
e.g.
clusters = {"0-6": 1, "7-9": 5, "9-12": 10, "12-18": 0}
"""
data_dir = _path_join(_get_source()[2], 'www', 'data', '')
layers = ast.literal_eval(_config('clusters', section='app'))
clusters = set(layers.values())
for cluster in clusters:
_create_cluster('{0}'.format(data_dir), cluster)
# create gardens geojson (non clustered)
file_name = os.path.join(data_dir, 'Gardens.json')
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -f GeoJSON {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" -sql "SELECT positionable_id AS id, positionable_type AS type, location FROM position_infos WHERE positionable_type = \'Garden\'"'.format(
file_name,
**_config(section='db')
)
local(cmd)
def _get_details_from_point(point, properties):
# get details of a point
conn = psycopg2.connect(
"dbname={name} user={user} port={port} password={password}".format(
**_config(section='db')))
cur = conn.cursor()
cur.execute(
"SELECT positionable_id, positionable_type, year FROM position_infos WHERE location = ST_GeomFromText('POINT(%s %s)')",
point)
entry = cur.fetchone()
properties['id'] = entry[0]
properties['type'] = entry[1]
if entry[2] == None:
properties['year'] = 'unknown'
else:
properties['year'] = entry[2]
cur.close()
conn.close()
def convert_db_sqlite():
"""
Convert database to sqlite database
"""
file_name = os.path.join(
_get_source()[1],
'platforms',
'android',
'assets',
'db',
'botanitours.sqlite')
if os.path.exists(file_name):
local('rm {0}'.format(file_name))
cmd = 'ogr2ogr -preserve_fid -f SQLite -dsco SPATIALITE=yes {0} "PG:host=localhost dbname={name} user={user} password={password} port={port}" gardens plant_common_names plant_images plants position_infos'.format(
file_name,
**_config(section='db')
)
local(cmd)
update_app()
def _get_details_from_point(point, properties):
# get details of a point
conn = psycopg2.connect("dbname={name} user={user} port={port} password={password}".format(**_config(section='db')))
cur = conn.cursor()
cur.execute("SELECT positionable_id, positionable_type, year FROM position_infos WHERE location = ST_GeomFromText('POINT(%s %s)')", point)
entry = cur.fetchone()
properties['id'] = entry[0]
properties['type'] = entry[1]
if entry[2] == None:
properties['year'] = 'unknown'
else:
properties['year'] = entry[2]
cur.close()
conn.close()