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 runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
self.queryIndex = sIndex
self.queryTable = gridTable
cqp = copy.copy(self.qp)
cqp.queryType = 'generic'
if self.qp.queryType == 'rectangle':
cqp.queryType = 'rectangle'
cqp.statistics = None
cqp.minx = "'||to_char(bbox.sdo_ordinates(1))||'"
cqp.maxx = "'||to_char(bbox.sdo_ordinates(3))||'"
cqp.miny = "'||to_char(bbox.sdo_ordinates(2))||'"
cqp.maxy = "'||to_char(bbox.sdo_ordinates(4))||'"
(query, _) = dbops.getSelect(cqp, self.flatTable,
self.addContainsCondition, self.colsDict)
oracleops.mogrifyExecute(
cursor, """
DECLARE
bbox sdo_geometry;
BEGIN
select sdo_geom_mbr (geom) into bbox from """ + gridTable +
""" where id = """ + str(sIndex) + """;
execute immediate 'INSERT INTO """ + self.resultTable + """ """ + query +
"""';
END;""")
connection.close()
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 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 initCreatePCHilbert(self, cursor, srid, minX, minY, maxX, maxY,
flatTable, blockTable, baseTable, blockSize,
tolerance):
# this one also populates
oracleops.mogrifyExecute(
cursor, """
DECLARE
ptcld sdo_pc;
ptn_params varchar2(80) := 'blk_capacity=""" + str(blockSize) + """';
extent sdo_geometry := sdo_geometry(2003,""" + str(srid) +
""",NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(""" +
str(minX) + """,""" + str(minY) + """,""" + str(maxX) + """,""" +
str(maxY) + """));
other_attrs XMLType := xmltype('
<opc:sdoPcObjectMetadata
xmlns:opc="http://xmlns.oracle.com/spatial/vis3d/2011/sdovis3d.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
blockingMethod="Hilbert R-tree">
</opc:sdoPcObjectMetadata>');
BEGIN
ptcld := sdo_pc_pkg.init ('""" + baseTable + """', 'PC', '""" +
blockTable + """', ptn_params, extent, """ + str(tolerance) +
""", 3, NULL, NULL, other_attrs);
insert into """ + baseTable + """ values (ptcld);
commit;
sdo_pc_pkg.create_pc (ptcld, '""" + flatTable + """', NULL);
END;
""")
self.updateBlocksSRID(cursor, blockTable, srid)
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 runGenericQueryParallelCandChild(self, chunkIds):
connection = self.getConnection()
cursor = connection.cursor()
zCondition = dbops.addZCondition(self.qp, 'pnt.z', None)
elements = []
for _, crange in groupby(chunkIds, lambda n, c=count(): n - next(c)):
listcrange = list(crange)
if len(listcrange) == 1:
elements.append('(BLK_ID=' + str(listcrange[0]) + ')')
else:
elements.append('(BLK_ID between ' + str(listcrange[0]) +
' and ' + str(listcrange[-1]) + ')')
oracleops.mogrifyExecute(
cursor, """INSERT INTO """ + self.resultTable + """
SELECT """ + dbops.getSelectCols(self.qp.columns, {
'x': 'x',
'y': 'y',
'z': 'z'
}, None) + """ FROM table ( sdo_PointInPolygon (
cursor (SELECT """ + dbops.getSelectCols(
self.columns, self.getColumnNamesDict(True), None, True) +
""" FROM
(select points,num_points from """ + self.blockTable +
""" WHERE """ + ' OR '.join(elements) + """) pcblob,
TABLE (sdo_util.getvertices(sdo_pc_pkg.to_geometry(pcblob.points,pcblob.num_points,3,NULL))) pnt """
+ dbops.getWhereStatement(zCondition) + """),
(select geom from """ + self.queryTable + """ where id = """ +
str(self.queryIndex) + """), """ + str(self.tolerance) +
""", NULL))""")
connection.close()
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 createBlockIndex(self, cursor, srid, minX, minY, maxX, maxY, blockTable, indexTableSpace, workTableSpace, numProcesses):
oracleops.mogrifyExecute(cursor,"""insert into USER_SDO_GEOM_METADATA values ('""" + blockTable + """','BLK_EXTENT',
sdo_dim_array(sdo_dim_element('X',""" + str(minX) + """,""" + str(maxX) + """,""" + str(self.tolerance) + """),
sdo_dim_element('Y',""" + str(minY) + """,""" + str(maxY) + """,""" + str(self.tolerance) + """)),""" + str(srid) + """)""")
oracleops.mogrifyExecute(cursor,"""create index """ + blockTable + """_SIDX on """ + blockTable + """ (blk_extent) indextype is mdsys.spatial_index
parameters ('tablespace=""" + indexTableSpace + """ work_tablespace=""" + workTableSpace + """ layer_gtype=polygon sdo_indx_dims=2 sdo_rtr_pctfree=0')""" + self.getParallelString(numProcesses))
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 loadFromFileSequential(self, fileAbsPath, index, numFiles):
fileBlockTable = self.getFileBlockTable(index)
connection = self.getConnection()
cursor = connection.cursor()
oracleops.mogrifyExecute(cursor, "INSERT INTO " + self.blockTable + " SELECT * FROM " + fileBlockTable)
oracleops.mogrifyExecute(cursor, "DROP TABLE " + fileBlockTable)
connection.close()
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 createIndex(self, cursor, tableName, columns, indexTableSpace,
numProcesses):
oracleops.mogrifyExecute(
cursor, """
CREATE INDEX """ + tableName + """_IDX on """ + tableName + """ (""" +
(','.join(self.getDBColumns(columns, False))) + """)
tablespace """ + indexTableSpace + """ pctfree 0 nologging """ +
self.getParallelString(numProcesses))
def childInsert(self, iMortonRanges, xMortonRanges):
connection = self.getConnection()
cursor = connection.cursor()
cqp = copy.copy(self.qp)
cqp.statistics = None
(query, queryArgs) = dbops.getSelectMorton(iMortonRanges, xMortonRanges, cqp, self.flatTable, self.addContainsCondition, self.colsDict, self.getHintStatement(self.hints))
oracleops.mogrifyExecute(cursor, "INSERT INTO " + self.resultTable + " " + query, queryArgs)
connection.close()
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 not self.flatTableIOT:
self.createIndex(cursor, self.flatTable, self.index, self.indexTableSpace, self.numProcessesLoad)
self.computeStatistics(cursor, self.flatTable)
connection.close()
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 loadFromFileSequential(self, fileAbsPath, index, numFiles):
fileBlockTable = self.getFileBlockTable(index)
connection = self.getConnection()
cursor = connection.cursor()
oracleops.mogrifyExecute(
cursor, "INSERT INTO " + self.blockTable + " SELECT * FROM " +
fileBlockTable)
oracleops.mogrifyExecute(cursor, "DROP TABLE " + fileBlockTable)
connection.close()
def computeStatistics(self, cursor, tableName):
oracleops.mogrifyExecute(
cursor, "ANALYZE TABLE " + tableName +
" compute system statistics for table")
oracleops.mogrifyExecute(
cursor, """
BEGIN
dbms_stats.gather_table_stats('""" + self.userName + """','""" +
tableName +
"""',NULL,NULL,FALSE,'FOR ALL COLUMNS SIZE AUTO',8,'ALL');
END;""")
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 childInsert(self, iMortonRanges, xMortonRanges):
connection = self.getConnection()
cursor = connection.cursor()
cqp = copy.copy(self.qp)
cqp.statistics = None
(query, queryArgs) = dbops.getSelectMorton(
iMortonRanges, xMortonRanges, cqp, self.flatTable,
self.addContainsCondition, self.colsDict,
self.getHintStatement(self.hints))
oracleops.mogrifyExecute(
cursor, "INSERT INTO " + self.resultTable + " " + query, queryArgs)
connection.close()
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 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 not self.flatTableIOT:
self.createIndex(cursor, self.flatTable, self.index,
self.indexTableSpace, self.numProcessesLoad)
self.computeStatistics(cursor, self.flatTable)
connection.close()
def createBlockIndex(self, cursor, srid, minX, minY, maxX, maxY, blockTable, indexTableSpace, workTableSpace, numProcesses):
strsrid = ''
if srid == '':
strsrid = '4269'
else:
strsrid = str(srid)
oracleops.mogrifyExecute(cursor,"""insert into USER_SDO_GEOM_METADATA values ('""" + blockTable + """','BLK_EXTENT',
sdo_dim_array(sdo_dim_element('X',""" + str(minX) + """,""" + str(maxX) + """,""" + str(self.tolerance) + """),
sdo_dim_element('Y',""" + str(minY) + """,""" + str(maxY) + """,""" + str(self.tolerance) + """)),""" + strsrid + """)""")
oracleops.mogrifyExecute(cursor,"""create index """ + blockTable + """_SIDX on """ + blockTable + """ (blk_extent) indextype is mdsys.spatial_index
parameters ('tablespace=""" + indexTableSpace + """ work_tablespace=""" + workTableSpace + """ layer_gtype=polygon sdo_indx_dims=2 sdo_rtr_pctfree=0')""" + self.getParallelString(numProcesses))
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 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 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 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 the quadtree
qtDomain = (0, 0, int((maxX-minX)/scaleX), int((maxY-minY)/scaleY))
self.quadtree = QuadTree(qtDomain, 'auto')
# Differentiate QuadTree nodes that are fully in the query region
self.mortonDistinctIn = False
connection.close()
self.colsDict = self.getColumnNamesDict(False)
def initCreatePC(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance, workTableSpace, create = True):
c=''
if create:
c = "sdo_pc_pkg.create_pc (ptcld, '" + flatTable + "', NULL);"
oracleops.mogrifyExecute(cursor,"""
DECLARE
ptn_params varchar2(80) := 'blk_capacity=""" + str(blockSize) + """';
extent sdo_geometry := sdo_geometry(2003,""" + str(srid) + """,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(""" + str(minX) + """,""" + str(minY) + """,""" + str(maxX) + """,""" + str(maxY) + """));
ptcld sdo_pc;
BEGIN
ptcld := sdo_pc_pkg.init ('""" + baseTable + """', 'PC', '""" + blockTable + """', ptn_params, extent, """ + str(tolerance) + """, 3, NULL, NULL, NULL);
insert into """ + baseTable + """ values (ptcld);
commit;
""" + c + """
END;
""")
def initCreatePC(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance, workTableSpace, create = True):
c=''
if create:
c = "sdo_pc_pkg.create_pc (ptcld, '" + flatTable + "', NULL);"
oracleops.mogrifyExecute(cursor,"""
DECLARE
ptn_params varchar2(80) := 'blk_capacity=""" + str(blockSize) + """';
extent sdo_geometry := sdo_geometry(2003,""" + str(srid) + """,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(""" + str(minX) + """,""" + str(minY) + """,""" + str(maxX) + """,""" + str(maxY) + """));
ptcld sdo_pc;
BEGIN
ptcld := sdo_pc_pkg.init ('""" + baseTable + """', 'PC', '""" + blockTable + """', ptn_params, extent, """ + str(tolerance) + """, 3, NULL, NULL, NULL);
insert into """ + baseTable + """ values (ptcld);
commit;
""" + c + """
END;
""")
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 runGenericQueryParallelGridChild(self, index, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
zCondition = dbops.addZCondition(self.qp, 'pnt.z', None)
# q = 'SELECT count(*) FROM ' + self.blockTable + ', ' + gridTable + ' WHERE id = ' + str(index) + " AND SDO_ANYINTERACT(blk_extent, geom) = 'TRUE'"
# oracleops.mogrifyExecute(cursor, q)
# print self.queryIndex, 'TOUCHED_BLOCKS CHILD ', index , cursor.fetchone()[0]
query = """
INSERT INTO """ + self.resultTable + """
SELECT """ + dbops.getSelectCols(self.qp.columns, self.getColumnNamesDict(True), None, True) + """ FROM
table (sdo_pc_pkg.clip_pc((SELECT pc FROM """ + self.baseTable + """),
(SELECT geom FROM """ + gridTable + """ WHERE id = """ + str(index) + """),
NULL,NULL,NULL,NULL)) pcblob,
table (sdo_util.getvertices(sdo_pc_pkg.to_geometry(pcblob.points,pcblob.num_points,3,NULL))) pnt """ + dbops.getWhereStatement(zCondition)
oracleops.mogrifyExecute(cursor, query)
connection.close()
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)
# 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 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 runGenericQueryParallelCandChild(self, chunkIds):
connection = self.getConnection()
cursor = connection.cursor()
zCondition = dbops.addZCondition(self.qp, 'pnt.z', None)
elements = []
for _,crange in groupby(chunkIds, lambda n, c=count(): n-next(c)):
listcrange = list(crange)
if len(listcrange) == 1:
elements.append('(BLK_ID=' + str(listcrange[0])+ ')')
else:
elements.append('(BLK_ID between ' + str(listcrange[0]) + ' and ' + str(listcrange[-1])+')')
oracleops.mogrifyExecute(cursor, """INSERT INTO """ + self.resultTable + """
SELECT """ + dbops.getSelectCols(self.qp.columns, {'x':'x','y':'y','z':'z'}, None) + """ FROM table ( sdo_PointInPolygon (
cursor (SELECT """ + dbops.getSelectCols(self.columns, self.getColumnNamesDict(True), None, True) + """ FROM
(select points,num_points from """ + self.blockTable + """ WHERE """ + ' OR '.join(elements) + """) pcblob,
TABLE (sdo_util.getvertices(sdo_pc_pkg.to_geometry(pcblob.points,pcblob.num_points,3,NULL))) pnt """ + dbops.getWhereStatement(zCondition) + """),
(select geom from """ + self.queryTable + """ where id = """ + str(self.queryIndex) + """), """ + str(self.tolerance) + """, NULL))""")
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 the quadtree
qtDomain = (0, 0, int(
(maxX - minX) / scaleX), int((maxY - minY) / scaleY))
self.quadtree = QuadTree(qtDomain, 'auto')
# Differentiate QuadTree nodes that are fully in the query region
self.mortonDistinctIn = False
connection.close()
self.colsDict = self.getColumnNamesDict(False)
def initCreatePCHilbert(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance):
# this one also populates
oracleops.mogrifyExecute(cursor,"""
DECLARE
ptcld sdo_pc;
ptn_params varchar2(80) := 'blk_capacity=""" + str(blockSize) + """';
extent sdo_geometry := sdo_geometry(2003,""" + str(srid) + """,NULL,sdo_elem_info_array(1,1003,3),sdo_ordinate_array(""" + str(minX) + """,""" + str(minY) + """,""" + str(maxX) + """,""" + str(maxY) + """));
other_attrs XMLType := xmltype('
<opc:sdoPcObjectMetadata
xmlns:opc="http://xmlns.oracle.com/spatial/vis3d/2011/sdovis3d.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
blockingMethod="Hilbert R-tree">
</opc:sdoPcObjectMetadata>');
BEGIN
ptcld := sdo_pc_pkg.init ('""" + baseTable + """', 'PC', '""" + blockTable + """', ptn_params, extent, """ + str(tolerance) + """, 3, NULL, NULL, other_attrs);
insert into """ + baseTable + """ values (ptcld);
commit;
sdo_pc_pkg.create_pc (ptcld, '""" + flatTable + """', NULL);
END;
""")
self.updateBlocksSRID(cursor, blockTable, srid)
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 runGenericQueryParallelGridChild(self, index, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
zCondition = dbops.addZCondition(self.qp, 'pnt.z', None)
# q = 'SELECT count(*) FROM ' + self.blockTable + ', ' + gridTable + ' WHERE id = ' + str(index) + " AND SDO_ANYINTERACT(blk_extent, geom) = 'TRUE'"
# oracleops.mogrifyExecute(cursor, q)
# print self.queryIndex, 'TOUCHED_BLOCKS CHILD ', index , cursor.fetchone()[0]
query = """
INSERT INTO """ + self.resultTable + """
SELECT """ + dbops.getSelectCols(self.qp.columns,
self.getColumnNamesDict(True), None,
True) + """ FROM
table (sdo_pc_pkg.clip_pc((SELECT pc FROM """ + self.baseTable + """),
(SELECT geom FROM """ + gridTable + """ WHERE id = """ + str(
index) + """),
NULL,NULL,NULL,NULL)) pcblob,
table (sdo_util.getvertices(sdo_pc_pkg.to_geometry(pcblob.points,pcblob.num_points,3,NULL))) pnt """ + dbops.getWhereStatement(
zCondition)
oracleops.mogrifyExecute(cursor, query)
connection.close()
def runGenericQueryParallelGridChild(self, sIndex, gridTable):
connection = self.getConnection()
cursor = connection.cursor()
self.queryIndex = sIndex
self.queryTable = gridTable
cqp = copy.copy(self.qp)
cqp.queryType = 'generic'
if self.qp.queryType == 'rectangle':
cqp.queryType = 'rectangle'
cqp.statistics = None
cqp.minx = "'||to_char(bbox.sdo_ordinates(1))||'"
cqp.maxx = "'||to_char(bbox.sdo_ordinates(3))||'"
cqp.miny = "'||to_char(bbox.sdo_ordinates(2))||'"
cqp.maxy = "'||to_char(bbox.sdo_ordinates(4))||'"
(query, _) = dbops.getSelect(cqp, self.flatTable, self.addContainsCondition, self.colsDict)
oracleops.mogrifyExecute(cursor, """
DECLARE
bbox sdo_geometry;
BEGIN
select sdo_geom_mbr (geom) into bbox from """ + gridTable + """ where id = """ + str(sIndex) + """;
execute immediate 'INSERT INTO """ + self.resultTable + """ """ + query + """';
END;""")
connection.close()
def initialize(self):
# Creates the user that will store the tables
if self.columns != 'xyz':
raise Exception('ERROR: This loader only currently accepts XYZ!. First you need to change the JAVA incremental loader')
if self.cUser:
self.createUser()
# Get the point cloud folder description
logging.info('Getting files, extent and SRID from input folder ' + self.inputFolder)
(self.inputFiles, _, _, _, boundingCube, _) = lasops.getPCFolderDetails(self.inputFolder, numProc = self.numProcessesLoad)
(self.minX, self.minY, _, self.maxX, self.maxY, _) = boundingCube
# Creates connection
connection = self.getConnection()
cursor = connection.cursor()
# Create blocks table and base table
self.createBlocksTable(cursor, self.blockTable, self.tableSpace, self.compression, self.baseTable)
self.blockSeq = self.blockTable + '_ID_SEQ'
oracleops.mogrifyExecute(cursor, "CREATE SEQUENCE " + self.blockSeq)
self.initCreatePC(cursor, self.srid, self.minX, self.minY, self.maxX, self.maxY, None, self.blockTable, self.baseTable, self.blockSize, self.tolerance, self.workTableSpace, False)
connection.close()
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 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 createIndex(self, cursor, tableName, columns, indexTableSpace, numProcesses):
oracleops.mogrifyExecute(cursor,"""
CREATE INDEX """ + tableName + """_IDX on """ + tableName + """ (""" + (','.join(self.getDBColumns(columns, False))) + """)
tablespace """ + indexTableSpace + """ pctfree 0 nologging """ + self.getParallelString(numProcesses))
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 createBlockIdIndex(self, cursor, blockTable, indexTableSpace):
oracleops.mogrifyExecute(cursor,"""ALTER TABLE """ + blockTable + """ add constraint """ + blockTable + """_PK primary key (obj_id, blk_id) using index tablespace """ + indexTableSpace)
def createBlockIdIndex(self, cursor, blockTable, indexTableSpace):
oracleops.mogrifyExecute(cursor,"""ALTER TABLE """ + blockTable + """ add constraint """ + blockTable + """_PK primary key (obj_id, blk_id) using index tablespace """ + indexTableSpace)
def updateBlocksSRID(self, cursor, blockTable, srid):
oracleops.mogrifyExecute(cursor, "update " + blockTable + " b set b.blk_extent.sdo_srid = " + str(srid))
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 computeStatistics(self, cursor, tableName):
oracleops.mogrifyExecute(cursor, "ANALYZE TABLE " + tableName + " compute system statistics for table")
oracleops.mogrifyExecute(cursor,"""
BEGIN
dbms_stats.gather_table_stats('""" + self.userName + """','""" + tableName + """',NULL,NULL,FALSE,'FOR ALL COLUMNS SIZE AUTO',8,'ALL');
END;""")
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 updateBlocksSRID(self, cursor, blockTable, srid):
oracleops.mogrifyExecute(cursor, "update " + blockTable + " b set b.blk_extent.sdo_srid = " + str(srid))
