Raccolta dati Rovereto 2012

griddy.py

#!/usr/bin/env python
#-*- coding:utf-8 -*-

# QUESTO È LO SCRIPT CHE HO USATO PER CREARE LA TABELLA INIZIALMENTE

SPKEY = "0Ap3raRAXNEtldE9WdDJtbHBzb3JxV01hTFF5cEQ4OXc"
UNAME = "filosottile.wiki"
PASSW = "xxxxxxxxxxxxxxxx"

DATA = """ *** I DATI RESTITUITI DA FACEBOOK *** """

import json
data = json.loads(DATA)

import gdata.spreadsheet.service
gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = UNAME
gd_client.password = PASSW
gd_client.ProgrammaticLogin()

for u in data['data']:
    d = {}
    if u['current_location']: 
        d = { u'città': u['current_location']['city'],
              u'regione': u['current_location']['state'] }
    d['compleanno'] = u['birthday']
    d['nome'] = u['name']
    gd_client.InsertRow(d, SPKEY, 'od6')

mappy.py

#!/usr/bin/env python
#-*- coding:utf-8 -*-

from geopy import geocoders  
g = geocoders.Google(domain='maps.google.it')

import simplekml
kml = simplekml.Kml()

import time

SPKEY = "0Ap3raRAXNEtldE9WdDJtbHBzb3JxV01hTFF5cEQ4OXc"
UNAME = "filosottile.wiki"
PASSW = "xxxxxxxxxxxxxxxx"
import gdata.spreadsheet.service
gd_client = gdata.spreadsheet.service.SpreadsheetsService()
gd_client.email = UNAME
gd_client.password = PASSW
gd_client.ProgrammaticLogin()
grid = {}
feed = gd_client.GetCellsFeed(SPKEY, 'od6')
for entry in feed.entry:
    col = entry.title.text[0]
    row = entry.title.text[1:]
    if not row in grid: grid[row] = {}
    grid[row][col] = entry.content.text
del grid['1']

for r in grid.values():
    if not 'C' in r: continue
    cit = u', '.join((r['C'].decode('utf8'), r['B'].decode('utf8'), 'Italy'))
    print cit
    place, (lat, lng) = g.geocode(cit.encode('utf8'), exactly_one=False)[0]
    print r['A'], place, lat, lng
    kml.newpoint(name=r['A'].replace('è', 'e')
                            .replace('é', 'e')
                            .replace('ì', 'i')
                            .replace('ù', 'u')
                            .replace('à', 'a')
                            .replace('ò', 'o'), coords=[(lng, lat)])
    time.sleep(2)
    
kml.save("mappy.kml")

query.sql

/* Questa la query che ho usato per ottenere i dati da FB */
select birthday, current_location, name from user where uid in (select uid from group_member where gid = 411629912227803)