Build GeoJSON of CyclePhilly tracks from MySQL database

cyclephilly_geojson_generator.py

#!/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()