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 updateOSGLocation(cursor, osgLocationId, xml_element, bgSRID, bgOffset):
""" Update an OSG location by its osgLocationId"""
(names, values) = parseLocation(cursor, xml_element, bgSRID, bgOffset)
auxs = []
for i in range(len(values)):
auxs.append('%s')
utils.dbExecute(cursor, 'UPDATE OSG_LOCATION SET (' + ','.join(names) + ') = (' + ','.join(auxs) + ') WHERE osg_location_id = %s', values + [osgLocationId,])
def clean_temp_table(args):
drop_table_sql = "DROP TABLE IF EXISTS sites_geoms_temp"
utils.dbExecute(cursor, drop_table_sql)
msg = 'Removed table sites_geoms_temp (if existed).'
# print msg
logger.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 update_geom_col_type(cursor):
""" function to update the initial geometries column type """
num_items = utils.countElementsTable(cursor, 'item')
msg = "Number of elements in item table: %s" % num_items
logger.debug(msg)
col_type = utils.typeColumnTable(cursor, 'geom', 'item')
msg = "Current geom column type is %s." % col_type
logger.debug(msg)
if (num_items == 0) or (col_type == 'polygon'):
# alter the geometry field type
alter_type_sql = "ALTER TABLE item ALTER COLUMN geom TYPE geometry(MultiPolygon, " + str(
utils.SRID) + ") USING geom:: geometry(MultiPolygon, " + str(
utils.SRID) + ")"
utils.dbExecute(cursor, alter_type_sql)
msg = "Current geom column type is MultiPolygon, " + str(utils.SRID)
logger.debug(msg)
def insertDB(cursor, tableName, names, values, returnColumn = None):
""" Generic method to insert a row in a table"""
try:
auxs = []
for i in range(len(names)):
auxs.append('%s')
insertStatement = 'INSERT INTO ' + tableName + ' (' + ','.join(names) +') VALUES (' + ','.join(auxs) + ')'
insertValues = values[:]
if returnColumn != None:
insertStatement += ' RETURNING ' + returnColumn
utils.dbExecute(cursor, insertStatement, insertValues)
if returnColumn != None:
return cursor.fetchone()[0]
else:
return None
except Exception as e:
cursor.connection.rollback()
msg = 'Could not do insert in table ' + tableName + ' ' + str(names) + ' = ' + str(values)
print msg
logging.error(msg)
return None
def deleteOSG(cursor, aoType, labelName = None, itemId = None, objectId = None, rawDataItemId = None):
''' Function to delete a and OSG item objects or an OSG label (and their related OSG location entries) '''
if aoType == utils.AO_TYPE_OBJ:
if (itemId == None) or (objectId == None):
raise Exception ('Item Object operations require not null itemId and objectId')
# extract osg_location_id of the object
data,rows = utils.fetchDataFromDB(cursor, 'SELECT osg_location_id FROM OSG_ITEM_OBJECT WHERE item_id = %s AND object_number = %s', [itemId, objectId])
utils.dbExecute(cursor, 'DELETE FROM OSG_ITEM_OBJECT WHERE item_id = %s AND object_number = %s', [itemId, objectId])
# delete from OSG_LOCATION
utils.dbExecute(cursor, 'DELETE FROM OSG_LOCATION WHERE osg_location_id = %s', [data[0][0],])
elif aoType == utils.AO_TYPE_LAB:
if labelName == None:
raise Exception ('Label operations require not null labelName')
# extract osg_location_id of the label
data, rows = utils.fetchDataFromDB(cursor, 'SELECT osg_location_id FROM OSG_LABEL WHERE osg_label_name = %s', [labelName,])
# delete from OSG_LABEL
utils.dbExecute(cursor,'DELETE FROM OSG_LABEL WHERE osg_label_name = %s', [labelName,])
# delete from OSG_LOCATION
utils.dbExecute(cursor, 'DELETE FROM OSG_LOCATION WHERE osg_location_id = %s', [data[0][0],])
else:
raise Exception('Not possible to delete object ' + labelName)
def update_geometries(list_ids, new):
""" function to update/insert the footprint geometries into the item table
got a given list of IDs. the new flag indicated weather INSERT or UPDATE
is needed """
number = 1
for (sid, ) in list_ids:
msg = "Processing %s site from %s sites in total" % (number,
len(list_ids))
# print msg
logger.debug(msg)
fetch_geom_sql = "SELECT site_id AS item_id, ST_Multi(ST_Transform( ST_Union(geom), %s)) AS geom FROM sites_geoms_temp WHERE site_id = %s GROUP BY site_id"
data, num = utils.fetchDataFromDB(cursor, fetch_geom_sql,
[utils.SRID, sid], [], False)
item_id = data[0][0]
background = False
geometry = data[0][1]
if new:
insert_geom_sql = "INSERT INTO item VALUES (%s,%s,%s)"
utils.dbExecute(cursor, insert_geom_sql,
[item_id, background, geometry])
# We also insert for the new item an entry in item_object
utils.dbExecute(
cursor,
"INSERT INTO ITEM_OBJECT (item_id, object_number) VALUES (%s,%s)",
[item_id, utils.ITEM_OBJECT_NUMBER_ITEM])
else:
update_geom_sql = "UPDATE item SET background=%s,geom=%s WHERE item_id=%s"
utils.dbExecute(cursor, update_geom_sql,
[background, geometry, item_id])
number = number + 1
msg = "The geometries have been updated!"
logger.debug(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 deleteCameras(cursor):
utils.dbExecute(cursor, 'DELETE FROM OSG_ITEM_CAMERA CASCADE')
utils.dbExecute(cursor, 'DELETE FROM OSG_CAMERA CASCADE')
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):
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)