def __init__(self,
db,
key,
svc=VEHICLES_URL,
loc='Portland',
format='json'):
''' call the car2go service, retrieve new positions, and update car position database
'''
# step 0: setup
self.house_num_and_zip_re = re.compile('[0-9]*, 9[0-9]+')
# step 1: new car2go data
self.pos = []
self.url = "{0}?oauth_consumer_key={1}&format={2}&loc={3}".format(
svc, key, format, loc)
print self.url
raw = urllib.urlopen(self.url)
car2go_data = json.load(raw)
# step 2: if we have valid data, update the database
if car2go_data and len(car2go_data['placemarks']) > 0:
session = db.get_session()
# step 2a: clear out the 'latest' flag from the position table (so we know the new service data is the 'latest' positions)
Position.clear_latest_column(session, Car2GoVehicle.identity)
# step 2b: update the positions
for v in car2go_data['placemarks']:
self.append_pos(session, v)
continue
# step 2c: commit the new positions
session.flush()
session.commit()
def __init__(self, db, key, svc=VEHICLES_URL, loc='Portland', format='json'):
''' call the car2go service, retrieve new positions, and update car position database
'''
# step 0: setup
self.house_num_and_zip_re = re.compile('[0-9]*, 9[0-9]+')
# step 1: new car2go data
self.pos = []
self.url = "{0}?oauth_consumer_key={1}&format={2}&loc={3}".format(svc, key, format, loc)
print self.url
raw = urllib.urlopen(self.url)
car2go_data = json.load(raw)
# step 2: if we have valid data, update the database
if car2go_data and len(car2go_data['placemarks']) > 0:
session = db.get_session()
# step 2a: clear out the 'latest' flag from the position table (so we know the new service data is the 'latest' positions)
Position.clear_latest_column(session, Car2GoVehicle.identity)
# step 2b: update the positions
for v in car2go_data['placemarks']:
self.append_pos(session, v)
continue
# step 2c: commit the new positions
session.flush()
session.commit()
def latest_positions_geojson(session):
''' return geojson of latest vehicle positions
'''
positions = q.latest_positions(session)
features = Position.to_geojson_features(positions)
ret_val = features_to_json(features)
return ret_val
def vehicle_position_history_geojson(session, id):
''' return geojson of latest positions
'''
positions = q.position_history(session, id)
features = Position.to_geojson_features(positions)
ret_val = features_to_json(features)
return ret_val
def vehicle_position_history_geojson(session, id):
""" return geojson of latest positions
"""
positions = q.position_history(session, id)
features = Position.to_geojson_features(positions)
ret_val = features_to_json(features)
return ret_val
def nearest_vehicles(session, lon, lat, dist):
''' from the 'latest' positions, find
'''
v = session.query(Position).\
filter(Position.latest == True).\
filter(Position.calc_distance(Position.lon, Position.lat, (lon, lat)) < dist)
return v
def nearest_vehicles(session, lon, lat, dist):
''' from the 'latest' positions, find
'''
v = session.query(Position).\
filter(Position.latest == True).\
filter(Position.calc_distance(Position.lon, Position.lat, (lon, lat)) < dist)
return v
def latest_positions_geojson(session):
""" return geojson of latest vehicle positions
"""
positions = q.latest_positions(session)
features = Position.to_geojson_features(positions)
ret_val = features_to_json(features)
return ret_val
def nearest_positions(session, lon, lat, dist, limit=1000):
''' from ALL positions (latest and historical), find points
'''
p = session.query(Position).filter(
Position.calc_distance(Position.lon, Position.lat,
(lon, lat)) < dist).limit(limit)
return p
def update_zipcar_db(cls, db, pods, vehicles):
""" NOTE: key parameter is being passed around, since that will eventually be needed for Zipcar
"""
session = None
try:
session = db.get_session()
# step 1: remove old stuff....
zlist = session.query(ZipcarVehicle).all()
# note: looping through and calling session.delete(z) is the only way I could get SQLAlchemy to delete the
# FK relational entry to position table.
for z in zlist:
session.delete(z)
session.query(ZipcarPod).delete()
session.commit()
session.flush()
# step 2: add pods
for p in pods:
session.add(p)
session.commit()
session.flush()
# step 3: add vehicles
for v in vehicles:
session.add(v)
session.commit()
session.flush()
# step 4: add position data to each vehicle
Position.clear_latest_column(session, ZipcarVehicle.identity)
for v in vehicles:
v.update_position(session, v.lat, v.lon, v.street, v.city, v.state, v.zip, 1)
except Exception as err:
log.exception('Exception: {0}'.format(err))
pass
finally:
if session:
# step 3: commit stuff...
session.commit()
session.flush()
def vehicle_information(session, id):
''' return vehicle data as json
'''
v = q.vehicle_information(session, id)
v = v.to_dict()
p = q.position_history(session, id)
p = Position.to_dict_list(p)
v['positions'] = p
ret_val = json.dumps(v)
return ret_val
def vehicle_information(session, id):
""" return vehicle data as json
"""
v = q.vehicle_information(session, id)
v = v.to_dict()
p = q.position_history(session, id)
p = Position.to_dict_list(p)
v["positions"] = p
ret_val = json.dumps(v)
return ret_val
def update_zipcar_db(cls, db, pods, vehicles):
''' NOTE: key parameter is being passed around, since that will eventually be needed for Zipcar
'''
session = None
try:
session = db.get_session()
# step 1: remove old stuff....
zlist = session.query(ZipcarVehicle).all()
# note: looping through and calling session.delete(z) is the only way I could get SQLAlchemy to delete the
# FK relational entry to position table.
for z in zlist:
session.delete(z)
session.query(ZipcarPod).delete()
session.commit()
session.flush()
# step 2: add pods
for p in pods:
session.add(p)
session.commit()
session.flush()
# step 3: add vehicles
for v in vehicles:
session.add(v)
session.commit()
session.flush()
# step 4: add position data to each vehicle
Position.clear_latest_column(session, ZipcarVehicle.identity)
for v in vehicles:
v.update_position(session, v.lat, v.lon, v.street, v.city,
v.state, v.zip, 1)
except Exception, err:
log.exception('Exception: {0}'.format(err))
pass
def update_position(self, session, lat, lon, address=None, city=None, state=None, zipcode=None, time_span=144):
""" query the db for a position for this vehicle ... if the vehicle appears to be parked in the
same place as an earlier update, update the
NOTE: the position add/update needs to be committed to the db by the caller of this method
"""
# step 0: cast some variables
pid = str(self.id)
lat = round(lat, 6)
lon = round(lon, 6)
# step 1: get position object from db ...criteria is to find last position
# update within an hour, and the car hasn't moved lat,lon
hours_ago = datetime.datetime.now() - datetime.timedelta(hours=time_span)
p = None
try:
q = session.query(Position).filter(
and_(
Position.vehicle_id == pid,
Position.updated >= hours_ago,
Position.lat == lat,
Position.lon == lon,
)
)
p = q.first()
#import pdb; pdb.set_trace()
except Exception as err:
log.exception('Exception: {0}'.format(err))
# step 2: we didn't find an existing position in the Position history table, so add a new one
try:
if p is None:
p = Position()
p.vehicle_id = pid
p.carshare_co = str(self.carshare_company)
p.set_position(lat, lon, address, city, state, zipcode)
session.add(p)
else:
# step 3: update the position record if need be
p.set_position(lat, lon, address, city, state, zipcode)
except Exception as err:
log.exception('Exception: {0}, committing position to db for vehicle id={1}, lat={2}, lon={3}'.format(err, p.vehicle_id, lat, lon))
session.rollback()
finally:
try:
session.commit()
session.flush()
except:
session.rollback()
return p
def nearest_positions(session, lon, lat, dist, limit=1000):
''' from ALL positions (latest and historical), find points
'''
p = session.query(Position).filter(Position.calc_distance(Position.lon, Position.lat, (lon, lat)) < dist).limit(limit)
return p
def main():
p = Position()
class Vehicle(Base):
__tablename__ = 'vehicles'
id = Column(String, primary_key=True, nullable=False)
carshare_company = Column(String, nullable=False)
name = Column(String)
created = Column(DateTime, default=datetime.datetime.now())
updated = Column(DateTime, default=datetime.datetime.now())
__mapper_args__ = {
'polymorphic_on': carshare_company,
'polymorphic_identity': __tablename__,
'with_polymorphic':'*'
}
@abc.abstractmethod
def set_attributes(self, dict):
''' copy known values from the dict into this object, then update the timestamp
'''
raise NotImplementedError("Please implement this method")
def update_position(self, session, lat, lon, address=None, city=None, state=None, zipcode=None, time_span=144):
''' query the db for a position for this vehicle ... if the vehicle appears to be parked in the
same place as an earlier update, update the
NOTE: the position add/update needs to be committed to the db by the caller of this method
'''
# step 0: cast some variables
pid = str(self.id)
lat = round(lat, 6)
lon = round(lon, 6)
# step 1: get position object from db ...criteria is to find last position
# update within an hour, and the car hasn't moved lat,lon
hours_ago = datetime.datetime.now() - datetime.timedelta(hours=time_span)
p = None
try:
q = session.query(Position).filter(
and_(
Position.vehicle_id == pid,
Position.updated >= hours_ago,
Position.lat == lat,
Position.lon == lon,
)
)
p = q.first()
#import pdb; pdb.set_trace()
except Exception, err:
log.exception('Exception: {0}'.format(err))
# step 2: we didn't find an existing position in the Position history table, so add a new one
try:
if p is None:
p = Position()
p.vehicle_id = pid
p.carshare_co = str(self.carshare_company)
p.set_position(lat, lon, address, city, state, zipcode)
session.add(p)
else:
# step 3: update the position record if need be
p.set_position(lat, lon, address, city, state, zipcode)
except Exception, err:
log.exception('Exception: {0}, committing position to db for vehicle id={1}, lat={2}, lon={3}'.format(err, p.vehicle_id, lat, lon))
session.rollback()
