def pythonParallelization(self):
        connection = self.getConnection()
        cursor = connection.cursor()
        colsDict = self.getColumnNamesDict(False)

        if self.parallelType == 'cand':
            idsQuery = "SELECT " + self.getParallelHint() + " BLK_ID FROM " + self.blockTable + ", " + self.queryTable + " WHERE SDO_FILTER(BLK_EXTENT,GEOM) = 'TRUE' AND id = " + str(self.queryIndex)
            (eTime, result) = dbops.genericQueryParallelCand(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, colsDict, 
                                                             self.qp.statistics, self.resultTable, idsQuery, None, 
                                                             self.runGenericQueryParallelCandChild, self.numProcessesQuery, oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
            #returnDict[queryId] = self.genericQueryParallelCand()
        elif self.parallelType in ('grid','griddis'):
#            q = 'SELECT count(*) FROM ' + self.blockTable + ', ' + self.queryTable + ' WHERE id = ' + str(self.queryIndex) + " AND SDO_ANYINTERACT(blk_extent, geom) = 'TRUE'"
#            oracleops.mogrifyExecute(cursor, q)
#            print self.queryIndex, 'TOUCHED_BLOCKS TOTAL   ', cursor.fetchone()[0]
            
            gridTable = ('query_grid_' + str(self.queryIndex)).upper()
            oracleops.dropTable(cursor, gridTable, True)
            (eTime, result) =  dbops.genericQueryParallelGrid(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, colsDict, 
                                                             self.qp.statistics, self.resultTable, gridTable, self.createGridTableMethod,
                                                             self.runGenericQueryParallelGridChild, self.numProcessesQuery, 
                                                             (self.parallelType == 'griddis'), oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
            
        connection.close()
        return (eTime, result)
    def 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 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)
Example #4
0
    def pythonParallelization(self):
        connection = self.getConnection()
        cursor = connection.cursor()
        gridTable = ('query_grid_' + str(self.queryIndex)).upper()
        oracleops.dropTable(cursor, gridTable, True)
        (eTime, result) =  dbops.genericQueryParallelGrid(cursor, self.qp.queryMethod, oracleops.mogrifyExecute, self.qp.columns, self.colsDict,
         self.qp.statistics, self.resultTable, gridTable, self.createGridTableMethod,
         self.runGenericQueryParallelGridChild, self.numProcessesQuery,
         (self.parallelType == 'griddis'), oracleops.createSQLFile, oracleops.executeSQLFileCount, self.getConnectionString(False))
        connection.close()

        return (eTime, result)
 def initialize(self):
     # Get connection
     connection = self.getConnection()
     cursor = connection.cursor()
     # Get SRID of the stored PC
     oracleops.mogrifyExecute(cursor, "SELECT srid FROM user_sdo_geom_metadata WHERE table_name = '" + self.blockTable + "'")
     self.srid = cursor.fetchone()[0]
     
     # Create table to store the query geometries
     oracleops.dropTable(cursor, self.queryTable, check = True)
     oracleops.mogrifyExecute(cursor, "CREATE TABLE " + self.queryTable + " ( id number primary key, geom sdo_geometry) " + self.getTableSpaceString(self.tableSpace) + " pctfree 0 nologging")
     connection.close()
Example #6
0
    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)
Example #7
0
 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 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 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 createBlocksTable(self, cursor, blockTable, tableSpace, compression, baseTable = None, includeBlockId = False):    
        """ Create the blocks table and meta-data table"""
        oracleops.dropTable(cursor, blockTable, True)
        if baseTable != None:
            oracleops.dropTable(cursor, baseTable, True)
        
        # Tables to contain point data and point cloud metadata
        oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + blockTable + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging 
  lob(points) store as securefile (""" + self.getTableSpaceString(tableSpace) + self.getCompressString(compression) + """ cache reads nologging)
as SELECT * FROM mdsys.SDO_PC_BLK_TABLE where 0 = 1""")
        
        if baseTable != None:
            if includeBlockId:
                oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + baseTable + """ (id number, pc sdo_pc) """ +  self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")
            else:
                oracleops.mogrifyExecute(cursor,"""
