def make_dguno_spatial(self, progress, pglayername, layer, fieldname,
output):
"""
:param progress: Processing progressbar and info
:param pglayername: PostgreSQL layer to load subset from
:param layer: layer with dgunummer
:param fieldname: field with dgunummer
:param output: result output file
:return: True if non error - else False
"""
layerobj = dataobjects.getObjectFromUri(layer)
dguno_list = [ft[fieldname] for ft in layerobj.getFeatures()]
dguno_str = "'" + "', '".join(dguno_list) + "'"
where_sql = 'boreholeno in ({})'.format(dguno_str)
# JupiterAux.log_info('wheresql: {}'.format(where_sql))
db = JupiterDb()
# Find input boreholeno that have match in db
str_no_match = ''
list_no_boreholeno_match = db.boring_not_in_csv(dguno_str, dguno_list)
if list_no_boreholeno_match:
str_no_match = ''.join(list_no_boreholeno_match)
# JupiterAux.msg_box(''.join(list_no_boreholeno_match))
# Find input boreholeno that have duplicates
import collections
counter = collections.Counter(dguno_list)
str_duplicates = ''
if counter:
d = dict((k, v) for k, v in counter.items() if v >= 2)
import json
str_duplicates = json.dumps(d)
uri = db.getUri()
uri.setDataSource("jupiter", pglayername, "geom", where_sql)
ok, pglayer = self.getQgsVectorLayerFromUri(uri, pglayername)
if not ok:
return False
if not self.writeOutputVector(progress, pglayer, output):
return False
#if not self.addStyle(pglayername, vlayer):
#return False
return True, str_no_match, str_duplicates
def add_alphaview(self, progress, pglayername, output):
"""Load a non spatial table or view from postgresql - currently pesticide list
:param progress: Information text and progress
:param pglayername: Name of alpha view to load
:param output: processing.core.outputs.OutputVector object
:return: True if no error
"""
db = JupiterDb()
uri = db.getUri()
uri.setDataSource("jupiter", pglayername, None)
JupiterAux.log_info(
'Loading alphalayer: {} from URI'.format(pglayername),
progress=progress)
ok, pglayer = self.getQgsVectorLayerFromUri(uri, pglayername)
if not ok:
return False
if not self.writeOutputVector(
progress, pglayer, output, isSpatial=False):
return False
return True
def add_maplayer(self,
progress,
pglayername,
output,
extent,
selectionLayername=None,
rowid=None,
where_sql2=None):
"""Adds a postgis layer to map
:type progress: Processing progressbar and info
:type pglayername: string name of pglayer to select from Jupiter
:param output: processing.core.outputs.OutputVector object
:type extent: Bounding box of query, if none use selected geom(s) as bound
:type selectionLayername: string name of layer with selected geom to use as border in query
:type rowid: needed for qgis to open views
:where_sql: additional sql, not only bound search ... update doc
WARNING: Do not use semicolon after where sql at uri.setDataSource
"""
# import sys
# sys.path.append(r'C:\Program Files\JetBrains\PyCharm 2017.2.3\debug-eggs\pycharm-debug.egg')
# import pydevd
# pydevd.settrace('localhost', port=53100, stdoutToServer=True, stderrToServer=True)
JupiterAux.log_info('add_maplayer: {}'.format(pglayername), progress)
db = JupiterDb()
uri = db.getUri()
where_sql = ''
# https://gis.stackexchange.com/questions/239601/sqlqueries-in-postgis-database-layer-using-python-and-qgis/239682#239682
if extent == "0,0,0,0":
''' Query by geometry of selection in a layer '''
wkt = self.selectedFeatureToWKT(selectionLayername)
if not where_sql2:
where_sql = "ST_WITHIN(geom , ST_GeomFromText('{}', 25832))".format(
wkt)
else:
where_sql = "ST_WITHIN(geom , ST_GeomFromText('{}', 25832) ) AND {}".format(
wkt, where_sql2)
else:
''' Query by entered extent '''
bbox = self.extentToBoundingbox(extent)
if not where_sql2:
where_sql = "geom && ST_MakeEnvelope({}, {}, {}, {}, 25832)". \
format(bbox.xMinimum(), bbox.yMinimum(), bbox.xMaximum(), bbox.yMaximum())
else:
where_sql = "geom && ST_MakeEnvelope({}, {}, {}, {}, 25832) AND {}" \
.format(bbox.xMinimum(), bbox.yMinimum(), bbox.xMaximum(), bbox.yMaximum(), where_sql2)
uri.setDataSource("jupiter", pglayername, "geom", where_sql)
JupiterAux.log_info(
'Loading layer: "{}" from URI with where clause: "{}"'.format(
pglayername, where_sql2),
progress=progress)
# Views must have defined a rowid
if rowid:
uri.setKeyColumn(rowid)
ok, pglayer = self.getQgsVectorLayerFromUri(uri, pglayername)
if not ok:
return False
if not self.writeOutputVector(progress, pglayer, output):
return False
#if not self.addStyle(pglayername, vlayer, uri):
#return False
# TODO layer is not add to map until end of table_geoalgorithm.processAlgorithm
# TODO therefor is the showFeatureCount below not working
# self.showFeatureCount(vlayer.name())
return True