def pythonParallelization(self):
connection = self.getConnection()
cursor = connection.cursor()
colsDict = self.getColumnNamesDict(False)
if self.parallelType == 'cand':
idsQuery = "SELECT " + self.getParallelHint(
) + " BLK_ID FROM " + self.blockTable + ", " + self.queryTable + " WHERE SDO_FILTER(BLK_EXTENT,GEOM) = 'TRUE' AND id = " + str(
self.queryIndex)
(eTime, result) = dbops.genericQueryParallelCand(
cursor, self.qp.queryMethod, oracleops.mogrifyExecute,
self.qp.columns, colsDict, self.qp.statistics,
self.resultTable, idsQuery, None,
self.runGenericQueryParallelCandChild, self.numProcessesQuery,
oracleops.createSQLFile, oracleops.executeSQLFileCount,
self.getConnectionString(False))
#returnDict[queryId] = self.genericQueryParallelCand()
elif self.parallelType in ('grid', 'griddis'):
# q = 'SELECT count(*) FROM ' + self.blockTable + ', ' + self.queryTable + ' WHERE id = ' + str(self.queryIndex) + " AND SDO_ANYINTERACT(blk_extent, geom) = 'TRUE'"
# oracleops.mogrifyExecute(cursor, q)
# print self.queryIndex, 'TOUCHED_BLOCKS TOTAL ', cursor.fetchone()[0]
gridTable = ('query_grid_' + str(self.queryIndex)).upper()
oracleops.dropTable(cursor, gridTable, True)
(eTime, result) = dbops.genericQueryParallelGrid(
cursor, self.qp.queryMethod, oracleops.mogrifyExecute,
self.qp.columns, colsDict, self.qp.statistics,
self.resultTable, gridTable, self.createGridTableMethod,
self.runGenericQueryParallelGridChild, self.numProcessesQuery,
(self.parallelType == 'griddis'), oracleops.createSQLFile,
oracleops.executeSQLFileCount, self.getConnectionString(False))
connection.close()
return (eTime, result)
def createBlocksTable(self,
cursor,
blockTable,
tableSpace,
compression,
baseTable=None,
includeBlockId=False):
""" Create the blocks table and meta-data table"""
oracleops.dropTable(cursor, blockTable, True)
if baseTable != None:
oracleops.dropTable(cursor, baseTable, True)
# Tables to contain point data and point cloud metadata
oracleops.mogrifyExecute(
cursor, """
CREATE TABLE """ + blockTable + self.getTableSpaceString(tableSpace) +
""" pctfree 0 nologging
lob(points) store as securefile (""" + self.getTableSpaceString(tableSpace) +
self.getCompressString(compression) + """ cache reads nologging)
as SELECT * FROM mdsys.SDO_PC_BLK_TABLE where 0 = 1""")
if baseTable != None:
if includeBlockId:
oracleops.mogrifyExecute(
cursor, """
CREATE TABLE """ + baseTable + """ (id number, pc sdo_pc) """ +
self.getTableSpaceString(tableSpace) +
""" pctfree 0 nologging""")
else:
oracleops.mogrifyExecute(
cursor, """
CREATE TABLE """ + baseTable + """ (pc sdo_pc) """ +
self.getTableSpaceString(tableSpace) +
""" pctfree 0 nologging""")
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
oracleops.dropTable(cursor, self.resultTable, True)
if self.numProcessesQuery > 1 and self.parallelType != 'nati':
if self.qp.queryType in ('rectangle','circle','generic') :
return self.pythonParallelization()
else:
logging.error('Python parallelization only available for queries which are not NN!')
return (eTime, result)
t0 = time.time()
query = self.getSelect()
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.resultTable + " AS " + query)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable, None, True, self.qp.columns, self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
oracleops.createSQLFile(cursor, sqlFileName, query, None)
result = oracleops.executeSQLFileCount(self.getConnectionString(False), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def createFlatMeta(self, cursor, tableName):
# Create the meta-data table
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + tableName +
" (tablename varchar(50), srid number, minx number, miny number, maxx number, maxy number, scalex number, scaley number)"
)
def pythonParallelization(self):
connection = self.getConnection()
cursor = connection.cursor()
colsDict = self.getColumnNamesDict(False)
if self.parallelType == 'cand':
idsQuery = "SELECT " + self.getParallelHint() + " BLK_ID FROM " + self.blockTable + ", " + self.queryTable + " WHERE SDO_FILTER(BLK_EXTENT,GEOM) = 'TRUE' AND id = " + str(self.queryIndex)
(eTime, result) = dbops.genericQueryParallelCand(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, colsDict,
self.qp.statistics, self.resultTable, idsQuery, None,
self.runGenericQueryParallelCandChild, self.numProcessesQuery, oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
#returnDict[queryId] = self.genericQueryParallelCand()
elif self.parallelType in ('grid','griddis'):
# q = 'SELECT count(*) FROM ' + self.blockTable + ', ' + self.queryTable + ' WHERE id = ' + str(self.queryIndex) + " AND SDO_ANYINTERACT(blk_extent, geom) = 'TRUE'"
# oracleops.mogrifyExecute(cursor, q)
# print self.queryIndex, 'TOUCHED_BLOCKS TOTAL ', cursor.fetchone()[0]
gridTable = ('query_grid_' + str(self.queryIndex)).upper()
oracleops.dropTable(cursor, gridTable, True)
(eTime, result) = dbops.genericQueryParallelGrid(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, colsDict,
self.qp.statistics, self.resultTable, gridTable, self.createGridTableMethod,
self.runGenericQueryParallelGridChild, self.numProcessesQuery,
(self.parallelType == 'griddis'), oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
connection.close()
return (eTime, result)
def createTableAsSelect(self, cursor, newTableName, tableName, columns, tableSpace, numProcesses):
oracleops.dropTable(cursor, newTableName, True)
oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + newTableName + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
""" + self.getParallelString(numProcesses) + """
as
select """ + (','.join(self.getDBColumns(columns,False))) + """ from """ + tableName)
def createIOTTable(self,
cursor,
iotTableName,
tableName,
tableSpace,
icolumns,
ocolumns,
keycolumns,
numProcesses,
check=False,
hilbertFactor=None):
""" Create Index-Organized-Table and populate it from tableName Table"""
oracleops.dropTable(cursor, iotTableName, True)
hilbertColumnName = 'd'
if hilbertFactor != None:
hilbertColumnName = 'd+(rownum*' + hilbertFactor + ') d'
icols = self.getDBColumns(icolumns, False, hilbertColumnName)
ocols = self.getDBColumns(ocolumns, False)
kcols = self.getDBColumns(keycolumns, False)
oracleops.mogrifyExecute(
cursor, """
CREATE TABLE """ + iotTableName + """
(""" + (','.join(ocols)) + """
, constraint """ + iotTableName + """_PK primary key (""" +
(','.join(kcols)) + """))
organization index
""" + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
""" + self.getParallelString(numProcesses) + """
as
SELECT """ + (','.join(icols)) + """ FROM """ + tableName)
def populateBlocks(self, cursor, srid, minX, minY, maxX, maxY, flatTable,
blockTable, baseTable, blockSize, columns, tolerance,
workTableSpace):
"""Populate blocks from points in a flat table and delete flat table afterwards"""
viewFlatTable = 'VIEW_' + flatTable
# Drop previous Base and Block tables if existing
cursor.execute('SELECT view_name FROM all_views WHERE view_name = :1',
[
viewFlatTable,
])
if len(cursor.fetchall()):
oracleops.mogrifyExecute(cursor, 'DROP VIEW ' + viewFlatTable)
# Create a view that contains of the flat table to include the rid column required by the blocks
oracleops.mogrifyExecute(
cursor, "CREATE VIEW " + viewFlatTable + " as SELECT '0' rid, " +
(','.join(self.getDBColumns(columns, False))) + " from " +
flatTable)
#Initialize point cloud metadata and create point cloud
self.initCreatePC(cursor, srid, minX, minY, maxX, maxY, viewFlatTable,
blockTable, baseTable, blockSize, tolerance,
workTableSpace)
#oracleops.mogrifyExecute(cursor,"""ALTER TABLE """ + self.blockTable + """ add constraint """ + self.blockTable + """_PK primary key (obj_id, blk_id) using index tablespace """ + self.indexTableSpace)
oracleops.mogrifyExecute(cursor, """DROP VIEW """ + viewFlatTable)
oracleops.dropTable(cursor, flatTable)
def createTableAsSelect(self, cursor, newTableName, tableName, columns, tableSpace, numProcesses):
oracleops.dropTable(cursor, newTableName, True)
oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + newTableName + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
""" + self.getParallelString(numProcesses) + """
as
select """ + (','.join(self.getDBColumns(columns,False))) + """ from """ + tableName)
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
oracleops.dropTable(cursor, self.resultTable, True)
if self.numProcessesQuery > 1 and self.parallelType != 'nati':
if self.qp.queryType in ('rectangle', 'circle', 'generic'):
return self.pythonParallelization()
else:
logging.error(
'Python parallelization only available for queries which are not NN!'
)
return (eTime, result)
t0 = time.time()
query = self.getSelect()
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + self.resultTable + " AS " + query)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
None, True, self.qp.columns,
self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
oracleops.createSQLFile(cursor, sqlFileName, query, None)
result = oracleops.executeSQLFileCount(
self.getConnectionString(False), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
oracleops.dropTable(cursor, self.resultTable, True)
wkt = self.qp.wkt
if self.qp.queryType == 'nn':
g = loads(self.qp.wkt)
wkt = dumps(g.buffer(self.qp.rad))
t0 = time.time()
scaledWKT = wktops.scale(wkt, self.scaleX, self.scaleY, self.minX,
self.minY)
(mimranges,
mxmranges) = self.quadtree.getMortonRanges(scaledWKT,
self.mortonDistinctIn,
maxRanges=MAXIMUM_RANGES)
if len(mimranges) == 0 and len(mxmranges) == 0:
logging.info('None morton range in specified extent!')
return (eTime, result)
self.hints = []
if not self.flatTableIOT:
self.hints.append('INDEX(' + self.flatTable + ' ' +
self.flatTable + '_IDX)')
if self.qp.queryMethod != 'stream' and self.numProcessesQuery > 1 and self.parallelType != 'nati' and self.qp.queryType in (
'rectangle', 'circle', 'generic'):
return self.pythonParallelization(t0, mimranges, mxmranges)
if self.numProcessesQuery > 1:
self.hints.append('PARALLEL (' + str(self.numProcessesQuery) + ')')
(query, queryArgs) = dbops.getSelectMorton(
mimranges, mxmranges, self.qp, self.flatTable,
self.addContainsCondition, self.colsDict,
self.getHintStatement(self.hints))
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(
cursor,
"CREATE TABLE " + self.resultTable + " AS " + query + "",
queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.colsDict,
(not self.mortonDistinctIn),
self.qp.columns,
self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
oracleops.createSQLFile(cursor, sqlFileName, query, queryArgs)
result = oracleops.executeSQLFileCount(
self.getConnectionString(False), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def createFlatTable(self, cursor, tableName, tableSpace, columns):
""" Creates a empty flat table"""
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(
cursor, """
CREATE TABLE """ + tableName + """ (""" +
(',\n'.join(self.getDBColumns(columns, True))) + """) """ +
self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")
def populateBlocksHilbert(self, cursor, srid, minX, minY, maxX, maxY,
flatTable, blockTable, baseTable, blockSize,
tolerance):
# NOTE: In this case we do not require to create a view, since the fixed format by the hilbert pre-processor makes it directly compatible
# if we change the pre-processor we need to change these ones
self.initCreatePCHilbert(cursor, srid, minX, minY, maxX, maxY,
flatTable, blockTable, baseTable, blockSize,
tolerance)
oracleops.dropTable(cursor, flatTable)
def createGridTableMethod(self, cursor, gridTable, ncols, nrows):
(minX, maxX) = (self.qp.minx, self.qp.maxx)
(minY, maxY) = (self.qp.miny, self.qp.maxy)
rangeX = maxX - minX
rangeY = maxY - minY
tileSizeX = rangeX / float(ncols)
tileSizeY = rangeY / float(nrows)
#scaleX = 0.01
#scaleY = 0.01
scaleX = float(self.tolerance)
scaleY = float(self.tolerance)
tilesTableName = "TEMP_" + gridTable
tileCounter = 0
oracleops.dropTable(cursor, tilesTableName, True)
cursor.execute("CREATE TABLE " + tilesTableName +
" (ID NUMBER PRIMARY KEY, GEOM SDO_GEOMETRY )")
for xIndex in range(ncols):
for yIndex in range(nrows):
minTileX = minX + (xIndex * tileSizeX)
maxTileX = minX + ((xIndex + 1) * tileSizeX)
minTileY = minY + (yIndex * tileSizeY)
maxTileY = minY + ((yIndex + 1) * tileSizeY)
# To avoid overlapping tiles
if xIndex < ncols - 1:
maxTileX -= scaleX
if yIndex < nrows - 1:
maxTileY -= scaleY
#print '\t'.join((str(xIndex), str(yIndex), '%.2f' % minTileX, '%.2f' % minTileY, '%.2f' % maxTileX, '%.2f' % maxTileY))
insertStatement = "INSERT INTO " + tilesTableName + """ (id,geom) VALUES (:1, SDO_GEOMETRY(2003, :2, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(:3,:4, :5,:6)))"""
insertArgs = [
tileCounter, self.srid, minTileX, minTileY, maxTileX,
maxTileY
]
cursor.execute(insertStatement, insertArgs)
tileCounter += 1
query = """
CREATE TABLE """ + gridTable + """ AS
SELECT T.id, SDO_GEOM.SDO_INTERSECTION(A.geom, T.geom, """ + str(
self.tolerance) + """) as geom
FROM """ + self.queryTable + """ A, """ + tilesTableName + """ T
WHERE A.id = """ + str(self.queryIndex)
oracleops.mogrifyExecute(cursor, query)
cursor.execute("CREATE INDEX " + gridTable + "_id_idx ON " +
gridTable + "(ID)")
cursor.connection.commit()
def pythonParallelization(self):
connection = self.getConnection()
cursor = connection.cursor()
gridTable = ('query_grid_' + str(self.queryIndex)).upper()
oracleops.dropTable(cursor, gridTable, True)
(eTime, result) = dbops.genericQueryParallelGrid(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, self.colsDict,
self.qp.statistics, self.resultTable, gridTable, self.createGridTableMethod,
self.runGenericQueryParallelGridChild, self.numProcessesQuery,
(self.parallelType == 'griddis'), oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
connection.close()
return (eTime, result)
def initialize(self):
# Get connection
connection = self.getConnection()
cursor = connection.cursor()
# Get SRID of the stored PC
oracleops.mogrifyExecute(cursor, "SELECT srid FROM user_sdo_geom_metadata WHERE table_name = '" + self.blockTable + "'")
self.srid = cursor.fetchone()[0]
# Create table to store the query geometries
oracleops.dropTable(cursor, self.queryTable, check = True)
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.queryTable + " ( id number primary key, geom sdo_geometry) " + self.getTableSpaceString(self.tableSpace) + " pctfree 0 nologging")
connection.close()
def pythonParallelization(self):
connection = self.getConnection()
cursor = connection.cursor()
gridTable = ('query_grid_' + str(self.queryIndex)).upper()
oracleops.dropTable(cursor, gridTable, True)
(eTime, result) = dbops.genericQueryParallelGrid(
cursor, self.qp.queryMethod, oracleops.mogrifyExecute,
self.qp.columns, self.colsDict, self.qp.statistics,
self.resultTable, gridTable, self.createGridTableMethod,
self.runGenericQueryParallelGridChild, self.numProcessesQuery,
(self.parallelType == 'griddis'), oracleops.createSQLFile,
oracleops.executeSQLFileCount, self.getConnectionString(False))
connection.close()
return (eTime, result)
def close(self):
connection = self.getConnection()
cursor = connection.cursor()
metaArgs = (self.flatTable, self.srid, self.minX, self.minY, self.maxX, self.maxY, self.scaleX, self.scaleY)
oracleops.mogrifyExecute(cursor, "INSERT INTO " + self.metaTable + " VALUES (:1,:2,:3,:4,:5,:6,:7,:8)" , metaArgs)
if self.flatTableIOT:
tempFlatTable = self.flatTable + '_TEMP'
oracleops.mogrifyExecute(cursor, "ALTER TABLE " + self.flatTable + " RENAME TO " + tempFlatTable )
self.createIOTTable(cursor, self.flatTable, tempFlatTable, self.tableSpace, self.columns, self.columns, self.index, self.numProcessesLoad)
oracleops.dropTable(cursor, tempFlatTable, False)
else:
self.createIndex(cursor, self.flatTable, self.index, self.indexTableSpace, self.numProcessesLoad)
self.computeStatistics(cursor, self.flatTable)
connection.close()
def initialize(self):
# Get connection
connection = self.getConnection()
cursor = connection.cursor()
# Get SRID of the stored PC
oracleops.mogrifyExecute(cursor, "SELECT srid, minx, miny, maxx, maxy, scalex, scaley from " + self.metaTable)
(self.srid, minX, minY, maxX, maxY, scaleX, scaleY) = cursor.fetchone()
# Create table to store the query geometries
oracleops.dropTable(cursor, self.queryTable, check = True)
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.queryTable + " ( id number primary key, geom sdo_geometry) " + self.getTableSpaceString(self.tableSpace) + " pctfree 0 nologging")
connection.close()
self.colsDict = self.getColumnNamesDict(False)
def createGridTableMethod(self, cursor, gridTable, ncols, nrows):
(minX, maxX) = (self.qp.minx, self.qp.maxx)
(minY, maxY) = (self.qp.miny, self.qp.maxy)
rangeX = maxX - minX
rangeY = maxY - minY
tileSizeX = rangeX / float(ncols)
tileSizeY = rangeY / float(nrows)
#scaleX = 0.01
#scaleY = 0.01
scaleX = float(self.tolerance)
scaleY = float(self.tolerance)
tilesTableName = "TEMP_" + gridTable
tileCounter = 0
oracleops.dropTable(cursor, tilesTableName, True)
cursor.execute("CREATE TABLE " + tilesTableName + " (ID NUMBER PRIMARY KEY, GEOM SDO_GEOMETRY )")
for xIndex in range(ncols):
for yIndex in range(nrows):
minTileX = minX + (xIndex * tileSizeX)
maxTileX = minX + ((xIndex+1) * tileSizeX)
minTileY = minY + (yIndex * tileSizeY)
maxTileY = minY + ((yIndex+1) * tileSizeY)
# To avoid overlapping tiles
if xIndex < ncols-1:
maxTileX -= scaleX
if yIndex < nrows-1:
maxTileY -= scaleY
#print '\t'.join((str(xIndex), str(yIndex), '%.2f' % minTileX, '%.2f' % minTileY, '%.2f' % maxTileX, '%.2f' % maxTileY))
insertStatement = "INSERT INTO " + tilesTableName + """ (id,geom) VALUES (:1, SDO_GEOMETRY(2003, :2, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(:3,:4, :5,:6)))"""
insertArgs = [tileCounter, self.srid, minTileX, minTileY, maxTileX, maxTileY]
cursor.execute(insertStatement, insertArgs)
tileCounter += 1
query = """
CREATE TABLE """ + gridTable + """ AS
SELECT T.id, SDO_GEOM.SDO_INTERSECTION(A.geom, T.geom, """ + str(self.tolerance) + """) as geom
FROM """ + self.queryTable + """ A, """ + tilesTableName + """ T
WHERE A.id = """ + str(self.queryIndex)
oracleops.mogrifyExecute(cursor, query)
cursor.execute("CREATE INDEX " + gridTable + "_id_idx ON " + gridTable + "(ID)")
cursor.connection.commit()
def createExternalTable(self, cursor, lasFiles, tableName, columns, lasDirVariableName, numProcesses):
# Executes the external table setting it to use hilbert_prep.sh script (which must be located in the EXE_DIR Oracle directory)
# badfile LOG_DIR:'hilbert_prep_%p.bad'EXE_DIR:
# logfile LOG_DIR:'hilbert_prep_%p.log'""" + lasFiles + """
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + tableName + """ (""" + (',\n'.join(self.getDBColumns(columns, True))) + """)
organization external
(
type oracle_loader
default directory """ + lasDirVariableName + """
access parameters (
records delimited by newline
preprocessor 'hilbert_prep.sh'
fields terminated by ',')
location ('000024.las')
)
""" + self.getParallelString(numProcesses) + """ reject limit 0""")
def initialize(self):
# Get connection
connection = self.getConnection()
cursor = connection.cursor()
# Get SRID of the stored PC
oracleops.mogrifyExecute(
cursor,
"SELECT srid FROM user_sdo_geom_metadata WHERE table_name = '" +
self.blockTable + "'")
self.srid = cursor.fetchone()[0]
# Create table to store the query geometries
oracleops.dropTable(cursor, self.queryTable, check=True)
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + self.queryTable +
" ( id number primary key, geom sdo_geometry) " +
self.getTableSpaceString(self.tableSpace) + " pctfree 0 nologging")
connection.close()
def createExternalTable(self, cursor, lasFiles, tableName, columns, lasDirVariableName, numProcesses):
# Executes the external table setting it to use hilbert_prep.sh script (which must be located in the EXE_DIR Oracle directory)
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + tableName + """ (""" + (',\n'.join(self.getDBColumns(columns, True))) + """)
organization external
(
type oracle_loader
default directory """ + lasDirVariableName + """
access parameters (
records delimited by newline
preprocessor EXE_DIR:'hilbert_prep.sh'
badfile LOG_DIR:'hilbert_prep_%p.bad'
logfile LOG_DIR:'hilbert_prep_%p.log'
fields terminated by ',')
location ('""" + lasFiles + """')
)
""" + self.getParallelString(numProcesses) + """ reject limit 0""")
def populateBlocks(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, columns, tolerance, workTableSpace):
"""Populate blocks from points in a flat table and delete flat table afterwards"""
viewFlatTable = 'VIEW_' + flatTable
# Drop previous Base and Block tables if existing
cursor.execute('SELECT view_name FROM all_views WHERE view_name = :1',[viewFlatTable,])
if len(cursor.fetchall()):
oracleops.mogrifyExecute(cursor,'DROP VIEW ' + viewFlatTable)
# Create a view that contains of the flat table to include the rid column required by the blocks
oracleops.mogrifyExecute(cursor,"CREATE VIEW " + viewFlatTable + " as SELECT '0' rid, " + (','.join(self.getDBColumns(columns, False))) + " from " + flatTable)
#Initialize point cloud metadata and create point cloud
self.initCreatePC(cursor, srid, minX, minY, maxX, maxY, viewFlatTable, blockTable, baseTable, blockSize, tolerance, workTableSpace)
#oracleops.mogrifyExecute(cursor,"""ALTER TABLE """ + self.blockTable + """ add constraint """ + self.blockTable + """_PK primary key (obj_id, blk_id) using index tablespace """ + self.indexTableSpace)
oracleops.mogrifyExecute(cursor,"""DROP VIEW """ + viewFlatTable)
oracleops.dropTable(cursor, flatTable)
def createBlocksTable(self, cursor, blockTable, tableSpace, compression, baseTable = None, includeBlockId = False):
""" Create the blocks table and meta-data table"""
oracleops.dropTable(cursor, blockTable, True)
if baseTable != None:
oracleops.dropTable(cursor, baseTable, True)
# Tables to contain point data and point cloud metadata
oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + blockTable + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
lob(points) store as securefile (""" + self.getTableSpaceString(tableSpace) + self.getCompressString(compression) + """ cache reads nologging)
as SELECT * FROM mdsys.SDO_PC_BLK_TABLE where 0 = 1""")
if baseTable != None:
if includeBlockId:
oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + baseTable + """ (id number, pc sdo_pc) """ + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")
else:
oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + baseTable + """ (pc sdo_pc) """ + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
oracleops.dropTable(cursor, self.resultTable, True)
wkt = self.qp.wkt
if self.qp.queryType == 'nn':
g = loads(self.qp.wkt)
wkt = dumps(g.buffer(self.qp.rad))
t0 = time.time()
scaledWKT = wktops.scale(wkt, self.scaleX, self.scaleY, self.minX, self.minY)
(mimranges,mxmranges) = self.quadtree.getMortonRanges(scaledWKT, self.mortonDistinctIn, maxRanges = MAXIMUM_RANGES)
if len(mimranges) == 0 and len(mxmranges) == 0:
logging.info('None morton range in specified extent!')
return (eTime, result)
self.hints = []
if not self.flatTableIOT:
self.hints.append('INDEX(' + self.flatTable + ' ' + self.flatTable + '_IDX)')
if self.qp.queryMethod != 'stream' and self.numProcessesQuery > 1 and self.parallelType != 'nati' and self.qp.queryType in ('rectangle','circle','generic') :
return self.pythonParallelization(t0, mimranges, mxmranges)
if self.numProcessesQuery > 1:
self.hints.append('PARALLEL (' + str(self.numProcessesQuery) + ')')
(query, queryArgs) = dbops.getSelectMorton(mimranges, mxmranges, self.qp, self.flatTable, self.addContainsCondition, self.colsDict, self.getHintStatement(self.hints))
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.resultTable + " AS " + query + "", queryArgs)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable, self.colsDict, (not self.mortonDistinctIn), self.qp.columns, self.qp.statistics)
else:
sqlFileName = str(queryId) + '.sql'
oracleops.createSQLFile(cursor, sqlFileName, query, queryArgs)
result = oracleops.executeSQLFileCount(self.getConnectionString(False), sqlFileName)
eTime = time.time() - t0
connection.close()
return (eTime, result)
def createIOTTable(self, cursor, iotTableName, tableName, tableSpace, icolumns, ocolumns, keycolumns, numProcesses, check = False, hilbertFactor = None):
""" Create Index-Organized-Table and populate it from tableName Table"""
oracleops.dropTable(cursor, iotTableName, True)
hilbertColumnName = 'd'
if hilbertFactor != None:
hilbertColumnName = 'd+(rownum*' + hilbertFactor + ') d'
icols = self.getDBColumns(icolumns,False, hilbertColumnName)
ocols = self.getDBColumns(ocolumns,False)
kcols = self.getDBColumns(keycolumns,False)
oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + iotTableName + """
(""" + (','.join(ocols)) + """
, constraint """ + iotTableName + """_PK primary key (""" + (','.join(kcols)) + """))
organization index
""" + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
""" + self.getParallelString(numProcesses) + """
as
SELECT """ + (','.join(icols)) + """ FROM """ + tableName)
def initialize(self):
# Get connection
connection = self.getConnection()
cursor = connection.cursor()
# Get SRID of the stored PC
oracleops.mogrifyExecute(
cursor,
"SELECT srid, minx, miny, maxx, maxy, scalex, scaley from " +
self.metaTable)
(self.srid, minX, minY, maxX, maxY, scaleX, scaleY) = cursor.fetchone()
# Create table to store the query geometries
oracleops.dropTable(cursor, self.queryTable, check=True)
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + self.queryTable +
" ( id number primary key, geom sdo_geometry) " +
self.getTableSpaceString(self.tableSpace) + " pctfree 0 nologging")
connection.close()
self.colsDict = self.getColumnNamesDict(False)
def createFlatMeta(self, cursor, tableName):
# Create the meta-data table
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + tableName + " (tablename varchar(50), srid number, minx number, miny number, maxx number, maxy number, scalex number, scaley number)")
def populateBlocksHilbert(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance):
# NOTE: In this case we do not require to create a view, since the fixed format by the hilbert pre-processor makes it directly compatible
# if we change the pre-processor we need to change these ones
self.initCreatePCHilbert(cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance)
oracleops.dropTable(cursor, flatTable)
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters,
False) #iterationId == 0,
oracleops.dropTable(cursor, self.resultTable, True)
range_table_name = "range_" + queryId
oracleops.dropTable(cursor, range_table_name, True)
# rangetable_sql = "CREATE TABLE " + range_table_name + " (K1 NUMBER PRIMARY KEY, K2 NUMBER) ORGANIZATION INDEX "
rangetable_sql = "CREATE TABLE " + range_table_name + " (K1 NUMBER, K2 NUMBER, CONSTRAINT pk_index PRIMARY KEY (K1)) ORGANIZATION INDEX "
cursor.execute(rangetable_sql)
logging.info(rangetable_sql)
if self.numProcessesQuery > 1 and self.parallelType != 'nati':
if self.qp.queryType in ('rectangle', 'circle', 'generic'):
return self.pythonParallelization()
else:
logging.error(
'Python parallelization only available for queries which are not NN!'
)
return (eTime, result)
t0 = time.time()
connstring = self.getConnectionString()
# (query, _) = dbops.getSelect(self.qp, self.flatTable, self.addContainsCondition, self.colsDict, self.getParallelHint())
(query, _) = dbops.getSelect2(self.qp, queryId, self.flatTable,
self.addContainsCondition, self.colsDict,
range_table_name, connstring,
self.getParallelHint())
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + self.resultTable + " AS " + query)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.colsDict, True,
self.qp.columns,
self.qp.statistics)
else:
#sqlFileName = str(queryId) + '.sql'
#oracleops.createSQLFile(cursor, sqlFileName, query, None)
#result = oracleops.executeSQLFileCount(self.getConnectionString(False), sqlFileName)
#eTime = time.time() - t0
oracleops.mogrifyExecute(
cursor, "CREATE TABLE " + self.resultTable + " AS " + query)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.colsDict, True,
self.qp.columns,
self.qp.statistics)
# second refinement
tblname = self.resultTable + "_01"
zname = self.colsDict['z'][0]
lname = self.colsDict['l'][0]
#4D----xyzl
if self.qp.minz != -99999999 and self.qp.minl != -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minz) + """ <= """ + \
zname + """ AND """ + zname + """ <= """ + str(self.qp.maxz) + """ AND """ + str(self.qp.minl) + """ <= """ + \
lname + """ AND """ + lname + """ <= """ + str(self.qp.maxl) + """ ))"""
#3D---only l filter
if self.qp.minz == -99999999 and self.qp.minl != -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minl) + """ <= """ + \
lname + """ AND """ + lname + """ <= """ + str(self.qp.maxl) + """ ))"""
#3D---only z filter
if self.qp.minz != -99999999 and self.qp.minl == -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minz) + """ <= """ + \
zname + """ AND """ + zname + """ <= """ + str(self.qp.maxz) + """ ))"""
#2D---xy
if self.qp.minz == -99999999 and self.qp.minl == -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) )"""
oracleops.mogrifyExecute(cursor, querya)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable,
self.colsDict, True,
self.qp.columns,
self.qp.statistics)
connection.close()
return (eTime, result)
def query(self, queryId, iterationId, queriesParameters):
(eTime, result) = (-1, None)
connection = self.getConnection()
cursor = connection.cursor()
self.prepareQuery(cursor, queryId, queriesParameters, False)#iterationId == 0,
oracleops.dropTable(cursor, self.resultTable, True)
range_table_name = "range_" + queryId
oracleops.dropTable(cursor, range_table_name, True)
# rangetable_sql = "CREATE TABLE " + range_table_name + " (K1 NUMBER PRIMARY KEY, K2 NUMBER) ORGANIZATION INDEX "
rangetable_sql = "CREATE TABLE " + range_table_name + " (K1 NUMBER, K2 NUMBER, CONSTRAINT pk_index PRIMARY KEY (K1)) ORGANIZATION INDEX "
cursor.execute(rangetable_sql)
logging.info(rangetable_sql)
if self.numProcessesQuery > 1 and self.parallelType != 'nati':
if self.qp.queryType in ('rectangle','circle','generic') :
return self.pythonParallelization()
else:
logging.error('Python parallelization only available for queries which are not NN!')
return (eTime, result)
t0 = time.time()
connstring = self.getConnectionString()
# (query, _) = dbops.getSelect(self.qp, self.flatTable, self.addContainsCondition, self.colsDict, self.getParallelHint())
(query, _) = dbops.getSelect2(self.qp, queryId, self.flatTable, self.addContainsCondition, self.colsDict, range_table_name, connstring, self.getParallelHint() )
if self.qp.queryMethod != 'stream': # disk or stat
oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.resultTable + " AS " + query)
(eTime, result) = dbops.getResult(cursor, t0, self.resultTable, self.colsDict, True, self.qp.columns, self.qp.statistics)
else:
#sqlFileName = str(queryId) + '.sql'
#oracleops.createSQLFile(cursor, sqlFileName, query, None)
#result = oracleops.executeSQLFileCount(self.getConnectionString(False), sqlFileName)
#eTime = time.time() - t0
oracleops.mogrifyExecute( cursor, "CREATE TABLE " + self.resultTable + " AS " + query )
(eTime, result) = dbops.getResult( cursor, t0, self.resultTable, self.colsDict, True, self.qp.columns,
self.qp.statistics )
# second refinement
tblname = self.resultTable + "_01"
zname = self.colsDict['z'][0]
lname = self.colsDict['l'][0]
#4D----xyzl
if self.qp.minz != -99999999 and self.qp.minl != -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minz) + """ <= """ + \
zname + """ AND """ + zname + """ <= """ + str(self.qp.maxz) + """ AND """ + str(self.qp.minl) + """ <= """ + \
lname + """ AND """ + lname + """ <= """ + str(self.qp.maxl) + """ ))"""
#3D---only l filter
if self.qp.minz == -99999999 and self.qp.minl != -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minl) + """ <= """ + \
lname + """ AND """ + lname + """ <= """ + str(self.qp.maxl) + """ ))"""
#3D---only z filter
if self.qp.minz != -99999999 and self.qp.minl == -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) WHERE ( """ + str(self.qp.minz) + """ <= """ + \
zname + """ AND """ + zname + """ <= """ + str(self.qp.maxz) + """ ))"""
#2D---xy
if self.qp.minz == -99999999 and self.qp.minl == -99999999:
querya = """CREATE TABLE """ + tblname + """ AS (SELECT /* + PARALLEL( 8 ) */ * FROM TABLE( mdsys.sdo_PointInPolygon( CURSOR( SELECT * FROM """ + self.resultTable + """ ),
MDSYS.SDO_GEOMETRY( '""" + self.qp.wkt + """', 28992), 0.001)) )"""
oracleops.mogrifyExecute( cursor, querya)
(eTime, result) = dbops.getResult( cursor, t0, self.resultTable, self.colsDict, True, self.qp.columns,
self.qp.statistics )
connection.close()
return (eTime, result)
def createFlatTable(self, cursor, tableName, tableSpace, columns):
""" Creates a empty flat table"""
oracleops.dropTable(cursor, tableName, True)
oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + tableName + """ (""" + (',\n'.join(self.getDBColumns(columns, True))) + """) """ + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")
