Genealogy Line Work

generate_lines.py

import urllib2
import urllib

base_url = 'http://lucasnad27.cartodb.com/api/v2/sql'
api_key = 'none of your business'
delete_sql = urllib.quote('delete from CONNECTORS')
insert_sql = urllib.quote('Insert into connectors (the_geom,person_id) select ST_MakeLine(points), c.person_id from (select n.the_geom as points, n.person_id from nodes as n order by start_date) c group by c.person_id')

req = urllib2.Request('%s/?q=%s&api_key=%s' % (base_url, delete_sql, api_key))
response = urllib2.urlopen(req)
print response.read()

req = urllib2.Request('%s/?q=%s&api_key=%s' % (base_url, insert_sql, api_key))
response = urllib2.urlopen(req)
print response.read()

generate_lines.sql

Insert into connectors (the_geom,person_id) 
select 
  ST_MakeLine(points), 
  c.person_id from 
(
select 
  n.the_geom as points, 
  n.person_id 
from nodes as n 
order by start_date
) c 
group by c.person_id