Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
GPS TAG Generation
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment