def getFieldPoints(sql): """ Function to return locations of field points from database according to sql query. """ pointDict = {} if sql is None: sql = """ SELECT fractional.obs_key, fractional.obs_time::date, fractional.site, ST_X(field_geom.geom) as longitude, ST_Y(field_geom.geom) as latitude, crust, dist, rock, green, crypto, dead, litter, mid_g, mid_d, mid_b, crn, over_g, over_d, over_b, persist_gr, num_points FROM fractional, field_geom, states WHERE field_geom.obs_key = fractional.obs_key AND st_intersects(field_geom.geom,states.geom) AND states.state_name = 'NT' """ con = metadb.connect(api=metadb.DB_API) cursor = con.cursor() cursor.execute(sql) description = cursor.description headers = getHeaders(description) points = cursor.fetchall() return points, headers
def getImageNames(cmdargs): """ Use the database to generate a list of all the filenames for the given scene, for the given stage. """ scene = "p%3.3dr%3.3d" % (cmdargs.path, cmdargs.row) sql = """ select satellite, instrument, date from landsat_list where product = 're' and scene = '%s' order by date """ % scene DBcon = metadb.connect(api=metadb.DB_API) DBcursor = DBcon.cursor() DBcursor.execute(sql) results = DBcursor.fetchall() #pdb.set_trace() fileList = [] for (sat, instr, date) in results: newDate = date[0:4] filename = "lztmre_%s_%s_%sm3.img" % (scene, newDate, cmdargs.stage) # Use the database to get the right projection code for this scene filename = metadb.stdProjFilename(filename, cursor=DBcursor) fileList.append(filename) return fileList
def searchDatabaseScenes(pointDict,timeLag,defStage,winsize): """ Search database to find scenes which intersect field data points and were acquired within specified number of days of field work """ keys = pointDict.keys() for key in keys: pointDict[key].image[1] = ImageDict() pointDict[key].image[2] = ImageDict() pointDict[key].image[3] = ImageDict() pointDict[key].image[4] = ImageDict() pointDict[key].image[5] = ImageDict() pointDict[key].image[6] = ImageDict() pointDict[key].image[7] = ImageDict() pointDict[key].image[8] = ImageDict() for stage in ['d%s' % defStage]: if stage == 'd%s' % defStage: print defStage recallStage = 'd%s' % defStage data_source = 'usgs' for key in keys: fieldDate = pointDict[key].obs_time.split(' ')[0] fieldDate = fieldDate.replace('-','') fieldDate = '%s-%s-%s' % (fieldDate[:4],fieldDate[4:6],fieldDate[6:]) pointDict[key].obs_time = fieldDate con = metadb.connect(api=metadb.DB_API) cursor = con.cursor() query = """ SELECT abs(cast('%s' as date) - f.cal_date),SLATSFilename(f.sat_type,f.scene_date) FROM FindFootprintsByPoint(%s, %s) as f, landsat_list WHERE landsat_list.scene_date = f.scene_date AND abs(cast('%s' as date) - f.cal_date) < %s AND landsat_list.product = 're' AND data_source ='%s' """ % (fieldDate,pointDict[key].longitude,pointDict[key].latitude,fieldDate,timeLag,data_source) try: cursor.execute(query) results = cursor.fetchall() results.sort() index=[1,2,3,4,5,6,7,8] if len(results) > 0: for i in index: try: result = results[i-1] image = qvf.changestage(result[1], '%s' % stage) if qv.existsonfilestore(image) and pointDict[key].image[i].imageName is None: pointDict[key].image[i].imageName = image pointDict[key].image[i].timeLag = result[0] pointDict[key].image[i].tmp3pix = qvf.changestage('%s_%s_%spix.tif' % (image.split('.')[0],pointDict[key].site.strip(),winsize),'tmp') else: pointDict[key].image[i].imageName = 'None' pointDict[key].image[i].timeLag = 'None' pointDict[key].image[i].tmp3pix = 'None' except IndexError: pointDict[key].image[i].imageName = 'None' pointDict[key].image[i].timeLag = 'None' pointDict[key].image[i].tmp3pix = 'None' image='None' elif len(results) == 0: image = 'None' print pointDict[key].site,fieldDate,pointDict[key].longitude,pointDict[key].latitude else: print 'Really bad problem.' except: print 'failed', query, pointDict[key].site return pointDict