def getSelect(self, qp, iMortonRanges, xMortonRanges):
     queryArgs = []
     query = ''
     
     zname = self.columnsNameDict['z'][0]
     kname = 'quadcellid'
     
     if len(iMortonRanges):
         if qp.queryType == 'nn':
             raise Exception('If using NN len(iMortonRanges) must be 0!')
         cols = dbops.getSelectCols(qp.columns, self.columnsNameDict, None, True)
         inMortonCondition = dbops.addMortonCondition(qp, iMortonRanges, kname, queryArgs) 
         inZCondition = dbops.addZCondition(qp, zname, queryArgs)
         query = "SELECT " + cols + " FROM (SELECT PC_Explode(pa) as qpoint from " + self.blockTable + dbops.getWhereStatement(inMortonCondition) + ") as qtable1 " + dbops.getWhereStatement(inZCondition) + " UNION "
     else:
         cols = dbops.getSelectCols(qp.columns, self.columnsNameDict, qp.statistics, True)
     
     mortonCondition = dbops.addMortonCondition(qp, xMortonRanges, kname, queryArgs)
     
     if qp.queryType in ('rectangle', 'circle', 'generic'):
         containsCondition = self.addContains(queryArgs)
         zCondition = dbops.addZCondition(qp, zname, queryArgs)
         query += "SELECT " + cols + " FROM (SELECT PC_Explode(PC_Intersection(pa,geom)) as qpoint from " + self.blockTable + ', ' + self.queryTable + dbops.getWhereStatement([mortonCondition, containsCondition]) + ") as qtable2 " + dbops.getWhereStatement(zCondition)
     elif qp.queryType != 'nn':
         #Approximation
         query += "SELECT " + cols + " FROM (SELECT PC_Explode(pa) as qpoint from " + self.blockTable + dbops.getWhereStatement(mortonCondition) + ") as qtable3 "
     return (query, queryArgs)
    def getSelect(self):
        isClipPcParallel = (self.qp.queryType in ('rectangle','circle','generic')) and (self.numProcessesQuery > 1)
        columnNamesDict = self.getColumnNamesDict(not isClipPcParallel)
        selectedColumns = dbops.getSelectCols(self.qp.columns, columnNamesDict, self.qp.statistics)
        zCondition = dbops.addZCondition(self.qp, columnNamesDict['z'][0], None)
        
        if self.qp.queryType in ('rectangle','circle','generic'):
            if self.numProcessesQuery == 1:
                query = """
    SELECT """ + selectedColumns + """ 
    FROM
      table(sdo_pc_pkg.clip_pc(
          (SELECT pc FROM """ + self.baseTable + """),
          (SELECT geom FROM """ + self.queryTable + """ WHERE id = """ + str(self.queryIndex) + """),
          null, 1, 1)) query_blocks,
    table(sdo_util.getvertices(sdo_pc_pkg.to_geometry(
          query_blocks.points,
          query_blocks.num_points,
          3, null))) pnt
    """ + dbops.getWhereStatement(zCondition)
            else:
                query = """
WITH
  candidates AS (
    SELECT blocks.blk_id, subqueries.ind_dim_qry, subqueries.other_dim_qry
    FROM """ + self.blockTable + """ blocks,
      (SELECT 1 min_res, 1 max_res, 
             (SELECT geom FROM """ + self.queryTable + """ WHERE id = """ + str(self.queryIndex) + """) ind_dim_qry, 
             cast(null as sdo_mbr) other_dim_qry 
       FROM dual ) subqueries
    WHERE
      blocks.pcblk_min_res <= max_res and
      blocks.pcblk_max_res >= min_res and
      SDO_ANYINTERACT(blocks.blk_extent, subqueries.ind_dim_qry) = 'TRUE')
SELECT """ + self.getParallelHint() + """ """ + selectedColumns + """ 
FROM
  table(
    sdo_pc_pkg.clip_pc_parallel(
      cursor(select * from candidates),
      (select pc from """ + self.baseTable + """)))
"""  + dbops.getWhereStatement(zCondition)

        else: # NN query
            numBlocksNeigh = int(math.pow(2 + math.ceil(math.sqrt(math.ceil(float(self.qp.num)/float(self.blockSize)))), 2))
            if self.numProcessesQuery > 1:
                logging.warning('Ignoring parallel querying for NN query: It caused internal error!')
            query = """
SELECT """ + selectedColumns + """ 
FROM (SELECT a.points, a.num_points 
      FROM """ + self.blockTable + """ a, """ + self.queryTable + """ b 
      WHERE b.ID = """ + str(self.queryIndex) + """ AND SDO_NN(a.BLK_EXTENT,b.GEOM)='TRUE' 
      AND ROWNUM <= """ + str(numBlocksNeigh) + """) c,
     table (sdo_util.getvertices(sdo_pc_pkg.to_geometry(c.points,c.num_points,3,NULL))) pnt
WHERE ROWNUM <= """ + str(self.qp.num) + """ """ + dbops.getWhereStatement(zCondition) + """ 
ORDER BY (POWER((pnt.x - """ + str(self.qp.cx) + """),2) + POWER((pnt.y - """ + str(self.qp.cy) + """),2)) """            
        return query
 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 getSelectParallel(self, cursor, qp, queryTable, queryIndex, isCand = False, chunkIds = None):
        cols = dbops.getSelectCols(qp.columns, self.columnsNameDict, qp.statistics, True)
        queryArgs =  []
        if isCand:
            specCondition = self.addContainsChunkIds(queryArgs, queryIndex, queryTable, chunkIds) 
        else:
            specCondition = self.addContains(queryArgs, queryIndex, queryTable)

        zCondition = dbops.addZCondition(qp, self.columnsNameDict['z'][0], queryArgs)    
        query = "SELECT " + cols + " from (SELECT pc_explode(pc_intersection(pa,geom)) AS qpoint from " + self.blockTable + ", " + queryTable + dbops.getWhereStatement(specCondition) + ") AS qtable "+ dbops.getWhereStatement(zCondition)
        return (query,queryArgs)
 def getSelect(self, qp):
     cols = dbops.getSelectCols(qp.columns, self.columnsNameDict, qp.statistics, True)
     if qp.queryType != 'nn':
         queryArgs = [self.queryIndex, ]
         zCondition = dbops.addZCondition(qp, self.columnsNameDict['z'][0], queryArgs)   
         query = "SELECT " + cols + " from (SELECT pc_explode(pc_intersection(pa,geom)) AS qpoint from " + self.blockTable + ", (SELECT geom FROM " + self.queryTable + " WHERE id = %s) A WHERE pc_intersects(pa,geom)) AS qtable " + dbops.getWhereStatement(zCondition)
     else:
         numBlocksNeigh = int(math.pow(2 + math.ceil(math.sqrt(math.ceil(float(qp.num)/float(self.blockSize)))), 2))
         queryArgs = [self.queryIndex, numBlocksNeigh]            
         zCondition = dbops.addZCondition(qp, self.columnsNameDict['z'][0], queryArgs)
         queryArgs.extend([qp.cx, qp.cy, qp.num])
         orderBy = "ORDER BY ((" + self.columnsNameDict['x'][0] + " - %s)^2 + (" + self.columnsNameDict['y'][0] + " - %s)^2)"
         query = "SELECT " + cols + " FROM ( SELECT PC_explode(pa) as qpoint FROM  (SELECT pa FROM " + self.blockTable + ", " + self.queryTable + " C WHERE C.id = %s ORDER BY geometry(pa) <#> geom LIMIT %s) as A ) as B " + dbops.getWhereStatement(zCondition) + orderBy + " LIMIT %s"
     return (query,queryArgs)
    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 close(self):
     # Restart DB to flush data in memory to disk
     logging.info('Restarting DB')
     os.system('monetdb stop ' + self.dbName)
     os.system('monetdb start ' + self.dbName)
     
     connection = self.getConnection()
     cursor = connection.cursor()
     
     if not self.imprints:
         ftName = self.tempFlatTable
     else:
         ftName = self.flatTable
     
     # We set the table to read only
     if self.partitioning:
         for m in sorted(self.tilesFiles):
             partitionName = ftName + str(m)
             monetdbops.mogrifyExecute(cursor, "alter table " + partitionName + " set read only")
     else:
         monetdbops.mogrifyExecute(cursor, "alter table " + ftName + " set read only")
     
     if self.imprints:
         if self.partitioning:
             # Create imprints index
             logging.info('Creating imprints for different partitions and columns')
             for m in sorted(self.tilesFiles):
                 partitionName = ftName + str(m)
                 for c in self.columns:
                     colName = self.DM_FLAT[c][0]
                     if c == 'x':
                         minDim = self.minX - 10
                         maxDim = self.minX - 9
                     elif c == 'y':
                         minDim = self.minY - 10
                         maxDim = self.minY - 9
                     elif c == 'z':
                         minDim = self.minZ - 10
                         maxDim = self.minZ - 9
                     else:
                         minDim = 0
                         maxDim = 1
                     monetdbops.mogrifyExecute(cursor, "select " + colName + " from " + partitionName + " where " + colName + " between %s and %s", [minDim, maxDim])
                 monetdbops.mogrifyExecute(cursor, "analyze sys." + partitionName + " (" + dbops.getSelectCols(self.columns, self.DM_FLAT) + ") minmax")
         else:
             logging.info('Creating imprints')
             for c in self.columns:
                 colName = self.DM_FLAT[c][0]
                 if c == 'x':
                     minDim = self.minX - 10
                     maxDim = self.minX - 9
                 elif c == 'y':
                     minDim = self.minY - 10
                     maxDim = self.minY - 9
                 elif c == 'z':
                     minDim = self.minZ - 10
                     maxDim = self.minZ - 9
                 else:
                     minDim = 0
                     maxDim = 1
                 monetdbops.mogrifyExecute(cursor, "select " + colName + " from " + self.flatTable + " where " + colName + " between %s and %s", [minDim, maxDim])
             monetdbops.mogrifyExecute(cursor, "analyze sys." + self.flatTable + " (" + dbops.getSelectCols(self.columns, self.DM_FLAT) + ") minmax")
     else:
         if self.partitioning:
             for m in sorted(self.tilesFiles):
                 partitionName = ftName + str(m)
                 newPartitionName = self.flatTable + str(m)
                 monetdbops.mogrifyExecute(cursor, 'CREATE TABLE ' + newPartitionName + ' AS SELECT * FROM ' + partitionName + ' ORDER BY ' + dbops.getSelectCols(self.index, self.DM_FLAT) + ' WITH DATA')
                 monetdbops.mogrifyExecute(cursor, "alter table " + newPartitionName + " set read only")
                 monetdbops.mogrifyExecute(cursor, 'DROP TABLE ' + partitionName)
         else:
             monetdbops.mogrifyExecute(cursor, 'CREATE TABLE ' + self.flatTable + ' AS SELECT * FROM ' + self.tempFlatTable + ' ORDER BY ' + dbops.getSelectCols(self.index, self.DM_FLAT) + ' WITH DATA')
             monetdbops.mogrifyExecute(cursor, "alter table " + self.flatTable + " set read only")
             monetdbops.mogrifyExecute(cursor, 'DROP TABLE ' + self.tempFlatTable)
         
     if self.partitioning:
         for m in sorted(self.tilesFiles):
             partitionName = self.flatTable + str(m)
             monetdbops.mogrifyExecute(cursor, "ALTER TABLE " + self.flatTable + " add table " + partitionName)
     
     connection.close()