def getLastNHoursSummaryFromRadarPrecip(self, platform_handle, dateTime, prevHourCnt, obs_type, uom): sum = None #Get the sensor ID for the obs we are interested in so we can use it to query the data. sensorID = xeniaSQLite.sensorExists(self, obs_type, uom, platform_handle) if(sensorID != None and sensorID != -1): start_date = dateTime - timedelta(hours=prevHourCnt) #m_date >= strftime('%%Y-%%m-%%dT%%H:%%M:%%S', datetime( '%s', '-%d hours' )) AND \ sql = "SELECT SUM(m_value) \ FROM multi_obs \ WHERE\ m_date >= '%s' AND\ m_date < '%s' AND\ sensor_id = %d AND m_value >= 0.0;"\ % (start_date.strftime('%Y-%m-%dT%H:%M:%S'), dateTime.strftime('%Y-%m-%dT%H:%M:%S'), sensorID) try: dbCursor = self.DB.cursor() dbCursor.execute(sql) except sqlite3.Error, e: if self.logger: self.logger.exception(e) else: sum = dbCursor.fetchone()[0] if sum: sum = float(sum)
def getPrecedingRadarDryDaysCount(self, platform_handle, dateTime, obs_type, uom): dry_cnt = -9999 sensorId = xeniaSQLite.sensorExists(self, obs_type, uom, platform_handle) if sensorId != None and sensorId != -1: #We want to start our dry day search the day before our dateTime. sql = "SELECT m_date FROM multi_obs WHERE m_date < '%s' AND sensor_id=%d AND m_value > 0 ORDER BY m_date DESC LIMIT 1;"\ % (dateTime.strftime("%Y-%m-%dT%H:%M:%S"), sensorId) try: dbCursor = self.DB.cursor() dbCursor.execute(sql) except sqlite3.Error, e: if self.logger: self.logger.exception(e) dry_cnt = None else: row = dbCursor.fetchone() if row: first_val = timezone('UTC').localize(datetime.strptime(row['m_date'], "%Y-%m-%dT%H:%M:%S")) #Now let's check and make sure we're not missing data in between out date of interest #and the first date we have with rainfall. delta = dateTime - first_val if delta.total_seconds() > (24 * 3600): if not self.findGaps(dateTime, first_val, sensorId): #Get rid of the time zone. #start_date = datetime.strptime(dateTime.strftime("%Y-%m-%dT%H:%M:%S"), "%Y-%m-%dT%H:%M:%S") #delta = start_date - first_val dry_cnt = delta.days else: if self.logger: self.logger.debug("NEXRAD data gap found between: %s - %s" %\ (dateTime.strftime("%Y-%m-%dT%H:%M:%S"), row['m_date'])) else: dry_cnt = 0
def add_sensor_to_platform(self, platform_handle, sensor_name, uom_name, s_order=1): sensor_id = xeniaSQLite.sensorExists(self, sensor_name, uom_name, platform_handle, 1) if sensor_id == -1: sensor_id = xeniaSQLite.addSensor(self, sensor_name, uom_name, platform_handle, 1, 0, s_order, None, True) if sensor_id == -1: raise ValueError("Unable to add sensor id for Obs:%s(%s) on platform: %s" % (sensor_name,uom_name, platform_handle)) m_type_id = xeniaSQLite.getMTypeFromObsName(self, sensor_name, uom_name, platform_handle, 1) return sensor_id, m_type_id
def calcRadarRainfallIntensity(self, platform_handle, date, intervalInMinutes=60, obs_type='precipitation_radar_weighted_average', uom='mm'): rainfallIntensity = -9999.0 #mTypeID = xeniaSQLite.getMTypeFromObsName(self, obs_type, uom, platform_handle,1) sensor_id = xeniaSQLite.sensorExists(self, obs_type, uom, platform_handle) if sensor_id: rainfallIntensity = self.calcIntensity( platform_handle, sensor_id, date, intervalInMinutes) #We do not store radar data that has 0 for precipitation, so we want to make sure not to send #-1.0 as the return value as -1.0 indicates no data due to a data problem. #if rainfallIntensity == -9999.0: # rainfallIntensity = 0.0 else: self.logger.error("No sensor for: precipitation_radar_weighted_average(in) found for platform: %s" %(platform_handle)) return rainfallIntensity
def calcAvgWindSpeedAndDir(self, platName, wind_speed_obsname, wind_speed_uom, wind_dir_obsname, wind_dir_uom, startDate, endDate): windComponents = [] dirComponents = [] vectObj = vectorMagDir(); #Get the wind speed and direction so we can correctly average the data. #Get the sensor ID for the obs we are interested in so we can use it to query the data. windSpdId = xeniaSQLite.sensorExists(self, wind_speed_obsname, wind_speed_uom, platName) windDirId = xeniaSQLite.sensorExists(self, wind_dir_obsname, wind_dir_uom, platName) if windSpdId is not None and windSpdId != -1 and\ windDirId is not None and windDirId != -1: spd_sql = "SELECT m_date ,m_value FROM multi_obs\ WHERE sensor_id = %d AND\ (m_date >= '%s' AND \ m_date < '%s' ) ORDER BY m_date"\ %(windSpdId, startDate, endDate) if(self.logger): self.logger.debug("Wind Speed SQL: %s" % (spd_sql)) dir_sql = "SELECT m_date ,m_value FROM multi_obs\ WHERE sensor_id = %d AND\ (m_date >= '%s' AND \ m_date < '%s' ) ORDER BY m_date"\ %(windDirId, startDate, endDate) if(self.logger): self.logger.debug("Wind Dir SQL: %s" % (dir_sql)) try: windSpdCursor = self.DB.cursor() windSpdCursor.execute(spd_sql) windDirCursor = self.DB.cursor() windDirCursor.execute(dir_sql) except sqlite3.Error, e: if self.logger: self.logger.exception(e) else: scalarSpd = None spdCnt = 0 for spdRow in windSpdCursor: if scalarSpd == None: scalarSpd = 0 scalarSpd += spdRow['m_value'] spdCnt += 1 for dirRow in windDirCursor: if spdRow['m_date'] == dirRow['m_date']: if self.logger: self.logger.debug("Calculating vector for Speed(%s): %f Dir(%s): %f" % (spdRow['m_date'], spdRow['m_value'], dirRow['m_date'], dirRow['m_value'])) #Vector using both speed and direction. windComponents.append(vectObj.calcVector(spdRow['m_value'], dirRow['m_value'])) #VEctor with speed as constant(1), and direction. dirComponents.append(vectObj.calcVector(1, dirRow['m_value'])) break #Get our average on the east and north components of the wind vector. spdAvg = None dirAvg = None scalarSpdAvg = None vectorDirAvg = None #If we have the direction only components, this is unity speed with wind direction, calc the averages. if len(dirComponents): eastCompAvg = 0 northCompAvg = 0 scalarSpdAvg = scalarSpd / spdCnt for vectorTuple in dirComponents: eastCompAvg += vectorTuple[0] northCompAvg += vectorTuple[1] eastCompAvg = eastCompAvg / len(dirComponents) northCompAvg = northCompAvg / len(dirComponents) spdAvg,vectorDirAvg = vectObj.calcMagAndDir(eastCompAvg, northCompAvg) if self.logger: self.logger.debug("Platform: %s Scalar Speed Avg: %f Vector Dir Avg: %f" % (platName,scalarSpdAvg,vectorDirAvg)) #2013-11-21 DWR Add check to verify we have components. Also reset the eastCompAvg and northCompAvg to 0 #before doing calcs. #If we have speed and direction vectors, calc the averages. if len(windComponents): eastCompAvg = 0 northCompAvg = 0 for vectorTuple in windComponents: eastCompAvg += vectorTuple[0] northCompAvg += vectorTuple[1] eastCompAvg = eastCompAvg / len(windComponents) northCompAvg = northCompAvg / len(windComponents) #Calculate average with speed and direction components. spdAvg,dirAvg = vectObj.calcMagAndDir(eastCompAvg, northCompAvg) if(self.logger): self.logger.debug("Platform: %s Vector Speed Avg: %f Vector Dir Avg: %f" % (platName,spdAvg,dirAvg)) windSpdCursor.close() windDirCursor.close()