def get(params):
if not 'order' in params:
params['order'] = ''
if 'iso' in params:
params['and'] = "AND iso = upper('%s')" % params['iso']
params['join'] = 'RIGHT'
query = GET_COUNTRY.format(**params)
else: # List all countries:
params['and'] = ''
params['join'] = 'LEFT'
query = GET.format(**params)
import logging
logging.info(query)
result = cdb.execute(query, params)
if result:
countries = json.loads(result.content)['rows']
if countries and 'iso' in params:
iso = params.get('iso').upper()
query = RELATED_STORIES.format(iso=iso)
story_result = cdb.execute(query)
story = json.loads(story_result.content)['rows']
if story:
story = story[0]
story['media'] = json.loads(story['media'] or '""')
countries[0]['story'] = story
return dict(countries=countries)
def _cdb(params):
query = SUM.format(**params)
result = cdb.execute(query, {})
if result:
result = json.loads(result.content)['rows'][0]
return dict(iso=params.get('iso'), loss=result['loss'],
gain=result['gain'])
def alerts(params):
query = ALERTS_ALL_COUNT.format(**params)
alerts_count = json.loads(
cdb.execute(query, params))['rows'][0]['alerts_count']
if 'iso' in params:
query = ALERTS_COUNTRY.format(**params)
result = cdb.execute(query, params)
if result:
result = json.loads(result)['rows']
elif 'geom' in params:
query = ALERTS_ALL_COUNTRIES.format(**params)
result = cdb.execute(query, params)
if result:
result = json.loads(result)['rows']
else:
raise AssertionError('geom or iso parameter required')
return dict(total_count=alerts_count, countries=result)
def _index(args):
if not 'order' in args:
args['order'] = ''
if not 'interval' in args:
args['interval'] = '12 Months'
query = CountrySql.INDEX.format(**args)
rows = _handler(cdb.execute(query))
return dict(countries=rows)
def alerts(params):
query = ALERTS_ALL_COUNT.format(**params)
alerts_count = json.loads(cdb.execute(query,
params))['rows'][0]['alerts_count']
if 'iso' in params:
query = ALERTS_COUNTRY.format(**params)
result = cdb.execute(query, params)
if result:
result = json.loads(result)['rows']
elif 'geom' in params:
query = ALERTS_ALL_COUNTRIES.format(**params)
result = cdb.execute(query, params)
if result:
result = json.loads(result)['rows']
else:
raise AssertionError('geom or iso parameter required')
return dict(total_count=alerts_count, countries=result)
def analyze(params):
if 'geom' in params:
query = GEOJSON_SQL.format(**params)
elif 'iso' in params:
query = ISO_SQL.format(**params)
else:
raise ValueError('FORMA analysis expects geom or iso parameter')
return cdb.execute(query)
def _body(self, alert, n, e, s):
body = """You have subscribed to forest change alerts through Global Forest Watch. This message reports new forest change alerts for one of your areas of interest (a country or self-drawn polygon).
A total of {value} {name} {unit} were detected within your area of interest in the past {interval}. Explore the details of this dataset on Global Forest Watch <a href="http://globalforestwatch.com/sources#forest_change">here</a>.
Your area of interest is {aoi}, as shown in the map below:
{aoi-vis}
You can unsubscribe or manage your subscriptions by emailing: [email protected]
You will receive a separate e-mail for each distinct polygon, country, or shape on the GFW map. You will also receive a separate e-mail for each dataset for which you have requested alerts (FORMA alerts, Imazon SAD Alerts, and NASA QUICC alerts.)
Please note that this information is subject to the Global Forest Watch <a href='http://globalforestwatch.com/terms'>Terms of Service</a>.
"""
html = """You have subscribed to forest change alerts through Global Forest Watch. This message reports new forest change alerts for one of your areas of interest (a country or self-drawn polygon).
<p>
A total of {value} {name} {unit} were detected within your area of interest in the past {interval}. Explore the details of this dataset on Global Forest Watch <a href="http://globalforestwatch.com/sources#forest_change">here</a>.
<p>
Your area of interest is {aoi}, as shown in the map below:
<p>
{aoi-vis}
<p>
You can unsubscribe or manage your subscriptions by emailing: [email protected]
<p>
You will receive a separate e-mail for each distinct polygon, country, or shape on the GFW map. You will also receive a separate e-mail for each dataset for which you have requested alerts (FORMA alerts, Imazon SAD Alerts, and NASA QUICC alerts.)
<p>
Please note that this information is subject to the Global Forest Watch <a href='http://globalforestwatch.com/terms'>Terms of Service</a>.
"""
logging.info('HEY')
alert['interval'] = 'month'
if not alert['value']:
alert['value'] = 0
if 'geom' in s:
alert['aoi'] = 'a user drawn polygon'
coords = json.loads(s['geom'])['coordinates'][0][0]
coords = [[float(j) for j in i] for i in coords]
poly = polyline.encode_coords(coords)
url = u"http://maps.googleapis.com/maps/api/staticmap?sensor=false&size=600x400&path=fillcolor:0xAA000033|color:0xFFFFFF00|enc:%s" % poly
alert['aoi-vis'] = '<img src="%s">' % url
else:
alert['aoi'] = 'a country (%s)' % s['iso']
sql = "SELECT ST_AsGeoJSON(ST_ConvexHull(the_geom)) FROM world_countries where iso3 = upper('%s')" % s['iso']
logging.info('SQL %s' % sql)
response = cdb.execute(sql)
if response.status_code == 200:
result = json.loads(response.content)
coords = json.loads(result['rows'][0]['st_asgeojson'])['coordinates']
poly = polyline.encode_coords(coords[0])
url = "http://maps.googleapis.com/maps/api/staticmap?sensor=false&size=600x400&path=fillcolor:0xAA000033|color:0xFFFFFF00|enc:%s" % poly
alert['aoi-vis'] = '<img src="%s">' % url
else:
raise Exception('CartoDB Failed (status=%s, content=%s, \
q=%s)' % (response.status_code,
response.content,
sql))
return body.format(**alert), html.format(**alert)
def subsription(params):
if 'geom' in params:
params['geom'] = json.dumps(params.get('geom'))
query = GEOJSON_SUB_SQL.format(**params)
elif 'iso' in params:
query = ISO_SUB_SQL.format(**params)
else:
raise ValueError('FORMA subscription expects geom or iso param')
return cdb.execute(query)
def get_country_story(params):
params['table'] = TABLE
result = cdb.execute(COUTRY_STORY.format(**params), auth=True)
story_id = json.loads(result.content)
if story_id['total_rows'] == 1:
params['id'] = story_id['rows'][0]['id']
return get_story(params)
else:
return
def get_story(params):
params['table'] = TABLE
result = cdb.execute(GET.format(**params), auth=True)
if result.status_code != 200:
raise Exception('CartoDB error getting story (%s)' % result.content)
if result:
data = json.loads(result.content)
if 'total_rows' in data and data['total_rows'] == 1:
story = data['rows'][0]
return _prep_story(story)
def analyze(params):
if 'geom' in params:
query = ANALYSIS_GEOM.format(**params)
elif 'iso' in params:
iso = params.get('iso').upper()
if iso == 'BRA':
query = ANALYSIS.format(**params)
else:
query = "select * from imazon_clean2 where orig_oid = '-999'"
return cdb.execute(query)
def analyze(params):
params["select_geom"] = ""
if "iso" in params:
params["iso"] = params["iso"].upper()
geom = params.get("geom")
if geom:
query = ANALYSIS_GEOM.format(**params)
else:
query = ANALYSIS.format(**params)
return cdb.execute(query)
def analyze(params):
params['select_geom'] = ''
if 'iso' in params:
params['iso'] = params['iso'].upper()
geom = params.get('geom')
if geom:
query = ANALYSIS_GEOM.format(**params)
else:
query = ANALYSIS.format(**params)
return cdb.execute(query)
def get(params):
params['table'] = TABLE
result = cdb.execute(GET.format(**params), auth=True)
if result.status_code != 200:
raise Exception('CaroDB error getting story (%s)' % result.content)
if result:
data = json.loads(result.content)
if 'total_rows' in data and data['total_rows'] == 1:
story = data['rows'][0]
return _prep_story(story)
def subsription(params):
if 'geom' in params:
params['geom'] = json.dumps(params.get('geom'))
query = GEOJSON_SUB_SQL.format(**params)
elif 'iso' in params:
query = ISO_SUB_SQL.format(**params)
else:
raise ValueError('FORMA subscription expects geom or iso param')
# import logging
# logging.info(query)
return cdb.execute(query)
def get(params):
query = ALERTS_ALL_COUNT.format(**params)
alerts_count = json.loads(
cdb.execute(query, params).content)['rows'][0]['alerts_count']
if not 'order' in params:
params['order'] = ''
if 'iso' in params:
if has_alerts(params): # Has forma alerts:
params['and'] = "AND iso = upper('%s')" % params['iso']
params['join'] = 'RIGHT'
query = GET.format(**params)
else: # No forma alerts:
query = GET_NO_ALERTS.format(**params)
else: # List all countries:
params['and'] = ''
params['join'] = 'LEFT'
query = GET.format(**params)
result = cdb.execute(query, params)
if result:
countries = json.loads(result.content)['rows']
return dict(total_count=alerts_count, countries=countries)
def count_stories(params):
if params.get('iso'):
sql = COUNT_ISO
else:
sql = COUNT_GEOM
params['table'] = TABLE
result = cdb.execute(sql.format(**params), auth=True)
data = json.loads(result.content)
rows = data.get('rows')
if rows:
return rows[0].get('value') or 0
else:
return 0
def count_stories(params):
if params.get('iso'):
sql = COUNT_ISO
else:
sql = COUNT_GEOM
params['table'] = TABLE
result = cdb.execute(sql.format(**params), auth=True)
data = json.loads(result.content)
rows = data.get('rows')
if rows:
return rows[0].get('value') or 0
else:
return 0
def create(params):
"""Create new story with params."""
props = dict(details='', email='', name='',
title='', token='', visible='True', date='',
location='', geom='', media='[]', table=TABLE)
props.update(params)
for key in ['details', 'title', 'name', 'email', 'location']:
props[key] = props[key].encode('utf-8')
if not props.get('date'):
props['date'] = str(datetime.datetime.now())
props['geom'] = json.dumps(props['geom'])
if 'media' in props:
props['media'] = json.dumps(props['media'])
sql = INSERT.format(**props)
return cdb.execute(sql, auth=True)
def list(params):
and_where = ''
if 'geom' in params:
and_where = """AND ST_Intersects(the_geom::geography,
ST_SetSRID(ST_GeomFromGeoJSON('{geom}'),4326)::geography)"""
if 'since' in params:
and_where += """ AND date >= '{since}'::date"""
if and_where:
and_where = and_where.format(**params)
result = cdb.execute(
LIST.format(and_where=and_where, table=TABLE), auth=True)
if result:
data = json.loads(result.content)
if 'total_rows' in data and data['total_rows'] > 0:
return map(_prep_story, data['rows'])
def list_stories(params):
and_where = ''
if 'geom' in params:
and_where = """AND ST_Intersects(the_geom::geography,
ST_SetSRID(ST_GeomFromGeoJSON('{geom}'),4326)::geography)"""
if 'since' in params:
and_where += """ AND date >= '{since}'::date"""
if and_where:
and_where = and_where.format(**params)
result = cdb.execute(LIST.format(and_where=and_where, table=TABLE),
auth=True)
if result:
data = json.loads(result.content)
if 'total_rows' in data and data['total_rows'] > 0:
return map(_prep_story, data['rows'])
def analyze(params):
begin = params.get('begin')
if len(begin) == 4:
begin = '%s-01-01' % begin
params['begin'] = begin
end = params.get('end')
if len(end) == 4:
end = '%s-01-01' % end
params['end'] = end
if 'geom' in params:
query = GEOJSON_SQL.format(**params)
elif 'iso' in params:
query = ISO_SQL.format(**params)
else:
raise ValueError('FORMA analysis expects geom or iso parameter')
return cdb.execute(query)
def analyze(params):
begin = params.get('begin')
if len(begin) == 4:
begin = '%s-01-01' % begin
params['begin'] = begin
end = params.get('end')
if len(end) == 4:
end = '%s-01-01' % end
params['end'] = end
if 'geom' in params:
query = GEOJSON_SQL.format(**params)
elif 'iso' in params:
query = ISO_SQL.format(**params)
else:
raise ValueError('FORMA analysis expects geom or iso parameter')
return cdb.execute(query)
def _get_max_date(self,name):
alert_tables = {
'forma':'forma_api',
'terrai':'latin_decrease_current_points',
'imazon':'imazon_sad',
'quicc':'modis_forest_change_copy'
}
table_name = alert_tables.get(name) or name
sql = 'SELECT MAX(date) FROM %s;' % table_name
response = cdb.execute(sql)
if response.status_code == 200:
max_date = json.loads(response.content)['rows'][0]['max']
date = arrow.get(max_date)
return date
else:
return arrow.now()
def _get_max_date(self, name):
alert_tables = {
'forma': 'forma_api',
'terrai': 'latin_decrease_current_points',
'imazon': 'imazon_sad',
'quicc': 'modis_forest_change_copy'
}
table_name = alert_tables.get(name) or name
sql = 'SELECT MAX(date) FROM %s;' % table_name
response = cdb.execute(sql)
if response.status_code == 200:
max_date = json.loads(response.content)['rows'][0]['max']
date = arrow.get(max_date)
return date
else:
return arrow.now()
def execute(cls, args, sql):
try:
query, download_query = sql.process(args)
download_url = cdb.get_url(download_query, args)
if 'format' in args:
return 'redirect', download_url
else:
action, response = 'respond', cdb.execute(query)
response = cls._query_response(response, args, query)
response['download_urls'] = get_download_urls(
download_query, args)
if 'error' in response:
action = 'error'
return action, response
except Exception, e:
return 'execute() error', e
def _recentDate(self, name, nth=1):
date = None
sql = self._latestSQL(name, nth + 1)
if name == "stories":
name = "forma"
if sql:
response = cdb.execute(sql)
if response.status_code == 200:
last_date = json.loads(response.content)['rows'][nth]['date']
if last_date:
date = arrow.get(last_date).replace(days=+1)
if not date:
date = arrow.now().replace(months=-2)
return date.format("YYYY-MM-DD")
def _recentDate(self,name,nth=1):
date = None
sql = self._latestSQL(name,nth+1)
if name == "stories":
name = "forma"
if sql:
response = cdb.execute(sql)
if response.status_code == 200:
last_date = json.loads(response.content)['rows'][nth]['date']
if last_date:
date = arrow.get(last_date).replace(days=+1)
if not date:
date = arrow.now().replace(months=-2)
return date.format("YYYY-MM-DD")
def create_story(params):
"""Create new story with params."""
props = dict(details='',
email='',
name='',
title='',
token='',
visible='True',
date='',
location='',
geom='',
media='[]',
table=TABLE)
props.update(params)
for key in ['details', 'title', 'name', 'email', 'location']:
props[key] = props[key].encode('utf-8').replace("'", "''")
if not props.get('date'):
props['date'] = str(datetime.datetime.now())
props['geom'] = json.dumps(props['geom'])
if 'media' in props:
props['media'] = json.dumps(props['media'])
sql = INSERT.format(**props)
return cdb.execute(sql, auth=True)
def _getBounds(args):
query = CountrySql.BOUNDS.format(**args)
return dict(bounds=json.loads(_handler(cdb.execute(query))[0]['bounds']))
def _getTopoJson(args):
query = CountrySql.TOPO_JSON.format(**args)
rows = _handler(
cdb.execute(query, params=dict(format='topojson')))
return dict(topojson=rows)
def _getReforestation(args):
query = CountrySql.REFORESTATION.format(**args)
return dict(reforestation=_handler(cdb.execute(query)))
def _show(args):
query = CountrySql.SHOW.format(**args)
rows = _handler(cdb.execute(query))
return rows[0]
def _getForests(args):
query = CountrySql.FORESTS.format(**args)
return dict(forests=_handler(cdb.execute(query)))
def _getForestCertification(args):
query = CountrySql.FOREST_CERTIFICATION.format(**args)
return dict(forest_certification=_handler(cdb.execute(query)))
def _getLossOutsidePlantations(args):
if 'thresh' not in args:
args['thresh'] = 30
query = CountrySql.LOSS_OUTSIDE_PLANTATION.format(**args)
return dict(loss_outside_plantations=_handler(cdb.execute(query)))
def _getBurnedForests(args):
query = CountrySql.BURNED_FOREST.format(**args)
return dict(burned_forests=_handler(cdb.execute(query)))
def _getSubnatBounds(args):
query = CountrySql.SUBNAT_BOUNDS.format(**args)
rows = _handler(cdb.execute(query))
results = map(_processSubnatRow, rows)
return dict(subnat_bounds=results)
def has_alerts(params):
return json.loads(
cdb.execute(
HAS_ALERTS.format(**params)).content)['rows'][0]['count'] != 0
def _getForma(args):
query = CountrySql.FORMA.format(**args)
return dict(forma=_handler(cdb.execute(query)))
def _getTenure(args):
query = CountrySql.TENURE.format(**args)
return dict(tenure=_handler(cdb.execute(query)))