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