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