As discussed previously in Section 3.2.7 to effectively manage the catalogue of time-series AVHRR scenes a meta-data PostGIS database was created con- taining AVHRR scene meta-data attributes. Alongside this database the MI- DAS PostGIS database was created containing the time-series of weather sta- tion measurements. Using these databases measurements from AVHRR and MIDAS data were indexed by time and location, enabling spatio-temporal analysis of temperatures across London.
The database interface layer provides the framework with access to the Post- GIS tables containing the AVHRR meta-data and MIDAS measurements. The
database interface uses three libraries, each performing different functions: the Psycopg module is an external library (see Table 4.1) which is a Post- greSQL adaptor for Python. Psycopg was used in the framework to develop a conversion between PostgreSQL tables and NumPy structured arrays. The RPy module is the Python interface to the R statistical language and can be used to access the RPostgreSQL adaptor (see Table 4.1), which converts data between PostgreSQL and R data types. This was used during development of statistical routines as it allows conversion of PostgreSQL/PostGIS data into the R data frame data-type, nested within Python, suitable for testing statis- tical analysis. The CSQL library was constructed as part of the framework during the data pre-processing (Section 3.1) to execute queries on the com- mand line and return large volumes of data from PostGIS to Python and Bash scripts for batch data processing.
The creation of a spatio-temporally paired data-set of AVHRR EST and MI- DAS air temperatures (see Section 5.2.2) was achieved by extracting values for pixels in AVHRR EST scenes and writing these values to a new table in the PostGIS MIDAS database. This process relied on the database interface, and is presented here as an example of database connectivity within the developed framework. The complete source code for the script to extract AVHRR pixel values is presented in Appendix H. Figure 4.8 shows the flow-line of processes for the extraction of pixels in the script.
The process for extracting pixel values can be divided into five sub-processes (Figure 4.8), with a database connection defined using the Psycopg library. The selection of scenes for use (see Section 5.2.2 for selection criteria) was achieved using an SQL query (see extract of source code, Listing 4.8, Line 94). The query to select AVHRR scenes performs a temporary join between the metadata table (’order_avhrr_1985_2008_cloud0’ and the file names table (’avhrr_ta2a’) based on scene primary key defined during the AVHRR pre- processing (see Section 3.2 and Figure 3.4). The query then selects the file names of daytime scenes (06:00-21:00) and returns a list of scenes (primary key, filename, satellite, date and time) ordered by date and time. The query was executed by the database using the Psycopg module (Line 95), and re-
turned a list of file names for the selected AVHRR EST scenes (Line 96).
# Connection to database
90 conn = psycopg2.connect(database=opts.indatabase, user=opts.user, host=
opts.server, password=pgs) # Open database cursor
92 cur = conn.cursor()
# Get list of files to process from database
94
SQL = "SELECT t.primary_key, t.fname, o.satellite, o.date_of_scene, o.
time_of_scene FROM avhrr_ta2a t, order_avhrr_1985_2008_cloud0 o WHERE o.primary_key = t.primary_key AND o.time_of_scene >= ’06:00:00’ AND o.time_of_scene <= ’21:00:00’ ORDER BY o. date_of_scene, o.time_of_scene;"
cur.execute(SQL)
96 fdata = cur.fetchall()
Listing 4.8: Extract of source code (see Appendix H) for extraction of AVHRR EST pixel values showing selection of scenes using the AVHRR meta-data database.
To speed-up the pixel value extraction process over the time-series the Python Multiprocessing module functions Queue and Process were used to execute multiple process threads for the extraction of pixel values simultaneously (Figure 4.8 (2)). The number of threads was determined by the number of processors available on the host machine. Given two processors, the file name list is divided into two, and each processor was used to extract pixel values from half of the file list (Listing 4.9).
F igure 4.8: Data flow-line showing the extraction of A VHRR EST values from raster files to a P ostGIS database table .
126 # Create a queue to run processes
q = Queue() 128 p1 = Process(target=processRun,args=(q,fdata[:length],opts.outdatabase, opts.user,opts.server,pgs)) p2 = Process(target=processRun,args=(q,fdata[length:],opts.outdatabase, opts.user,opts.server,pgs)) 130 # Start processes (dump data to table)
p1.start()
132 p2.start()
Listing 4.9: Extract of source code (see Appendix H) for extraction of AVHRR EST pixel values showing multiple computation threads using the Multiprocessing module.
To extract the pixel values, each of the files in the file name list was read into a NumPy array using the RasterIO module. The values for the required pixels were extracted based on row and column number (Figure 4.8 (3,4)). Listing 4.10 shows the getpixelval function which was created for this purpose.
28
def getpixelval(raster, Xpix, Ypix):
’’’Accepts Np raster and returns value of specified pixel’’’
30
return raster[Ypix,Xpix]
Listing 4.10: Extract of source code (see Appendix H) showing the getpixelval function for the extraction of AVHRR EST pixel values.
Once the required pixel values were extracted these were written to a new table in the PostGIS MIDAS database using SQL insert statements with the Psycopg library (Figure 4.8 (5)). Listing 4.11 shows an extract from the source code (Appendix H) of the SQL database insert for EST values from the pixel containing the London Weather Centre station. After the process was com- pleted, the new MIDAS table held the AVHRR derived EST values for the four London weather stations selected for the analysis of EST and air temper- atures (see Section 5.2.1).
56 # Listing showing extract of avhrr2pgdb - inserting EST for LWC via a
Psycopg connection.
# Note, cur is a PostgreSQL database connection cursor from the Psycopg library.
58 # 19144 - LHR
cur.execute("INSERT INTO est_gla_avhrr_t (
primary_key, fname, satellite,
date_of_scene, time_of_scene, src_id, est)
VALUES (%s, %s, %s, %s, %s, %s, %s);",((
pkey, fname, sat, date, time, 19144, lwc))) 60 #print pkey, fname, sjp, lhr, nth, kew, lwc
Listing 4.11: Extract of source code (Appendix H) showing the insertion of EST values for London Weather Centre into the database.