# built/tested with python 3.3 on pollock 

# Note that this performs a nonsensical update to the data, it's only an example.

import psycopg2

connectionString = "host='hopper.cluster.earlham.edu' dbname='field_science' user='charliep' password='test'"
connection = psycopg2.connect(connectionString)
	
cursor1 = connection.cursor()
sqlStatement1 = "\
	select serial_number, exif_focallen, exif_absolutealtitude, exif_cameraroll, exif_camerapitch, exif_gpslatitude, exif_gpslongitude \
	from image_metadata \
	where exif_directory = 'Iceland-2016/images-structured/Skalanes/Skalanes_Survey_2016'"
cursor1.execute(sqlStatement1)

cursor2 = connection.cursor()
sqlStatement2 = "\
	update image_metadata set nw_cornerlatitude = %s, nw_cornerlongitude = %s, ne_cornerlatitude = %s, ne_cornerlongitude = %s, sw_cornerlatitude = %s, sw_cornerlongitude = %s, se_cornerlatitude = %s, se_cornerlongitude = %s where serial_number = %s"

for record in cursor1:
#	print(record)
	serial = record[0]
    xsensor = 6.16   #width of sensor in mm
    ysensor = 4.62   #height of sensor in mm https://forum.dji.com/thread-28597-1-1.html
    focallen = record[1]   #focal length of lens in mm
    altitude = record[2]   #height of drone (absolute or relative?) in m; leaning towards absolute
    xgimbal = record[3]   #x-axis gimbal angle (camera roll)
    ygimbal = record[4]   #y-axis gimbal angle (camera pitch) +/-90???
    GPSlat0 = record[5]  #65 deg 17' 38.24" N
    GPSlong0 = record[6] #13 deg 42' 10.65" W

    earthCirc = 6378137
	
    #calculate field of view
    fovwidth = 2 * degrees(atan(xsensor/(2*focallen)))
    fovheight = 2 * degrees(atan(ysensor/(2*focallen)))
    
    #calculate the distances
    drone2bottom = altitude * (tan(radians(xgimbal - .5 * fovwidth)))
    drone2top = altitude * (tan(radians(xgimbal + .5 * fovwidth)))
    drone2left = altitude * (tan(radians(ygimbal - .5 * fovheight)))
    drone2right = altitude * (tan(radians(ygimbal + .5 * fovheight)))
    
    #calculate the footprint
    footprintHeight = drone2right - drone2left
    footprintWidth = drone2top - drone2bottom
    
    #GPS coordinates of image

    # NW Coordinates
    NW_CornerLatitude = GPSlat0 + (180/pi) * (drone2right/earthCirc)
    NW_CornerLongitude = GPSlong0 + (180/pi) * (drone2top/earthCirc)/cos(GPSlat0)

    # NE Coordinates
    NE_cornerLatitude = GPSlat0 + (180/pi) * (drone2left/earthCirc)
    NE_CornerLongitude = GPSlong0 + (180/pi) * (drone2top/earthCirc)/cos(GPSlat0)

    # SW Coordinates
    SW_cornerLatitude = GPSlat0 + (180/pi) * (drone2right/earthCirc)
    SW_CornerLongitude = GPSlong0 + (180/pi) * (drone2bottom/earthCirc)/cos(GPSlat0)

    # SE Coordinates
    SE_cornerLatitude = GPSlat0 + (180/pi) * (drone2left/earthCirc)
    SE_CornerLongitude = GPSlong0 + (180/pi) * (drone2bottom/earthCirc)/cos(GPSlat0)
    
#	print(NECornerLatitude)
	cursor2.execute(sqlStatement2, (NWCornerLatitude, NWCornerLongitude, NECornerLatitude, NECornerLongitude, SWCornerLatitude, SWCornerLongitude, SECornerLatitude, SWCornerLongitude, serial))
	connection.commit()

cursor1.close()
cursor2.close()
connection.close()	
exit()