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
