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 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 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 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)