GPS TAG Generation

gps_tag_generator.py

import psycopg2, psycopg2.extras

DSN = {
   'dbname':   "xxx", 
   'user':     "xxx", 
   'password': "xxx"}

def main():
    con =  psycopg2.connect(connection_factory=psycopg2.extras.NamedTupleConnection, **DSN)
    cur = con.cursor()

    if False:
        cur.execute("DELETE FROM next_ts")
        con.commit()

        cur.executemany(
            "INSERT INTO next_ts(trip_id, ts, next_ts) VALUES (%s,%s,%s)",
            next_ts_iter(con.cursor())
        )
        con.commit()

    if True:
        # CREATE TABLE gps_tag(trip_id BigInt, t0 BigInt, t1 BigInt, tag varchar(40))

        cur.execute("DELETE FROM gps_tag")
        con.commit()

        cur.executemany(
            "INSERT INTO gps_tag(trip_id, t0, t1, tag) VALUES (%s,%s,%s,%s)",
            tag_iter(con.cursor(), con.cursor())
        )
        con.commit()
    
    con.close()


## PROCEDURES ##

def next_ts_iter(cur):
    count = 0
    cur.execute("""
    SELECT trip_id, ts FROM sensor_gps 
    WHERE trip_id > 170
    ORDER BY trip_id DESC, ts""")

    last_id = -1
    last_ts = -1
    for rec in cur:
        if (last_id != rec.trip_id):
            # new trip starts
            pass

        else: # current trip
            print "TRIP - trip: %d, ts: %d, next_ts: %d" % (rec.trip_id, last_ts, rec.ts)
            yield (rec.trip_id, last_ts, rec.ts)

        last_id = rec.trip_id
        last_ts = rec.ts


def tag_iter(cur1, cur2):
    for trip, t0, t1 in next_ts_iter(cur1):
        cur2.execute("""
        SELECT tag, COUNT(tag) FROM har_annotation WHERE 
        trip_id = %s AND 
        ts BETWEEN %s AND %s
        GROUP BY tag 
        ORDER BY COUNT(tag) DESC
        LIMIT 1
        """, [trip, t0, t1])
        res = cur2.fetchone()
        tag = res.tag if (res != None) else "None"

        print "TAG - trip: %d, t0: %d, t1: %d, tag: %s" % (trip, t0, t1, tag)
        yield (trip, t0, t1, tag)
        
if __name__ == "__main__":
    main()