def runChild(procIndex, itemsQueue, resultsQueue, lasFolder, dbname, dbuser, dbpass, dbhost, dbport): connection, cursor = utils.connectToDB(dbname, dbuser, dbpass, dbhost, dbport) kill_received = False while not kill_received: itemId = None try: # This call will patiently wait until new job is available itemId = itemsQueue.get() except: # if there is an error we will quit the generation kill_received = True if itemId == None: # If we receive a None job, it means we can stop this workers # (all the create-image jobs are done) kill_received = True else: logging.info('PROC%d: Getting minimum and maximum Z for item %d' % (procIndex,itemId)) outputFile = 'temp_%03d.las' % itemId try: (returnOk, vertices, minZ, maxZ, avgZ, numpoints) = GetItemLAS.create_cut_out(cursor, lasFolder, outputFile, itemId, BUFFER, CONCAVE) # We do not need the cutout if os.path.isfile(outputFile): os.remove(outputFile) if returnOk: logging.info('PROC%d: Updating DB minimum and maximum Z for item %d' % (procIndex,itemId)) utils.dbExecute(cursor, "UPDATE ITEM SET (min_z,max_z) = (%s,%s) WHERE item_id = %s", [minZ, maxZ, itemId]) except Exception, e: connection.rollback() logging.error('PROC%d: Can not update minimum and maximum Z for item %d' % (procIndex,itemId)) logging.error(e) resultsQueue.put((procIndex, itemId))
def run(args): logname = os.path.basename(__file__) + '.log' utils.start_logging(filename=logname, level=args.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) (returnOk, vertices, minZ, maxZ, avgZ, numpoints) = create_cut_out(cursor, args.las, args.output, args.itemid, args.buffer, args.concave) if returnOk: # Create CSV with vertices of footprint footoutput = args.output + '_footprint.csv' logging.info('Creating CSV %s with vertices of concave hull of footprint' % footoutput) fpOutput = open(footoutput, 'w') for point in vertices: point.append(str(avgZ)) fpOutput.write(','.join(point) + '\n') fpOutput.close() logging.info('#Points: %d' % numpoints) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logging.info(msg)
def main(): connection = utils.connectToDB() utils.ignoreWarnings() leaguesInput = raw_input('Please enter desired league ids separated by comma (all for all of them): ') seasonsInput = raw_input('Please enter desired seasons separated by comma (all for all of them): ') directedInput = raw_input('Do you want to analyze a directed network? (0/1): ') weightedInput = raw_input('Do you want to analyze a weighted network? (0/1): ') if leaguesInput.lower() == 'all': leagues = databaseBridger.getAllLeagues(connection) leagues = list(map(lambda league: league[0], leagues)) else: leagues = leaguesInput.rstrip(',').split(',') leagues = [int(league) for league in leagues] if bool(int(weightedInput)): simpleWeightsInput = raw_input('Do you want to have weights only by score? (0/1): ') if bool(int(simpleWeightsInput)): logWeightsInput = 0 else: logWeightsInput = raw_input('Do you want to calculate weights with logarithmic function? (0/1): ') else: logWeightsInput = 0 analyzeBySeasonInput = raw_input('Do you want to analyze network properties season by season? (0/1): ') analyzeOverTimeInput = raw_input('Do you want to analyze properties over time? (0/1): ') isDirected = bool(int(directedInput)) isWeighted = bool(int(weightedInput)) hasSimpleWeights = bool(int(simpleWeightsInput)) hasLogWeights = bool(int(logWeightsInput)) analyzeOverTime = bool(int(analyzeOverTimeInput)) analyzeBySeason = bool(int(analyzeBySeasonInput)) if analyzeBySeason: printToFileInput = raw_input('Do you want to have output in a file? (0/1): ') printToFile = bool(int(printToFileInput)) else: printToFile = False csvOutputInput = raw_input('Do you want to have basic network stats output in a CSV? (0/1): ') printToCsv = bool(int(csvOutputInput)) # 26 RGB colors for relative entropy graph colors = constants.rgb26 # mix them so similar colors will not get plotted together shuffle(colors) # scale RGB values to the [0, 1] interval for i in range(len(colors)): r, g, b = colors[i] colors[i] = (r / 255.0, g / 255.0, b / 255.0) analyze(connection, leagues, seasonsInput, isDirected, isWeighted, analyzeBySeason, analyzeOverTime, hasLogWeights, hasSimpleWeights, printToFile, printToCsv, colors) return 0
def run(opts): # Start logging logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # database connection connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) if opts.itemid == '?': utils.listRawDataItems(cursor) return elif opts.itemid == '' or opts.itemid == '!': query = """ SELECT raw_data_item_id,abs_path,background FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) JOIN RAW_DATA_ITEM_PC USING (raw_data_item_id) WHERE raw_data_item_id NOT IN ( SELECT raw_data_item_id FROM POTREE_DATA_ITEM_PC)""" # Get the list of items that are not converted yet (we sort by background to have the background converted first) raw_data_items, num_raw_data_items = utils.fetchDataFromDB( cursor, query) for (rawDataItemId, absPath, isBackground) in raw_data_items: if opts.itemid == '': levels = getNumLevels(opts, isBackground) createPOTree(cursor, rawDataItemId, opts.potreeDir, levels) else: m = '\t'.join((str(rawDataItemId), absPath)) print m logging.info(m) else: for rawDataItemId in opts.itemid.split(','): rows, num_rows = utils.fetchDataFromDB( cursor, 'SELECT background FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) WHERE raw_data_item_id = %s', [int(rawDataItemId)]) if num_rows == 0: logging.error('There is not a raw data item with id %d' % int(rawDataItemId)) return isBackground = rows[0][0] levels = getNumLevels(opts, isBackground) createPOTree(cursor, int(rawDataItemId), opts.potreeDir, levels) # close DB connection utils.closeConnectionDB(connection, cursor) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logging.info(msg)
def main(): connection = utils.connectToDB() print "[Stats sum calculator] Calculating clubs sums and updating clubs table..." utils.calculateClubsSums(connection) print "[Stats sum calculator] Clubs table updated." print "[Stats sum calculator] Calculating players career sums and updating players table..." utils.calculatePlayersCareerSums(connection) print "[Stats sum calculator] Players table updated."
def run(args): logname = os.path.basename(__file__) + '.log' utils.start_logging(filename=logname, level=args.log) # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) itemIds = None if args.itemid != '': itemIds = args.itemid.split(',') utils.listRawDataItems(cursor, itemIds)
def run(args): # start logging logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=utils.DEFAULT_LOG_LEVEL) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' %localtime print msg logging.info(msg) # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) itemIds = [] if args.itemid == '': data,num = utils.fetchDataFromDB(cursor, 'SELECT item_id FROM ITEM WHERE NOT background') for (itemId,) in data: itemIds.append(itemId) else: itemIds = args.itemid.split(',') # close the conection to the DB utils.closeConnectionDB(connection, cursor) # Create queues itemsQueue = multiprocessing.Queue() # The queue of tasks (queries) resultsQueue = multiprocessing.Queue() # The queue of results for itemId in itemIds: itemsQueue.put(int(itemId)) for i in range(args.cores): #we add as many None jobs as numUsers to tell them to terminate (queue is FIFO) itemsQueue.put(None) procs = [] # We start numUsers users processes for i in range(args.cores): procs.append(multiprocessing.Process(target=runChild, args=(i, itemsQueue, resultsQueue, args.las, args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport))) procs[-1].start() for i in range(len(itemIds)): [procIndex, itemId] = resultsQueue.get() # wait for all users to finish their execution for i in range(args.cores): procs[i].join() # measure elapsed time elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logging.info(msg)
def run(opts): # Start logging #logname = os.path.basename(__file__) + '.log' logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # database connection connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) if opts.itemid == '?': utils.listRawDataItems(cursor) return elif opts.itemid == '' or opts.itemid == '!': query = """ SELECT raw_data_item_id, abs_path FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) WHERE NOT background AND raw_data_item_id NOT IN ( SELECT raw_data_item_id FROM OSG_DATA_ITEM_PC_SITE UNION SELECT raw_data_item_id FROM OSG_DATA_ITEM_MESH UNION SELECT raw_data_item_id FROM OSG_DATA_ITEM_PICTURE)""" # Get the list of items that are not converted yet (we sort by background to have the background converted first) raw_data_items, num_raw_data_items = utils.fetchDataFromDB( cursor, query) for (rawDataItemId, absPath) in raw_data_items: if opts.itemid == '': createOSG(cursor, rawDataItemId, opts.osgDir) else: m = '\t'.join((str(rawDataItemId), absPath)) print m logging.info(m) else: for rawDataItemId in opts.itemid.split(','): createOSG(cursor, int(rawDataItemId), opts.osgDir) # close DB connection utils.closeConnectionDB(connection, cursor) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logging.info(msg)
def run(opts): # Start logging logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # database connection connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) if opts.itemid == '?': utils.listRawDataItems(cursor) return elif opts.itemid == '' or opts.itemid == '!': query = """ SELECT raw_data_item_id,abs_path,background FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) JOIN RAW_DATA_ITEM_PC USING (raw_data_item_id) WHERE raw_data_item_id NOT IN ( SELECT raw_data_item_id FROM POTREE_DATA_ITEM_PC)""" # Get the list of items that are not converted yet (we sort by background to have the background converted first) raw_data_items, num_raw_data_items = utils.fetchDataFromDB(cursor, query) for (rawDataItemId,absPath,isBackground) in raw_data_items: if opts.itemid == '' : levels = getNumLevels(opts, isBackground) createPOTree(cursor, rawDataItemId, opts.potreeDir, levels) else: m = '\t'.join((str(rawDataItemId),absPath)) print m logging.info(m) else: for rawDataItemId in opts.itemid.split(','): rows,num_rows = utils.fetchDataFromDB(cursor, 'SELECT background FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) WHERE raw_data_item_id = %s', [int(rawDataItemId)]) if num_rows == 0: logging.error('There is not a raw data item with id %d' % int(rawDataItemId)) return isBackground = rows[0][0] levels = getNumLevels(opts, isBackground) createPOTree(cursor, int(rawDataItemId), opts.potreeDir, levels) # close DB connection utils.closeConnectionDB(connection, cursor) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logging.info(msg)
def run(args): logname = os.path.basename(__file__) + '.log' utils.start_logging(filename=logname, level=args.log) # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) itemIds = None if args.itemid != '': itemIds = args.itemid.split(',') utils.listRawDataItems(cursor, itemIds)
def run(opts): # Start logging #logname = os.path.basename(__file__) + '.log' logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # database connection connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) if opts.itemid == '?': utils.listRawDataItems(cursor) return elif opts.itemid == '' or opts.itemid == '!': query = """ SELECT raw_data_item_id, abs_path FROM RAW_DATA_ITEM JOIN ITEM USING (item_id) WHERE NOT background AND raw_data_item_id NOT IN ( SELECT raw_data_item_id FROM OSG_DATA_ITEM_PC_SITE UNION SELECT raw_data_item_id FROM OSG_DATA_ITEM_MESH UNION SELECT raw_data_item_id FROM OSG_DATA_ITEM_PICTURE)""" # Get the list of items that are not converted yet (we sort by background to have the background converted first) raw_data_items, num_raw_data_items = utils.fetchDataFromDB(cursor, query) for (rawDataItemId, absPath) in raw_data_items: if opts.itemid == '': createOSG(cursor, rawDataItemId, opts.osgDir) else: m = '\t'.join((str(rawDataItemId),absPath)) print m logging.info(m) else: for rawDataItemId in opts.itemid.split(','): createOSG(cursor, int(rawDataItemId), opts.osgDir) # close DB connection utils.closeConnectionDB(connection, cursor) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logging.info(msg)
def runChild(procIndex, itemsQueue, resultsQueue, lasFolder, dbname, dbuser, dbpass, dbhost, dbport): connection, cursor = utils.connectToDB(dbname, dbuser, dbpass, dbhost, dbport) kill_received = False while not kill_received: itemId = None try: # This call will patiently wait until new job is available itemId = itemsQueue.get() except: # if there is an error we will quit the generation kill_received = True if itemId == None: # If we receive a None job, it means we can stop this workers # (all the create-image jobs are done) kill_received = True else: logging.info('PROC%d: Getting minimum and maximum Z for item %d' % (procIndex, itemId)) outputFile = 'temp_%03d.las' % itemId try: (returnOk, vertices, minZ, maxZ, avgZ, numpoints) = GetItemLAS.create_cut_out( cursor, lasFolder, outputFile, itemId, BUFFER, CONCAVE) # We do not need the cutout if os.path.isfile(outputFile): os.remove(outputFile) if returnOk: logging.info( 'PROC%d: Updating DB minimum and maximum Z for item %d' % (procIndex, itemId)) utils.dbExecute( cursor, "UPDATE ITEM SET (min_z,max_z) = (%s,%s) WHERE item_id = %s", [minZ, maxZ, itemId]) except Exception, e: connection.rollback() logging.error( 'PROC%d: Can not update minimum and maximum Z for item %d' % (procIndex, itemId)) logging.error(e) resultsQueue.put((procIndex, itemId))
def calculateMasseyRatings(leagueId, seasonId): connection = utils.connectToDB() matchesData = databaseBridger.getAllMatches(connection, leagueId, seasonId, 'regular') clubsDict = dict() numClubs = 0 games = [] for matchRecord in matchesData: homeClub = int(matchRecord[2]) awayClub = int(matchRecord[3]) # reindex club ids if homeClub in clubsDict: homeClubReindexed = clubsDict[homeClub] else: homeClubReindexed = numClubs numClubs = numClubs + 1 clubsDict[homeClub] = homeClubReindexed if awayClub in clubsDict: awayClubReindexed = clubsDict[awayClub] else: awayClubReindexed = numClubs numClubs = numClubs + 1 clubsDict[awayClub] = awayClubReindexed homeScore = matchRecord[4] awayScore = matchRecord[5] games.append( [homeClubReindexed, awayClubReindexed, homeScore - awayScore]) numClubs = len(clubsDict) M = buildGamesMatrix(games, numClubs) E = buildOutcomes(games) M = numpy.vstack((M, numpy.ones([numClubs]))) E = numpy.append(E, 0) ratings = numpy.linalg.lstsq(M, E)[0] return ratings, numClubs
def run(opts): # Set logging #logname = os.path.splitext(os.path.basename(opts.sql))[0] + '.log' logname = os.path.basename(opts.sql) + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' %localtime print msg logging.info(msg) os.system('createdb ' + utils.postgresConnectString(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport, True)) connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) msg = 'Adding PostGIS extension' logging.info(msg) #print msg cursor.execute("CREATE EXTENSION POSTGIS") connection.commit() success_loading = utils.load_sql_file(cursor, opts.sql) msg = 'Granting relevant permissions' logging.info(msg) #print msg if success_loading: cursor.execute("select tablename from pg_tables where schemaname = 'public'") tablesNames = cursor.fetchall() for (tableName,) in tablesNames: cursor.execute('GRANT SELECT ON ' + tableName + ' TO public') for tableName in ('ITEM', 'ITEM_OBJECT', 'OSG_LOCATION', 'OSG_LABEL', 'OSG_CAMERA', 'OSG_ITEM_CAMERA', 'OSG_ITEM_OBJECT'): cursor.execute( 'GRANT SELECT,INSERT,UPDATE,DELETE ON ' + tableName + ' TO public') connection.commit() connection.close() msg = 'Finished. Total elapsed time %.02f seconds. See %s' % ((time.time() - t0), logname) logging.info(msg) print msg
def main(): connection = utils.connectToDB() # --- PARSE ALL FILES IN A DIRECTORY --- # rootDirectory = "../FileGetter/html/" for dirname1, dirnames1, filenames1 in os.walk(rootDirectory): # loop through leagues for leagueDirectory in dirnames1: currentDirectory1 = os.path.join(dirname1, leagueDirectory) for dirname2, dirnames2, filenames2 in os.walk(currentDirectory1): # loop through seasons for seasonDirectory in dirnames2: currentDirectory2 = os.path.join(currentDirectory1, seasonDirectory) # loop through clubs for filename in os.listdir(currentDirectory2): print "[File Parser] Parsing file %s, league: %s, season: %s..." % \ (filename, leagueDirectory, seasonDirectory) startTime = time.time() parseFile(connection, currentDirectory2 + '/' + filename, leagueDirectory, seasonDirectory) endTime = time.time() print "[File Parser] Parsed file %s, league: %s, season: %s | Time spent %f s" % \ (filename, leagueDirectory, seasonDirectory, (endTime - startTime)) print "\n[File Parser] Parsed season %s, league: %s\n" % \ (seasonDirectory, leagueDirectory) print "\n[File Parser] Parsed all seasons for league %s\n" % \ leagueDirectory # --- PARSE ONE FILE ONLY --- # # filename = "../FileGetter/html/LaLiga/15/VCF_1049" # parseFile(filename, 'LaLiga', '15') # --- PARSE AND UPDATE PLAYER CLUB SEASON DETAILS --- # parseAllPlayerClubSeasonDetails(connection)
def main(): connection = utils.connectToDB() csvFilename = raw_input('Please enter CSV filename: ') csvPath = '../FileConqueror/csv/matches/' csvFile = csvPath + csvFilename leagueInput = raw_input( 'Please enter what kind of data CSV file includes [NBA/football/various]: ' ) delimeterInput = raw_input( 'Please enter the delimeter for this CSV file: ') if (leagueInput.lower() == 'nba'): parseNBACSVFile(connection, csvFile, delimeterInput) elif (leagueInput.lower() == 'football'): parseFootballCSVFile(connection, csvFile, delimeterInput) elif (leagueInput.lower() == 'various'): parseVariousSportsCSVFile(connection, csvFile, delimeterInput)
# Extract out each rule rule = {'name': '', 'description': '', 'text': ''} rules = [] for line in rulesData.split('\n'): if line.startswith('rule'): # Add previous rule to list if rule['text'] != '': rules.append(rule) rule = {'name': '', 'description': '', 'text': ''} rule['name'] = line.replace('rule', '').strip() if line.strip().startswith('description'): rule['description'] = line.replace('description =', '').replace('\"', '').strip() rule['text'] += line + '\n' rules.append(rule) cur, db = utils.connectToDB() # Disable all previous rules stmt = "UPDATE rules set enabled = 0" cur.execute(stmt) db.commit() # Add our new rules for rule in rules: stmt = "INSERT INTO rules (name, description) VALUES (%(name)s, %(description)s)" cur.execute(stmt, {'name': rule['name'], 'description': rule['description']}) rule_id = cur.lastrowid db.commit() stmt = "INSERT INTO rules_text (id, text) VALUES (%(id)s, %(text)s)" cur.execute(stmt, {'id': rule_id, 'text': rule['text']})
def run(args): global logger global offsetX global offsetY global offsetZ logname = os.path.basename(args.output) + '.log' logger = utils.start_logging(filename=logname, level=args.log) # start logging localtime = utils.getCurrentTimeAsAscii() msg = __file__ + ' script logging start at %s' % localtime print msg logger.info(msg) t0 = time.time() # connect to DB and get a cursor connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost) # We assume the osg location is relative # We need to make it absolute by adding the offset of the background with srid as provided query = """ SELECT C.offset_x, C.offset_y, C.offset_z FROM raw_data_item A, raw_data_item_pc B, osg_data_item_pc_background C WHERE A.raw_data_item_id = B.raw_data_item_id AND B.raw_data_item_id = C.raw_data_item_id AND A.srid = %s""" queryArgs = [ args.srid, ] backgroundOffsets, num_backgrounds = utils.fetchDataFromDB( cursor, query, queryArgs) if num_backgrounds: (offsetX, offsetY, offsetZ) = backgroundOffsets[0] # get all items query = 'SELECT item_id, ST_ASGEOJSON(geom), min_z, max_z FROM item WHERE NOT background ORDER BY item_id' sites, num_sites = utils.fetchDataFromDB(cursor, query) data = [] for (itemId, itemGeom, minz, maxz) in sites: # Generate the JSON data for this item dataSite = {} dataSite["id"] = itemId if itemGeom != None: dataSite["footprint"] = json.loads(itemGeom)['coordinates'] dataSite["footprint_altitude"] = [minz, maxz] addThumbnail(cursor, itemId, dataSite) addSiteMetaData(cursor, itemId, dataSite) addPointCloud(cursor, itemId, dataSite, args.srid) addMeshes(cursor, itemId, dataSite, args.srid) addObjectsMetaData(cursor, itemId, dataSite, args.srid) data.append(dataSite) # close the Db connection utils.closeConnectionDB(connection, cursor) # save the data into JSON file save2JSON(args.output, data) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logger.info(msg)
def run(args): global logger global connection global cursor # start logging logname = os.path.basename(args.input) + '.log' logger = utils.start_logging(filename=logname, level=utils.DEFAULT_LOG_LEVEL) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logger.info(msg) if not os.path.isfile(args.input): msg = "Input file is not found!" print msg logger.error(msg) return # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) if args.input.endswith('shp'): prjFile = args.input.replace('shp', 'prj') if not os.path.isfile(prjFile): msg = "Input PRJ file is not found!" print msg logger.error(msg) return sqlFile = os.path.basename(args.input).replace('shp', 'sql') shp2psql = 'shp2pgsql -s ' + str(getEPSG( prjFile)) + ' -c ' + args.input + ' sites_geoms_temp > ' + sqlFile print shp2psql logger.info(shp2psql) os.system(shp2psql) else: sqlFile = args.input for line in open(sqlFile, 'r').read().split('\n'): if line.count('CREATE TABLE'): if line.count('sites_geoms_temp') == 0: msg = "The table in the SQL file must be named sites_geom_temp. Replace the table name to sites_geoms_temp!" print msg logger.error(msg) return # clean the temp table if exsisted clean_temp_table(args) # load the table sites_geoms_temp from the SQL file ot the DB success_loading = utils.load_sql_file(cursor, sqlFile) if args.input.endswith('shp'): os.system('rm -rf ' + sqlFile) if success_loading: # check if the SITES table is empty, then change the type of the geom field update_geom_col_type(cursor) # find the lists of new IDs and list of overlapping IDs no_item_well_temp_ids, both_in_item_and_temp_ids = find_lists(cursor) # insert the object geometries per site for the sites not in item, but in the sites_geoms_temp table update_geometries(no_item_well_temp_ids, True) # update the union of object geometries per site for the sites both in item and sites_geoms_temp table update_geometries(both_in_item_and_temp_ids, False) # clean the temp table clean_temp_table(args) # close the conection to the DB utils.closeConnectionDB(connection, cursor) # measure elapsed time elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logger.info(msg) return
def main(): connection = utils.connectToDB() utils.calculateClubsSums(connection)
def run(args): # set logging level global logger global connection global cursor logname = os.path.basename(__file__) + '.log' logger = utils.start_logging(filename=logname, level=args.log) localtime = utils.getCurrentTimeAsAscii() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logger.info(msg) # start timer t0 = time.time() # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) if args.itemid == '?': utils.listRawDataItems(cursor) return else: for rawDataItemId in args.itemid.split(','): # fetch the abs_path abs_paths = fetch_abs_path(rawDataItemId) msg = 'Abs path fetched: %s' % abs_paths print msg logger.info(msg) # fetch the potree abs_paths abs_potree_paths, num_potree = fetch_potree_abs_paths(rawDataItemId) msg = '%s abs potree paths fetched %s' %(num_potree, abs_potree_paths) print msg logger.info(msg) # fetch the nexus abs_paths abs_nexus_paths, num_nexus = fetch_nexus_abs_paths(rawDataItemId) msg = '%s abs nexus paths fetched %s' %(num_nexus, abs_nexus_paths) print msg logger.info(msg) # fetch the OSG abs_paths PC abs_osg_pc_paths, num_osg_pc = fetch_osg_abs_paths_pc(rawDataItemId) msg = '%s abs OSG paths for PC fetched: %s' %(num_osg_pc, abs_osg_pc_paths) print msg logger.info(msg) # fetch the OSG abs_paths mesh abs_osg_mesh_paths, num_osg_mesh = fetch_osg_abs_paths_mesh(rawDataItemId) msg = '%s abs OSG paths for meshes fetched: %s' %(num_osg_mesh, abs_osg_mesh_paths) print msg logger.info(msg) # fetch the OSG abs_paths picture abs_osg_picture_paths, num_osg_picture = fetch_osg_abs_paths_picture(rawDataItemId) msg = '%s abs OSG paths for pictures fetched: %s' %(num_osg_picture, abs_osg_picture_paths) print msg logger.info(msg) # fetch the OSG abs_paths PC BG abs_osg_pc_bg_paths, num_osg_pc_bg = fetch_osg_abs_paths_pc_bg(rawDataItemId) msg = '%s abs OSG paths for PC BG fetched: %s' %(num_osg_pc_bg, abs_osg_pc_bg_paths) print msg logger.info(msg) # remove the files related to the above absolute paths for abs_paths_to_remove in (abs_paths, abs_potree_paths, abs_nexus_paths, abs_osg_pc_paths, abs_osg_mesh_paths, abs_osg_picture_paths, abs_osg_pc_bg_paths): remove_data(abs_paths_to_remove) msg = 'Removed data locations related to raw data item %s (%s)!' % (rawDataItemId, abs_paths[0]) print msg logger.info(msg) # measure elapsed time elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logger.info(msg)
def run(opts): global logger # Define logger and start logging #logname = os.path.basename(opts.output) + '.log' logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' logger = utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logger.info(msg) if not opts.output.endswith(".conf.xml"): logger.error('The output file must end with .conf.xml') raise IOError('The output file must end with .conf.xml') # Create python postgres connection global cursor connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) # Check that provided background is in DB query = """ SELECT OSG_DATA_ITEM_PC_BACKGROUND.abs_path,srid FROM OSG_DATA_ITEM_PC_BACKGROUND JOIN RAW_DATA_ITEM USING (raw_data_item_id)""" rows, num_rows = utils.fetchDataFromDB(cursor, query) backGroundAbsPath = None backgroundSRID = None for (bgAbsPath,bgSRID) in rows: if opts.background == os.path.basename(bgAbsPath): backGroundAbsPath = bgAbsPath backgroundSRID = bgSRID if backGroundAbsPath == None: errorMsg = 'Background ' + opts.background + ' is not found' logger.error(errorMsg) raise Exception(errorMsg) # Get the root object: the OSG configuration rootObject = viewer_conf_api.osgRCconfiguration() # set version rootObject.set_version("0.2") # Add all the different XML of the active objects # (we add distinct since the boundings will share XMLs) query = """ SELECT DISTINCT xml_abs_path FROM OSG_DATA_ITEM ORDER BY xml_abs_path""" rows, num_rows = utils.fetchDataFromDB(cursor, query) for (xmlPath,) in rows: if xmlPath.count(opts.osg) == 0: logger.error('Mismatch between given OSG data directory ' + 'and DB content') rootObject.add_objectLibrary(viewer_conf_api.objectLibrary (url=os.path.relpath(xmlPath, opts.osg))) # Add the object library with the boundings rootObject.add_objectLibrary(viewer_conf_api.objectLibrary (url=utils.BOUNDINGS_XML_RELATIVE)) # Add the cameras that are in the DB cameras = viewer_conf_api.cameras() query = """ SELECT osg_camera_name, srid, x, y, z, h, p, r FROM OSG_CAMERA JOIN OSG_LOCATION USING (osg_location_id)""" rows, num_rows = utils.fetchDataFromDB(cursor, query) for (name, srid, x, y, z, h, p, r) in rows: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) cameras.add_camera(viewer_conf_api.camera (name=name, x=x, y=y, z=z, h=h, p=p, r=r)) # Add Default cameras for the items that have no camera in the DB query = """ SELECT item_id, ST_SRID(geom), st_x(st_centroid(geom)), st_y(st_centroid(geom)), min_z + ((max_z - min_z) / 2) FROM ITEM WHERE NOT background AND geom IS NOT null AND item_id NOT IN ( SELECT DISTINCT item_id FROM OSG_ITEM_CAMERA ) ORDER BY item_id""" rows, numitems = utils.fetchDataFromDB(cursor, query) for (itemId, srid, x, y, z) in rows: # only call getOSGPosition if [x,y,z] are not None # should item_id = -1 be added? if all(position is not None for position in [x,y,z]) and itemId>0: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) cameras.add_camera(viewer_conf_api.camera (name=utils.DEFAULT_CAMERA_PREFIX + str(itemId), x=x, y=y, z=z)) rootObject.set_cameras(cameras) # Add the XML content of the preferences rootObject.set_preferences(viewer_conf_api.parseString(DEFAULT_PREFENCES)) attributes = viewer_conf_api.attributes() # Use generic method to fill all properties. # We need the name in the XML, the column name in the DB and # the table name in the DB for property in utils.ATTRIBUTES_ORDER: (cName, tName) = utils.ATTRIBUTES[property] elements = getattr(viewer_conf_api, property + 's')() # We need to call the columns and tables with extra "" because # they were created from the Access DB # utils.dbExecute(cursor, 'SELECT "' + cName + '" FROM "' + tName + '"') utils.dbExecute(cursor, 'SELECT ' + cName + ' FROM ' + tName) for (element,) in cursor: getattr(elements, 'add_' + property)(getattr( viewer_conf_api, property)(name=element)) getattr(attributes, 'set_' + property + 's')(elements) rootObject.set_attributes(attributes) # Add all the static objects, i.e. the OSG from the background # Add the static object for the background staticObjects = viewer_conf_api.staticObjects() staticObjects.add_staticObject(viewer_conf_api.staticObject (url=os.path.relpath( glob.glob(backGroundAbsPath + '/' + utils.OSG_DATA_PREFIX + '.osgb')[0], opts.osg))) # Add hardcode DOME staticObjects.add_staticObject(viewer_conf_api.staticObject (url=utils.DOMES_OSG_RELATIVE)) rootObject.set_staticObjects(staticObjects) # Add the 5 different layers of active objects activeObjects = viewer_conf_api.activeObjects() # First we add points, meshes and pcitures which are related to # the active_objects_sites layersData = [('points', 'OSG_DATA_ITEM_PC_SITE', utils.AO_TYPE_PC), ('photos', 'OSG_DATA_ITEM_PICTURE', utils.AO_TYPE_PIC), ('meshes', 'OSG_DATA_ITEM_MESH', utils.AO_TYPE_MESH)] for (layerName, tableName, inType) in layersData: layer = viewer_conf_api.layer(name=layerName) query = """ SELECT item_id, raw_data_item_id, OSG_LOCATION.srid, x, y, z, xs, ys, zs, h, p, r, cast_shadow FROM """ + tableName + """ JOIN OSG_DATA_ITEM USING (osg_data_item_id) JOIN OSG_LOCATION USING (osg_location_id) JOIN RAW_DATA_ITEM USING (raw_data_item_id) ORDER BY item_id""" rows, numitems = utils.fetchDataFromDB(cursor, query) for (itemId, rawDataItemId, srid, x, y, z, xs, ys, zs, h, p, r, castShadow) in rows: # only call getOSGPosition if [x,y,z] are not None if all(position is not None for position in [x,y,z]): if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) uniqueName = utils.codeOSGActiveObjectUniqueName(cursor, inType, rawDataItemId) activeObject = viewer_conf_api.activeObject(prototype=uniqueName, uniqueName=uniqueName) setting = viewer_conf_api.setting( x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) # Add the boundings layer = viewer_conf_api.layer(name='boundings') # We first add the boundings that are currently in the DB query = """ SELECT item_id, object_number, x, y, z, xs, ys, zs, h, p, r, OSG_LOCATION.cast_shadow, srid FROM OSG_ITEM_OBJECT JOIN OSG_LOCATION USING (osg_location_id) ORDER BY item_id,object_number""" osgItemObjects, numOsgItemObjects = utils.fetchDataFromDB(cursor, query) # osgItemObjects is (itemId, objectNumber, x, y, z, xs, ys, zs, h, p, r, castShadow, srid) # Now we add Default OSG data items for the objects that are not in OSG_ITEM_OBJECT table query = """ SELECT item_id,object_number FROM item_object WHERE (item_id,object_number) NOT IN (SELECT item_id,object_number FROM OSG_ITEM_OBJECT) ORDER BY item_id,object_number""" objects, num_objects = utils.fetchDataFromDB(cursor, query) for (itemId, objectNumber) in objects: srid = None (x,y,z) = (0,0,0) (xs,ys,zs) = (1,1,1) query = """ SELECT ST_SRID(geom), st_x(st_centroid(geom)), st_y(st_centroid(geom)), min_z + ((max_z - min_z) / 2), st_xmax(geom)-st_xmin(geom) as dx, st_ymax(geom)-st_ymin(geom) as dy, (max_z - min_z) as dz FROM ITEM WHERE item_id = %s and geom is not %s""" queryArgs = [itemId, None] footprints, num_footprints = utils.fetchDataFromDB(cursor, query, queryArgs) if num_footprints: (srid, x, y, z, xs, ys, zs) = footprints[0] if xs == 0: xs = 1 if ys == 0: ys = 1 if zs == 0: zs = 1 osgItemObjects.append([itemId, objectNumber, x, y, z, xs, ys, zs, 0, 0, 0, False, srid]) # Now let's add them to the XML for (itemId, objectNumber, x, y, z, xs, ys, zs, h, p, r, castShadow, srid) in osgItemObjects: # only call getOSGPosition if [x,y,z] are not None if all(position is not None for position in [x,y,z]) and itemId>0: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) uniqueName = utils.codeOSGActiveObjectUniqueName(cursor, utils.AO_TYPE_OBJ, itemId = itemId, objectId = objectNumber) proto = "Bounding Box" activeObject = viewer_conf_api.activeObject(prototype=proto, uniqueName=uniqueName) setting = viewer_conf_api.setting( x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) # Add the labels layer = viewer_conf_api.layer(name='labels') utils.dbExecute(cursor, 'SELECT osg_label_name, text, red, green, blue, ' + 'rotate_screen, outline, font, srid, x, y, z, xs, ys, zs, h, ' + 'p, r, cast_shadow FROM OSG_LABEL INNER JOIN ' + 'OSG_LOCATION ON OSG_LABEL.osg_location_id=' + 'OSG_LOCATION.osg_location_id') rows = cursor.fetchall() for (name, text, red, green, blue, rotatescreen, outline, font, srid, x, y, z, xs, ys, zs, h, p, r, castShadow) in rows: proto = "labelPrototype" uniqueName = utils.codeOSGActiveObjectUniqueName(cursor, utils.AO_TYPE_LAB, labelName = name) if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) activeObject = viewer_conf_api.activeObject( prototype=proto, uniqueName=uniqueName, labelText=text, labelColorRed=red, labelColorGreen=green, labelColorBlue=blue, labelRotateScreen=rotatescreen, outline=outline, Font=font) setting = viewer_conf_api.setting( x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) rootObject.set_activeObjects(activeObjects) # Create the XML rootObject.export(open(opts.output, 'w'), 0) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logger.info(msg)
def run(args): logname = os.path.basename(args.input) + '.log' utils.start_logging(filename=logname, level=args.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) logging.info('Checking validity of SQL file') # Check that beginning of the file does not contain a create database statement if os.popen('head -500 ' + args.input + ' | grep "CREATE DATABASE"').read().count("CREATE DATABASE"): msg = "You must remove CREATE DATABASE statement from the SQL file" print msg logging.error(msg) return # Check that ther are not defaults in TIMESTAMPS that would cause errors if os.popen('grep "TIMESTAMP DEFAULT" ' + args.input).read().count("TIMESTAMP DEFAULT"): msg = "You must remove any DEFAULT value of any TIMESTAMP column" print msg logging.error(msg) return # Check that ther are not index creations if os.popen('grep "INDEX" ' + args.input).read().count("INDEX"): msg = "You must remove any INDEX creation" print msg logging.error(msg) return if os.popen("""grep '"' """ + args.input).read().count('"'): msg = 'You must remove any double quote (")' print msg logging.error(msg) dangerousWords = [] for line in open(args.input, 'r').read().split('\n'): if not line.startswith('--'): for word in line.split(): if word.count('"') == 1: dangerousWords.append(word) if len(dangerousWords): msg = 'Also, before removing all ", take care of table and column names that would be incorrect when removing ".\n If any of the following is a table or column name please be sure that it does not have white spaces: ' + ','.join( dangerousWords) print msg logging.error(msg) return return # Establish connection with DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) # First we drop all tables in attribute logging.info("Dropping all previous attribute tables") for tablename in ('tbl2_site_relation', 'tbl2_object_depression', 'tbl2_object_decoration', 'tbl2_object_material', 'tbl1_object', 'tbl1_site'): cursor.execute('DROP TABLE IF EXISTS ' + tablename + ' CASCADE') connection.commit() # First we need to drop the previous constraints in tbl1_site and tbl1_object # logging.info("Dropping constraints in tbl1_site and tbl1_object tables") # for tablename in ('tbl1_site','tbl1_object'): # cursor.execute("select constraint_name from information_schema.table_constraints where table_name=%s", [tablename,]) # constraintNames = cursor.fetchall() # for (constraintName, ) in constraintNames: # cursor.execute('ALTER TABLE ' + tablename + ' DROP CONSTRAINT %s CASCADE', [constraintName,]) # connection.commit() # This script will drop all attribute tables and create them again logging.info('Executing SQL file %s' % args.input) #utils.load_sql_file(cursor, args.input) connParams = utils.postgresConnectString(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport, True) logFile = os.path.basename(args.input) + '.log' command = 'psql ' + connParams + ' -f ' + args.input + ' &> ' + logFile logging.info(command) os.system(command) #Check errors if os.popen('cat ' + logFile + ' | grep ERROR').read().count("ERROR"): msg = 'There was some errors in the data loading. Please see log ' + logFile print msg logging.error(msg) return # Set select permissions to all new tables logging.info('Granting select permissions to all tables') cursor.execute( "select tablename from pg_tables where schemaname = 'public'") tablesNames = cursor.fetchall() for (tableName, ) in tablesNames: cursor.execute('GRANT SELECT ON ' + tableName + ' TO public') # We check that the added Sites and Objects are also in Data Management part of the DB # All sites in tbl1_site must have an entry in ITEM logging.info( 'Adding items in attribute data that are missing in ITEM table') query = 'SELECT site_id from tbl1_site WHERE site_id NOT IN (SELECT item_id FROM item)' sites, num_sites = utils.fetchDataFromDB(cursor, query) for (siteId, ) in sites: utils.dbExecute( cursor, "INSERT INTO ITEM (item_id, background) VALUES (%s,%s)", [siteId, False]) utils.dbExecute( cursor, "INSERT INTO ITEM_OBJECT (item_id, object_number) VALUES (%s,%s)", [siteId, utils.ITEM_OBJECT_NUMBER_ITEM]) # All objects in tbl1_object must also be in ITEM_OBJECT logging.info( 'Adding items objects in attribute data that are missing in ITEM_OBJECT table' ) query = 'SELECT site_id,object_id from tbl1_object WHERE (site_id,object_id) NOT IN (SELECT item_id,object_number FROM item_object)' sites_objects, num_sites_objects = utils.fetchDataFromDB(cursor, query) for (siteId, objectId) in sites_objects: utils.dbExecute( cursor, "INSERT INTO ITEM_OBJECT (item_id, object_number) VALUES (%s,%s)", [siteId, objectId]) #We add again the constraints that link management and attribute data logging.info('Adding constraints between attribute and items') cursor.execute("""ALTER TABLE tbl1_object ADD FOREIGN KEY (site_id, object_id) REFERENCES ITEM_OBJECT (item_id, object_number) ON UPDATE NO ACTION ON DELETE NO ACTION""") connection.commit() cursor.execute("""ALTER TABLE tbl1_site ADD FOREIGN KEY (site_id) REFERENCES ITEM (item_id) ON UPDATE NO ACTION ON DELETE NO ACTION""") connection.commit() elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logging.info(msg)
def run(opts): # Define logging and start logging logname = os.path.basename(opts.config) + '.log' utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # Parse xml configuration file data = ET.parse(opts.config).getroot() # Database connection connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) # get offset and srid of the background defined in the conf file (bgOffsetX, bgOffsetY, bgOffsetZ, bgSRID) = getBackgroundOffset(data, cursor) bgOffset = (bgOffsetX, bgOffsetY, bgOffsetZ) # Process updates updateAOS = data.xpath('//*[@status="updated"]') # loop over all updates found in the xml config file for ao in updateAOS: #(aoType, proto, uniqueName, siteId, activeobjectId, objectNumber) = \ #getDetails(ao) uniqueName = ao.get('uniqueName') (aoType, itemId, rawDataItemId, objectId, labelName) = utils.decodeOSGActiveObjectUniqueName(cursor, uniqueName) if aoType == None: msg = 'Ignoring operation on %s. Could not decode uniqueName' % uniqueName print msg logging.warning(msg) else: # check if the object is in the DB osgLocationId = getOSGLocationId(cursor, aoType, labelName, itemId, objectId, rawDataItemId) if osgLocationId != None: # update the DB with the information in the xml config file if aoType == utils.AO_TYPE_LAB: # Some other params may have changed in the label msg = 'Updating label %s' % labelName print msg logging.info(msg) deleteOSG(cursor, aoType, labelName) osgLocationId = insertOSGLocation(cursor, ao.getchildren()[0], bgSRID, bgOffset) insertDB(cursor, 'OSG_LABEL', ('osg_label_name', 'osg_location_id', 'text', 'red', 'green', 'blue', 'rotate_screen', 'outline', 'font'), (labelName, osgLocationId, ao.get('labelText'), ao.get('labelColorRed'), ao.get('labelColorGreen'), ao.get('labelColorBlue'), ao.get('labelRotateScreen'), ao.get('outline'), ao.get('Font'))) else: msg = 'Updating OSG location %d from %s' % (osgLocationId, uniqueName) print msg logging.info(msg) updateOSGLocation(cursor, osgLocationId, ao.getchildren()[0], bgSRID, bgOffset) else: if aoType == utils.AO_TYPE_OBJ: # It is a bounding that has been moved and it is not currently in the DB. Let's insert it! msg = 'Insert missing OSG_ITEM_OBJECT (%s,%s)' % (itemId, objectId) print msg logging.info(msg) osgLocationId = insertOSGLocation(cursor, ao.getchildren()[0], bgSRID, bgOffset) insertDB(cursor, 'OSG_ITEM_OBJECT', ('item_id', 'object_number', 'osg_location_id'), (itemId, objectId, osgLocationId)) else: # log error if object is not found in DB msg = 'Update not possible. OSG_ITEM_OBJECT from %s not found in DB' % uniqueName print msg logging.error(msg) # Process deletes (only possible for site objects) deleteAOS = data.xpath('//*[@status="deleted"]') # loop over all deletes found in the xml config file for ao in deleteAOS: uniqueName = ao.get('uniqueName') (aoType, itemId, rawDataItemId, objectId, labelName) = utils.decodeOSGActiveObjectUniqueName(cursor, uniqueName) if aoType==None: msg = 'Ignoring operation on %s. Could not decode uniqueName' % uniqueName print msg logging.warning(msg) else: if aoType in (utils.AO_TYPE_OBJ, utils.AO_TYPE_LAB): # check if the object is in the DB osgLocationId = getOSGLocationId(cursor, aoType, labelName, itemId, objectId) if osgLocationId != None: # Delete the OSG-related entries from the DB msg = 'Deleting OSG related entries for %s' % uniqueName print msg logging.info(msg) deleteOSG(cursor, aoType, labelName, itemId, objectId) else: # log error if object is not found in DB msg = 'Not possible to delete. OSG_ITEM_OBJECT from %s not found in DB. Maybe already deleted?' % uniqueName print msg logging.warning(msg) else: # log error if trying to delete a non-site object msg = 'Ignoring delete in %s: Meshes, pictures and PCs can not be deleted' % uniqueName print msg logging.error(msg) # Process new objects (only possible for site objects) newAOS = data.xpath('//*[@status="new"]') # loop over all new objects found in the xml config file for ao in newAOS: uniqueName = ao.get('uniqueName') (aoType, itemId, rawDataItemId, objectId, labelName) = utils.decodeOSGActiveObjectUniqueName(cursor, uniqueName) if aoType==None: msg = 'Ignoring operation on %s. Could not decode uniqueName' % uniqueName print msg logging.warning(msg) else: if aoType in (utils.AO_TYPE_OBJ, utils.AO_TYPE_LAB): # check if the object is in the DBbesafe i osgLocationId = getOSGLocationId(cursor, aoType, labelName, itemId, objectId) if osgLocationId != None: # log error if the new object is already in the DB msg = 'OSG_ITEM_OBJECT from %s already in DB. Ignoring add' % uniqueName print msg logging.warning(msg) else: osgLocationId = insertOSGLocation(cursor, ao.getchildren()[0], bgSRID, bgOffset) if aoType == utils.AO_TYPE_OBJ: # add object to the DB if objectId == utils.ITEM_OBJECT_NUMBER_ITEM: msg = 'Adding missing ITEM %s' % objectId print msg logging.info(msg) insertDB(cursor, 'ITEM', ('item_id', 'background'), (itemId, False)) msg = 'Adding ITEM_OBJECT (%d,%d)' % (itemId, objectId) print msg logging.info(msg) insertDB(cursor, 'ITEM_OBJECT', ('item_id', 'object_number'), (itemId, objectId)) insertDB(cursor, 'OSG_ITEM_OBJECT', ('item_id', 'object_number', 'osg_location_id'), (itemId, objectId, osgLocationId)) else: # add label to the DB msg = 'Adding label %s' % uniqueName print msg logging.info(msg) insertDB(cursor, 'OSG_LABEL', ('osg_label_name', 'osg_location_id', 'text', 'red', 'green', 'blue', 'rotate_screen', 'outline', 'font'), (labelName, osgLocationId, ao.get('labelText'), ao.get('labelColorRed'), ao.get('labelColorGreen'), ao.get('labelColorBlue'), ao.get('labelRotateScreen'), ao.get('outline'), ao.get('Font'))) else: # log error if trying to add a non-site object msg = 'Ignoring new in %s: Meshes, pictures and PCs can not be added' % uniqueName print msg logging.error(msg) # Process the cameras (the DEF CAMs are added for all objects and can not be deleted or updated) cameras = data.xpath('//camera[not(starts-with(@name,"' + utils.DEFAULT_CAMERA_PREFIX + '"))]') # Delete all previous cameras and related entries deleteCameras(cursor) # add all cameras for camera in cameras: name = camera.get('name') itemId = None if name.count(utils.USER_CAMERA): try: itemId = int(name[name.index(utils.USER_CAMERA) + len(utils.USER_CAMERA):].split('_')[0]) except: msg = 'Incorrect name %s for a ITEM camera' % name print msg logging.warn(msg) itemId = None msg = 'Adding camera %s' % name print msg logging.info(msg) osgLocationId = insertOSGLocation(cursor, camera, bgSRID, bgOffset) insertDB(cursor, 'OSG_CAMERA', ('osg_camera_name', 'osg_location_id'), (name, osgLocationId)) if itemId != None: insertDB(cursor, 'OSG_ITEM_CAMERA', ('item_id', 'osg_camera_name'), (itemId, name)) # close DB connection utils.closeConnectionDB(connection, cursor) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logging.info(msg)
def main(): # set log output level utils.mode = 'normal' # NBA leagues = [14] # top football # leagues = [7, 17, 22, 36, 47] # NBA + top football # leagues = [7, 14, 17, 22, 36, 47] # NBA + top football extended # leagues = [7, 11, 14, 17, 20, 22, 30, 36, 43, 47] # top football extended # leagues = [7, 11, 17, 20, 22, 30, 36, 43, 47] # hockey # leagues = [1, 8, 15, 21, 25, 31, 38, 42] # handball # leagues = [2, 3, 10, 12, 13, 24] # volleyball # leagues = [4, 5, 29, 41, 44] # basketball # leagues = [14, 28, 33, 39, 40, 45] # various sports combined # leagues = [14, 28, 33, 17, 22, 2, 24, 29, 8, 25] # common seasons (various sports) # seasonsInput = '2008,2009,2010,2011' # common seasons (reduced various sports) # seasonsInput = '2009,2010,2011' # common seasons (NBA + football) # seasonsInput = '2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2013,2014' # all seasons seasonsInput = 'all' connection = utils.connectToDB() isDirected = True isWeighted = True analyzeBySeason = True analyzeOverTime = True hasLogWeights = True hasSimpleWeights = False printToFile = True printToCsv = False colors = [ (244, 67, 54), (156, 39, 176), (63, 81, 181), (3, 169, 244), (0, 150, 136), (205, 220, 57), (255, 235, 59), (255, 152, 0), (121, 85, 72), (158, 158, 158), (96, 125, 139), (233, 30, 99), (103, 58, 183), ] # scale RGB values to the [0, 1] interval for i in range(len(colors)): r, g, b = colors[i] colors[i] = (r / 255.0, g / 255.0, b / 255.0) sportAnalyzr.analyze(connection, leagues, seasonsInput, isDirected, isWeighted, analyzeBySeason, analyzeOverTime, hasLogWeights, hasSimpleWeights, printToFile, printToCsv, colors)
def main(): connection = utils.connectToDB() cursor = connection.cursor() # type input inputType = raw_input( 'Do you want price fluctuation for a specific club (current squad) or for selected players? (Club/Players): ' ) byClubs = (inputType.lower() == 'club') # players/club input if (not byClubs): playersInput = raw_input( 'Please enter desired player IDs separated by comma: ') players = playersInput.split(',') players = [int(player) for player in players] playersString = ','.join(map(str, players)) else: clubInput = raw_input('Please enter desired club ID: ') clubId = int(clubInput) players = [] # seasons input seasonsInput = raw_input( 'Please enter desired seasons separated by comma (all for all of them): ' ) if (seasonsInput == 'all'): seasons = constants.allSeasons[0:-1] lastSeason = seasons[-1] seasons = [int(season) + 2000 for season in seasons] seasonsString = constants.allSeasonsString else: seasons = seasonsInput.split(',') lastSeason = seasons[-1] seasonsString = ','.join(map(str, seasons)) seasons = [int(season) + 2000 for season in seasons] # filename input filename = raw_input("Please enter image filename: ") if (byClubs): print "[Visualizer ValuePlotter] Generating chart for club %s for seasons %s..."%\ (filename, seasonsString) else: print "[Visualizer ValuePlotter] Generating chart for players %s for seasons %s..." %\ (playersString, seasonsString) # 30 RGB colors for chart colors = constants.rgb30 # mix them so similar colors will not represent players with similar value shuffle(colors) # scale RGB values to the [0, 1] interval for i in range(len(colors)): r, g, b = colors[i] colors[i] = (r / 255.0, g / 255.0, b / 255.0) plotLength = 20 - max(0, (15 - (len(seasons) * 3))) plt.figure(figsize=(plotLength, 15)) # prepare the data playersDataDict = dict() lastSeasonDataDict = dict() sortedPlayers = list() playersNames = dict() if (byClubs): cursor.execute(''' SELECT pcs.idP, pcs.idS, pcs.playerValue, p.firstName, p.lastName FROM playerclubseason pcs JOIN player p USING (idP) WHERE pcs.idP IN ( SELECT idP FROM playerclubseason WHERE idS = %s AND idClub = %d ) AND idS IN (%s) ORDER BY idP, idS ''' % (lastSeason, clubId, seasonsString)) playersData = cursor.fetchall() cursor.execute( ''' SELECT DISTINCT pcs.idP, pcs.playerValue FROM playerclubseason pcs JOIN player p USING (idP) WHERE pcs.idP IN ( SELECT idP FROM playerclubseason WHERE idS = ? AND idClub = ? ) AND pcs.idS = ? ORDER BY playerValue ''', lastSeason, clubId, lastSeason) lastSeasonData = cursor.fetchall() else: cursor.execute(''' SELECT pcs.idP, pcs.idS, pcs.playerValue, p.firstName, p.lastName FROM playerclubseason pcs JOIN player p USING (idP) WHERE pcs.idP IN (%s) AND pcs.idS IN (%s) ORDER BY idP, idS ''' % (playersString, seasonsString)) playersData = cursor.fetchall() cursor.execute(''' SELECT DISTINCT pcs.idP, pcs.playerValue FROM playerclubseason pcs JOIN player p USING (idP) WHERE pcs.idS = %s AND pcs.idP IN (%s) ORDER BY playerValue ''' % (lastSeason, playersString)) lastSeasonData = cursor.fetchall() maxValue = 0 for lastSeasonDataForPlayer in lastSeasonData: playerId = lastSeasonDataForPlayer[0] value = lastSeasonDataForPlayer[1] sortedPlayers.append(playerId) if (value): value = float(value) / 1000000 else: value = 0.0 lastSeasonDataDict[playerId] = value for playerData in playersData: playerId = playerData[0] seasonId = playerData[1] value = playerData[2] firstName = playerData[3] lastName = playerData[4] if (byClubs): if (playerId not in players): players.append(playerId) if (not value or int(value) == -1): value = 0 else: value = int(round(value)) if (value > maxValue): maxValue = value if (playerId in playersDataDict.keys()): playersDataDict[playerId][seasons.index(seasonId + 2000)] = value else: playersDataDict[playerId] = [0] * len(seasons) playersDataDict[playerId][seasons.index(seasonId + 2000)] = value if (lastName): name = lastName else: name = firstName try: playerName = unicode(name, 'latin-1') except TypeError: playerName = name playersNames[playerId] = playerName # remove some plot frame lines ax = plt.subplot(111) ax.spines["top"].set_visible(False) ax.spines["right"].set_visible(False) ax.get_xaxis().tick_bottom() ax.get_yaxis().tick_left() # limit the range of the plot to where the data is plt.xlim(seasons[0], seasons[len(seasons) - 1]) valueMaximum = int(round(maxValue)) valueStep = int(round(maxValue / 10)) seasonsMinimum = seasons[0] seasonsMaximum = seasons[-1] + 1 seasonsStep = 1 plt.yticks(range(0, valueMaximum, valueStep), [ u"\xA3" + str(x / 1000000) + " mil " for x in range(0, valueMaximum, valueStep) ], fontsize=14) plt.xticks( range(seasonsMinimum, seasonsMaximum, seasonsStep), [str(x) for x in range(seasonsMinimum, seasonsMaximum, seasonsStep)], fontsize=14) # print tick lines across the plot for y in range(0, valueMaximum, valueStep): plt.plot(range(seasons[0], seasons[len(seasons) - 1] + 1), [y] * len(range(seasons[0], seasons[len(seasons) - 1] + 1)), "--", lw=0.5, color="black", alpha=0.3) # remove the tick marks plt.tick_params(axis="both", which="both", bottom="off", top="off", labelbottom="on", left="off", right="off", labelleft="on") positions = list() displace = maxValue / 65 displaceCaptionY = maxValue / 15 displaceCaptionX = (seasons[0] + seasons[-1]) / 2.0 lastPosY = 0 for idx, playerId in enumerate(sortedPlayers): # each line with different color plt.plot(seasons, playersDataDict[playerId], lw=2.5, color=colors[idx % len(colors)]) value = playersDataDict[playerId][-1] posY = int(round(value)) # don't allow more valuable players to be below less valuable players if (posY < lastPosY): posY = lastPosY # prevent overlapping text if (posY in positions): posY += displace while (posY in positions): posY += displace positions.append(posY) if (idx != len(sortedPlayers) - 1): nextPlayerValue = playersDataDict[sortedPlayers[idx + 1]][-1] if (posY > nextPlayerValue): positions.append(nextPlayerValue) plt.text(seasons[-1], posY, playersNames[playerId], fontsize=14, color=colors[idx % len(colors)]) lastPosY = posY caption = "Football players market value fluctuation through seasons %s-%s"\ % (seasons[0], seasons[-1]) if (byClubs): caption += ' for club ' + filename plt.text(displaceCaptionX, -displaceCaptionY, caption, fontsize=20, ha="center") if (byClubs): print "[Visualizer ValuePlotter] Chart for club %s for seasons %s generated."%\ (filename, seasonsString) else: print "[Visualizer ValuePlotter] Chart for players %s for seasons %s generated." %\ (playersString, seasonsString) # check if directory 'Visualizations' exists and create it if necessary directory = 'Visualizations/ValuePlots' if not os.path.exists(directory): os.makedirs(directory) # plt.show() plt.savefig(directory + '/' + filename + '.png')
rule = {'name': '', 'description': '', 'text': ''} rules = [] for line in rulesData.split('\n'): if line.startswith('rule'): # Add previous rule to list if rule['text'] != '': rules.append(rule) rule = {'name': '', 'description': '', 'text': ''} rule['name'] = line.replace('rule', '').strip() if line.strip().startswith('description'): rule['description'] = line.replace('description =', '').replace('\"', '').strip() rule['text'] += line + '\n' rules.append(rule) cur, db = utils.connectToDB() # Disable all previous rules stmt = "UPDATE rules set enabled = 0" cur.execute(stmt) db.commit() # Add our new rules for rule in rules: stmt = "INSERT INTO rules (name, description) VALUES (%(name)s, %(description)s)" cur.execute(stmt, { 'name': rule['name'], 'description': rule['description'] }) rule_id = cur.lastrowid db.commit()
def run(args): global logger global offsetX global offsetY global offsetZ logname = os.path.basename(args.output) + '.log' logger = utils.start_logging(filename=logname, level=args.log) # start logging localtime = utils.getCurrentTimeAsAscii() msg = __file__ + ' script logging start at %s'% localtime print msg logger.info(msg) t0 = time.time() # connect to DB and get a cursor connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost) # We assume the osg location is relative # We need to make it absolute by adding the offset of the background with srid as provided query = """ SELECT C.offset_x, C.offset_y, C.offset_z FROM raw_data_item A, raw_data_item_pc B, osg_data_item_pc_background C WHERE A.raw_data_item_id = B.raw_data_item_id AND B.raw_data_item_id = C.raw_data_item_id AND A.srid = %s""" queryArgs = [args.srid,] backgroundOffsets, num_backgrounds = utils.fetchDataFromDB(cursor, query, queryArgs) if num_backgrounds: (offsetX,offsetY,offsetZ) = backgroundOffsets[0] # get all items query = 'SELECT item_id, ST_ASGEOJSON(geom), min_z, max_z FROM item WHERE NOT background ORDER BY item_id' sites, num_sites = utils.fetchDataFromDB(cursor, query) data = [] for (itemId, itemGeom, minz, maxz) in sites: # Generate the JSON data for this item dataSite = {} dataSite["id"] = itemId if itemGeom != None: dataSite["footprint"] = json.loads(itemGeom)['coordinates'] dataSite["footprint_altitude"] = [minz,maxz] addThumbnail(cursor, itemId, dataSite) addSiteMetaData(cursor, itemId, dataSite) addPointCloud(cursor, itemId, dataSite, args.srid) addMeshes(cursor, itemId, dataSite, args.srid) addObjectsMetaData(cursor, itemId, dataSite, args.srid) data.append(dataSite) # close the Db connection utils.closeConnectionDB(connection, cursor) # save the data into JSON file save2JSON(args.output, data) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % (elapsed_time, logname) print(msg) logger.info(msg)
def main(): connection = utils.connectToDB() cursor = connection.cursor() # DATABASE TABLES print "[DB Setup] Creating empty database tables" # SEASONS cursor.execute(''' CREATE TABLE IF NOT EXISTS seasons ( `id` INT NOT NULL, `name` VARCHAR(255), PRIMARY KEY (`id`) ); ''') # LEAGUES cursor.execute(''' CREATE TABLE IF NOT EXISTS leagues ( `id` INT NOT NULL AUTO_INCREMENT, `acronym` VARCHAR(45), `name` VARCHAR(255), PRIMARY KEY (`id`), CONSTRAINT uk_leagues UNIQUE (`acronym`, `name`) ); ''') # CLUBS cursor.execute(''' CREATE TABLE IF NOT EXISTS clubs ( `id` INT NOT NULL AUTO_INCREMENT, `acronym` VARCHAR(45), `name` VARCHAR(255), `league_id` INT, PRIMARY KEY (`id`), CONSTRAINT fk_clubs_league_id FOREIGN KEY (`league_id`) REFERENCES `leagues`(`id`), CONSTRAINT uk_clubs UNIQUE (`acronym`, `name`, `league_id`) ); ''') # MATCHES cursor.execute(''' CREATE TABLE IF NOT EXISTS matches ( `id` INT NOT NULL AUTO_INCREMENT, `season_id` INT, `league_id` INT, `date` DATE, `stage` VARCHAR(255), `home_club_id` INT, `away_club_id` INT, `home_score` INT, `away_score` INT, `home_odds` FLOAT, `away_odds` FLOAT, `home_odds_prog` FLOAT, `away_odds_prog` FLOAT, `extra_time` TINYINT, PRIMARY KEY (`id`), INDEX `k_matches_extra_time` (`extra_time`), CONSTRAINT fk_matches_season_id FOREIGN KEY (`season_id`) REFERENCES `seasons`(`id`), CONSTRAINT fk_matches_league_id FOREIGN KEY (`league_id`) REFERENCES `leagues`(`id`), CONSTRAINT fk_matches_home_club_id FOREIGN KEY (`home_club_id`) REFERENCES `clubs`(`id`), CONSTRAINT fk_matches_away_club_id FOREIGN KEY (`away_club_id`) REFERENCES `clubs`(`id`), CONSTRAINT uk_matches UNIQUE (`season_id`, `date`, `home_club_id`, `away_club_id`) ); ''') connection.commit() # SEASONS print "[DB Setup] Inserting seasons" for i in xrange(1976, 2016): try: cursor.execute(''' INSERT IGNORE INTO seasons(id, name) VALUES (?, ?) ''', i, i) except pyodbc.DatabaseError, e: print "[DB Setup] ERROR - DatabaseError", e traceback.print_exc()
def run(args): # start logging logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' utils.start_logging(filename=logname, level=utils.DEFAULT_LOG_LEVEL) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logging.info(msg) # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) itemIds = [] if args.itemid == '': data, num = utils.fetchDataFromDB( cursor, 'SELECT item_id FROM ITEM WHERE NOT background') for (itemId, ) in data: itemIds.append(itemId) else: itemIds = args.itemid.split(',') # close the conection to the DB utils.closeConnectionDB(connection, cursor) # Create queues itemsQueue = multiprocessing.Queue() # The queue of tasks (queries) resultsQueue = multiprocessing.Queue() # The queue of results for itemId in itemIds: itemsQueue.put(int(itemId)) for i in range( args.cores ): #we add as many None jobs as numUsers to tell them to terminate (queue is FIFO) itemsQueue.put(None) procs = [] # We start numUsers users processes for i in range(args.cores): procs.append( multiprocessing.Process( target=runChild, args=(i, itemsQueue, resultsQueue, args.las, args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport))) procs[-1].start() for i in range(len(itemIds)): [procIndex, itemId] = resultsQueue.get() # wait for all users to finish their execution for i in range(args.cores): procs[i].join() # measure elapsed time elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logging.info(msg)
def run(opts): global logger # Define logger and start logging #logname = os.path.basename(opts.output) + '.log' logname = os.path.splitext(os.path.basename(__file__))[0] + '.log' logger = utils.start_logging(filename=logname, level=opts.log) localtime = utils.getCurrentTimeAsAscii() t0 = time.time() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logger.info(msg) if not opts.output.endswith(".conf.xml"): logger.error('The output file must end with .conf.xml') raise IOError('The output file must end with .conf.xml') # Create python postgres connection global cursor connection, cursor = utils.connectToDB(opts.dbname, opts.dbuser, opts.dbpass, opts.dbhost, opts.dbport) # Check that provided background is in DB query = """ SELECT OSG_DATA_ITEM_PC_BACKGROUND.abs_path,srid FROM OSG_DATA_ITEM_PC_BACKGROUND JOIN RAW_DATA_ITEM USING (raw_data_item_id)""" rows, num_rows = utils.fetchDataFromDB(cursor, query) backGroundAbsPath = None backgroundSRID = None for (bgAbsPath, bgSRID) in rows: if opts.background == os.path.basename(bgAbsPath): backGroundAbsPath = bgAbsPath backgroundSRID = bgSRID if backGroundAbsPath == None: errorMsg = 'Background ' + opts.background + ' is not found' logger.error(errorMsg) raise Exception(errorMsg) # Get the root object: the OSG configuration rootObject = viewer_conf_api.osgRCconfiguration() # set version rootObject.set_version("0.2") # Add all the different XML of the active objects # (we add distinct since the boundings will share XMLs) query = """ SELECT DISTINCT xml_abs_path FROM OSG_DATA_ITEM ORDER BY xml_abs_path""" rows, num_rows = utils.fetchDataFromDB(cursor, query) for (xmlPath, ) in rows: if xmlPath.count(opts.osg) == 0: logger.error('Mismatch between given OSG data directory ' + 'and DB content') rootObject.add_objectLibrary( viewer_conf_api.objectLibrary( url=os.path.relpath(xmlPath, opts.osg))) # Add the object library with the boundings rootObject.add_objectLibrary( viewer_conf_api.objectLibrary(url=utils.BOUNDINGS_XML_RELATIVE)) # Add the cameras that are in the DB cameras = viewer_conf_api.cameras() query = """ SELECT osg_camera_name, srid, x, y, z, h, p, r FROM OSG_CAMERA JOIN OSG_LOCATION USING (osg_location_id)""" rows, num_rows = utils.fetchDataFromDB(cursor, query) for (name, srid, x, y, z, h, p, r) in rows: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) cameras.add_camera( viewer_conf_api.camera(name=name, x=x, y=y, z=z, h=h, p=p, r=r)) # Add Default cameras for the items that have no camera in the DB query = """ SELECT item_id, ST_SRID(geom), st_x(st_centroid(geom)), st_y(st_centroid(geom)), min_z + ((max_z - min_z) / 2) FROM ITEM WHERE NOT background AND geom IS NOT null AND item_id NOT IN ( SELECT DISTINCT item_id FROM OSG_ITEM_CAMERA ) ORDER BY item_id""" rows, numitems = utils.fetchDataFromDB(cursor, query) for (itemId, srid, x, y, z) in rows: # only call getOSGPosition if [x,y,z] are not None # should item_id = -1 be added? if all(position is not None for position in [x, y, z]) and itemId > 0: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) cameras.add_camera( viewer_conf_api.camera(name=utils.DEFAULT_CAMERA_PREFIX + str(itemId), x=x, y=y, z=z)) rootObject.set_cameras(cameras) # Add the XML content of the preferences rootObject.set_preferences(viewer_conf_api.parseString(DEFAULT_PREFENCES)) attributes = viewer_conf_api.attributes() # Use generic method to fill all properties. # We need the name in the XML, the column name in the DB and # the table name in the DB for property in utils.ATTRIBUTES_ORDER: (cName, tName) = utils.ATTRIBUTES[property] elements = getattr(viewer_conf_api, property + 's')() # We need to call the columns and tables with extra "" because # they were created from the Access DB # utils.dbExecute(cursor, 'SELECT "' + cName + '" FROM "' + tName + '"') utils.dbExecute(cursor, 'SELECT ' + cName + ' FROM ' + tName) for (element, ) in cursor: getattr(elements, 'add_' + property)(getattr(viewer_conf_api, property)(name=element)) getattr(attributes, 'set_' + property + 's')(elements) rootObject.set_attributes(attributes) # Add all the static objects, i.e. the OSG from the background # Add the static object for the background staticObjects = viewer_conf_api.staticObjects() staticObjects.add_staticObject( viewer_conf_api.staticObject(url=os.path.relpath( glob.glob(backGroundAbsPath + '/' + utils.OSG_DATA_PREFIX + '.osgb')[0], opts.osg))) # Add hardcode DOME staticObjects.add_staticObject( viewer_conf_api.staticObject(url=utils.DOMES_OSG_RELATIVE)) rootObject.set_staticObjects(staticObjects) # Add the 5 different layers of active objects activeObjects = viewer_conf_api.activeObjects() # First we add points, meshes and pcitures which are related to # the active_objects_sites layersData = [('points', 'OSG_DATA_ITEM_PC_SITE', utils.AO_TYPE_PC), ('photos', 'OSG_DATA_ITEM_PICTURE', utils.AO_TYPE_PIC), ('meshes', 'OSG_DATA_ITEM_MESH', utils.AO_TYPE_MESH)] for (layerName, tableName, inType) in layersData: layer = viewer_conf_api.layer(name=layerName) query = """ SELECT item_id, raw_data_item_id, OSG_LOCATION.srid, x, y, z, xs, ys, zs, h, p, r, cast_shadow FROM """ + tableName + """ JOIN OSG_DATA_ITEM USING (osg_data_item_id) JOIN OSG_LOCATION USING (osg_location_id) JOIN RAW_DATA_ITEM USING (raw_data_item_id) ORDER BY item_id""" rows, numitems = utils.fetchDataFromDB(cursor, query) for (itemId, rawDataItemId, srid, x, y, z, xs, ys, zs, h, p, r, castShadow) in rows: # only call getOSGPosition if [x,y,z] are not None if all(position is not None for position in [x, y, z]): if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) uniqueName = utils.codeOSGActiveObjectUniqueName( cursor, inType, rawDataItemId) activeObject = viewer_conf_api.activeObject(prototype=uniqueName, uniqueName=uniqueName) setting = viewer_conf_api.setting( x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) # Add the boundings layer = viewer_conf_api.layer(name='boundings') # We first add the boundings that are currently in the DB query = """ SELECT item_id, object_number, x, y, z, xs, ys, zs, h, p, r, OSG_LOCATION.cast_shadow, srid FROM OSG_ITEM_OBJECT JOIN OSG_LOCATION USING (osg_location_id) ORDER BY item_id,object_number""" osgItemObjects, numOsgItemObjects = utils.fetchDataFromDB(cursor, query) # osgItemObjects is (itemId, objectNumber, x, y, z, xs, ys, zs, h, p, r, castShadow, srid) # Now we add Default OSG data items for the objects that are not in OSG_ITEM_OBJECT table query = """ SELECT item_id,object_number FROM item_object WHERE (item_id,object_number) NOT IN (SELECT item_id,object_number FROM OSG_ITEM_OBJECT) ORDER BY item_id,object_number""" objects, num_objects = utils.fetchDataFromDB(cursor, query) for (itemId, objectNumber) in objects: srid = None (x, y, z) = (0, 0, 0) (xs, ys, zs) = (1, 1, 1) query = """ SELECT ST_SRID(geom), st_x(st_centroid(geom)), st_y(st_centroid(geom)), min_z + ((max_z - min_z) / 2), st_xmax(geom)-st_xmin(geom) as dx, st_ymax(geom)-st_ymin(geom) as dy, (max_z - min_z) as dz FROM ITEM WHERE item_id = %s and geom is not %s""" queryArgs = [itemId, None] footprints, num_footprints = utils.fetchDataFromDB( cursor, query, queryArgs) if num_footprints: (srid, x, y, z, xs, ys, zs) = footprints[0] if xs == 0: xs = 1 if ys == 0: ys = 1 if zs == 0: zs = 1 osgItemObjects.append( [itemId, objectNumber, x, y, z, xs, ys, zs, 0, 0, 0, False, srid]) # Now let's add them to the XML for (itemId, objectNumber, x, y, z, xs, ys, zs, h, p, r, castShadow, srid) in osgItemObjects: # only call getOSGPosition if [x,y,z] are not None if all(position is not None for position in [x, y, z]) and itemId > 0: if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) uniqueName = utils.codeOSGActiveObjectUniqueName( cursor, utils.AO_TYPE_OBJ, itemId=itemId, objectId=objectNumber) proto = "Bounding Box" activeObject = viewer_conf_api.activeObject(prototype=proto, uniqueName=uniqueName) setting = viewer_conf_api.setting( x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) # Add the labels layer = viewer_conf_api.layer(name='labels') utils.dbExecute( cursor, 'SELECT osg_label_name, text, red, green, blue, ' + 'rotate_screen, outline, font, srid, x, y, z, xs, ys, zs, h, ' + 'p, r, cast_shadow FROM OSG_LABEL INNER JOIN ' + 'OSG_LOCATION ON OSG_LABEL.osg_location_id=' + 'OSG_LOCATION.osg_location_id') rows = cursor.fetchall() for (name, text, red, green, blue, rotatescreen, outline, font, srid, x, y, z, xs, ys, zs, h, p, r, castShadow) in rows: proto = "labelPrototype" uniqueName = utils.codeOSGActiveObjectUniqueName(cursor, utils.AO_TYPE_LAB, labelName=name) if (srid is not None) and (srid == bgSRID): x, y, z = getOSGPosition(x, y, z, srid) else: x, y, z = getOSGPosition(x, y, z) activeObject = viewer_conf_api.activeObject( prototype=proto, uniqueName=uniqueName, labelText=text, labelColorRed=red, labelColorGreen=green, labelColorBlue=blue, labelRotateScreen=rotatescreen, outline=outline, Font=font) setting = viewer_conf_api.setting(x=x, y=y, z=z, xs=xs, ys=ys, zs=zs, h=h, p=p, r=r, castShadow=(1 if castShadow else 0)) activeObject.set_setting(setting) layer.add_activeObject(activeObject) activeObjects.add_layer(layer) rootObject.set_activeObjects(activeObjects) # Create the XML rootObject.export(open(opts.output, 'w'), 0) elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logger.info(msg)
weightByApps = True # read country abbreviations from csv countries = dict() reader = csv.reader(open(constants.countrySVGMap), delimiter=",") for row in reader: try: countries[row[1]] = row[0].lower() except Exception, e: print "[Visualizer GeoPlotter] Exception occured!", e traceback.print_exc() pass connection = utils.connectToDB() cursor = connection.cursor() # loop through metrics, fetch the data, and color the map for metric in [ 'apps', 'goals', 'assists', 'ownGoals', 'yellowCards', 'redCards', 'penaltyGoals', 'concededGoals', 'cleanSheets' ]: print "[Visualizer GeoPlotter] Generating image for metric %s..." % metric gkMetric = (metric == 'concededGoals' or metric == 'cleanSheets') if ((metric != 'apps' and not gkMetric) and weightByApps): weightByAppsString = '/SUM(pcs.apps)' else: weightByAppsString = ''
# Create the required directories if not exits if not createDir(dirLogs): sys.exit('The directory "{}" could not be created'.format(dirLogs)) if not createDir(dirTweet): sys.exit('The directory "{}" could not be created'.format(dirTweet)) # Setup the logger logName = date.today().strftime("%Y-%m-%d") + '-tweet-upload.log' setupLogger(dirLogs, logName) # Get datetime interval for the past hour dt_save, datetime_start, datetime_end = getPastHourInterval() # Connect to the database conn = connectToDB(dbPath) if conn is None: logging.error('Error while connecting to the database') sys.exit(1) # Get the tweet data streamed in the past hour df = retrieveDataFromDB(conn, datetime_start, datetime_end) # Close the database connection try: conn.close() except Exception as e: logging.error(str(e)) logging.info('Error while closing the database connection') else: logging.info('Closed the database connection')
def run(args): # set logging level global logger global connection global cursor logname = os.path.basename(__file__) + '.log' logger = utils.start_logging(filename=logname, level=args.log) localtime = utils.getCurrentTimeAsAscii() msg = os.path.basename(__file__) + ' script starts at %s.' % localtime print msg logger.info(msg) # start timer t0 = time.time() # connect to the DB connection, cursor = utils.connectToDB(args.dbname, args.dbuser, args.dbpass, args.dbhost, args.dbport) if args.itemid == '?': utils.listRawDataItems(cursor) return else: for rawDataItemId in args.itemid.split(','): # fetch the abs_path abs_paths = fetch_abs_path(rawDataItemId) msg = 'Abs path fetched: %s' % abs_paths print msg logger.info(msg) # fetch the potree abs_paths abs_potree_paths, num_potree = fetch_potree_abs_paths( rawDataItemId) msg = '%s abs potree paths fetched %s' % (num_potree, abs_potree_paths) print msg logger.info(msg) # fetch the nexus abs_paths abs_nexus_paths, num_nexus = fetch_nexus_abs_paths(rawDataItemId) msg = '%s abs nexus paths fetched %s' % (num_nexus, abs_nexus_paths) print msg logger.info(msg) # fetch the OSG abs_paths PC abs_osg_pc_paths, num_osg_pc = fetch_osg_abs_paths_pc( rawDataItemId) msg = '%s abs OSG paths for PC fetched: %s' % (num_osg_pc, abs_osg_pc_paths) print msg logger.info(msg) # fetch the OSG abs_paths mesh abs_osg_mesh_paths, num_osg_mesh = fetch_osg_abs_paths_mesh( rawDataItemId) msg = '%s abs OSG paths for meshes fetched: %s' % ( num_osg_mesh, abs_osg_mesh_paths) print msg logger.info(msg) # fetch the OSG abs_paths picture abs_osg_picture_paths, num_osg_picture = fetch_osg_abs_paths_picture( rawDataItemId) msg = '%s abs OSG paths for pictures fetched: %s' % ( num_osg_picture, abs_osg_picture_paths) print msg logger.info(msg) # fetch the OSG abs_paths PC BG abs_osg_pc_bg_paths, num_osg_pc_bg = fetch_osg_abs_paths_pc_bg( rawDataItemId) msg = '%s abs OSG paths for PC BG fetched: %s' % ( num_osg_pc_bg, abs_osg_pc_bg_paths) print msg logger.info(msg) # remove the files related to the above absolute paths for abs_paths_to_remove in (abs_paths, abs_potree_paths, abs_nexus_paths, abs_osg_pc_paths, abs_osg_mesh_paths, abs_osg_picture_paths, abs_osg_pc_bg_paths): remove_data(abs_paths_to_remove) msg = 'Removed data locations related to raw data item %s (%s)!' % ( rawDataItemId, abs_paths[0]) print msg logger.info(msg) # measure elapsed time elapsed_time = time.time() - t0 msg = 'Finished. Total elapsed time: %.02f seconds. See %s' % ( elapsed_time, logname) print(msg) logger.info(msg)
def buildNetwork(leagueId, seasonId, competitionStage, directed=True, weighted=True, simpleWeights=False, logWeights=False): print "[Network Builder] Creating network for leagueId %d, seasonId %d, competition stage %s..." %\ (leagueId, seasonId, competitionStage) if utils.mode == 'debug': print "[Network Builder] Network properties: directed=%d, weighted=%d" % ( directed, weighted) connection = utils.connectToDB() if directed: graph = nx.MultiDiGraph() else: graph = nx.MultiGraph() matchesData = databaseBridger.getAllMatches(connection, leagueId, seasonId, competitionStage) for matchRecord in matchesData: homeClub = int(matchRecord[2]) awayClub = int(matchRecord[3]) homeClubName = matchRecord[11] awayClubName = matchRecord[12] homeScore = matchRecord[4] awayScore = matchRecord[5] extraTime = matchRecord[10] weight = 1 if not graph.has_node(homeClub): graph.add_node(homeClub, name=homeClubName) if not graph.has_node(awayClub): graph.add_node(awayClub, name=awayClubName) # if simpleWeights: # if homeScore: # graph.add_edge(awayClub, homeClub, weight=homeScore) # if awayScore: # graph.add_edge(homeClub, awayClub, weight=awayScore) # else: # if homeScore > awayScore: # if weighted: # weight = calculateEdgeWeight(homeScore, awayScore, bool(extraTime), logWeights) # # graph.add_edge(awayClub, homeClub, weight=weight) # else: # if weighted: # weight = calculateEdgeWeight(awayScore, homeScore, bool(extraTime), logWeights) # # graph.add_edge(homeClub, awayClub, weight=weight) # improved weights weight = calculateEdgeWeight(homeScore, awayScore, bool(extraTime), logWeights) graph.add_edge(awayClub, homeClub, weight=weight) weight = calculateEdgeWeight(awayScore, homeScore, bool(extraTime), logWeights) graph.add_edge(homeClub, awayClub, weight=weight) return graph