CREATE TABLE """ + baseTable + """ (pc sdo_pc) """ +  self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging""")  
    def query(self, queryId, iterationId, queriesParameters):
        (eTime, result) = (-1, None)
        connection = self.getConnection()
        cursor = connection.cursor()
        self.prepareQuery(cursor, queryId, queriesParameters, iterationId == 0)
        oracleops.dropTable(cursor, self.resultTable, True)    
       
        wkt = self.qp.wkt
        if self.qp.queryType == 'nn':
            g = loads(self.qp.wkt)
            wkt = dumps(g.buffer(self.qp.rad))
       
        t0 = time.time()
        scaledWKT = wktops.scale(wkt, self.scaleX, self.scaleY, self.minX, self.minY)    
        (mimranges,mxmranges) = self.quadtree.getMortonRanges(scaledWKT, self.mortonDistinctIn, maxRanges = MAXIMUM_RANGES)
       
        if len(mimranges) == 0 and len(mxmranges) == 0:
            logging.info('None morton range in specified extent!')
            return (eTime, result)

        self.hints = []
        if not self.flatTableIOT: 
            self.hints.append('INDEX(' + self.flatTable + ' ' + self.flatTable + '_IDX)')
        
        if self.qp.queryMethod != 'stream' and self.numProcessesQuery > 1 and self.parallelType != 'nati' and self.qp.queryType in ('rectangle','circle','generic') :
            return self.pythonParallelization(t0, mimranges, mxmranges)
        
        if self.numProcessesQuery > 1:
            self.hints.append('PARALLEL (' + str(self.numProcessesQuery) + ')')
        (query, queryArgs) = dbops.getSelectMorton(mimranges, mxmranges, self.qp, self.flatTable, self.addContainsCondition, self.colsDict, self.getHintStatement(self.hints))
        
        if self.qp.queryMethod != 'stream': # disk or stat
            oracleops.mogrifyExecute(cursor, "CREATE TABLE "  + self.resultTable + " AS " + query + "", queryArgs)
            (eTime, result) = dbops.getResult(cursor, t0, self.resultTable, self.colsDict, (not self.mortonDistinctIn), self.qp.columns, self.qp.statistics)
        else:
            sqlFileName = str(queryId) + '.sql'
            oracleops.createSQLFile(cursor, sqlFileName, query, queryArgs)
            result = oracleops.executeSQLFileCount(self.getConnectionString(False), sqlFileName)
            eTime = time.time() - t0
            
        connection.close()
        return (eTime, result)
    def createIOTTable(self, cursor, iotTableName, tableName, tableSpace, icolumns, ocolumns, keycolumns, numProcesses, check = False, hilbertFactor = None):
        """ Create Index-Organized-Table and populate it from tableName Table"""
        oracleops.dropTable(cursor, iotTableName, True)
        hilbertColumnName = 'd'
        if hilbertFactor != None:
            hilbertColumnName = 'd+(rownum*' + hilbertFactor + ') d'
        
        icols = self.getDBColumns(icolumns,False, hilbertColumnName)
        ocols = self.getDBColumns(ocolumns,False)
        kcols = self.getDBColumns(keycolumns,False)
        
        oracleops.mogrifyExecute(cursor, """
CREATE TABLE """ + iotTableName + """
(""" + (','.join(ocols)) + """
    , constraint """ + iotTableName + """_PK primary key (""" + (','.join(kcols)) + """))
    organization index
    """ + self.getTableSpaceString(tableSpace) + """ pctfree 0 nologging
    """ + self.getParallelString(numProcesses) + """
as
    SELECT """ + (','.join(icols)) + """ FROM """ + tableName)
    def 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""")
Example #15
0
    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 populateBlocksHilbert(self, cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance):
     # NOTE: In this case we do not require to create a view, since the fixed format by the hilbert pre-processor makes it directly compatible
     # if we change the pre-processor we need to change these ones
     self.initCreatePCHilbert(cursor, srid, minX, minY, maxX, maxY, flatTable, blockTable, baseTable, blockSize, tolerance)
     oracleops.dropTable(cursor, flatTable)
 def 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)")