def createFriends( configFile ): from xeniatools.xenia import recursivedefaultdict twitAccountList = recursivedefaultdict() #Grab all the account info out of the config file. twitList = configFile.getListHead( '//environment/twitterList' ) for child in configFile.getNextInList(twitList): platform = configFile.getEntry( 'handle',child ) twitterAccount = configFile.getEntry( 'twitterAccount',child ) twitterPwd = configFile.getEntry( 'twitterPwd',child ) twitAccountList[platform]['account'] = twitterAccount twitAccountList[platform]['password'] = twitterPwd #Now let's loop and friend all the buoys to one another for platform in twitAccountList: try: account = twitAccountList[platform]['account'] pwd = twitAccountList[platform]['password'] client = twitter.Api(account, pwd) for friendPlatform in twitAccountList: try: if( friendPlatform != platform ): account = twitAccountList[friendPlatform]['account'] user = client.CreateFriendship( account ) print( "%s friended user: %s" %( platform, user.screen_name ) ) except twitter.TwitterError,e: print("Twitter Error: %s" %(e.message)) continue except Exception, E: print( "Error from twitter call: %s" % (str(E)) ) continue
def createFriends(configFile): from xeniatools.xenia import recursivedefaultdict twitAccountList = recursivedefaultdict() #Grab all the account info out of the config file. twitList = configFile.getListHead('//environment/twitterList') for child in configFile.getNextInList(twitList): platform = configFile.getEntry('handle', child) twitterAccount = configFile.getEntry('twitterAccount', child) twitterPwd = configFile.getEntry('twitterPwd', child) twitAccountList[platform]['account'] = twitterAccount twitAccountList[platform]['password'] = twitterPwd #Now let's loop and friend all the buoys to one another for platform in twitAccountList: try: account = twitAccountList[platform]['account'] pwd = twitAccountList[platform]['password'] client = twitter.Api(account, pwd) for friendPlatform in twitAccountList: try: if (friendPlatform != platform): account = twitAccountList[friendPlatform]['account'] user = client.CreateFriendship(account) print("%s friended user: %s" % (platform, user.screen_name)) except twitter.TwitterError, e: print("Twitter Error: %s" % (e.message)) continue except Exception, E: print("Error from twitter call: %s" % (str(E))) continue
def processData(self): #DWR 2013-12-17 #Use the recursive dictionary so we can store the m_type and sensor_ids by: <obs name><uom><sorder> #Without doing this, platforms with multiples of the same sensors will not get each sensor represented #and we ended up using incorrect sensor ids. self.sensorMappings = recursivedefaultdict() #The date we use to time stamp the rows we add. self.rowDate = datetime.datetime.now().strftime("%Y-%m-%dT%H:%M:%S") try: lineCnt = 0 #DWR 2013-0509 lastRecDate = None dataRecs = self.getData() while(dataRecs): #if(self.logger): # self.logger.debug("Line: %d m_date: %s" % (self.csvDataFile.line_num, dataRecs['m_date'])) saveRec = True #DWR 2013-05-09 #Use the self.lastEntryDate variable for the test. if(self.lastEntryDate): if(dataRecs['m_date'] < self.lastEntryDate): saveRec = False if(saveRec): #DWR 2013-05-09 #Save the date, so when we hit the last data record, we have it saved and can then save it to the file. lastRecDate = dataRecs['m_date'] self.saveData(dataRecs) dataRecs = self.getData() lineCnt += 1 except StopIteration,e: if(self.logger): self.logger.info("End of file reached.")
def __init__(self, dbUser=None, dbName=None, dbHost=None, dbPwd=None, SQLiteDB=None, tablePerPlatform=False, chart=False): self.tablePerPlatform = tablePerPlatform #Flag that specifies whether or not we create a seperate HTML page per platform. True, each platform gets a seperate page, False all results on 1. self.chart = chart #Flag that specifies if we add a URL to a google chart for the observation. if (SQLiteDB == None): self.xeniaDB = xeniaPostGres() #Database object. if (self.xeniaDB.connect(None, dbUser, dbPwd, dbHost, dbName)): print("Successfully connected to Xenia DB: Name: %s at %s\n" % (dbName, dbHost)) else: print( "Failed to connect to Xenia DB: Host: %s Name: %s User: %s(%s)\nError: %s"\ %( dbHost, dbName, dbUser, dbPwd, self.xeniaDB.getErrorInfo() ) ) sys.exit(-1) else: self.xeniaDB = xeniaSQLite() #Database object. if (self.xeniaDB.connect(SQLiteDB)): print("Successfully connected to Xenia DB: Name: %s\n" % (SQLiteDB)) else: print( "Failed to connect to Xenia DB: %s\nError: %s"\ %( SQLiteDB, self.xeniaDB.getErrorInfo() ) ) sys.exit(-1) self.obsDataPoints = recursivedefaultdict( ) #Dictionary keyed on obs and sOrder for each data point. Used to collect the points to graph.
def getRemoteData(self, siteName, siteSetting): print("Getting remote data for: %s" %(siteName)) uomConvert = uomconversionFunctions(self.unitsConversionFile) if(siteSetting['ysiconfigfile'] != None): ysi = ysiObsSettings(siteSetting['dataQueryURL'], None, siteSetting['ysiconfigfile']) elif(siteSetting['paramScrapeURL'] != None): ysi = ysiObsSettings(siteSetting['dataQueryURL'], siteSetting['paramScrapeURL'], None) ysi.initList() obsDict = ysi.getAllObservations() obsHash = recursivedefaultdict() obsHash['platform'][siteName]['url'] = siteSetting['url'] obsHash['platform'][siteName]['latitude'] = siteSetting['latitude'] obsHash['platform'][siteName]['longitude'] = siteSetting['longitude'] for param in obsDict: #The ysi observation name has the name and units all in one string. This function #converts the obs name into our data dictionary name, and breaks out the units as well. (obs,fromUOM,sOrder) = self.convertToXeniaObsAndUOM(param) #Didn't have a match, so we'll use the source. if(len(obs) == 0): print("ERROR: Unable to find Xenia observation name for YSI param: %s." %(param)) parts = param.split('[') obs = parts[0] fromUOM = parts[1] fromUOM = fromUOM.replace("]", "") sOrder = "1" elev = "0" dataTuple = obsDict[param] #Now see if we need to convert into different units. toUOM = uomConvert.getConversionUnits(fromUOM, 'metric') for entry in dataTuple: date = entry[0] date = self.formDBDate(date) value = float(entry[1]) if(toUOM != None and len(toUOM)): convertedVal = uomConvert.measurementConvert(value, fromUOM, toUOM) if(convertedVal != None ): value = convertedVal else: toUOM = fromUOM #Build the obs hash. obsUOM = "%s.%s" %(obs,toUOM) obsHash['platform'][siteName]['date'][date]['obsuom'][obsUOM]['elev'][elev]['sorder'][sOrder]['value'] = value #obsHash[siteName][date][obs][elev][sOrder]['uom'] = toUOM return(obsHash)
def __init__(self, dbUser=None, dbName=None, dbHost=None, dbPwd=None, SQLiteDB=None, tablePerPlatform=False,chart=False): self.tablePerPlatform = tablePerPlatform #Flag that specifies whether or not we create a seperate HTML page per platform. True, each platform gets a seperate page, False all results on 1. self.chart = chart #Flag that specifies if we add a URL to a google chart for the observation. if(SQLiteDB == None): self.xeniaDB = xeniaPostGres() #Database object. if( self.xeniaDB.connect( None, dbUser, dbPwd, dbHost, dbName ) ): print( "Successfully connected to Xenia DB: Name: %s at %s\n" % ( dbName, dbHost) ) else: print( "Failed to connect to Xenia DB: Host: %s Name: %s User: %s(%s)\nError: %s"\ %( dbHost, dbName, dbUser, dbPwd, self.xeniaDB.getErrorInfo() ) ) sys.exit(-1) else: self.xeniaDB = xeniaSQLite() #Database object. if( self.xeniaDB.connect( SQLiteDB ) ): print( "Successfully connected to Xenia DB: Name: %s\n" % ( SQLiteDB) ) else: print( "Failed to connect to Xenia DB: %s\nError: %s"\ %( SQLiteDB, self.xeniaDB.getErrorInfo() ) ) sys.exit(-1) self.obsDataPoints = recursivedefaultdict() #Dictionary keyed on obs and sOrder for each data point. Used to collect the points to graph.
def __init__(self,htmlFilename,styleSheet): self.htmlFilename = htmlFilename self.platformResultsTable = platformResultsTable() self.platforms = recursivedefaultdict() try: self.htmlFile = open(self.htmlFilename, "w") self.htmlFile.write( "<html>\n" ) self.htmlFile.write( "<BODY id=\"RGB_BODY_BG\" >\n" ) self.htmlFile.write( "<link href=\"%s\" rel=\"stylesheet\" type=\"text/css\" media=\"screen\" />\n" % ( styleSheet ) ) except IOError, e: import sys import traceback info = sys.exc_info() excNfo = traceback.extract_tb(info[2], 1) items = excNfo[0] lastErrorFile = items[0] lastErrorLineNo = items[1] lastErrorFunc = items[2] print("%s Function: %s Line: %s File: %s" % (str(e), lastErrorFunc, lastErrorLineNo, lastErrorFile)) sys.exit(- 1)
def __init__(self, xmlConfigFilename): configSettings = xmlConfigFile(xmlConfigFilename) self.siteSettings = recursivedefaultdict() # Hash of various parameters for each customer site to process. paramList = configSettings.getListHead("//environment/ysiSettingsList") for child in configSettings.getNextInList(paramList): siteName = configSettings.getEntry("name",child) geoLoc = configSettings.getEntry("geoLoc",child) self.siteSettings[siteName]['latitude'] = 0.0 self.siteSettings[siteName]['longitude'] = 0.0 if(geoLoc != None): latLong = geoLoc.split(',') self.siteSettings[siteName]['latitude'] = latLong[0] self.siteSettings[siteName]['longitude'] = latLong[1] self.siteSettings[siteName]['url'] = configSettings.getEntry("platformURL",child) self.siteSettings[siteName]['ysiconfigfile'] = configSettings.getEntry("ysiParamFile",child) self.siteSettings[siteName]['outputtype'] = configSettings.getEntry("outputs/output/type",child) self.siteSettings[siteName]['outputfilename']= configSettings.getEntry("outputs/output/filename",child) self.siteSettings[siteName]['paramScrapeURL']= configSettings.getEntry("paramScrapeURL",child) self.siteSettings[siteName]['dataQueryURL'] = configSettings.getEntry("dataQueryURL",child) self.unitsConversionFile = configSettings.getEntry("//environment/unitsConversion/file") self.ysiMapToXeniaFile = configSettings.getEntry("//environment/ysiObservationMappingFile/file")
def __init__(self, htmlFilename, styleSheet): self.htmlFilename = htmlFilename self.platformResultsTable = platformResultsTable() self.platforms = recursivedefaultdict() try: self.htmlFile = open(self.htmlFilename, "w") self.htmlFile.write("<html>\n") self.htmlFile.write("<BODY id=\"RGB_BODY_BG\" >\n") self.htmlFile.write( "<link href=\"%s\" rel=\"stylesheet\" type=\"text/css\" media=\"screen\" />\n" % (styleSheet)) except IOError, e: import sys import traceback info = sys.exc_info() excNfo = traceback.extract_tb(info[2], 1) items = excNfo[0] lastErrorFile = items[0] lastErrorLineNo = items[1] lastErrorFunc = items[2] print("%s Function: %s Line: %s File: %s" % (str(e), lastErrorFunc, lastErrorLineNo, lastErrorFile)) sys.exit(-1)
def buildContent(self, xeniaDb, uomConverter, boundingBox): try: GEORSSPATH = 'http://129.252.37.90/xenia/feeds/georss/' DATAQUERYPAGEPATH = 'http://carolinasrcoos.org/queryStation.php?station=' ADCPGRAPHURL = 'http://carocoops.org/~dramage_prod/cgi-bin/rcoos/ADCPGraph.php?PLATFORMID=<ID>&INTERVAL=<INTERVAL>' TWITTERURL = 'http://twitter.com/' EMAILALERTPAGEPATH = "http://www.secoora.org/pages/alertpage.php?platform=" sql = "SELECT to_char(timezone('UTC', m_date), 'YYYY-MM-DD HH24:MI:SS') AS local_date \ ,m_date as m_date\ ,multi_obs.platform_handle as multi_obs_platform_handle\ ,obs_type.standard_name as obs_type_standard_name\ ,uom_type.standard_name as uom_type_standard_name\ ,multi_obs.m_type_id as multi_obs_m_type_id\ ,m_lon\ ,m_lat\ ,m_z\ ,m_value\ ,qc_level\ ,sensor.row_id as sensor_row_id\ ,sensor.s_order as sensor_s_order\ ,sensor.url as sensor_url\ ,platform.url as platform_url\ ,platform.description as platform_description\ ,organization.short_name as organization_short_name\ ,organization.url as organization_url\ ,m_type_display_order.row_id as m_type_display_order_row_id\ ,extract(epoch from m_date)\ from multi_obs\ left join sensor on sensor.row_id=multi_obs.sensor_id\ left join m_type on m_type.row_id=multi_obs.m_type_id\ left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id\ left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id\ left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id\ left join platform on platform.row_id=sensor.platform_id\ left join organization on organization.row_id=platform.organization_id\ left join m_type_display_order on m_type_display_order.m_type_id=multi_obs.m_type_id\ where\ m_date>(now()-interval '12 hours') AND\ Contains( GeomFromText( \'POLYGON((%s))\'), GeomFromText( 'POINT(' || fixed_longitude || ' ' || fixed_latitude ||')' ) )\ union\ select\ null as local_date,\ null as m_date,\ platform.platform_handle as multi_obs_platform_handle ,\ null as obs_type_standard_name,\ null as uom_type_standard_name,\ null as multi_obs_m_type_id,\ platform.fixed_longitude,\ platform.fixed_latitude,\ null as m_z,\ null as m_value ,\ null as qc_level,\ null as row_id,\ null as s_order,\ null as sensor_url,\ platform.url as platform_url ,\ platform.description as platform_description,\ organization.short_name as organization_short_name,\ organization.url as organization_url,\ null as m_type_display_order_row_id,\ null as epoch\ from platform\ left join organization on organization.row_id=platform.organization_id\ where platform.active=1 AND\ Contains( GeomFromText( \'POLYGON((%s))\'), GeomFromText( 'POINT(' || fixed_longitude || ' ' || fixed_latitude ||')' ) )\ order by multi_obs_platform_handle,m_type_display_order_row_id,sensor_s_order,m_date desc;" \ % (boundingBox,boundingBox) print(sql) latestObs = recursivedefaultdict() latestDate = None currentPlatform = None dbCursor = xeniaDb.dbConnection.executeQuery(sql) if (dbCursor != None): for obsRow in dbCursor: #print("Organization: %s platform: %s" %(obsRow['organization_short_name'], obsRow['multi_obs_platform_handle'])) if (currentPlatform == None): currentPlatform = obsRow['multi_obs_platform_handle'] if (latestDate == None): latestDate = str(obsRow['m_date']) else: if (obsRow['m_date'] != None): #We only want the most current obs. if (latestDate != str(obsRow['m_date']) and currentPlatform == obsRow['multi_obs_platform_handle']): continue currentPlatform = obsRow['multi_obs_platform_handle'] latestDate = str(obsRow['m_date']) if (obsRow['m_type_display_order_row_id'] != None): #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_type_id'] = obsRow['multi_obs_m_type_id'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'obs_name'] = obsRow[ 'obs_type_standard_name'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'uom'] = obsRow[ 'uom_type_standard_name'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'm_value'] = obsRow['m_value'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_z'] = obsRow['m_z'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'qc_level'] = obsRow['qc_level'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['sensor_url'] = obsRow['sensor_url'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'sensor_id'] = obsRow['sensor_row_id'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'local_date'] = obsRow['local_date'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow[ 'multi_obs_platform_handle']]['obs_list'][ obsRow['m_type_display_order_row_id']][ 'm_date'] = latestDate #assuming all observations are basically the same lat/lon as platform latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow['multi_obs_platform_handle']][ 'm_lat'] = obsRow['m_lat'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow['multi_obs_platform_handle']][ 'm_lon'] = obsRow['m_lon'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow['multi_obs_platform_handle']][ 'url'] = obsRow['platform_url'] latestObs[obsRow['organization_short_name']][ 'platform_list'][obsRow['multi_obs_platform_handle']][ 'platform_desc'] = obsRow['platform_description'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']][status] = $platform_status latestObs[obsRow['organization_short_name']][ 'name'] = obsRow['organization_short_name'] latestObs[obsRow['organization_short_name']][ 'url'] = obsRow['organization_url'] else: print(xeniaDb.dbConnection.getErrorInfo()) sys.exit(-1) dbCursor.close() operatorKeys = latestObs.keys() operatorKeys.sort() platformCnt = 0 operatorCnt = 0 insertDate = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) for operator in operatorKeys: for platform in latestObs[operator]['platform_list']: print("Processing platform: %s" % (platform)) htmlContent = '' contentHeader = '' platformContent = '' latestDate = None platformParts = platform.split('.') lcPlatform = platformParts[1].lower() operator = platformParts[0] links ='<a href=%s%s_%s_%s_GeoRSS_latest.xml target=new title="RSS Feed"><img src="resources/images/default/rss_small.jpg"/></a>'\ %(GEORSSPATH, platformParts[0], lcPlatform, platformParts[2]) if (lcPlatform == 'cap2' or lcPlatform == 'sun2' or lcPlatform == 'frp2' or lcPlatform == 'ocp1' or lcPlatform == 'ilm2' or lcPlatform == 'ilm3'): links += '<a href=%s%sRCOOS target=new title="Twitter Feed"><img src="resources/images/default/twitter.png"/></a>'\ %(TWITTERURL,lcPlatform) links += '<a href=%s%s target=new title="Data Query"><img src="resources/images/default/data_query.png"/></a>'\ %(DATAQUERYPAGEPATH, lcPlatform.upper()) links += '<a href=%s%s target=new title="Email Alerts"><img src="resources/images/default/mail.png"/></a>'\ %(EMAILALERTPAGEPATH, platform) desc = latestObs[operator]['platform_list'][platform][ 'platform_desc'] #No description in the database, so we'll make one based on the operator and platform if (len(desc) == 0): desc = "%s %s" % (operator, platformParts[1]) if (('url' in latestObs[operator]['platform_list'][platform]) != False): platformUrl = latestObs[operator]['platform_list'][ platform]['url'] elif (('url' in latestObs[operator]) != False): platformUrl = latestObs[operator]['url'] else: platformUrl = '' lat = 0.0 lon = 0.0 if (('m_lat' in latestObs[operator]['platform_list'][platform]) != False): lat = latestObs[operator]['platform_list'][platform][ 'm_lat'] else: print("No latitude defined for platform: %s" % (platform)) if (('m_lat' in latestObs[operator]['platform_list'][platform]) != False): lon = latestObs[operator]['platform_list'][platform][ 'm_lon'] else: print("No longitude defined for platform: %s" % (platform)) contentHeader = "<div id=\"popupobscontent\" class=\"popupobscontent\"><hr/><a href=\"%s\" target=new onclick=\"\">%s</a><p id=\"popupobsloc\" class=\"popupobsloc\">Latitude: %4.3f Longitude: %4.3f</p><p id=\"popupobslinks\" class=\"popupobslinks\">%s</p>"\ %(platformUrl, desc, lat, lon, links ) displayOrderKeys = latestObs[operator]['platform_list'][ platform]['obs_list'].keys() displayOrderKeys.sort() obsDate = "" for displayOrder in displayOrderKeys: if (latestDate == None): if (latestObs[operator]['platform_list'][platform] ['obs_list'][displayOrder]['m_date'] != None): #latestDate = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_date'] latestDate = latestObs[operator][ 'platform_list'][platform]['obs_list'][ displayOrder]['local_date'] obsDate = latestDate localDatetime = time.strptime( latestDate, '%Y-%m-%d %H:%M:%S') obsLocalEpochSecs = time.mktime(localDatetime) datetimeLabel = "<span id=\"popupobsstatus\" class=\"popupobsstatusold\">No data available within the past 6 hours</span>" localNow = time.mktime(time.localtime()) if ((localNow - obsLocalEpochSecs) > 21600): datetimeLabel = "<span id=\"popupobsstatus\" class=\"popupobsstatusold\">No data available within the past 6 hours</span>" else: tz = 'EST' if (time.daylight == 1): tz = 'EDT' day = time.strftime("%m/%d", localDatetime) datetimeLabel = time.strftime( "Surface conditions as of %I:%M %p", localDatetime) datetimeLabel = "%s %s on %s" % ( datetimeLabel, tz, day) if ((localNow - obsLocalEpochSecs) > 7200): datetimeLabel += "<br><span class=\"popupobsstatusstale\">Note: This report is more than 2 hours old</span>" platformContent = "<div id=\"popupobs\" class=\"popupobs\"><table class=\"popupobsdata\"><caption>%s</caption>"\ % (datetimeLabel) if (latestObs[operator]['platform_list'][platform] ['obs_list'][displayOrder]['m_value'] != None): obsUOM = latestObs[operator]['platform_list'][ platform]['obs_list'][displayOrder]['uom'] value = latestObs[operator]['platform_list'][ platform]['obs_list'][displayOrder]['m_value'] #Get the label we want to use for the observation obsLabel = uomConverter.getDisplayObservationName( latestObs[operator]['platform_list'][platform] ['obs_list'][displayOrder]['obs_name']) if (obsLabel == None): obsLabel = latestObs[operator][ 'platform_list'][platform]['obs_list'][ displayOrder]['obs_name'] #Get the units we want to convert the data to. This is also used as the label for the units in the text display. displayUOM = uomConverter.getConversionUnits( obsUOM, 'en') if (len(displayUOM) == 0): displayUOM = obsUOM value = uomConverter.measurementConvert( value, obsUOM, displayUOM) if (value == None): value = latestObs[operator]['platform_list'][ platform]['obs_list'][displayOrder][ 'm_value'] displayUOM = obsUOM googURL = self.buildGoogleChartLink( xeniaDb, platform, obsLabel, latestObs[operator]['platform_list'][platform] ['obs_list'][displayOrder]['sensor_id'], obsUOM, displayUOM, latestObs[operator]['platform_list'][platform] ['obs_list'][displayOrder]['m_date'], uomConverter) measureLabel = "%s %s" % (str(value), displayUOM) qcLevel = latestObs[operator]['platform_list'][ platform]['obs_list'][displayOrder]['qc_level'] #Add a bad or suspect label if the quality control flag is set to 1 or 2. if (qcLevel == 1): measureLabel += "(bad)" elif (qcLevel == 2): measureLabel += "(suspect)" platformContent += "<tr %s><td scope=\"row\">%s</td><td>%s</td></tr>"\ %(googURL, obsLabel, measureLabel) #Finished platform, increment our count. platformCnt += 1 #Check to see if we had any platform content, if not let's just tag it with nothing available before adding the entry into the #database. if (len(platformContent) == 0): platformContent = "<tr><td>No data available</td></tr>" print( "Platform has no data, possible inactive station: %s" % (platform)) htmlContent = "%s%s</table><div id=\"popupobsgraph\"></div>" % ( contentHeader, platformContent) """ lat = 0.0 if( 'm_lat' in latestObs[operator]['platform_list'][platform] != False): lat = latestObs[operator]['platform_list'][platform]['m_lat'] lon = 0.0 if('m_lon' in latestObs[operator]['platform_list'][platform] != False): lon = latestObs[operator]['platform_list'][platform]['m_lon'] """ dbCur = self.addRowToObsTable(insertDate, obsDate, lat, lon, operator, htmlContent, platform) if (dbCur == False): print(xeniaDb.dbConnection.getErrorInfo()) """ sql = "INSERT INTO html_content(wkt_geometry,organization,html,platform_handle)\ values ('POINT(%f %f)','%s','%s', '%s');"\ %(latestObs[operator]['platform_list'][platform]['m_lon'], latestObs[operator]['platform_list'][platform]['m_lat'], operator, htmlContent, platform) #print("Saving content.\n %s" %(sql)) dbCur = self.executeQuery(sql) if(dbCur != None): self.db.commit() else: print(xeniaDb.dbConnection.getErrorInfo()) """ operatorCnt += 1 self.db.commit() print("Processed %d operators and %d platforms." % (operatorCnt, platformCnt)) self.copyToWorkingDB() except Exception, E: print(traceback.format_exc())
def __init__(self): self.table = None self.platforms = recursivedefaultdict()
def checkSensorActivity(xeniaDb, hoursToLookback, emailList, outFilename, emailUser, emailPwd): try: print("Opening file: %s" %(outFilename)) outFile = open(outFilename, "w") if(hoursToLookback != None): outFile.write("Sensor activity check for the past %d hours.\n" %(hoursToLookback)) else: outFile.write("Sensor activity check for the entire record set in multi_obs.\n") platNfo = recursivedefaultdict() sql = "SELECT sensor.row_id AS row_id, sensor.active AS active, \ obs_type.standard_name as obs_type_standard_name,\ platform.platform_handle as platform_handle\ FROM sensor\ LEFT JOIN m_type on sensor.m_type_id = m_type.row_id\ LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id\ LEFT JOIN obs_type on obs_type.row_id = m_scalar_type.obs_type_id\ LEFT JOIN platform on sensor.platform_id = platform.row_id\ WHERE platform.active=1 ORDER BY sensor.row_id ASC;" dbCursor = xeniaDb.dbConnection.executeQuery(sql) if(dbCursor != None): for row in dbCursor: sensorId = row['row_id'] platNfo[sensorId]['platform_handle'] = row['platform_handle'] platNfo[sensorId]['obsName'] = row['obs_type_standard_name'] platNfo[sensorId]['active'] = row['active'] dbCursor.close() else: msg = "Error: %s" %(xeniaDb.dbConnection.getErrorInfo()) print(msg) outFile.write(msg) sys.exit(-1) reActivatedPlatforms = {} where = "" if(hoursToLookback != None): where = "WHERE m_date >= now() - interval '%d hours'" %(hoursToLookback) #Get all the DISTINCT sensor ids, then we'll mark them as active. sql = "SELECT DISTINCT(sensor_id) AS sensor_id,platform_handle,\ obs_type.standard_name as obs_type_standard_name\ FROM multi_obs\ LEFT JOIN m_type on multi_obs.m_type_id = m_type.row_id\ LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id\ LEFT JOIN obs_type on obs_type.row_id = m_scalar_type.obs_type_id %s;" %(where) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if(dbCursor != None): for row in dbCursor: sensorId = row['sensor_id'] if(platNfo.has_key(sensorId)): active = platNfo[sensorId]['active'] if(active != 1): platform_handle = platNfo[sensorId]['platform_handle'] obsName = platNfo[sensorId]['obsName'] msg = 'Platform: %s Sensor: %s(%d) inactive sensor now reporting.'\ %(platform_handle,obsName,sensorId) print(msg) outFile.write("%s\n" %(msg)) del platNfo[sensorId] else: platformHandle = row['platform_handle'] print("Platform: %s Sensor ID: %d not present in current active platform/sensor list. Added to re-activate list." %(platformHandle,sensorId)) if(reActivatedPlatforms.has_key(platformHandle) == False): reActivatedPlatforms[platformHandle] = [] info = {} info['sensorId'] = sensorId info['obsName'] = row['obs_type_standard_name'] reActivatedPlatforms[platformHandle].append(info) dbCursor.close() #If we had sensors come alive for platforms that were inactive, we re-activate the platforms and the sensors. if(len(reActivatedPlatforms)): print("Preparing to re-active Platforms and Sensors that have become active.") outFile.write("Reactivating Platforms and Sensors that have become active.\n") for platform in reActivatedPlatforms: sql = "UPDATE platform SET active=1 WHERE platform_handle='%s'" %(platform) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if(dbCursor != None): msg = "Platform: %s" %(platform) print(msg + " set active to 1.") outFile.write("%s\n" %(msg)) xeniaDb.dbConnection.commit() dbCursor.close() #sensorList = reActivatedPlatforms[platform] for info in reActivatedPlatforms[platform]: sql = "UPDATE sensor SET active=1 WHERE row_id=%d" %(info['sensorId']) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if(dbCursor != None): msg = "Platform: %s Sensor: %s(%d)" %(platform, info['obsName'], info['sensorId']) print(msg + " set active to 1.") outFile.write("%s\n" %(msg)) xeniaDb.dbConnection.commit() dbCursor.close() else: print("Error: %s" %(xeniaDb.dbConnection.getErrorInfo())) else: print("Error: %s" %(xeniaDb.dbConnection.getErrorInfo())) if(len(platNfo)): outFile.write("The following details show platforms and sensors that are marked as active but did not report.\n") for sensorId in platNfo: msg = "Platform: %s Sensor: %s(%d)" %(platNfo[sensorId]['platform_handle'],platNfo[sensorId]['obsName'],sensorId) print(msg + " did not report observations.") outFile.write("%s\n" %(msg)) outFile.close() smtp = smtpClass("inlet.geol.sc.edu", emailUser, emailPwd) smtp.subject('[secoora_auto_alert] Check Sensor Results') smtp.message('Attached is the latest run results for the sensorTableUtils script.') smtp.from_addr('*****@*****.**') smtp.rcpt_to(emailList) smtp.attach(outFilename) smtp.send() else: print("Error: %s" %(xeniaDb.dbConnection.getErrorInfo())) sys.exit(-1) except Exception, e: import traceback print(traceback.print_exc())
def checkSensorActivity(xeniaDb, hoursToLookback, emailList, outFilename, emailUser, emailPwd): try: print("Opening file: %s" % (outFilename)) outFile = open(outFilename, "w") if (hoursToLookback != None): outFile.write("Sensor activity check for the past %d hours.\n" % (hoursToLookback)) else: outFile.write( "Sensor activity check for the entire record set in multi_obs.\n" ) platNfo = recursivedefaultdict() sql = "SELECT sensor.row_id AS row_id, sensor.active AS active, \ obs_type.standard_name as obs_type_standard_name,\ platform.platform_handle as platform_handle\ FROM sensor\ LEFT JOIN m_type on sensor.m_type_id = m_type.row_id\ LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id\ LEFT JOIN obs_type on obs_type.row_id = m_scalar_type.obs_type_id\ LEFT JOIN platform on sensor.platform_id = platform.row_id\ WHERE platform.active=1 ORDER BY sensor.row_id ASC;" dbCursor = xeniaDb.dbConnection.executeQuery(sql) if (dbCursor != None): for row in dbCursor: sensorId = row['row_id'] platNfo[sensorId]['platform_handle'] = row['platform_handle'] platNfo[sensorId]['obsName'] = row['obs_type_standard_name'] platNfo[sensorId]['active'] = row['active'] dbCursor.close() else: msg = "Error: %s" % (xeniaDb.dbConnection.getErrorInfo()) print(msg) outFile.write(msg) sys.exit(-1) reActivatedPlatforms = {} where = "" if (hoursToLookback != None): where = "WHERE m_date >= now() - interval '%d hours'" % ( hoursToLookback) #Get all the DISTINCT sensor ids, then we'll mark them as active. sql = "SELECT DISTINCT(sensor_id) AS sensor_id,platform_handle,\ obs_type.standard_name as obs_type_standard_name\ FROM multi_obs\ LEFT JOIN m_type on multi_obs.m_type_id = m_type.row_id\ LEFT JOIN m_scalar_type on m_scalar_type.row_id = m_type.m_scalar_type_id\ LEFT JOIN obs_type on obs_type.row_id = m_scalar_type.obs_type_id %s;" % ( where) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if (dbCursor != None): for row in dbCursor: sensorId = row['sensor_id'] if (platNfo.has_key(sensorId)): active = platNfo[sensorId]['active'] if (active != 1): platform_handle = platNfo[sensorId]['platform_handle'] obsName = platNfo[sensorId]['obsName'] msg = 'Platform: %s Sensor: %s(%d) inactive sensor now reporting.'\ %(platform_handle,obsName,sensorId) print(msg) outFile.write("%s\n" % (msg)) del platNfo[sensorId] else: platformHandle = row['platform_handle'] print( "Platform: %s Sensor ID: %d not present in current active platform/sensor list. Added to re-activate list." % (platformHandle, sensorId)) if (reActivatedPlatforms.has_key(platformHandle) == False): reActivatedPlatforms[platformHandle] = [] info = {} info['sensorId'] = sensorId info['obsName'] = row['obs_type_standard_name'] reActivatedPlatforms[platformHandle].append(info) dbCursor.close() #If we had sensors come alive for platforms that were inactive, we re-activate the platforms and the sensors. if (len(reActivatedPlatforms)): print( "Preparing to re-active Platforms and Sensors that have become active." ) outFile.write( "Reactivating Platforms and Sensors that have become active.\n" ) for platform in reActivatedPlatforms: sql = "UPDATE platform SET active=1 WHERE platform_handle='%s'" % ( platform) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if (dbCursor != None): msg = "Platform: %s" % (platform) print(msg + " set active to 1.") outFile.write("%s\n" % (msg)) xeniaDb.dbConnection.commit() dbCursor.close() #sensorList = reActivatedPlatforms[platform] for info in reActivatedPlatforms[platform]: sql = "UPDATE sensor SET active=1 WHERE row_id=%d" % ( info['sensorId']) dbCursor = xeniaDb.dbConnection.executeQuery(sql) if (dbCursor != None): msg = "Platform: %s Sensor: %s(%d)" % ( platform, info['obsName'], info['sensorId']) print(msg + " set active to 1.") outFile.write("%s\n" % (msg)) xeniaDb.dbConnection.commit() dbCursor.close() else: print("Error: %s" % (xeniaDb.dbConnection.getErrorInfo())) else: print("Error: %s" % (xeniaDb.dbConnection.getErrorInfo())) if (len(platNfo)): outFile.write( "The following details show platforms and sensors that are marked as active but did not report.\n" ) for sensorId in platNfo: msg = "Platform: %s Sensor: %s(%d)" % ( platNfo[sensorId]['platform_handle'], platNfo[sensorId]['obsName'], sensorId) print(msg + " did not report observations.") outFile.write("%s\n" % (msg)) outFile.close() smtp = smtpClass("inlet.geol.sc.edu", emailUser, emailPwd) smtp.subject('[secoora_auto_alert] Check Sensor Results') smtp.message( 'Attached is the latest run results for the sensorTableUtils script.' ) smtp.from_addr('*****@*****.**') smtp.rcpt_to(emailList) smtp.attach(outFilename) smtp.send() else: print("Error: %s" % (xeniaDb.dbConnection.getErrorInfo())) sys.exit(-1) except Exception, e: import traceback print(traceback.print_exc())
def computeMonthlyDataPoints(self, platformList, beginYear, endYear, QAQCFlags, outputFilePath,writeRawDataPoints): import calendar #If we want to use the qc_level to determine which data to include, let's build the SQL for this. #qaqcWHERE = '' #if(len(QAQCFlags)): # for qcLevel in QAQCFlags: # if(len(qaqcWHERE)): # qaqcWHERE += 'OR ' # qaqcWHERE += "qc_level=%d " % (qcLevel) # qaqcWHERE = "AND (%s)" %(qaqcWHERE) for platformHandle in platformList: #Get all the observations on the platform platformNfoCur = self.dbConnection.getPlatformInfo(platformHandle) #Platform doesn't seem to exist, so move on. if(platformNfoCur == None): continue platformNfo = platformNfoCur.fetchone() platformID = int(platformNfo['row_id']) platformNfoCur.close() sql= "SELECT\ obs_type.standard_name \ ,uom_type.standard_name as uom \ ,sensor.row_id as sensor_id\ ,sensor.m_type_id as m_type_id\ ,sensor.s_order as s_order\ FROM sensor \ left join m_type on m_type.row_id=sensor.m_type_id \ left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id \ left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id \ left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id \ WHERE sensor.platform_id = %d ORDER BY obs_type.standard_name ASC"\ %(platformID) sensorCur = self.dbConnection.executeQuery(sql) #No sensors available on the platform. if(sensorCur == None): continue sensorNfo = recursivedefaultdict() for row in sensorCur: sensorNfo[row['standard_name']]['uom'] = row['uom'] sensorNfo[row['standard_name']]['sensor_id'] = int(row['sensor_id']) sensorNfo[row['standard_name']]['m_type_id'] = int(row['m_type_id']) sensorNfo[row['standard_name']]['sorder'] = int(row['s_order']) sensorCur.close() outputFile = "%s/%s-yearly-stats-%s_%s.csv" %(outputFilePath,platformHandle,beginYear,endYear) statsFile = open(outputFile,'w') statsFile.write('Observation,StartDate,EndDate,Min,Max,Average,StdDev,90thPercentile,TotalRecordCount\n') rawDataPoints = None if(writeRawDataPoints): outputFile = "%s/%s-yearly-raw.csv" %(outputFilePath,platformHandle) rawDataPoints = open(outputFile, 'w') if(rawDataPoints != None): rawDataPoints.write('Observation,StartDate,EndDate,Data\n') yearList = [] if(beginYear == None): # Get the distinct years if(self.dbConnection.dbType == dbTypes.PostGRES): sql = "SELECT DISTINCT(EXTRACT(YEAR FROM m_date)) as year FROM multi_obs WHERE platform_handle='%s'" %(platformHandle) else: sql = "SELECT DISTINCT(strftime('%%Y', m_date)) as year FROM multi_obs WHERE platform_handle='%s'" %(platformHandle) dbCursor = self.dbConnection.executeQuery(sql) if(dbCursor != None): for row in dbCursor: yearList.append(int(row['year'])) dbCursor.close() else: for i in range(beginYear, endYear+1): yearList.append(i) #This is a dictionary we use to hold all the months of data for the years. We use it as a collection #of stats() objects so we can calculate some overall stats for each month over the years. #obsOverallMonthStats = recursivedefaultdict() print("Processing: %s" % (platformHandle)) for year in yearList: for obsName in sensorNfo: uom = sensorNfo[obsName]['uom'] sensorID = sensorNfo[obsName]['sensor_id'] mTypeID = sensorNfo[obsName]['m_type_id'] #sOrder = sensorNfo[obsName]['sorder'] #Now for each month, we calc stats on the data. for month in range( 1,13 ): print("Obs: %s(%s) Year: %d Month: %d" %(obsName, uom, year, month)) monthStats = stats() dayCnt = calendar.monthrange(year, month) startDate = "%d-%02d-%02dT00:00:00" %(year,month,1) endDate = "%d-%02d-%2dT24:00:00" %(year,month,dayCnt[1]) if(rawDataPoints != None): rawDataPoints.write("%s,%s,%s" %(obsName,startDate,endDate)) #mTypeID = self.dbConnection.getMTypeFromObsName(obsName, uom, platformHandle, sOrder) #sql = "SELECT m_date,m_value FROM multi_obs WHERE (m_date >= '%s' AND m_date <= '%s')\ # AND sensor_id=%d %s;"\ # %(startDate,endDate,sensorID,qaqcWHERE) sql = "SELECT m_date,m_value,qc_level FROM multi_obs WHERE (m_date >= '%s' AND m_date <= '%s')\ AND sensor_id=%d;"\ %(startDate,endDate,sensorID) dbCursor = self.dbConnection.executeQuery(sql) if(dbCursor != None): for row in dbCursor: goodVal = False #Use all data. if(len(QAQCFlags) == 0): goodVal = True elif(row['qc_level'] != None): for qaqcFlag in QAQCFlags: if(qaqcFlag == row['qc_level']): goodVal = True break if(goodVal): m_value = row['m_value'] if(m_value != None): m_value = float(m_value) monthStats.addValue(m_value) if(rawDataPoints != None): rawDataPoints.write(",%f" %(m_value)) monthStats.doCalculations() avg = monthStats.average if(avg == None): avg = -1.0 stdDev = monthStats.stdDev if(stdDev == None): stdDev = -1.0 popStdDev = monthStats.populationStdDev if(popStdDev == None): popStdDev = -1.0 UpperPercentile = monthStats.getValueAtPercentile(90) if(UpperPercentile == None): UpperPercentile = -1.0 min = monthStats.minVal if(min == None): min = -1.0 max = monthStats.maxVal if(max == None): max = -1.0 statsFile.write('%s,%s,%s,%f,%f,%f,%f,%f,%d\n'\ %(obsName,startDate,endDate,min,max,avg,stdDev,UpperPercentile,len(monthStats.items))) if(rawDataPoints != None): rawDataPoints.write("\n") else: i = 0 statsFile.close() if(rawDataPoints != None): rawDataPoints.close()
def buildContent(self, xeniaDb, uomConverter, boundingBox): try: GEORSSPATH = 'http://129.252.37.90/xenia/feeds/georss/'; DATAQUERYPAGEPATH = 'http://carolinasrcoos.org/queryStation.php?station='; ADCPGRAPHURL = 'http://carocoops.org/~dramage_prod/cgi-bin/rcoos/ADCPGraph.php?PLATFORMID=<ID>&INTERVAL=<INTERVAL>'; TWITTERURL = 'http://twitter.com/'; EMAILALERTPAGEPATH = "http://www.secoora.org/pages/alertpage.php?platform=" sql = "SELECT to_char(timezone('UTC', m_date), 'YYYY-MM-DD HH24:MI:SS') AS local_date \ ,m_date as m_date\ ,multi_obs.platform_handle as multi_obs_platform_handle\ ,obs_type.standard_name as obs_type_standard_name\ ,uom_type.standard_name as uom_type_standard_name\ ,multi_obs.m_type_id as multi_obs_m_type_id\ ,m_lon\ ,m_lat\ ,m_z\ ,m_value\ ,qc_level\ ,sensor.row_id as sensor_row_id\ ,sensor.s_order as sensor_s_order\ ,sensor.url as sensor_url\ ,platform.url as platform_url\ ,platform.description as platform_description\ ,organization.short_name as organization_short_name\ ,organization.url as organization_url\ ,m_type_display_order.row_id as m_type_display_order_row_id\ ,extract(epoch from m_date)\ from multi_obs\ left join sensor on sensor.row_id=multi_obs.sensor_id\ left join m_type on m_type.row_id=multi_obs.m_type_id\ left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id\ left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id\ left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id\ left join platform on platform.row_id=sensor.platform_id\ left join organization on organization.row_id=platform.organization_id\ left join m_type_display_order on m_type_display_order.m_type_id=multi_obs.m_type_id\ where\ m_date>(now()-interval '12 hours') AND\ Contains( GeomFromText( \'POLYGON((%s))\'), GeomFromText( 'POINT(' || fixed_longitude || ' ' || fixed_latitude ||')' ) )\ union\ select\ null as local_date,\ null as m_date,\ platform.platform_handle as multi_obs_platform_handle ,\ null as obs_type_standard_name,\ null as uom_type_standard_name,\ null as multi_obs_m_type_id,\ platform.fixed_longitude,\ platform.fixed_latitude,\ null as m_z,\ null as m_value ,\ null as qc_level,\ null as row_id,\ null as s_order,\ null as sensor_url,\ platform.url as platform_url ,\ platform.description as platform_description,\ organization.short_name as organization_short_name,\ organization.url as organization_url,\ null as m_type_display_order_row_id,\ null as epoch\ from platform\ left join organization on organization.row_id=platform.organization_id\ where platform.active=1 AND\ Contains( GeomFromText( \'POLYGON((%s))\'), GeomFromText( 'POINT(' || fixed_longitude || ' ' || fixed_latitude ||')' ) )\ order by multi_obs_platform_handle,m_type_display_order_row_id,sensor_s_order,m_date desc;"\ % (boundingBox,boundingBox) print(sql) latestObs = recursivedefaultdict() latestDate = None currentPlatform = None dbCursor = xeniaDb.dbConnection.executeQuery( sql ) if(dbCursor != None): for obsRow in dbCursor: #print("Organization: %s platform: %s" %(obsRow['organization_short_name'], obsRow['multi_obs_platform_handle'])) if(currentPlatform == None): currentPlatform = obsRow['multi_obs_platform_handle'] if(latestDate == None): latestDate = str(obsRow['m_date']) else: if(obsRow['m_date'] != None): #We only want the most current obs. if(latestDate != str(obsRow['m_date']) and currentPlatform == obsRow['multi_obs_platform_handle']): continue currentPlatform = obsRow['multi_obs_platform_handle'] latestDate = str(obsRow['m_date']) if(obsRow['m_type_display_order_row_id'] != None): #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_type_id'] = obsRow['multi_obs_m_type_id'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['obs_name'] = obsRow['obs_type_standard_name'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['uom'] = obsRow['uom_type_standard_name'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_value'] = obsRow['m_value'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_z'] = obsRow['m_z'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['qc_level'] = obsRow['qc_level'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['sensor_url'] = obsRow['sensor_url'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['sensor_id'] = obsRow['sensor_row_id'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['local_date'] = obsRow['local_date'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['obs_list'][obsRow['m_type_display_order_row_id']]['m_date'] = latestDate #assuming all observations are basically the same lat/lon as platform latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['m_lat'] = obsRow['m_lat'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['m_lon'] = obsRow['m_lon'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['url'] = obsRow['platform_url'] latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']]['platform_desc'] = obsRow['platform_description'] #latestObs[obsRow['organization_short_name']]['platform_list'][obsRow['multi_obs_platform_handle']][status] = $platform_status latestObs[obsRow['organization_short_name']]['name'] = obsRow['organization_short_name'] latestObs[obsRow['organization_short_name']]['url'] = obsRow['organization_url'] else: print( xeniaDb.dbConnection.getErrorInfo() ) sys.exit(-1) dbCursor.close() operatorKeys = latestObs.keys() operatorKeys.sort() platformCnt = 0 operatorCnt = 0 insertDate = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) for operator in operatorKeys: for platform in latestObs[operator]['platform_list']: print("Processing platform: %s" %(platform)) htmlContent = '' contentHeader = '' platformContent = '' latestDate = None platformParts = platform.split('.') lcPlatform = platformParts[1].lower() operator = platformParts[0] links ='<a href=%s%s_%s_%s_GeoRSS_latest.xml target=new title="RSS Feed"><img src="resources/images/default/rss_small.jpg"/></a>'\ %(GEORSSPATH, platformParts[0], lcPlatform, platformParts[2]); if( lcPlatform == 'cap2' or lcPlatform == 'sun2' or lcPlatform == 'frp2' or lcPlatform == 'ocp1' or lcPlatform == 'ilm2' or lcPlatform == 'ilm3' ): links += '<a href=%s%sRCOOS target=new title="Twitter Feed"><img src="resources/images/default/twitter.png"/></a>'\ %(TWITTERURL,lcPlatform) links += '<a href=%s%s target=new title="Data Query"><img src="resources/images/default/data_query.png"/></a>'\ %(DATAQUERYPAGEPATH, lcPlatform.upper()); links += '<a href=%s%s target=new title="Email Alerts"><img src="resources/images/default/mail.png"/></a>'\ %(EMAILALERTPAGEPATH, platform); desc = latestObs[operator]['platform_list'][platform]['platform_desc'] #No description in the database, so we'll make one based on the operator and platform if(len(desc) == 0): desc = "%s %s" % (operator, platformParts[1]) if( ('url' in latestObs[operator]['platform_list'][platform]) != False): platformUrl = latestObs[operator]['platform_list'][platform]['url'] elif(('url' in latestObs[operator]) != False): platformUrl = latestObs[operator]['url'] else: platformUrl = '' lat = 0.0 lon = 0.0 if(('m_lat' in latestObs[operator]['platform_list'][platform]) != False): lat = latestObs[operator]['platform_list'][platform]['m_lat'] else: print("No latitude defined for platform: %s" %(platform)) if(('m_lat' in latestObs[operator]['platform_list'][platform]) != False): lon = latestObs[operator]['platform_list'][platform]['m_lon'] else: print("No longitude defined for platform: %s" %(platform)) contentHeader = "<div id=\"popupobscontent\" class=\"popupobscontent\"><hr/><a href=\"%s\" target=new onclick=\"\">%s</a><p id=\"popupobsloc\" class=\"popupobsloc\">Latitude: %4.3f Longitude: %4.3f</p><p id=\"popupobslinks\" class=\"popupobslinks\">%s</p>"\ %(platformUrl, desc, lat, lon, links ) displayOrderKeys = latestObs[operator]['platform_list'][platform]['obs_list'].keys() displayOrderKeys.sort() obsDate = "" for displayOrder in displayOrderKeys: if(latestDate == None): if(latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_date'] != None): #latestDate = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_date'] latestDate = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['local_date'] obsDate = latestDate localDatetime = time.strptime(latestDate, '%Y-%m-%d %H:%M:%S') obsLocalEpochSecs = time.mktime(localDatetime) datetimeLabel = "<span id=\"popupobsstatus\" class=\"popupobsstatusold\">No data available within the past 6 hours</span>" localNow = time.mktime(time.localtime()) if((localNow - obsLocalEpochSecs) > 21600): datetimeLabel = "<span id=\"popupobsstatus\" class=\"popupobsstatusold\">No data available within the past 6 hours</span>" else: tz = 'EST' if(time.daylight == 1): tz = 'EDT' day = time.strftime("%m/%d", localDatetime) datetimeLabel = time.strftime("Surface conditions as of %I:%M %p", localDatetime) datetimeLabel = "%s %s on %s" %(datetimeLabel, tz, day) if((localNow - obsLocalEpochSecs) > 7200): datetimeLabel += "<br><span class=\"popupobsstatusstale\">Note: This report is more than 2 hours old</span>" platformContent = "<div id=\"popupobs\" class=\"popupobs\"><table class=\"popupobsdata\"><caption>%s</caption>"\ % (datetimeLabel) if(latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_value'] != None): obsUOM = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['uom'] value = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_value'] #Get the label we want to use for the observation obsLabel = uomConverter.getDisplayObservationName(latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['obs_name']) if(obsLabel == None): obsLabel = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['obs_name'] #Get the units we want to convert the data to. This is also used as the label for the units in the text display. displayUOM = uomConverter.getConversionUnits( obsUOM, 'en' ) if(len(displayUOM) == 0): displayUOM = obsUOM value = uomConverter.measurementConvert( value, obsUOM, displayUOM ) if(value == None): value = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_value'] displayUOM = obsUOM googURL = self.buildGoogleChartLink(xeniaDb, platform, obsLabel, latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['sensor_id'], obsUOM, displayUOM, latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['m_date'], uomConverter) measureLabel = "%s %s" %(str(value), displayUOM); qcLevel = latestObs[operator]['platform_list'][platform]['obs_list'][displayOrder]['qc_level'] #Add a bad or suspect label if the quality control flag is set to 1 or 2. if(qcLevel == 1): measureLabel += "(bad)" elif(qcLevel == 2): measureLabel += "(suspect)" platformContent += "<tr %s><td scope=\"row\">%s</td><td>%s</td></tr>"\ %(googURL, obsLabel, measureLabel) #Finished platform, increment our count. platformCnt += 1 #Check to see if we had any platform content, if not let's just tag it with nothing available before adding the entry into the #database. if(len(platformContent) == 0): platformContent = "<tr><td>No data available</td></tr>" print("Platform has no data, possible inactive station: %s" %(platform)) htmlContent = "%s%s</table><div id=\"popupobsgraph\"></div>" %(contentHeader,platformContent) """ lat = 0.0 if( 'm_lat' in latestObs[operator]['platform_list'][platform] != False): lat = latestObs[operator]['platform_list'][platform]['m_lat'] lon = 0.0 if('m_lon' in latestObs[operator]['platform_list'][platform] != False): lon = latestObs[operator]['platform_list'][platform]['m_lon'] """ dbCur = self.addRowToObsTable(insertDate, obsDate, lat, lon, operator, htmlContent, platform) if(dbCur == False): print(xeniaDb.dbConnection.getErrorInfo()) """ sql = "INSERT INTO html_content(wkt_geometry,organization,html,platform_handle)\ values ('POINT(%f %f)','%s','%s', '%s');"\ %(latestObs[operator]['platform_list'][platform]['m_lon'], latestObs[operator]['platform_list'][platform]['m_lat'], operator, htmlContent, platform) #print("Saving content.\n %s" %(sql)) dbCur = self.executeQuery(sql) if(dbCur != None): self.db.commit() else: print(xeniaDb.dbConnection.getErrorInfo()) """ operatorCnt += 1 self.db.commit() print("Processed %d operators and %d platforms." %(operatorCnt, platformCnt)) self.copyToWorkingDB() except Exception, E: print(traceback.format_exc())
def computeMonthlyDataPoints(self, platformList, beginYear, endYear, QAQCFlags, outputFilePath, writeRawDataPoints): import calendar # If we want to use the qc_level to determine which data to include, let's build the SQL for this. # qaqcWHERE = '' # if(len(QAQCFlags)): # for qcLevel in QAQCFlags: # if(len(qaqcWHERE)): # qaqcWHERE += 'OR ' # qaqcWHERE += "qc_level=%d " % (qcLevel) # qaqcWHERE = "AND (%s)" %(qaqcWHERE) for platformHandle in platformList: # Get all the observations on the platform platformNfoCur = self.dbConnection.getPlatformInfo(platformHandle) # Platform doesn't seem to exist, so move on. if platformNfoCur == None: continue platformNfo = platformNfoCur.fetchone() platformID = int(platformNfo["row_id"]) platformNfoCur.close() sql = ( "SELECT\ obs_type.standard_name \ ,uom_type.standard_name as uom \ ,sensor.row_id as sensor_id\ ,sensor.m_type_id as m_type_id\ ,sensor.s_order as s_order\ FROM sensor \ left join m_type on m_type.row_id=sensor.m_type_id \ left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id \ left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id \ left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id \ WHERE sensor.platform_id = %d ORDER BY obs_type.standard_name ASC" % (platformID) ) sensorCur = self.dbConnection.executeQuery(sql) # No sensors available on the platform. if sensorCur == None: continue sensorNfo = recursivedefaultdict() for row in sensorCur: sensorNfo[row["standard_name"]]["uom"] = row["uom"] sensorNfo[row["standard_name"]]["sensor_id"] = int(row["sensor_id"]) sensorNfo[row["standard_name"]]["m_type_id"] = int(row["m_type_id"]) sensorNfo[row["standard_name"]]["sorder"] = int(row["s_order"]) sensorCur.close() outputFile = "%s/%s-yearly-stats-%s_%s.csv" % (outputFilePath, platformHandle, beginYear, endYear) statsFile = open(outputFile, "w") statsFile.write("Observation,StartDate,EndDate,Min,Max,Average,StdDev,90thPercentile,TotalRecordCount\n") rawDataPoints = None if writeRawDataPoints: outputFile = "%s/%s-yearly-raw.csv" % (outputFilePath, platformHandle) rawDataPoints = open(outputFile, "w") if rawDataPoints != None: rawDataPoints.write("Observation,StartDate,EndDate,Data\n") yearList = [] if beginYear == None: # Get the distinct years if self.dbConnection.dbType == dbTypes.PostGRES: sql = ( "SELECT DISTINCT(EXTRACT(YEAR FROM m_date)) as year FROM multi_obs WHERE platform_handle='%s'" % (platformHandle) ) else: sql = ( "SELECT DISTINCT(strftime('%%Y', m_date)) as year FROM multi_obs WHERE platform_handle='%s'" % (platformHandle) ) dbCursor = self.dbConnection.executeQuery(sql) if dbCursor != None: for row in dbCursor: yearList.append(int(row["year"])) dbCursor.close() else: for i in range(beginYear, endYear + 1): yearList.append(i) # This is a dictionary we use to hold all the months of data for the years. We use it as a collection # of stats() objects so we can calculate some overall stats for each month over the years. # obsOverallMonthStats = recursivedefaultdict() print("Processing: %s" % (platformHandle)) for year in yearList: for obsName in sensorNfo: uom = sensorNfo[obsName]["uom"] sensorID = sensorNfo[obsName]["sensor_id"] mTypeID = sensorNfo[obsName]["m_type_id"] # sOrder = sensorNfo[obsName]['sorder'] # Now for each month, we calc stats on the data. for month in range(1, 13): print("Obs: %s(%s) Year: %d Month: %d" % (obsName, uom, year, month)) monthStats = stats() dayCnt = calendar.monthrange(year, month) startDate = "%d-%02d-%02dT00:00:00" % (year, month, 1) endDate = "%d-%02d-%2dT24:00:00" % (year, month, dayCnt[1]) if rawDataPoints != None: rawDataPoints.write("%s,%s,%s" % (obsName, startDate, endDate)) # mTypeID = self.dbConnection.getMTypeFromObsName(obsName, uom, platformHandle, sOrder) # sql = "SELECT m_date,m_value FROM multi_obs WHERE (m_date >= '%s' AND m_date <= '%s')\ # AND sensor_id=%d %s;"\ # %(startDate,endDate,sensorID,qaqcWHERE) sql = ( "SELECT m_date,m_value,qc_level FROM multi_obs WHERE (m_date >= '%s' AND m_date <= '%s')\ AND sensor_id=%d;" % (startDate, endDate, sensorID) ) dbCursor = self.dbConnection.executeQuery(sql) if dbCursor != None: for row in dbCursor: goodVal = False # Use all data. if len(QAQCFlags) == 0: goodVal = True elif row["qc_level"] != None: for qaqcFlag in QAQCFlags: if qaqcFlag == row["qc_level"]: goodVal = True break if goodVal: m_value = row["m_value"] if m_value != None: m_value = float(m_value) monthStats.addValue(m_value) if rawDataPoints != None: rawDataPoints.write(",%f" % (m_value)) monthStats.doCalculations() avg = monthStats.average if avg == None: avg = -1.0 stdDev = monthStats.stdDev if stdDev == None: stdDev = -1.0 popStdDev = monthStats.populationStdDev if popStdDev == None: popStdDev = -1.0 UpperPercentile = monthStats.getValueAtPercentile(90) if UpperPercentile == None: UpperPercentile = -1.0 min = monthStats.minVal if min == None: min = -1.0 max = monthStats.maxVal if max == None: max = -1.0 statsFile.write( "%s,%s,%s,%f,%f,%f,%f,%f,%d\n" % ( obsName, startDate, endDate, min, max, avg, stdDev, UpperPercentile, len(monthStats.items), ) ) if rawDataPoints != None: rawDataPoints.write("\n") else: i = 0 statsFile.close() if rawDataPoints != None: rawDataPoints.close()