Created
June 12, 2014 23:14
-
-
Save flibbertigibbet/f158c1d45a3fee79251f to your computer and use it in GitHub Desktop.
Build GeoJSON of CyclePhilly tracks from MySQL database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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