Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Build GeoJSON of CyclePhilly tracks from MySQL database
#!/usr/bin/env python
import pymysql
import geojson
from geojson import FeatureCollection, Feature, LineString, Point
dt_fmt = '%Y-%m-%d %H:%M'
db = pymysql.connect(host='', user='', passwd='', db='')
c = db.cursor()
c.execute('select max(id) from trip;')
results = c.fetchone()
max_id = results[0]
print('going to process %d trips...' % max_id)
next_trip = 1
my_feats = [] # list of features
while next_trip <= max_id:
print('processing trip id #%d' % next_trip)
got_trip = c.execute('select * from trip where id=%s', next_trip)
if not got_trip:
print('did not find trip #%d' % next_trip)
next_trip += 1
continue
trip_info = c.fetchone()
user_id = trip_info[1]
trip_purpose = trip_info[2]
trip_notes = trip_info[3]
got_coords = c.execute('select recorded, latitude, longitude from coord where trip_id=%s order by recorded asc;', next_trip)
if not got_coords:
print('did not find any coordinates for trip #%d' % next_trip)
next_trip += 1
continue
lnstr = [] # coordinates for trip linestring
start_time = None
end_time = None
coords = c.fetchall()
start_time = coords[0][0].strftime(dt_fmt)
end_time = coords[len(coords)-1][0].strftime(dt_fmt)
props = {'start': start_time,
'end': end_time,
'purpose': trip_purpose,
'notes': trip_notes,
'user': user_id}
for coord in coords:
lat = coord[1]
lon = coord[2]
lnstr.append((lon, lat))
line = LineString(lnstr)
feat = Feature(geometry=line, id=next_trip, properties=props)
my_feats.append(feat)
line = None
feat = None
props = None
next_trip += 1
c.close()
coll = FeatureCollection(my_feats)
outf = open('tripgeo.json', 'wb')
outf.write(geojson.dumps(coll, sort_keys=True))
outf.close()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment