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 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 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
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 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 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 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))
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():
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'))
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 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 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 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 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 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 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 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 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 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(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 __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 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'))
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 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
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 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
def index():
form = DotCartoForm()
fi = None
first = None
old_map_name = None
if form.validate_on_submit():
# import ipdb; ipdb.set_trace()
# cred = json.load(open('credentials.json')) # modify credentials.json.sample
# if username == '':
username = form.carto_api_endpoint.data
# if apikey == '':
apikey = form.carto_api_key.data
# if cartojson == '':
cartojson = form.cartojsontemplate.data.replace(
' ', '_').lower() + '.carto.json'
# if first == '':
print 'cartjson assigned'
# if cartojson == 'dma_heatmap.carto.json':
# first = 'dma_master_polygons_merge'
if cartojson == 'dmas_oct.carto.json':
first = 'dma_visit_index_template_data_may2017v1'
elif cartojson == 'visits_final.carto.json':
first = 'visitindexheatmapexample'
print first
# if second == '':
second = form.new_dataset_names.data
# if cartojson == 'dma_heatmap.carto.json':
# old_map_name = "TEMPLATE (USE THIS) - DMA Heatmap"
if cartojson == 'dmas_oct.carto.json':
old_map_name = "NinthDecimal DMAs - 0914"
elif cartojson == 'visits_final.carto.json':
old_map_name = "NinthDecimal Visits Index - 0914"
print first
new_map_name = form.map_title_name.data
# cartojson = 'template.carto.json'
curTime = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
inFile = 'data/' + cartojson
ouFile = 'data/_temp/' + cartojson.replace(
'.carto.json', '') + '_' + curTime + '.carto.json'
fiFile = 'data/_temp/' + new_map_name.replace(
' ', '_').lower() + '_' + cartojson.replace(
'.carto.json', '') + '_' + curTime + '.carto.json'
print inFile, ouFile, first, second
openFileReplaceDatasetSave(inFile, ouFile, first, second)
print inFile, ouFile, old_map_name, new_map_name
openFileReplaceDatasetSave(ouFile, fiFile, old_map_name, new_map_name)
cl = CartoDBAPIKey(apikey, username)
# Import csv file, set privacy as 'link' and create a default viz
fi = FileImport(fiFile, cl, create_vis='true', privacy='link')
fi.run()
return render_template("index.html", form=form, result=[str(fi)])
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():
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 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 push_to_cartodb(f):
"""
send dataset f to cartodb, return success of import
"""
print "attempting to import into cartodb"
config = loadConfig()
cl = CartoDBAPIKey(config["API_KEY"], config["user"])
fi = FileImport(f, cl, table_name='python_table_test')
fi.run()
return fi.success
def main():
carto_conn = CartoDBAPIKey(secrets.apikey, secrets.domain)
am_conn = get_connection_or_die()
for project in ['KATM_BrownBear']:
locations = get_locations_to_remove(am_conn, project)
vectors = get_vectors_to_remove(am_conn, project)
remove(am_conn, carto_conn, locations, vectors)
locations = get_locations_for_carto(am_conn, project)
vectors = get_vectors_for_carto(am_conn, project)
insert(am_conn, carto_conn, locations, vectors)
fix_format_of_vector_columns(carto_conn)
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 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 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 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 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 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
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)
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 tocarto(table, url, geom_field=None):
from cartodb import CartoDBAPIKey, CartoDBException, FileImport
# parse url
# get name of
filename = name + '.csv'
# write to csv
# TODO can FileImport take a stream so we don't have to write to csv first?
(etl.reproject(table, 4326, geom_field=geom_field)
.rename('shape', 'the_geom')
.tocsv(filename)
)
API_KEY = '<api key>'
DOMAIN = '<domain>'
cl = CartoDBAPIKey(API_KEY, DOMAIN)
fi = FileImport(filename, cl, privacy='public')
fi.run()
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 cleanNinthDecimal(inFile, inFileName, username, apikey):
curTime = datetime.now().strftime('%Y_%m_%d_%H_%M_%S')
ouFileName = inFileName.replace('.csv', '')
ouFile = 'data/ninth_decimal_' + curTime + '.csv'
ouFile = 'data/_send/' + ouFileName + '.csv'
with open(ouFile, 'w') as csvoutput:
writer = csv.writer(csvoutput, lineterminator='\n')
# This code opens our bytestream with \r as the newline
newline_wrapper = TextIOWrapper(inFile, newline=None) # newline='\r')
reader = csv.reader(
newline_wrapper) # , delimiter = ',')#, lineterminator='\r')
all = []
row = next(reader)
row.append('the_geom')
all.append(row)
geoFenceColLoc = row.index('geofence')
for row in reader:
row.append(reorderLatLng(row[geoFenceColLoc]))
all.append(row)
writer.writerows(all)
cl = CartoDBAPIKey(apikey, username)
# Import csv file, set privacy as 'link' and create a default viz
fi = FileImport(ouFile,
cl,
create_vis='false',
privacy='link',
content_guessing='false',
type_guessing='false')
fi.run()
return fi
def truncate_table1(table=settings.CARTODB_TABLE1):
query = 'TRUNCATE %s' % table
cl = CartoDBAPIKey(settings.CARTODB_API_KEY1, settings.CARTODB_DOMAIN1)
cl.sql(query)
import os
from powertrack.api import *
from cartodb import CartoDBAPIKey, CartoDBException
config = ConfigParser.RawConfigParser()
config.read("app.conf")
APP_NAME= config.get('general','app_name')
IMPORT_API_ENDPOINT = config.get('cartodb', 'import_api_endpoint')
ACCOUNT_NAME = config.get('cartodb', 'account_name')
API_KEY = config.get('cartodb', 'api_key')
TABLE_NAME = config.get('cartodb', 'table_name')
RUN_AFTER_S = int(config.get('intervals', 'run_after_s'))
p = PowerTrack(api="search")
cl = CartoDBAPIKey(API_KEY, ACCOUNT_NAME)
# Split categories
categories = [cat.split(" ") for cat in config.get('twitter','categories').split("|")]
print ("======================================")
print (" Starting script {} for user {}".format(APP_NAME,ACCOUNT_NAME))
print ("======================================")
# Get tweets from GNIP
while 1:
# Read the start timestamp
with open("{table_name}_next.conf".format(table_name=TABLE_NAME), "r") as conf:
#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)
import matplotlib.pyplot as plt
import numpy as np
import seaborn
import scipy.special
import sent
import sys, csv
from subprocess import call
###
### CARTODB API KEY: 24b3f4810d68e1f8e9c3f17c0580181b4e034edb
###
my_cartodb_domain = 'https://samnolen.cartodb.com'
my_cartodb_url = 'https://samnolen.cartodb.com/api/v1/imports/?api_key='
my_cartodb_api_key = '24b3f4810d68e1f8e9c3f17c0580181b4e034edb'
cl = CartoDBAPIKey(my_cartodb_api_key, my_cartodb_domain)
app = Flask(__name__)
app.vars = {}
#app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
@app.route('/')
def main():
return redirect('/index')
@app.route('/index', methods=['GET', 'POST'])
def index():
if request.method == 'GET':
return render_template('index.html')
from powertrack.api import PowerTrack
from cartodb import CartoDBAPIKey, FileImport
config = ConfigParser.RawConfigParser()
config.read("champions.conf")
ACCOUNT_NAME = config.get('cartodb', 'account_name')
API_KEY = config.get('cartodb', 'api_key')
TABLE_NAME = config.get('cartodb', 'table_name')
START_TIMESTAMP = datetime.strptime(config.get('interval', 'start_timestamp'),
"%Y%m%d%H%M")
END_TIMESTAMP = datetime.strptime(config.get('interval', 'end_timestamp'),
"%Y%m%d%H%M")
p = PowerTrack(api="search")
cdb = CartoDBAPIKey(API_KEY, ACCOUNT_NAME)
categories = [
[
"#HalaMadrid", "#APorLaUndecima", "#RMUCL", "#RMFans", "#RMCity",
"#RmHistory", "@realmadrid", "@realmadriden", "@readmadridfra",
"@realmadridarab", "@realmadridjapan"
],
["#LaUndecima"],
["Zidane"],
["Gareth Bale", "@GarethBale11"],
["Sergio Ramos", "@SergioRamos"],
["Cristiano Ronaldo", "@Cristiano"],
["Keylor Navas", "@NavasKeylor"],
["@officialpepe"],
["Luka Modric", "@lm19official"],
from _settings import *
from cartodb import CartoDBAPIKey, CartoDBException, FileImport
# gitignored secret info
from _secret_info import cartodb_domain, API_KEY
cl = CartoDBAPIKey(API_KEY, cartodb_domain)
tr95 = wp + '/to_cartodb/nyctrees1995.csv'
tr05 = wp + '/to_cartodb/nyctrees2005.csv'
tr15 = wp + '/to_cartodb/nyctrees2015.csv'
fi = FileImport(
tr95, cl, create_vis='true', privacy='public'
) # Import csv file, set privacy as 'link' and create a default viz
fi.run() #https://github.com/CartoDB/cartodb-python
fi = FileImport(
tr05, cl, create_vis='true', privacy='public'
) # Import csv file, set privacy as 'link' and create a default viz
fi.run() #https://github.com/CartoDB/cartodb-python
fi = FileImport(
tr15, cl, create_vis='true', privacy='public'
) # Import csv file, set privacy as 'link' and create a default viz
fi.run() #https://github.com/CartoDB/cartodb-python
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)
#import required modules
import exceptions
import urllib2, json
from cartodb import CartoDBAPIKey, CartoDBException
#cartodb variables
api_key = 'ebd93f0d0daf2ab7d2b31e2449e307cfe0744252'
cartodb_domain = 'troy'
cl = CartoDBAPIKey(api_key, cartodb_domain)
def insert_into_cartodb(sql_query):
try:
# your CartoDB account:
print cl.sql(sql_query)
except CartoDBException as e:
print ("some error ocurred", e)
def get_max_id():
return cl.sql('SELECT MAX(id) FROM humanitarian_response')['rows'][0]['max']
#parse the json
def getResults(data):
# Use the json module to load the string data into a dictionary
api_url = json.loads(data)
for i in api_url["data"]:
#defining the variables that will be pulled into CartoDB
id = i["id"]
label = i["label"]
cluster_id = i["bundles"][0]["id"]
cluster_label = i["bundles"][0]["label"]
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)
def setUp(self):
self.client = CartoDBAPIKey(API_KEY, user, api_version='v2')