def insert_into_cartodb(sql_query):
cl = CartoDBAPIKey(var_api_key, var_cartodb_domain)
try:
# your CartoDB account:
print cl.sql(sql_query)
except CartoDBException as e:
print ("some error ocurred", e)
def create( table_name, columns, api_key=CARTODB_API_KEY, cartodb_domain=cartodb_domain):
cl = CartoDBAPIKey(api_key, cartodb_domain)
try:
resp = cl.sql("drop table {}".format(table_name))
except CartoDBException as e:
print ("unable to drop table", e)
# Do not add column 'cartodb_id' as this is created by the later call to cdb_cartodbfytable()
# Though if it were needed, it would most likely look like this -
# cartodb_id int4 default nextval('untitled_table_cartodb_id_seq1'::regclass) not null
# Columns created_at, updated_at, the_geometry and the_geom_webmercator are also created automatically if absent.
# created_at timestamptz default now() not null, updated_at timestamptz default now() not null
# the_geometry, the_geom_webmercator geometry
try:
resp = cl.sql("create table {} ({})".format(table_name, columns))
except CartoDBException as e:
print ("unable to create table", e)
print resp
try:
resp = cl.sql("select cdb_cartodbfytable('{}')".format(table_name))
except CartoDBException as e:
print ("unable to cartodbfy table", e)
return
def purgeCartoDBMpas(mpas=mpas, dryrun=False):
'''Execute Mpa remove statements using the CartoDB API via the cartodb module for
mpas in the CartoDB mpatlas table that are not found in the passed mpas queryset.
mpas = Mpa queryset [default is all non-rejected MPAs with geom boundaries]
dryrun = [False] if true, just return list of mpa_ids to be purged but don't run SQL.
Returns list of mpa.mpa_ids that were removed, empty list if none removed.
'''
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
nummpas = mpas.count()
local_ids = mpas.values_list('mpa_id', flat=True)
cartodb_idsql = '''
SELECT mpa_id FROM mpatlas ORDER BY mpa_id;
'''
try:
result = cl.sql(cartodb_idsql)
except CartoDBException as e:
error_ids.extend(step_ids)
print('CartoDB Error for getting mpa_ids', e)
cartodb_ids = [i['mpa_id'] for i in result['rows']]
missing = list(set(cartodb_ids) - set(local_ids))
missing.sort()
deletesql = '''
DELETE FROM mpatlas WHERE mpa_id IN %(missing)s;
''' % ({'missing': adaptParam(tuple(missing))})
if not dryrun:
try:
cl.sql(deletesql)
except CartoDBException as e:
error_ids.extend(step_ids)
print 'CartoDB Error deleting %s mpas:' % len(missing), e
return missing
class CartoDBConnector(object):
def __init__(self):
self.user = os.environ.get('CARTODB_EMAIL')
self.api_key = os.environ.get('CARTODB_APIKEY')
self.cartodb_domain = os.environ.get('CARTODB_DOMAIN')
self.cl = CartoDBAPIKey(self.api_key, self.cartodb_domain)
def newResponse(self, response_data):
try:
sqlValues = {
"survey" : response_data.get('survey'),
"surveyid" : response_data.get('survey_id'),
"path":response_data.get('path'),
"choose_a_road" : response_data.get('choose_a_road'),
"how_do_you_feel" : response_data.get('how_do_you_feel'),
"link_to_destinations" : response_data.get('link_to_destinations'),
"is_the_gradient_amenable_to_cycling" : response_data.get('is_the_gradient_amenable_to_cycling'),
"street_offers_priority_through_intersections" : response_data.get("street_offers_priority_through_intersections"),
"type_of_cyclist" : response_data.get('type_of_cyclist')
}
sqlStr = "INSERT INTO dynamicconnections_bmwguggenheimlab \
(survey, surveyid, type_of_cyclist, the_geom, choose_a_road, how_do_you_feel, link_to_destinations,is_the_gradient_amenable_to_cycling,street_offers_priority_through_intersections) \
values \
('%(survey)s', '%(surveyid)s', '%(type_of_cyclist)s', ST_GeomFromText('MULTILINESTRING((%(path)s))',4326), '%(choose_a_road)s', '%(how_do_you_feel)s', '%(link_to_destinations)s','%(is_the_gradient_amenable_to_cycling)s', '%(street_offers_priority_through_intersections)s')" % sqlValues
app.logger.debug('cartodb insert sql')
app.logger.debug(sqlStr)
query = self.cl.sql(sqlStr.encode('utf-8','replace'))
app.logger.debug('query results')
app.logger.debug(query)
return query
except CartoDBException as e:
print ("some error occurred",e)
return e
def test(self):
try:
sqlstr = "INSERT INTO dynamicconnections_bmwguggenheimlab \
(survey, surveyid, the_geom, choose_a_road, how_do_you_feel, link_to_destinations,is_the_gradient_amenable_to_cycling,street_offers_priority_through_intersections) \
values \
('None', 'None', ST_GeomFromText('MULTILINESTRING((13.40568 52.51951, 13.40669 52.51879, 13.40726 52.51843, 13.40835 52.51758, 13.40918 52.51698, 13.40998 52.5164, 13.41032 52.51623, 13.41057 52.51616, 13.41177 52.51596, 13.41234 52.51586, 13.41315 52.51576, 13.41348 52.51575))',4326), '%(street)s', 'stressed', 'yes','yes', 'no')" % {'street':'Spandauer Straße and Stralauer Straße'}
query = self.cl.sql(sqlstr.encode('utf-8','replace'))
return query
except CartoDBException as e:
print ("some error ocurred", e)
return e
def geodata():
# Query: INSERT INTO geopaths (the_geom) VALUES (ST_SetSRID(ST_Point(" + coords[0].toString() + ", " + coords[1].toString() + "),4326))
cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user)
geodata = request.json
for geo in geodata:
try:
cl.sql("INSERT INTO geopaths (the_geom) VALUES (ST_SetSRID(ST_Point(" +
str(geo[0]) + ", " + str(geo[1]) + "),4326))")
except CartoDBException as e:
print("some error ocurred", e)
return redirect(url_for('index'))
def updateMpa(m): '''Executes Mpa update/insert statements using the CartoDB API via the cartodb module. Returns mpa.mpa_id or None if error''' if not isinstance(m, Mpa): m = Mpa.objects.get(pk=m) cl = CartoDBAPIKey(API_KEY, cartodb_domain) try: cl.sql(updateMpaSQL(m)) return m.pk except CartoDBException as e: print 'CartoDB Error for mpa_id %s:' % m.pk, e return None
def flush_and_transmit(features):
print "Connecting to CartoDB..."
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
try:
print "Clearing old feature set..."
cl.sql("TRUNCATE TABLE warning_geom;")
print "Inserting new features..."
for feat in features:
cl.sql("INSERT INTO warning_geom (name, description, the_geom) VALUES ('%s','%s', ST_SetSRID(ST_GeometryFromText('%s'), 4326))" % (feat['key'], feat['desc'], feat['wkt']))
except CartoDBException as e:
print ("some error ocurred", e)
def upload(property_id):
"""Uploads the data for the supplied integer ID"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
x = cleaner.carto_entry(property_id)
if x['values'] is not None:
query = 'INSERT INTO %s %s VALUES %s' %(TABLE, x['header'], x['values'])
try:
print "uploading %s" % property_id
cl.sql(query)
except CartoDBException as e:
print ("some error ocurred", e)
print query
def upload(property_id):
"""Uploads the data for the supplied integer ID"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
x = cleaner.carto_entry(property_id)
if x['values'] is not None:
query = 'INSERT INTO %s %s VALUES %s' % (TABLE, x['header'],
x['values'])
try:
print "uploading %s" % property_id
cl.sql(query)
except CartoDBException as e:
print("some error ocurred", e)
print query
def flush_and_transmit(features):
print "Connecting to CartoDB..."
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
try:
print "Clearing old feature set..."
cl.sql("TRUNCATE TABLE warning_geom;")
print "Inserting new features..."
for feat in features:
cl.sql(
"INSERT INTO warning_geom (name, description, the_geom) VALUES ('%s','%s', ST_SetSRID(ST_GeometryFromText('%s'), 4326))"
% (feat['key'], feat['desc'], feat['wkt']))
except CartoDBException as e:
print("some error ocurred", e)
def index_s(id):
cl = CartoDBAPIKey('',cartodb_user)
try:
carto_geoj = json.dumps(cl.sql("SELECT * FROM points WHERE cartodb_id= %d;" % id , format='geojson'))
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id= %d;" % id)
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=carto_geoj,
carto_places=labels,
last_row_id=last_row_id, id=id)
def clean(self):
api_key = self.cleaned_data['api_key']
domain = self.cleaned_data['domain']
table_name = self.cleaned_data['table_name']
name_col = self.cleaned_data['name_col']
pcode_col = self.cleaned_data['pcode_col']
parent_code_col = self.cleaned_data['parent_code_col']
client = CartoDBAPIKey(api_key, domain)
try:
sites = client.sql(
'select * from {} limit 1'.format(table_name)
)
except CartoDBException as e:
logging.exception("CartoDB exception occured", exc_info=True)
raise ValidationError("Couldn't connect to CartoDB table: "+table_name)
else:
row = sites['rows'][0]
if name_col not in row:
raise ValidationError('The Name column ({}) is not in table: {}'.format(
name_col, table_name
))
if pcode_col not in row:
raise ValidationError('The PCode column ({}) is not in table: {}'.format(
pcode_col, table_name
))
if parent_code_col and parent_code_col not in row:
raise ValidationError('The Parent Code column ({}) is not in table: {}'.format(
parent_code_col, table_name
))
return self.cleaned_data
def updateAllMpas(mpas=mpas, step=10, limit=None): '''Execute bulk Mpa update/insert statements using the CartoDB API via the cartodb module. mpas = Mpa queryset [default is all non-rejected MPAs with geom boundaries] step = number of Mpas to update per http transaction limit = only process a subset of records, useful for testing Returns list of mpa.mpa_ids that were not processed due to errors, empty list if no errors ''' cl = CartoDBAPIKey(API_KEY, cartodb_domain) nummpas = mpas.count() if limit: nummpas = min(limit, nummpas) print 'Processing %s of %s mpa records at a time' % (step, nummpas) r = range(0,nummpas+2,step) if r and r[-1] < nummpas: r.append(nummpas) error_ids = [] start = time.time() for i in xrange(0,len(r)-1): r0 = r[i] r1 = r[i+1] print 'Records [%s - %s]' % (r0, r1-1) step_ids = [] upsert = '' for m in mpas[r0:r1]: try: upsert += updateMpaSQL(m) step_ids.append(m.pk) except: error_ids.append(m.pk) print 'Skipping Mpa', m.pk # Now update this batch of records in CartoDB try: cl.sql(upsert) except CartoDBException as e: print 'CartoDB Error for mpa_ids %s:' % step_ids, e print 'Trying single updates.' for mpa_id in step_ids: try: updateMpa(mpa_id) except CartoDBException as e: error_ids.append(mpa_id) print 'CartoDB Error for mpa_id %s:' % mpa_id, e end = time.time() print 'TOTAL', end - start, 'sec elapsed' return error_ids
def insert_data1(query):
"""Connect to CartoDB and insert the data contained in tye query."""
cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1)
try:
print(cl.sql(query))
except CartoDBException as e:
print("some error ocurred", e)
raise
class CartoDBOutput:
SQL = 'INSERT INTO {table} ({column_names}) VALUES ({column_values})'
def __init__(self, apikey, domain, table):
self.table = table
self.cl = CartoDBAPIKey(apikey, domain)
def insert_line(self, lat, lon):
""" Insert a line in the configured CartoDB table. Lat/Lon are in EPSG:4326. """
formatted_sql = self.SQL.format(table=self.table,
column_names='the_geom',
column_values='ST_SetSRID(ST_Point(' + lon + ', ' + lat + '), 4326)')
self.cl.sql(formatted_sql)
def truncate_table(self):
self.cl.sql("TRUNCATE TABLE {table}".format(table=self.table))
def insert_data1(query):
"""Connect to CartoDB and insert the data contained in tye query."""
cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1)
try:
print(cl.sql(query))
except CartoDBException as e:
print("some error ocurred", e)
raise
def get_api(self):
"""
Reads secrets from local file and connects to CartoDB API.
"""
with open('secrets.json') as secrets_file:
secrets = json.load(secrets_file)
api_key = secrets['api_key']
cartodb_domain = secrets['cartodb_domain']
api = CartoDBAPIKey(api_key, cartodb_domain)
try:
api.sql('SELECT * FROM energy_tweets_table')
except CartoDBException:
raise
return api
class CartoTransaction(object):
#_SQL_INSERT = "insert into {0} ( the_geom, type, happened_at, message, spotid ) values( g, t, h, m, i ) select {1} where {1} not in (select spotid from {0} where spotid = {1}
_SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message, spotid ) values( %s, %s, %s, %s, %s);"
_SQL_DELETE = "delete from %s where spotid = %s;"
def __init__(self, api_key, domain, table, debug=False):
self.cl = CartoDBAPIKey(api_key, domain)
self.table = table
self.queries = []
self.debug = debug
def commit(self):
if len(self.queries) == 0:
return
stmts = "\n".join(self.queries)
query = "BEGIN;\n"
query += stmts
query += "COMMIT;\n"
if self.debug:
print query
resp = self.cl.sql(query)
if self.debug:
print resp
def _craft_insert(self, the_geom, event_type, happened_at, message,
spotid):
if happened_at is None:
happened_at = ''
if message is None:
message = ''
return self._SQL_INSERT % (self.table, the_geom, quote(event_type),
quote(happened_at), quote(message),
quote(spotid))
def insert_point(self, point):
the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" % (point.longitude,
point.latitude)
insert = self._craft_insert(the_geom, "checkin", point.dateTime,
point.messageContent, str(point.id))
self.queries.append(insert)
def update_line(self, spotid, coords, remove_first=False):
geojson = json.dumps({
"type": "MultiLineString",
"coordinates": [coords]
})
the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson)
insert = self._craft_insert(the_geom, "track",
str(datetime.datetime.now()), None,
str(spotid))
delete = self._SQL_DELETE % (self.table, quote(spotid))
self.queries.append(delete)
self.queries.append(insert)
def update_sites(api_key='',
domain='',
username='',
password='',
list_name='',
site_type='',
name_col='',
code_col='',
target_list=''):
carto_client = CartoDBAPIKey(api_key, domain)
ai_client = ActivityInfoClient(username, password)
# create an index of sites by p_code
existing = dict(
(site['code'], dict(site, index=i))
for (i, site) in enumerate(ai_client.get_locations(target_list))
if 'code' in site)
sites = carto_client.sql('select * from {}'.format(list_name))
send_message('Starting upload of {}'.format(list_name))
bad_codes = []
updated_sites = 0
for row in sites['rows']:
p_code = str(row[code_col]).strip()
site_name = row[name_col].encode('UTF-8')
cad = ai['Cadastral Area'].find_one({'code': str(row['cad_code'])})
if cad is None:
bad_codes.append(row['cad_code'])
continue
caz = ai['Caza'].find_one({'id': cad['parentId']})
gov = ai['Governorate'].find_one({'id': caz['parentId']})
if p_code not in existing and site_name:
payload = dict(id=int(random.getrandbits(31)),
locationTypeId=int(target_list),
name='{}: {}'.format(site_type, site_name)[0:40],
axe='{}'.format(p_code),
latitude=row['latitude'],
longitude=row['longitude'],
workflowstatusid='validated')
payload['E{}'.format(gov['levelId'])] = gov['id']
payload['E{}'.format(caz['levelId'])] = caz['id']
payload['E{}'.format(cad['levelId'])] = cad['id']
response = ai_client.call_command('CreateLocation', **payload)
if response.status_code == requests.codes.no_content:
updated_sites += 1
print 'Updated {}'.format(payload['name'])
else:
print 'Error for {}'.format(payload['name'])
print 'Bad codes: {}'.format(bad_codes)
print 'Updated sites: {}'.format(updated_sites)
send_message('Updated {} sites'.format(updated_sites))
def all():
cl = CartoDBAPIKey('', cartodb_user)
try:
carto_geoj = cl.sql("SELECT * FROM points;", format='geojson')
id = "All"
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points ")
labels = [[y for y in json.loads(x['pelias_label'])]
for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html',
carto_geoj=json.dumps(carto_geoj),
carto_places=labels,
last_row_id=last_row_id,
id=id)
def update():
cl = CartoDBAPIKey('',cartodb_user)
prevRow = int(request.args.get('rowid',''))
try:
#TODO: user parameter binding instead of string concatenation
carto_geoj = cl.sql("SELECT the_geom FROM points WHERE cartodb_id > " + str(prevRow) + ";", format='geojson')
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql("SELECT pelias_label FROM points WHERE cartodb_id > " + str(prevRow) + ";")
labels = [[y for y in json.loads(x['pelias_label'])] for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error occurred", e)
return jsonify(multipoints=carto_geoj,
places=labels,
lastrowid=last_row_id)
def geodata():
cl = CartoDBAPIKey(cartodb_key, cartodb_user)
#TODO: validate that geoJSON is valid and nonmalicious
geodata = json.dumps(request.json)
try:
#TODO: Store places as array of string, not array of object
#TODO: user parameter binding instead of string concatenation
result = json.dumps(cl.sql("DROP TABLE temp ; CREATE TABLE temp AS WITH data AS (SELECT '" + geodata + "'::json AS fc) SELECT row_number() OVER () AS gid, ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom, feat->'properties' AS properties FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) AS f; INSERT INTO points (the_geom, pelias_label,session_id) SELECT ST_COLLECT(ST_SETSRID(geom, 4326)), json_agg(properties), max(gid) from temp;"))
except CartoDBException as e:
print("some error ocurred", e)
return redirect(url_for('index'))
class CartoTransaction(object):
_SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message ) values( %s, %s, %s, %s);"
def __init__(self, api_key, domain, table, debug=False):
self.cl = CartoDBAPIKey(api_key, domain)
self.table = table
self.queries = []
self.debug = debug
def commit(self):
if len(self.queries) == 0:
return
stmts = "\n".join(self.queries)
query = "BEGIN;\n"
query += stmts
query += "COMMIT;\n"
if self.debug:
print query
resp = self.cl.sql(query)
if self.debug:
print resp
def _craft_insert(self, the_geom, event_type, happened_at, message):
if happened_at is None:
happened_at = ''
if message is None:
message = ''
def quote(s):
return "'" + s + "'"
return self._SQL_INSERT % (self.table, the_geom, quote(event_type),
quote(happened_at), quote(message))
def insert_point(self, point):
the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" % (point.longitude,
point.latitude)
insert = self._craft_insert(the_geom, "checkin", point.dateTime,
point.message)
self.queries.append(insert)
def update_line(self, trip_id, coords):
geojson = json.dumps({
"type": "MultiLineString",
"coordinates": [coords]
})
the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson)
insert = self._craft_insert(the_geom, "track",
str(datetime.datetime.now()), None)
self.queries.append(insert)
def new_squat():
def value_to_db(value):
if value == 'on':
value = 'Yes'
return "'%s'" % value.replace("'", "''")
cl = CartoDBAPIKey(app.config.get('CARTODB_APIKEY'),
app.config.get('CARTODB_USER'))
try:
keys = request.form.keys()
sql = 'INSERT INTO %s (%s) VALUES (%s)' % (
app.config.get('CARTODB_TABLE'),
','.join(keys + ['needs_moderation',]),
','.join([value_to_db(request.form.get(k)) for k in keys] + ['true',]),
)
cl.sql(sql)
except CartoDBException:
traceback.print_exc()
response = app.make_response('OK')
if app.config.get('DEBUG'):
response.headers['Access-Control-Allow-Origin'] = '*'
return response
def processed_ids():
"""Returns a list of property IDs that have already been pushed to
CartoDB
"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
query = 'SELECT property_id FROM %s' % TABLE
try:
data = cl.sql(query)
except CartoDBException as e:
print("some error ocurred", e)
return [x['property_id'] for x in data['rows']]
def index():
cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user)
try:
carto_geoj = cl.sql("SELECT cartodb_id," +
"ST_AsGeoJSON(p1) as p1," +
"ST_AsGeoJSON(p2) as p2," +
"ST_AsGeoJSON(p3) as p3," +
"ST_AsGeoJSON(p4) as p4," +
"ST_AsGeoJSON(p5) as p5 " +
"FROM geopaths;")
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html', carto_geoj=carto_geoj)
def processed_ids():
"""Returns a list of property IDs that have already been pushed to
CartoDB
"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
query = 'SELECT property_id FROM %s' % TABLE
try:
data = cl.sql(query)
except CartoDBException as e:
print ("some error ocurred", e)
return [x['property_id'] for x in data['rows']]
def synchronize_entries(self, entries):
cl = CartoDBAPIKey(settings.CARTODB_SYNC['API_KEY'],
settings.CARTODB_SYNC['DOMAIN'])
#
# Apply synchronizations in the following order: insert, update,
# delete. In this way, we should hopefully avoid unsafe situations
# such as deleting an entry then trying to update it.
#
inserts = [self.get_cartodb_mapping(e.content_object) for e in entries \
if e.status == SyncEntry.PENDING_INSERT]
if inserts:
insert_statement = self.get_insert_statement(inserts)
try:
print(cl.sql(insert_statement))
except CartoDBException as e:
traceback.print_exc()
print('Exception while inserting:', e)
updates = [self.get_cartodb_mapping(e.content_object) for e in entries \
if e.status == SyncEntry.PENDING_UPDATE]
if updates:
statement = self.get_update_statement(updates)
print(statement)
try:
print(cl.sql(statement))
except CartoDBException as e:
traceback.print_exc()
print('Exception while updating:', e)
deletes = [e for e in entries if e.status == SyncEntry.PENDING_DELETE]
if deletes:
statement = self.get_delete_statement(deletes)
try:
print(cl.sql(statement))
except CartoDBException as e:
traceback.print_exc()
print('Exception while deleting:', e)
def update_sites(
api_key='cad5c2fd1aa5236083743f54264b203d903f3a06',
domain='unhcr',
table_name='imap_v5_cadcode',
site_type='IS',
name_col='pcodename',
code_col='p_code',
):
client = CartoDBAPIKey(api_key, domain)
sites = client.sql(
'select * from {}'.format(table_name)
)
for row in sites['rows']:
p_code = row[code_col]
site_name = row[name_col].encode('UTF-8')
cad = ai['Cadastral Area'].find_one({'code': row['cad_code']})
caz = ai['Caza'].find_one({'id': cad['parentId']})
gov = ai['Governorate'].find_one({'id': caz['parentId']})
location = ai.locations.find_one({'p_code': p_code})
if not location:
location = {
"p_code": p_code,
"ai_id": int(random.getrandbits(31)) # (31-bit random key),
}
location["ai_name"] = '{}: {}'.format(site_type, site_name)
location["name"] = site_name
location["type"] = site_type
location["latitude"] = row['latitude']
location["longitude"] = row['longitude']
location["adminEntities"] = {
str(gov['levelId']): {
"id": gov['id'],
"name": gov['name']
},
str(caz['levelId']): {
"id": caz['id'],
"name": caz['name']
},
str(cad['levelId']): {
"id": cad['id'],
"name": cad['name']
},
}
ai.locations.update({'p_code': p_code}, location, upsert=True)
print 'Updated {}: {}'.format(site_type, site_name)
def total_value():
"""Returns the total value of the unclaimed property that had
already been uploaded to CartoDB
"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
query = 'SELECT value FROM %s' % TABLE
try:
data = cl.sql(query)
except CartoDBException as e:
print("some error ocurred", e)
cash = sum([x['value'] for x in data['rows']])
return '{:10,.2f}'.format(cash)
def total_value():
"""Returns the total value of the unclaimed property that had
already been uploaded to CartoDB
"""
cl = CartoDBAPIKey(API_KEY, DOMAIN)
query = 'SELECT value FROM %s' % TABLE
try:
data = cl.sql(query)
except CartoDBException as e:
print ("some error ocurred", e)
cash = sum([x['value'] for x in data['rows']])
return '{:10,.2f}'.format(cash)
def update_sites(
api_key='cad5c2fd1aa5236083743f54264b203d903f3a06',
domain='unhcr',
table_name='imap_v5_cadcode',
site_type='IS',
name_col='pcodename',
code_col='p_code',
):
client = CartoDBAPIKey(api_key, domain)
sites = client.sql('select * from {}'.format(table_name))
for row in sites['rows']:
p_code = row[code_col]
site_name = row[name_col].encode('UTF-8')
cad = ai['Cadastral Area'].find_one({'code': row['cad_code']})
caz = ai['Caza'].find_one({'id': cad['parentId']})
gov = ai['Governorate'].find_one({'id': caz['parentId']})
location = ai.locations.find_one({'p_code': p_code})
if not location:
location = {
"p_code": p_code,
"ai_id": int(random.getrandbits(31)) # (31-bit random key),
}
location["ai_name"] = '{}: {}'.format(site_type, site_name)
location["name"] = site_name
location["type"] = site_type
location["latitude"] = row['latitude']
location["longitude"] = row['longitude']
location["adminEntities"] = {
str(gov['levelId']): {
"id": gov['id'],
"name": gov['name']
},
str(caz['levelId']): {
"id": caz['id'],
"name": caz['name']
},
str(cad['levelId']): {
"id": cad['id'],
"name": cad['name']
},
}
ai.locations.update({'p_code': p_code}, location, upsert=True)
print 'Updated {}: {}'.format(site_type, site_name)
def get_breweries(komm):
cl = CartoDBAPIKey(CDB_KEY, CDB_DOMAIN)
try:
res = cl.sql('''
SELECT
count(*)
FROM
osm_breweries
WHERE
ST_Contains(%s, the_geom)
''' % geojson_sql(komm['geometry']))
return res['rows'][0]['count']
except CartoDBException:
return -1
def update():
cl = CartoDBAPIKey('', cartodb_user)
prevRow = int(request.args.get('rowid', ''))
try:
#TODO: user parameter binding instead of string concatenation
carto_geoj = cl.sql("SELECT the_geom FROM points WHERE cartodb_id > " +
str(prevRow) + ";",
format='geojson')
#TODO: Parse array of strings, not array of objects as place labels
labels_resp = cl.sql(
"SELECT pelias_label FROM points WHERE cartodb_id > " +
str(prevRow) + ";")
labels = [[y for y in json.loads(x['pelias_label'])]
for x in labels_resp['rows']]
last_row_id_resp = cl.sql("SELECT MAX(cartodb_id) AS id FROM points")
last_row_id = last_row_id_resp['rows'][0]['id']
except CartoDBException as e:
print("some error occurred", e)
return jsonify(multipoints=carto_geoj,
places=labels,
lastrowid=last_row_id)
def update():
cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user)
prevRow = request.args.get('rowid','')
try:
carto_geoj = cl.sql("SELECT cartodb_id," +
"ST_AsGeoJSON(p1) as p1," +
"ST_AsGeoJSON(p2) as p2," +
"ST_AsGeoJSON(p3) as p3," +
"ST_AsGeoJSON(p4) as p4," +
"ST_AsGeoJSON(p5) as p5 " +
"FROM geopaths " +
"WHERE cartodb_id > " + str(prevRow) + ";")
except CartoDBException as e:
print("some error occurred", e)
return jsonify(carto_geoj)
def index():
cl = CartoDBAPIKey(keys.cartodb_key, keys.cartodb_user)
try:
carto_geoj = cl.sql("SELECT cartodb_id," +
"ST_AsGeoJSON(p1) as p1," +
"ST_AsGeoJSON(p2) as p2," +
"ST_AsGeoJSON(p3) as p3," +
"ST_AsGeoJSON(p4) as p4," +
"ST_AsGeoJSON(p5) as p5 " +
"FROM geopaths;")
last_row_id = max([row['cartodb_id'] for row in carto_geoj['rows']])
print("Length of database is: ", len(carto_geoj['rows']))
except CartoDBException as e:
print("some error ocurred", e)
return render_template('index.html', carto_geoj=carto_geoj, last_row_id=last_row_id)
def geodata():
cl = CartoDBAPIKey(cartodb_key, cartodb_user)
#TODO: validate that geoJSON is valid and nonmalicious
geodata = json.dumps(request.json)
try:
#TODO: Store places as array of string, not array of object
#TODO: user parameter binding instead of string concatenation
result = json.dumps(
cl.sql(
"DROP TABLE temp ; CREATE TABLE temp AS WITH data AS (SELECT '"
+ geodata +
"'::json AS fc) SELECT row_number() OVER () AS gid, ST_AsText(ST_GeomFromGeoJSON(feat->>'geometry')) AS geom, feat->'properties' AS properties FROM (SELECT json_array_elements(fc->'features') AS feat FROM data) AS f; INSERT INTO points (the_geom, pelias_label,session_id) SELECT ST_COLLECT(ST_SETSRID(geom, 4326)), json_agg(properties), max(gid) from temp;"
))
except CartoDBException as e:
print("some error ocurred", e)
return redirect(url_for('index'))
class CartoTransaction(object):
_SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message ) values( %s, %s, %s, %s);"
def __init__(self, api_key, domain, table, debug = False):
self.cl = CartoDBAPIKey(api_key, domain)
self.table = table
self.queries = []
self.debug = debug
def commit(self):
if len(self.queries) == 0:
return
stmts = "\n".join(self.queries)
query = "BEGIN;\n"
query += stmts
query += "COMMIT;\n"
if self.debug:
print query
resp = self.cl.sql(query)
if self.debug:
print resp
def _craft_insert(self, the_geom, event_type, happened_at, message):
if happened_at is None:
happened_at = ''
if message is None:
message = ''
def quote(s):
return "'" + s + "'"
return self._SQL_INSERT % (self.table, the_geom , quote(event_type), quote(happened_at), quote(message))
def insert_point(self, point):
the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" %(point.longitude, point.latitude)
insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.message)
self.queries.append(insert)
def update_line(self, trip_id, coords):
geojson = json.dumps({ "type" : "MultiLineString", "coordinates": [coords] })
the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson)
insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None)
self.queries.append(insert)
class CartoTransaction(object):
#_SQL_INSERT = "insert into {0} ( the_geom, type, happened_at, message, spotid ) values( g, t, h, m, i ) select {1} where {1} not in (select spotid from {0} where spotid = {1}
_SQL_INSERT = "insert into %s ( the_geom, type, happened_at, message, spotid ) values( %s, %s, %s, %s, %s);"
_SQL_DELETE = "delete from %s where spotid = %s;"
def __init__(self, api_key, domain, table, debug = False):
self.cl = CartoDBAPIKey(api_key, domain)
self.table = table
self.queries = []
self.debug = debug
def commit(self):
if len(self.queries) == 0:
return
stmts = "\n".join(self.queries)
query = "BEGIN;\n"
query += stmts
query += "COMMIT;\n"
if self.debug:
print query
resp = self.cl.sql(query)
if self.debug:
print resp
def _craft_insert(self, the_geom, event_type, happened_at, message, spotid):
if happened_at is None:
happened_at = ''
if message is None:
message = ''
return self._SQL_INSERT % (self.table, the_geom , quote(event_type), quote(happened_at), quote(message), quote(spotid))
def insert_point(self, point):
the_geom = "ST_SetSRID(ST_Point(%s,%s), 4326)" %(point.longitude, point.latitude)
insert = self._craft_insert(the_geom, "checkin", point.dateTime, point.messageContent, str(point.id))
self.queries.append(insert)
def update_line(self, spotid, coords, remove_first=False):
geojson = json.dumps({ "type" : "MultiLineString", "coordinates": [coords] })
the_geom = "ST_SetSRID(ST_GeomFromGeoJSON('%s'), 4326)" % (geojson)
insert = self._craft_insert(the_geom, "track", str(datetime.datetime.now()), None, str(spotid))
delete = self._SQL_DELETE % (self.table, quote(spotid))
self.queries.append(delete)
self.queries.append(insert)
def describe(table_name):
cl = CartoDBAPIKey(CARTODB_API_KEY, cartodb_domain)
try:
resp = cl.sql(
"SELECT * FROM information_schema.columns WHERE table_name ='{}'".
format(table_name))
except CartoDBException as e:
print("some error ocurred", e)
#print resp.keys()
#for c in resp['fields']:
# print c, resp['fields'][c]
for c in resp['rows']:
#print "-----------"
#for d in ['column_name','udt_name','column_default']:
#for d in c:
# if c[d] != None:
# print d, c[d]
item = "{} {}".format(c['column_name'], c['udt_name'])
if c['column_default']:
item = item + " default " + c['column_default']
print item
def get_foot_trails(komm):
cl = CartoDBAPIKey(CDB_KEY, CDB_DOMAIN)
try:
gj = geojson_sql(komm['geometry'])
query = '''
SELECT
SUM(
ST_Length(
ST_INTERSECTION(
s.the_geom::geography,
%s::geography
)
)
) / 1000 as len
FROM
fotrute s
WHERE
ST_Intersects(%s, s.the_geom);
''' % (gj, gj)
res = cl.sql(query)
length = res['rows'][0]['len']
return round(length) if length is not None else 0
except CartoDBException:
return -1
from cartodb import CartoDBAPIKey, CartoDBException
user = '******'
API_KEY ='a593f743aebe095ab7c75d230c71e2fd13e5bc24'
cartodb_domain = 'mik3cap'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
try:
print cl.sql('SELECT * FROM user_info')
except CartoDBException as e:
print ("some error ocurred", e)
try:
print cl.sql('SELECT * FROM movie')
except CartoDBException as e:
print ("some error ocurred", e)
try:
print cl.sql('SELECT * FROM venue')
except CartoDBException as e:
print ("some error ocurred", e)
try:
print cl.sql('SELECT * FROM plan')
except CartoDBException as e:
print ("some error ocurred", e)
# INSERT INTO user_info (email, fb_user_id, full_name) VALUES (%s, %s, %s)
def update_sites_from_cartodb(carto_table):
client = CartoDBAPIKey(carto_table.api_key, carto_table.domain)
sites_created = sites_updated = sites_not_added = 0
try:
# query for cartodb
qry = ''
if carto_table.parent_code_col and carto_table.parent:
qry = 'select st_AsGeoJSON(the_geom) as the_geom, {}, {}, {} from {}'.format(
carto_table.name_col,
carto_table.pcode_col,
carto_table.parent_code_col,
carto_table.table_name)
else:
qry = 'select st_AsGeoJSON(the_geom) as the_geom, {}, {} from {}'.format(
carto_table.name_col,
carto_table.pcode_col,
carto_table.table_name)
sites = client.sql(qry)
except CartoDBException as e:
logging.exception("CartoDB exception occured", exc_info=True)
else:
for row in sites['rows']:
pcode = str(row[carto_table.pcode_col]).strip()
site_name = row[carto_table.name_col]
if not site_name or site_name.isspace():
logger.warning("No name for location with PCode: {}".format(pcode))
sites_not_added += 1
continue
site_name = site_name.encode('UTF-8')
parent = None
parent_code = None
parent_instance = None
# attempt to reference the parent of this location
if carto_table.parent_code_col and carto_table.parent:
try:
parent = carto_table.parent.__class__
parent_code = row[carto_table.parent_code_col]
parent_instance = Location.objects.get(p_code=parent_code)
except Exception as exp:
msg = " "
if exp is parent.MultipleObjectsReturned:
msg = "{} locations found for parent code: {}".format(
'Multiple' if exp is parent.MultipleObjectsReturned else 'No',
parent_code
)
else:
msg = exp.message
logger.warning(msg)
sites_not_added += 1
continue
# create the actual location or retrieve existing based on type and code
succ, sites_not_added, sites_created, sites_updated = create_location(pcode, carto_table,
parent, parent_instance,
site_name, row,
sites_not_added, sites_created,
sites_updated)
return "Table name {}: {} sites created, {} sites updated, {} sites skipped".format(
carto_table.table_name, sites_created, sites_updated, sites_not_added
)
import time
from polyline.codec import PolylineCodec
from geojson import Feature, LineString
## Strava connection
client = Client()
client.access_token = 'd47099a29b2f3539e1c21af6d820e33a109a079e'
## CartoDB connection
API_KEY = 'cad54ea0c580a0c554b9e9562157e7c9bd9f37b0'
cartodb_domain = 'geodarcy'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
## remove duplicate rows
cl.sql(
'DELETE FROM wbstrava WHERE cartodb_id IN (SELECT cartodb_id FROM (SELECT cartodb_id, ROW_NUMBER() OVER (partition BY segment_id ORDER BY cartodb_id) AS rnum FROM wbstrava) t WHERE t.rnum > 1);'
)
## find segments with no geometry
queryResult = cl.sql('select segment_id from wbstrava where the_geom is null')
currentSegments = [x['segment_id'] for x in queryResult['rows']]
for id in currentSegments:
try:
segmentGeojson = PolylineCodec().decode(
client.get_segment(id).map.polyline)
flippedGeojson = [[x[1], x[0]] for x in segmentGeojson]
inputGeojson = '{"coordinates": ' + str(
flippedGeojson
) + ', "type": "LineString", "crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'
cl.sql('UPDATE wbstrava SET the_geom = ST_GeomFromGeoJSON(\'' +
inputGeojson + '\') WHERE segment_id=' + str(id))
sql_query = 'INSERT INTO humanitarian_response (the_geom, id, label, cluster_id, cluster_label, location_id, location_label) VALUES ('
# Add our values to the statement, making sure that we wrap string values
# in quotes
# 4/8, EB: Fixed bug on next line that rounded x/y down to very low
# precision
sql_query = sql_query + "'SRID=4326; POINT (%f %f)', '%s', '%s', '%s', '%s', '%s', '%s'" % (long, lat, id, label, cluster_id, cluster_label, location_id, location_label)
sql_query = sql_query + ')'
return str(sql_query)
def main():
# define a variable to hold the source URL
urlData = "http://www.humanitarianresponse.info/api/v1.0/assessments"
# Open the URL and read the data
webUrl = urllib2.urlopen(urlData)
if (webUrl.getcode() == 200):
data = webUrl.read()
# print out our customized results
getResults(data)
# This is where you call insert_into_cartodb()
insert_into_cartodb(sql_query)
else:
print "Received an error from server, cannot retrieve results " + str(webUrl.getcode())
if __name__ == "__main__":
main()
try:
cl.sql('DELETE FROM humanitarian_response WHERE cartodb_id NOT IN (SELECT MIN(cartodb_id) FROM humanitarian_response GROUP BY id)')
except CartoDBException as e:
print ("some error ocurred", e)
params={"api_key": API_KEY})
response_data = r.json()
state = response_data["state"]
print response_data
if state == "failure":
sys.exit(1)
table_name = response_data["table_name"]
print "TABLE_NAME"
# 2) Append new data from temp table to real table
cl = CartoDBAPIKey(API_KEY, "dcarrion")
try:
print cl.sql("CREATE SEQUENCE serial_%s START %s" %
(current_cartodb_id, current_cartodb_id))
except CartoDBException as e:
pass
try:
print cl.sql(
"INSERT INTO dcarrion.elecciones_partidos (actor_displayname,actor_followerscount,actor_friendscount,"
"actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype,"
"actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset,"
"actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id,"
"inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype,"
"location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type,"
"postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities,"
"twitter_filter_level,twitter_lang,verb,cartodb_id) SELECT actor_displayname,actor_followerscount,actor_friendscount,"
"actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype,"
"actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset,"
'&fcode=AIRP&formatted=tr&lang=iata&username=demo')
geoinfo = r.json()
print geoinfo
if geoinfo['totalResultsCount'] == 0:
raise CartoDBException('code ' + iata_code + 'not geocodeable')
else:
lat = geoinfo['geonames'][0]['lat']
lng = geoinfo['geonames'][0]['lng']
wkt = "'POINT(" + lng + ' ' + lat + ")'"
return wkt
try:
print "Grabbing destinations"
iata_dest = cl.sql(
"select distinct destination_city from athompson.flight_cube_query_09_06_15_dests"
)
print "Dests grabbed!"
except CartoDBException as e:
print("some error occured with dest grab", e)
for row in iata_dest['rows']:
iata_codes_dest.append(row['destination_city'])
try:
print "Geocoding dests"
for code in iata_codes_dest:
if code in []:
print "passing " + code
pass
else:
from cartodb import CartoDBAPIKey, CartoDBException
'''
UPDATE athompson.flight_cube_query_09_06_15 SET origin_city = 'ISA' where city_pair like 'ISA%';
select * from athompson.flight_cube_query_09_06_15 where origin_city is null order by city_pair asc
'''
API_KEY = 'REMOVE'
cartodb_domain = 'athompson'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
iata_codes_origin = []
iata_codes_dest = []
try:
print "Grabbing origins"
pair_origin = cl.sql(
"select distinct substring(city_pair from '^.{3}') from athompson.flight_cube_query_09_06_15"
)
print "Origins grabbed!"
except CartoDBException as e:
print("some error occured with origin grab", e)
for row in pair_origin['rows']:
iata_codes_origin.append(row['substring'])
try:
print "Origins processed, pushing updates to cartodb"
for code in iata_codes_origin:
print "updating origin " + code
update_origin = cl.sql(
"update athompson.flight_cube_query_09_06_15 set origin_city = '" +
code + "' where city_pair like '" + code + "%'")
print "geocoding iata airport: " + iata_code
r = requests.get('http://ws.geonames.org/searchJSON?name_equals=' +
iata_code + '&fcode=AIRP&formatted=tr&lang=iata&username=demo')
geoinfo = r.json()
print geoinfo
if geoinfo['totalResultsCount'] == 0:
raise CartoDBException('code ' + iata_code + 'not geocodeable')
else:
lat = geoinfo['geonames'][0]['lat']
lng = geoinfo['geonames'][0]['lng']
wkt = "'POINT(" + lng + ' ' + lat + ")'"
return wkt
try:
print "Grabbing origins"
iata_origin = cl.sql("select distinct origin_city from athompson.flight_cube_query_09_06_15_origins")
print "Origins grabbed!"
except CartoDBException as e:
print ("some error occured with origin grab", e)
for row in iata_origin['rows']:
iata_codes_origin.append(row['origin_city'])
try:
print "Geocoding origins"
for code in iata_codes_origin:
if code in ['TEV', 'OSB']:
print "passing " + code
pass
else:
print "geocoding origin " + code
continue
tmp_table_name = response_data["table_name"] # Just in case it changed during import
print ("TMP_TABLE_NAME", tmp_table_name)
# 2) Append new data from temp table to real table
try:
print (cl.sql("INSERT INTO {account_name}.{table_name} (actor_displayname,actor_followerscount,actor_friendscount,"
"actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype,"
"actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset,"
"actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id,"
"inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype,"
"location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type,"
"postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities,"
"twitter_filter_level,twitter_lang,verb,cartodb_id) SELECT actor_displayname,actor_followerscount,actor_friendscount,"
"actor_id,actor_image,actor_languages,actor_link,actor_links,actor_listedcount,actor_location,actor_objecttype,"
"actor_postedtime,actor_preferredusername,actor_statusescount,actor_summary,actor_twittertimezone,actor_utcoffset,"
"actor_verified,body,category_name,category_terms,favoritescount,generator_displayname,generator_link,geo,gnip,id,"
"inreplyto_link,link,location_displayname,location_geo,location_link,location_name,location_objecttype,"
"location_streetaddress,object_id,object_link,object_objecttype,object_postedtime,object_summary,object_type,"
"postedtime,provider_displayname,provider_link,provider_objecttype,retweetcount,the_geom,twitter_entities,"
"twitter_filter_level,twitter_lang,verb,nextval('{table_name}_cartodb_id_seq') as cartodb_id "
"FROM {account_name}.{tmp_table_name}".format(table_name=TABLE_NAME, tmp_table_name=tmp_table_name, account_name=ACCOUNT_NAME)))
except CartoDBException as e:
print ("some error ocurred", e)
continue
with open("{table_name}_next.conf".format(table_name=TABLE_NAME), "w") as conf:
conf.write(json.dumps({"start_timestamp": end_timestamp.strftime("%Y%m%d%H%M%S")}))
# 3) Delete temporary table
from cartodb import CartoDBAPIKey, CartoDBException
API_KEY ='YOUR_CARTODB_API_KEY'
cartodb_domain = 'jatorre'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
try:
print cl.sql('select * from mytable')
except CartoDBException as e:
print ("some error ocurred", e)from cartodb import CartoDBAPIKey, CartoDBException
API_KEY ='YOUR_CARTODB_API_KEY'
cartodb_domain = 'jatorre'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
try:
print cl.sql('select * from mytable')
except CartoDBException as e:
print ("some error ocurred", e)
#clear_table.py
from cartodb import CartoDBAPIKey, CartoDBException
user = '******'
API_KEY ='adfa3ceeaf052aaa42e2e2ec96d4df80ea42e950'
cartodb_domain = 'lauragator'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
sql_statement = "DELETE FROM spatial"
try:
print cl.sql(sql_statement)
except CartoDBException as e:
print ("some error ocurred", e)
from cartodb import CartoDBAPIKey, CartoDBException
API_KEY =''
cartodb_domain = 'palonso0vf'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
#try:
#except CartoDBException as e:
# print("some error ocurred", e)
#Peticion SQL
petition = ("""SELECT nameunit, ST_Area(the_geom::geography)/1000000 area FROM ign_spanish_adm3_municipalities where nameunit like '%Valladolid%';""")
catch = (cl.sql(petition))
for f in catch['rows']:
print f
def truncate_table1(table=settings.CARTODB_TABLE1):
query = 'TRUNCATE %s' % table
cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1)
cl.sql(query)
def delete_for_date1(target_date, table):
print("\n=== Deleting existing data for date %s... ===" % target_date)
date_formatted = target_date.strftime('%Y-%m-%d')
query = "DELETE FROM %s WHERE date='%s'" % (table, date_formatted)
cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1)
cl.sql(query)
from cartodb import CartoDBAPIKey, CartoDBException
import urllib2
api = slumber.API("http://ws.usig.buenosaires.gob.ar/rest/")
user = '******'
API_KEY = '5ebd92316d11854603dd60b67019350c7412de80'
cartodb_domain = 'gcba'
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
batch_size = 100
#
# print answer['resultado']['y']
# print answer['resultado']['x']
answer = cl.sql(
'SELECT cartodb_id, latitude, longitude, coord_x, coord_y FROM censo_arbolado_recoleta'
)
batch_sql = ""
batch_count = 0
for row in answer['rows']:
batch_count += 1
if not row['latitude']:
usig_answer = api.convertir_coordenadas.get(x=row['coord_x'],
y=row['coord_y'],
output='lonlat')
lng = usig_answer['resultado']['x']
lat = usig_answer['resultado']['y']
sql_text = "UPDATE censo_arbolado_recoleta SET longitude='%s', latitude='%s' WHERE cartodb_id = %s ;" % (
lng, lat, row['cartodb_id'])
print sql_text
batch_sql += sql_text
if __name__ == '__main__':
#
# For each row in whatever is returned from get_data(), create an SQL
# INSERT statement for that data, then insert it into CartoDB.
#
for media in Instagram_API(min_id=get_max_id()):
try:
# ONLY look at pieces of media with a location
if media.location:
sql_query = create_sql_statement(
media.location.point.longitude,
media.location.point.latitude,
media.images['standard_resolution'].url,
media.user.username,
media.id,
media.link,
media.created_time
)
# print(sql_query)
# This is where you call insert_into_cartodb()
insert_into_cartodb(sql_query)
except exceptions.AttributeError:
pass
if __name__ == '__main__':
try:
cl.sql('DELETE FROM instagram_media WHERE cartodb_id NOT IN (SELECT MIN(cartodb_id) FROM instagram_media GROUP BY url)')
except CartoDBException as e:
print ("some error ocurred", e)
