import psycopg2 #the python postgres query library
import numpy as np #Array mathematics
import matplotlib.pyplot as plt #plotting environment
from matplotlib import rc #import the subfunction 'rc' from matplot so that graphics can be  controlled
rc('font', family='serif')
import scipy.ndimage as nd #Supports multidimensional image processing
from __future__ import print_function #For Python2 print compatibility comment out if causing a problem in python 3

#Retrieve data from the database
#set up connection string to Postgres
connection_string ="dbname='dart_public' user='dart_public_ro' host='dartportal.leeds.ac.uk' password=''"

#connect to database
try:
    conn = psycopg2.connect(connection_string)
except:
    print ("I am unable to connect to the database")

cur = conn.cursor()



#set up the SQL query
inputSQL = """SELECT 
  geophysics_s_local_survey.eastinglocal, 
  geophysics_s_local_survey.northinglocal, 
  geophysics_s_local_survey.measurementvalue
FROM 
  public.geophysics_s_local_survey
WHERE 
  geophysics_s_local_survey.sensorconfiguration = 'B' AND 
  geophysics_s_local_survey.observationtimestamp = '2012-03-21' AND 
  geophysics_s_local_survey.dartfieldcode = 'HHCC';"""
#2012-03-21 HHCC
#2011-09-28 DDCF

'''
#set up the SQL query
inputSQL = """SELECT 
  geophysics_s_local_survey.eastinglocal, 
  geophysics_s_local_survey.northinglocal, 
  geophysics_s_local_survey.measurementvalue
FROM 
  public.geophysics_s_local_survey
WHERE 
  geophysics_s_local_survey.sensorconfiguration = 'VCPIB' AND 
  geophysics_s_local_survey.observationtimestamp = '2012-09-27' AND 
  geophysics_s_local_survey.dartfieldcode = 'HHCC';"""
'''
#run query on the database
cur.execute(inputSQL)

#place query results into list called rows
rows = cur.fetchall()
print ("\nShow me the data:\n")
#iterate on a row by row basis over the list rows and print out the result
for row in rows:
    print ("   ", row)

#read the data into a list
data = np.array(rows)
print ('the data as a list')
print (data)

#Display unly the third column in the geophysics data
data[:,2]


#restructure the array into a 20 x 20 matrix (appropriate for this data)
#although not sure if the orientation is right
griddata = data[:,2].reshape(20,20)


#print data
print('The data recast as in a 20x20 grid array')
print (griddata)

#Plot the data
plt.imshow(griddata,origin='lower')
#plt.imshow(griddata)

plt.gray()
cb=plt.colorbar()
cb.set_label('Value Range')
plt.xlabel('GridEast')
plt.ylabel('GridNorth')
plt.suptitle('Raw data')
plt.show()

print('Total number of elements in INPUT data: ', size(data[:,2])) #search on third column
print('Number of dimensions for INPUT data: ',ndim(data[:,2]))
print('SQRT of total number of elements in INPUT data: ', sqrt(size(data[:,2])))

griddata = data[:,2].reshape(sqrt(size(data[:,2])),sqrt(size(data[:,2])))

print (griddata)


print('Total number of elements in OUTPUT data: ', size(griddata))
print('Number of dimensions for OUTPUT data: ',ndim(griddata))
print('SQRT of total number of elements in OUTPUT data: ', sqrt(size(griddata)))
print('The data recast as in a ', sqrt(size(griddata)), ' x ', sqrt(size(griddata)), ' grid array')



#Calculate derivatives
gridSobel = nd.sobel(griddata)
gridLaplace = nd.laplace(griddata)
gridPrewitt = nd.prewitt(griddata)
gridGaussian = nd.gaussian_filter(griddata,1)
gridMinimum = nd.minimum_filter(griddata,size=(3,3))

#Plot a derivative
plt.imshow(gridGaussian,origin='lower')
plt.gray()
#show image
cb=plt.colorbar()
cb.set_label('Value Range')
plt.xlabel('GridEast')
plt.ylabel('GridNorth')
plt.suptitle('Raw data')
plt.show()

#The data and derivatives
plt.subplot(1,3,2)
plt.imshow(griddata,origin='lower')
plt.gray()
cb=plt.colorbar()
cb.set_label('Value Range')
plt.xlabel('GridEast')
plt.ylabel('GridNorth')
plt.title('Raw data')


plt.subplot(3,3,1)
plt.imshow(gridSobel,origin='lower')
plt.gray()
plt.title('Sobel filter')

plt.subplot(3,3,3)
plt.imshow(gridLaplace,origin='lower')
plt.gray()
plt.title('Laplace filter')

plt.subplot(3,3,4)
plt.imshow(gridPrewitt,origin='lower')
plt.gray()
plt.title('Prewitt filter')


plt.subplot(3,3,6)
plt.imshow(gridGaussian,origin='lower')
plt.gray()
plt.title('Gaussian filter')

plt.subplot(3,3,7)
plt.imshow(gridMinimum,origin='lower')
plt.gray()
plt.title('3x3 minimum filter')

plt.subplot(3,3,9)
plt.imshow(griddata-gridGaussian,origin='lower')
plt.gray()
plt.title('Data - Gaussian Filter')

plt.suptitle('Raw data and derivatives')
plt.show()