def add_tz_country_location(xmlimglist_plus_db_details,wteapi_key,infomarker_id,database):
#find associated trackpoints of images
session=database.db_session()
db_imageinfo=database.db_imageinfo
db_trackpoint=database.db_trackpoint
for imgdetail in xmlimglist_plus_db_details:
trkptid=imgdetail.imageinfo_detail.trackpoint_id
q = session.query(db_trackpoint).filter(db_trackpoint.id==trkptid)
if q.count() == 1:
trackpoint=q.one()
lat=trackpoint.latitude
lon=trackpoint.longitude
country=get_country(lat,lon,database)
location=talk2flickr.findplace(lat,lon,11)
tz_detail=get_timezone(database,lat,lon,trackpoint.timestamp,wteapi_key)
trackpoint.timezone_id=tz_detail.id
trackpoint.country_id=country.iso_numcode
trackpoint.location=location
session.commit()
print "Added trkpt-details for imgid:"+str(imgdetail.imageinfo_detail.id)+", trkptid:"+str(trackpoint.id)+", tzabb:"+tz_detail.abbreviation+", location:"+location
else:
print "No trackpoint for this image - therefore no timezone, location or country-details :-("
#we repeat finding trackpoint-details for the infomarker
q = session.query(db_trackpoint).filter(db_trackpoint.id==infomarker_id)
infomarker=q.one()
lat=infomarker.latitude
lon=infomarker.longitude
country=get_country(lat,lon,database)
location=talk2flickr.findplace(lat,lon,11)
tz_detail=get_timezone(database,lat,lon,infomarker.timestamp,wteapi_key)
infomarker.timezone_id=tz_detail.id
infomarker.country_id=country.iso_numcode
infomarker.location=location
session.commit()
q=session.query(db_trackpoint).filter(db_trackpoint.track_id==infomarker.track_id).order_by(asc(db_trackpoint.timestamp))
trackpoint=q.first()
lastlocation=talk2flickr.findplace(trackpoint.latitude,trackpoint.longitude,8)
print 'location of last trackpoint(used in pylons/log): '+lastlocation
if trackpoint.location==None:
trackpoint.location=lastlocation
session.commit()
else:
print 'last trackpoint already has a location-content, not updateing: '+trackpoint.location
def get_country(lat,lon,database):
session=database.db_session()
db_country=database.db_country
accuracy=1 #level of region-detail in flickr, 1 is world, 8 is district
flickr_countryname=talk2flickr.findplace(lat,lon,accuracy)
print "flickr_countryname: "+str(flickr_countryname)
if flickr_countryname !=None:
query_country=session.query(db_country).filter(db_country.flickr_countryname==flickr_countryname)
country=query_country.one()
print 'country found - id:'+ str(country.iso_numcode) + ' - details:' + str(country)
else:
print "no country found, returning dummy country!"
query_country=session.query(db_country).filter(db_country.iso_numcode==1)
country=query_country.one()
return country
import os
import hashlib
import sys
sys.path.append('/root/poab')
from image_functions import sortedlistdir as sortedlistdir,tags2flickrndb as tags2flickrndb
from geo_functions import get_country as get_country,get_timezone as get_timezone
from fill_photos_db import resize_990 as resize_990
import talk2flickr
import db_functions
from getfromxml import parsexml as parsexml,getcredentials as getcredentials
if __name__ == "__main__":
pg_user,pg_passwd,flickrapi_key,flickrapi_secret,wteapi_key=getcredentials('/root/poab/credentials.ini')
database=db_functions.initdatabase(pg_user,pg_passwd)
session=database.db_session()
db_track=database.db_track
db_trackpoint=database.db_trackpoint
q=session.query(db_track)
for track in q.all():
q=session.query(db_trackpoint).filter(db_trackpoint.track_id==track.id).order_by(asc(db_trackpoint.timestamp))
trackpoint=q.first()
print trackpoint.id
location=talk2flickr.findplace(trackpoint.latitude,trackpoint.longitude,8)
if trackpoint.location==None:
trackpoint.location=location
session.commit()
else:
print trackpoint.location
trackpoint.location=location
session.commit()
def gpx2database(trackpath,wteapi_key,database,trk_color,lat,lon,createdate):
print 'FUNCTION GPX2DATABASE'
session=database.db_session()
db_track=database.db_track
db_trackpoint=database.db_trackpoint
db_country=database.db_country
trk_ptnum=dict()
trk_ptnum[0]=0
trk_distance=dict()
trk_distance[0]=0
trk_span=dict()
trk_span[0]=timedelta(hours=0,minutes=0,seconds=0)
trkptlist=list()
latlonlist=list()
if os.listdir(trackpath)==[]:
print 'No Trackfile, checking for single trackpoint'
if lat and lon:
country=get_country(lat,lon,database)
location=talk2flickr.findplace(lat,lon,11)
q = session.query(db_trackpoint).filter(and_(db_trackpoint.latitude==lat,db_trackpoint.longitude==lon,db_trackpoint.timestamp==createdate))
if q.count()>0:
print 'Trackpoint already exists: '+str(q.one().id)
else:
tz_detail=get_timezone(database,lat,lon,createdate,wteapi_key)
session.add(db_trackpoint(None,tz_detail.id,country.iso_numcode,lat,lon,None,None,None,None,None,createdate,True,location))
session.commit()
if q.count() == 1:
trackpoint=q.one()
infomarker_id=trackpoint.id
else:
for gpxfile in os.listdir(trackpath):
if gpxfile.lower().endswith('.gpx'):
tree = etree.parse(trackpath+gpxfile)
gpx_ns = "http://www.topografix.com/GPX/1/1"
ext_ns = "http://gps.wintec.tw/xsd/"
root = tree.getroot()
fulltrack = root.getiterator("{%s}trk"%gpx_ns)
trackSegments = root.getiterator("{%s}trkseg"%gpx_ns)
i=1
for trk in fulltrack:
print 'gpxfile trk no.' + str(i)
track_desc=trk.find('{%s}desc'% gpx_ns).text #get the desc-tag from the gpx-file
trk_ptnum[i]=trk_ptnum[i-1]+int(track_desc.split()[3][:-1]) #cut out the value from the string e.g. "Total track points: 112."
trk_rspan=track_desc.split()[6][:-1] #cut out the value from the string e.g. "Total time: 0h18m25s."
trk_distance[i]=trk_distance[i-1]+float(track_desc.split()[8][:-2]) #cut out the value from the string e.g. "Journey: 4.813Km"
trk_tspan=re.compile(r'(?P<h>\d+)h(?P<m>\d+)m(?P<s>\d+)s').match(trk_rspan) #find the values of h,m,s and add them to "groups"
trk_span[i]=trk_span[i-1]+timedelta(hours=int(trk_tspan.group("h")), minutes=int(trk_tspan.group("m")),seconds=int(trk_tspan.group("s"))) #get the values from groups "h","m","s" and save them in a timeformat
i=i+1
for trackSegment in trackSegments:
for trackPoint in trackSegment:
lat=trackPoint.attrib['lat']
lon=trackPoint.attrib['lon']
altitude=trackPoint.find('{%s}ele'% gpx_ns).text
time=trackPoint.find('{%s}time'% gpx_ns).text.replace('T',' ')[:-1] #replace the "T" with " " and remove the "Z" from the end of the string
desc=trackPoint.find('{%s}desc'% gpx_ns).text.split(', ') #split the description to get "speed" and "direction"-values
velocity=0
direction=0
for value in desc:
if value.split('=')[0] == 'Speed':
velocity=value.split('=')[1][:-4]
elif value.split('=')[0] == 'Course':
direction=value.split('=')[1][:-4]
try:
temperature=trackPoint.find("{%s}extensions/{%s}TrackPointExtension/{%s}Temperature" % (gpx_ns,ext_ns,ext_ns)).text
pressure=trackPoint.find("{%s}extensions/{%s}TrackPointExtension/{%s}Pressure" % (gpx_ns,ext_ns,ext_ns)).text
except AttributeError:
temperature=None
pressure=None
#print lat,lon,time
trkptlist.append((lat,lon,altitude,velocity,temperature,direction,pressure,time))
latlonlist.append((float(lat),float(lon)))
#get the last value of each "desc"-segment, this value represents the total from the several gpx-files
trk_ptnumtotal=trk_ptnum[i-1]
trk_distancetotal=trk_distance[i-1]
trk_spantotal=trk_span[i-1]
print 'Total Trackpoints found: '+str(trk_ptnumtotal)
#create an encoded polyline from the latitude-longitude-list
gencpoly=glineenc.encode_pairs(latlonlist)
trkpt_firsttimestamp=trkptlist[0][7] #first timestamp in the trackpoint-list
query_track=session.query(db_track).filter(and_(db_track.date==trkpt_firsttimestamp,db_track.trkptnum==trk_ptnumtotal,db_track.distance==trk_distancetotal,db_track.timespan==trk_spantotal,db_track.gencpoly_pts==gencpoly[0].replace('\\','\\\\'),db_track.gencpoly_levels==gencpoly[1]))
if query_track.count() == 1:
for detail in query_track.all():
track_detail=detail
print 'track found - id:'+ str(track_detail.id)# + ' - details:' + str(track_detail)
elif query_track.count() > 1:
for detail in query_track.all():
track_detail=detail
print 'more than one track found! - id:'#+ str(track_detail.id) + ' - details:' + str(track_detail)
else:
session.add(db_track(trkpt_firsttimestamp,trk_ptnumtotal,trk_distancetotal,trk_spantotal,gencpoly[0].replace('\\','\\\\'),gencpoly[1],trk_color,None,None,None,None))
session.commit()
for detail in query_track.all():
track_detail=detail
print 'track created! - id:'+ str(track_detail.id)# + ' - details:' + str(track_detail)
i=0
print "\nAdding trackpoints to database:\n"
pb=progress.progressbarClass(track_detail.trkptnum,"=")
for trkpt in trkptlist:
lat,lon,altitude,velocity,temperature,direction,pressure,time=trkptlist[i]
query_trackpoint=session.query(db_trackpoint).filter(and_(db_trackpoint.track_id==track_detail.id,db_trackpoint.latitude==lat,db_trackpoint.longitude==lon,db_trackpoint.timestamp==time))
if query_trackpoint.count() == 1:
for detail in query_trackpoint.all():
trkpt_detail=detail
#print 'Trackpoint already exists - id:'+ str(trkpt_detail.id) + ' details:' + str(trkpt_detail)
elif query_trackpoint.count() > 1:
for detail in query_trackpoint.all():
trkpt_detail=detail
print 'trackpoint duplicate found! - id:'+ str(trkpt_detail.id) + ' - details:' + str(trkpt_detail)
else:
#trackpoints are unique, insert them now
session.add(db_trackpoint(track_detail.id,None,None,lat,lon,float(altitude),velocity,temperature,direction,pressure,time,False,None))
session.commit()
for detail in query_trackpoint.all():
trkpt_detail=detail
#print 'trackpoint added! - id:'+ str(trkpt_detail.id) + ' - details:' + str(trkpt_detail)
#in the middle of the track, we set the current infomarker.trackpoint_id to true as this is our infomarker-point
if i==track_detail.trkptnum/2:
for column in query_trackpoint.all():
column.infomarker=True
session.commit()
infomarker_id=trkpt_detail.id
pb.progress(i)
i=i+1
print "infomarker_id="+str(infomarker_id)
return infomarker_id
# for line in q.all():
# if line.infomarker_id in infomarkerlist:
# pass
# else:
# infomarkerlist.append(line.infomarker_id)
#
# db_log=database.db_log
# q=session.query(db_log).filter(db_log.infomarker_id!=None)
# for line in q.all():
# print line.infomarker_id
# if line.infomarker_id in infomarkerlist:
# pass
# else:
# infomarkerlist.append(line.infomarker_id)
#
#
pg_user,pg_passwd,flickrapi_key,flickrapi_secret,wteapi_key=getcredentials('/root/poab/credentials.ini')
database=db_functions.initdatabase(pg_user,pg_passwd)
session=database.db_session()
db_trackpoint=database.db_trackpoint
q=session.query(db_trackpoint).filter(db_trackpoint.location!=None)
for line in q.all():
print line.id,line.timestamp
print 'old:'+line.location
location=talk2flickr.findplace(line.latitude,line.longitude,16)
print 'new:'+location+'\n'
# time.sleep(1)
line.location=location
session.commit()
def add2flickrndb(imageinfo,resizepath,image,database,topic,photosetname,phototitle,xmlimglist,xmltaglist,flickrapi_key,flickrapi_secret):
session=database.db_session()
db_trackpoint=database.db_trackpoint
db_imageinfo=database.db_imageinfo
db_image2tag=database.db_image2tag
db_phototag=database.db_phototag
db_photosets=database.db_photosets
db_trackpoint=database.db_trackpoint
geoinfo=os.popen("/usr/bin/exiftool -specialinstructions "+resizepath+image+"|/usr/bin/awk {'print $5 $7'}").readlines()
print geoinfo
if geoinfo:
latitude,longitude=geoinfo[0].strip('\n').split(',',1)
query_trackpoint=session.query(db_trackpoint).filter(and_(db_trackpoint.latitude==latitude,db_trackpoint.longitude==longitude))
trackpoint_id=query_trackpoint.first().id
#no trackpoint was near this image, so we have no
#respective link for the db
else:
trackpoint_id=None
print 'photo linked to trackpoint id:'+str(trackpoint_id)
q=session.query(db_imageinfo).filter(db_imageinfo.id==imageinfo.id)
imageinfo=q.one()
imageinfo.flickrphotoid=talk2flickr.imgupload(resizepath+image,phototitle,imageinfo.flickrdescription,'')
print imageinfo.flickrphotoid
try:
#sets the geolocation of the newly uploaded picture on flickr
talk2flickr.setlocation(imageinfo.flickrphotoid,latitude,longitude,'16')
except UnboundLocalError:
print 'No geolocation in photo-exim-data :-('
imageinfo.flickrfarm,imageinfo.flickrserver,imageinfo.flickrphotoid,imageinfo.flickrsecret,originalsecret,imageinfo.originalformat,imageinfo.flickrdatetaken,flickr_tags,url,title,flickrdescription = talk2flickr.getimginfo(imageinfo.flickrphotoid)
imageexif=talk2flickr.getexif(imageinfo.flickrphotoid,imageinfo.flickrsecret)
imageinfo.aperture=imageexif['Aperture_clean']
imageinfo.shutter=imageexif['Exposure_clean']
imageinfo.focal_length=imageexif['Focal Length_clean']
imageinfo.iso=imageexif['ISO Speed_raw']
imageinfo.online=True
imageinfo.imgname='/'+resizepath.split('/',2)[2]+image
imageinfo.trackpoint_id=trackpoint_id
session.commit()
#add photo to existing photoset on flickr
q_photoset=session.query(db_photosets).filter(db_photosets.id==imageinfo.photoset_id)
photoset_detail=q_photoset.one()
talk2flickr.photoset_addphoto(photoset_detail.flickrsetid,imageinfo.flickrphotoid)
#update photocount
owner,primary,count,title,description=talk2flickr.get_photosetinfo(photoset_detail.flickrsetid)
if count != photoset_detail.flickrphotocount:
photoset_detail.flickrphotocount=count
session.commit()
#add tags
tags2flickrndb(imageinfo.id,imageinfo.flickrphotoid,xmltaglist,database)
#update trackpoint
q = session.query(db_trackpoint).filter(db_trackpoint.id==trackpoint_id)
if q.count() == 1:
trackpoint=q.one()
lat=trackpoint.latitude
lon=trackpoint.longitude
country=get_country(lat,lon,database)
location=talk2flickr.findplace(lat,lon,8)
tz_detail=get_timezone(database,lat,lon,trackpoint.timestamp,wteapi_key)
trackpoint.timezone_id=tz_detail.id
trackpoint.country_id=country.iso_numcode
trackpoint.location=location
session.commit()
print "Added trkpt-details for imgid:"+str(imageinfo.id)+", trkptid:"+str(trackpoint.id)+", tzabb:"+tz_detail.abbreviation+", location:"+location
else:
print "No trackpoint for this image - therefore no timezone, location or country-details :-("
session.close
if int(p.search(guid).group("guid"))<20968436430:
print 'guid less than first relevant entry, ignoring'
else:
q = session.query(db_log).filter(db_log.content==content)
if q.count()>0:
print 'tweet already in db: '+date,content
else:
p=re.compile("(?P<lat>-{0,1}\d{1,5}),(?P<lon>-{0,1}\d{1,6})")
if p.search(content):
p_lat=p.search(content).group("lat")
p_lon=p.search(content).group("lon")
#we split the regex-match at the last two digits and insert a colon
lat=p_lat[:-3]+"."+p_lat[-3:]
lon=p_lon[:-3]+"."+p_lon[-3:]
country=get_country(lat,lon,database)
location=talk2flickr.findplace(lat,lon,11)
print lat,lon,country.iso_numcode,location
q = session.query(db_trackpoint).filter(and_(db_trackpoint.latitude==lat,db_trackpoint.longitude==lon,db_trackpoint.timestamp==date))
if q.count()>0:
print 'Trackpoint already exists: '+str(q.one().id)
else:
tz_detail=get_timezone(database,lat,lon,datetime.datetime.strptime(date,'%Y-%m-%d %H:%M:%S'),wteapi_key)
session.add(db_trackpoint(None,tz_detail.id,country.iso_numcode,lat,lon,None,None,None,None,None,date,True,location))
session.commit()
if q.count() == 1:
trackpoint=q.one()
content=content.replace('derreisende: ','')
content=content.replace(p_lat+','+p_lon,'<a rel="map_colorbox" href="/track/simple/'+str(trackpoint.id)+'" target="_blank">'+lat+', '+lon+'</a>')
q = session.query(db_log).filter(db_log.topic==guid)
if q.count()>0:
print 'tweet already in db: '+date,content
