def test_configuration(self):
"""Test storage and retrieval for configuration parameters"""
uri = 'dbname=\'qgis_test\' service=\'driver={SQL Server};server=localhost;port=1433;database=qgis_test\' user=\'sa\' password=\'<YourStrong!Passw0rd>\' srid=4326 type=Point estimatedMetadata=\'true\' disableInvalidGeometryHandling=\'1\' table="qgis_test"."someData" (geom)'
md = QgsProviderRegistry.instance().providerMetadata('mssql')
conn = md.createConnection(uri, {})
ds_uri = QgsDataSourceUri(conn.uri())
self.assertEqual(ds_uri.username(), 'sa')
self.assertEqual(ds_uri.database(), 'qgis_test')
self.assertEqual(ds_uri.table(), '')
self.assertEqual(ds_uri.schema(), '')
self.assertEqual(ds_uri.geometryColumn(), '')
self.assertTrue(ds_uri.useEstimatedMetadata())
self.assertEqual(ds_uri.srid(), '')
self.assertEqual(ds_uri.password(), '<YourStrong!Passw0rd>')
self.assertEqual(ds_uri.param('disableInvalidGeometryHandling'), '1')
conn.store('coronavirus')
conn = md.findConnection('coronavirus', False)
ds_uri = QgsDataSourceUri(conn.uri())
self.assertEqual(ds_uri.username(), 'sa')
self.assertEqual(ds_uri.database(), 'qgis_test')
self.assertEqual(ds_uri.table(), '')
self.assertEqual(ds_uri.schema(), '')
self.assertTrue(ds_uri.useEstimatedMetadata())
self.assertEqual(ds_uri.geometryColumn(), '')
self.assertEqual(ds_uri.srid(), '')
self.assertEqual(ds_uri.password(), '<YourStrong!Passw0rd>')
self.assertEqual(ds_uri.param('disableInvalidGeometryHandling'),
'true')
conn.remove('coronavirus')
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_A,
context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(
inLayerA.dataProvider().dataSourceUri())
inLayerB = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_B,
context)
ogrLayerB = GdalUtils.ogrConnectionStringFromLayer(inLayerB)
layernameB = GdalUtils.ogrLayerName(
inLayerB.dataProvider().dataSourceUri())
fieldsA = self.parameterAsFields(parameters, self.FIELDS_A, context)
uri = QgsDataSourceUri(inLayerA.source())
geomColumnA = uri.geometryColumn()
uri = QgsDataSourceUri(inLayerB.source())
geomColumnB = uri.geometryColumn()
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
single = self.parameterAsBool(parameters, self.SINGLE, context)
if len(fieldsA) > 0:
fieldstring = ', '.join(["g1.{}".format(f) for f in fieldsA])
fieldstring = ", " + fieldstring
else:
fieldstring = ""
if single:
sqlstring = "-sql \"SELECT (ST_Dump(ST_Difference(g1." + geomColumnA + ",ST_Union(g2." + geomColumnB + ")))).geom::geometry(Polygon) AS geom" + fieldstring + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 GROUP BY g1." + geomColumnA + fieldstring + "\"" " -nln " + schema + "." + table + " -lco FID=gid -nlt POLYGON -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (ST_Multi(ST_CollectionExtract(ST_Difference(g1." + geomColumnA + ",ST_Union(g2." + geomColumnB + ")),3)))::geometry(MultiPolygon) AS geom" + fieldstring + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 GROUP BY g1." + geomColumnA + fieldstring + "\"" " -nln " + schema + "." + table + " -lco FID=gid -nlt MULTIPOLYGON -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_A,
context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(
inLayerA.dataProvider().dataSourceUri())
inLayerB = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_B,
context)
ogrLayerB = GdalUtils.ogrConnectionStringFromLayer(inLayerB)
layernameB = GdalUtils.ogrLayerName(
inLayerB.dataProvider().dataSourceUri())
fieldA = self.parameterAsString(parameters, self.FIELD_A, context)
fieldB = self.parameterAsString(parameters, self.FIELD_B, context)
uri = QgsDataSourceUri(inLayerA.source())
geomColumnA = uri.geometryColumn()
uri = QgsDataSourceUri(inLayerB.source())
geomColumnB = uri.geometryColumn()
multi = self.parameterAsBool(parameters, self.MULTI, context)
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
if multi:
sqlstring = "-sql \"WITH temp_table AS (SELECT ST_Union(" + geomColumnB + ") AS geom FROM " + layernameB + ") SELECT ST_ShortestLine(g1." + geomColumnA + ",g2.geom) AS geom, ST_Distance(g1." + geomColumnA + ",g2.geom) AS distance, g1." + fieldA + " AS id_from FROM " + layernameA + " AS g1, temp_table AS g2\" -nln " + schema + "." + table + " -lco FID=gid -lco GEOMETRY_NAME=geom -nlt LINESTRING --config PG_USE_COPY YES -a_srs EPSG:" + str(
sridA) + ""
else:
sqlstring = "-sql \"SELECT ST_ShortestLine(g1." + geomColumnA + ",g2." + geomColumnB + ") AS geom, ST_Distance(g1." + geomColumnA + ",g2." + geomColumnB + ") AS distance, g1." + fieldA + " AS id_from, g2." + fieldB + " AS id_to FROM " + layernameA + " AS g1, " + layernameB + " AS g2\" -nln " + schema + "." + table + " -lco FID=gid -lco GEOMETRY_NAME=geom -nlt LINESTRING --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_A,
context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(
inLayerA.dataProvider().dataSourceUri())
fieldA = self.parameterAsString(parameters, self.FIELD_A, context)
dsUriA = QgsDataSourceUri(inLayerA.source())
geomColumnA = dsUriA.geometryColumn()
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
sqlstring = "-sql \"SELECT ST_Makeline(g1." + geomColumnA + ",g2." + geomColumnA + ") AS geom, ST_Distance(g1." + geomColumnA + ",g2." + geomColumnA + ") AS distance, g1." + fieldA + " AS id_from, g2." + fieldA + " AS id_to FROM " + layernameA + " AS g1, " + layernameA + " AS g2 WHERE g1." + fieldA + " > " + "g2." + fieldA + "\" -nln " + schema + "." + table + " -lco FID=gid -lco GEOMETRY_NAME=geom -nlt LINESTRING --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def processAlgorithm(self, parameters, context, feedback):
inLayer = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER, context)
ogrLayer = GdalUtils.ogrConnectionStringFromLayer(inLayer)
layername = GdalUtils.ogrLayerName(inLayer.dataProvider().dataSourceUri())
dsUri = QgsDataSourceUri(inLayer.source())
geomColumn = dsUri.geometryColumn()
srid = inLayer.crs().postgisSrid()
fields = self.parameterAsFields(parameters, self.FIELDS, context)
dissfield = self.parameterAsString(parameters, self.DISSFIELD, context)
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
distance = self.parameterAsString(parameters, self.DISTANCE, context)
dissolveall = self.parameterAsBool(parameters, self.DISSOLVEALL, context)
single = self.parameterAsBool(parameters, self.SINGLE, context)
if len(fields) > 0:
fieldstring = ",".join(fields) + ","
else:
fieldstring = ""
if dissolveall:
if single:
query = '-sql "SELECT (ST_Dump(ST_Multi(ST_Union(ST_Buffer(' + geomColumn + ',' + distance + '))))).geom::geometry(POLYGON,' + str(srid) + ') AS geom FROM ' + layername + '" -nln ' + schema + '.' + table + ' -nlt POLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
else:
query = '-sql "SELECT (ST_Multi(ST_Union(ST_Buffer(' + geomColumn + ',' + distance + '))))::geometry(MULTIPOLYGON,' + str(srid) + ') AS geom FROM ' + layername + '" -nln ' + schema + '.' + table + ' -nlt MULTIPOLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
else:
if dissfield is not None:
if single:
query = '-sql "SELECT ' + dissfield + ',(ST_Dump(ST_Union(ST_Buffer(' + geomColumn + ',' + distance + ')))).geom::geometry(POLYGON,' + str(srid) + ') AS geom FROM ' + layername + ' GROUP BY ' + dissfield + '" -nln ' + schema + '.' + table + ' -nlt POLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
else:
query = '-sql "SELECT ' + dissfield + ',(ST_Multi(ST_Union(ST_Buffer(' + geomColumn + ',' + distance + '))))::geometry(MULTIPOLYGON,' + str(srid) + ') AS geom FROM ' + layername + ' GROUP BY ' + dissfield + '" -nln ' + schema + '.' + table + ' -nlt MULTIPOLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
else:
if single:
query = '-sql "SELECT ' + fieldstring + '(ST_Dump(ST_Multi(ST_Buffer(' + geomColumn + ',' + distance + ')))).geom::geometry(POLYGON,' + str(srid) + ') AS geom FROM ' + layername + '" -nln ' + schema + '.' + table + ' -nlt POLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
else:
query = '-sql "SELECT ' + fieldstring + '(ST_Multi(ST_Buffer(' + geomColumn + ',' + distance + ')))::geometry(MULTIPOLYGON,' + str(srid) + ') AS geom FROM ' + layername + '" -nln ' + schema + '.' + table + ' -nlt MULTIPOLYGON -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES'
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayer)
arguments.append(ogrLayer)
arguments.append(query)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def updateInputLayer(self):
if not self.reloadInputLayer() or not self.inLayer:
return False
# update the output table name, pk and geom column
self.cboTable.setEditText(self.inLayer.name())
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if srcUri.keyColumn() else self.default_pk
self.editPrimaryKey.setText(pk)
geom = srcUri.geometryColumn() if srcUri.geometryColumn() else self.default_geom
self.editGeomColumn.setText(geom)
srcCrs = self.inLayer.crs()
srid = srcCrs.postgisSrid() if srcCrs.isValid() else 4326
self.editSourceSrid.setText("%s" % srid)
self.editTargetSrid.setText("%s" % srid)
return True
def updateInputLayer(self):
if not self.reloadInputLayer() or not self.inLayer:
return False
# update the output table name, pk and geom column
self.cboTable.setEditText(self.inLayer.name())
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if srcUri.keyColumn() else self.default_pk
self.editPrimaryKey.setText(pk)
geom = srcUri.geometryColumn() if srcUri.geometryColumn() else self.default_geom
self.editGeomColumn.setText(geom)
srcCrs = self.inLayer.crs()
srid = srcCrs.postgisSrid() if srcCrs.isValid() else 4326
self.editSourceSrid.setText("%s" % srid)
self.editTargetSrid.setText("%s" % srid)
return True
def updateInputLayer(self):
if not self.reloadInputLayer() or not self.inLayer:
return False
# update the output table name, pk and geom column
self.cboTable.setEditText(self.inLayer.name())
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if srcUri.keyColumn() else self.default_pk
self.editPrimaryKey.setText(pk)
geom = srcUri.geometryColumn() if srcUri.geometryColumn() else self.default_geom
self.editGeomColumn.setText(geom)
srcCrs = self.inLayer.crs()
if not srcCrs.isValid():
srcCrs = QgsCoordinateReferenceSystem(4326)
self.widgetSourceSrid.setCrs(srcCrs)
self.widgetTargetSrid.setCrs(srcCrs)
return True
def updateInputLayer(self):
if not self.reloadInputLayer() or not self.inLayer:
return False
# update the output table name, pk and geom column
self.cboTable.setEditText(self.inLayer.name())
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if srcUri.keyColumn() else self.default_pk
self.editPrimaryKey.setText(pk)
geom = srcUri.geometryColumn() if srcUri.geometryColumn() else self.default_geom
self.editGeomColumn.setText(geom)
srcCrs = self.inLayer.crs()
if not srcCrs.isValid():
srcCrs = QgsCoordinateReferenceSystem(4326)
self.widgetSourceSrid.setCrs(srcCrs)
self.widgetTargetSrid.setCrs(srcCrs)
return True
def importLayer(self, layerType, providerKey, layerName, uriString,
parent):
global isImportVectorAvail
if not isImportVectorAvail:
return False
if layerType == 'raster':
return False # not implemented yet
inLayer = QgsRasterLayer(uriString, layerName, providerKey)
else:
inLayer = QgsVectorLayer(uriString, layerName, providerKey)
if not inLayer.isValid():
# invalid layer
QMessageBox.warning(
None, self.tr("Invalid layer"),
self.tr("Unable to load the layer %s") % inLayer.name())
return False
# retrieve information about the new table's db and schema
outItem = parent.internalPointer()
outObj = outItem.getItemData()
outDb = outObj.database()
outSchema = None
if isinstance(outItem, SchemaItem):
outSchema = outObj
elif isinstance(outItem, TableItem):
outSchema = outObj.schema()
# toIndex will point to the parent item of the new table
toIndex = parent
if isinstance(toIndex.internalPointer(), TableItem):
toIndex = toIndex.parent()
if inLayer.type() == inLayer.VectorLayer:
# create the output uri
schema = outSchema.name if outDb.schemas(
) is not None and outSchema is not None else ""
pkCol = geomCol = ""
# default pk and geom field name value
if providerKey in ['postgres', 'spatialite']:
inUri = QgsDataSourceUri(inLayer.source())
pkCol = inUri.keyColumn()
geomCol = inUri.geometryColumn()
outUri = outDb.uri()
outUri.setDataSource(schema, layerName, geomCol, "", pkCol)
self.importVector.emit(inLayer, outDb, outUri, toIndex)
return True
return False
def _layer_table_column(self, layer):
# Returns the table and column name
# that a layer belongs to.
table, column = '', ''
if hasattr(layer, 'dataProvider'):
if layer.dataProvider().name() == 'postgres':
layerConnStr = layer.dataProvider().dataSourceUri()
dataSourceURI = QgsDataSourceUri(layerConnStr)
table, column = dataSourceURI.table(), \
dataSourceURI.geometryColumn()
return table, column
def importLayer(self, layerType, providerKey, layerName, uriString, parent):
global isImportVectorAvail
if not isImportVectorAvail:
return False
if layerType == 'raster':
return False # not implemented yet
inLayer = QgsRasterLayer(uriString, layerName, providerKey)
else:
inLayer = QgsVectorLayer(uriString, layerName, providerKey)
if not inLayer.isValid():
# invalid layer
QMessageBox.warning(None, self.tr("Invalid layer"), self.tr("Unable to load the layer %s") % inLayer.name())
return False
# retrieve information about the new table's db and schema
outItem = parent.internalPointer()
outObj = outItem.getItemData()
outDb = outObj.database()
outSchema = None
if isinstance(outItem, SchemaItem):
outSchema = outObj
elif isinstance(outItem, TableItem):
outSchema = outObj.schema()
# toIndex will point to the parent item of the new table
toIndex = parent
if isinstance(toIndex.internalPointer(), TableItem):
toIndex = toIndex.parent()
if inLayer.type() == inLayer.VectorLayer:
# create the output uri
schema = outSchema.name if outDb.schemas() is not None and outSchema is not None else ""
pkCol = geomCol = ""
# default pk and geom field name value
if providerKey in ['postgres', 'spatialite']:
inUri = QgsDataSourceUri(inLayer.source())
pkCol = inUri.keyColumn()
geomCol = inUri.geometryColumn()
outUri = outDb.uri()
outUri.setDataSource(schema, layerName, geomCol, "", pkCol)
self.importVector.emit(inLayer, outDb, outUri, toIndex)
return True
return False
def processAlgorithm(self, parameters, context, feedback):
inLayer = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER,
context)
ogrLayer = GdalUtils.ogrConnectionStringFromLayer(inLayer)
layername = GdalUtils.ogrLayerName(
inLayer.dataProvider().dataSourceUri())
fields = self.parameterAsFields(parameters, self.FIELDS, context)
uri = QgsDataSourceUri(inLayer.source())
geomColumn = uri.geometryColumn()
wkbType = inLayer.wkbType()
srid = inLayer.crs().postgisSrid()
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
if len(fields) > 0:
fieldstring = "," + ",".join(fields)
else:
fieldstring = ""
if wkbType == QgsWkbTypes.Polygon:
layertype = "POLYGON"
sqlstring = "-sql \"SELECT (ST_Dump(ST_MakeValid(g1." + geomColumn + "))).geom::geometry(" + layertype + "," + str(
srid
) + ") AS geom" + fieldstring + " FROM " + layername + " AS g1\" -nlt " + layertype + " -nln " + schema + "." + table + " -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
layertype = "MULTIPOLYGON"
sqlstring = "-sql \"SELECT (ST_MakeValid(g1." + geomColumn + "))::geometry(" + layertype + "," + str(
srid
) + ") AS geom" + fieldstring + " FROM " + layername + " AS g1\" -nlt " + layertype + " -nln " + schema + "." + table + " -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayer)
arguments.append(ogrLayer)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def collect_layers_datasources(layer_node):
for index in range(layer_node.rowCount()):
collect_layers_datasources(layer_node.child(index))
layer = layer_node.data(Qt.UserRole)
if layer and layer_node.checkState() == Qt.Checked:
layer_provider = layer.dataProvider()
if isinstance(layer_provider, QgsVectorDataProvider):
storage_type = layer_provider.storageType()
elif isinstance(layer_provider, QgsRasterDataProvider):
storage_type = 'Raster'
else:
storage_type = 'Other'
datasource_uri = QgsDataSourceUri(
layer_provider.dataSourceUri())
datasource_db = datasource_uri.database()
if datasource_db:
datasource_db = os.path.normpath(datasource_db)
if storage_type not in self._datasources:
self._datasources[storage_type] = dict(
) if datasource_db else set()
if datasource_db:
if datasource_db not in self._datasources[storage_type]:
self._datasources[storage_type][datasource_db] = []
if datasource_uri.schema():
table_name = '{0}.{1}'.format(datasource_uri.schema(),
datasource_uri.table())
else:
table_name = datasource_uri.table()
table_item = [
"{0} ({1})".format(table_name,
datasource_uri.geometryColumn())
]
self._datasources[storage_type][datasource_db].append(
table_item)
if datasource_uri.sql():
table_item.append(
["SQL: {}".format(datasource_uri.sql())])
else:
dsfile = layer_provider.dataSourceUri().split(
'|')[0].strip()
self._datasources[storage_type].add(
os.path.normpath(dsfile))
def getConnectionParameterFromDbLayer(layer: QgsMapLayer) -> Dict[str,str]:
'''
Get connection parameters
from the layer datasource
'''
connectionParams = None
if layer.providerType() == 'postgres':
dbType = 'postgis'
else:
dbType = 'spatialite'
src = layer.source()
try:
uri = QgsDataSourceUri(src)
except:
uri = QgsDataSourceURI(src)
# TODO Use immutable namedtuple
connectionParams = {
'service' : uri.service(),
'dbname' : uri.database(),
'host' : uri.host(),
'port': uri.port(),
'user' : uri.username(),
'password': uri.password(),
'sslmode' : uri.sslMode(),
'key': uri.keyColumn(),
'estimatedmetadata' : str(uri.useEstimatedMetadata()),
'checkPrimaryKeyUnicity' : '',
'srid' : uri.srid(),
'type': uri.wkbType(),
'schema': uri.schema(),
'table' : uri.table(),
'geocol' : uri.geometryColumn(),
'sql' : uri.sql(),
'dbType': dbType
}
return connectionParams
def getPostgisLayerInfo(layer):
info = dict()
if layer:
provider = layer.dataProvider()
if provider.name() == 'postgres':
uri = QgsDataSourceUri(provider.dataSourceUri())
info['service'] = uri.service()
info['database'] = uri.database()
info['schema'] = uri.schema()
info['table'] = uri.table()
info['key'] = uri.keyColumn()
info['geom'] = uri.geometryColumn()
info['geomtype'] = uri.wkbType()
info['srid'] = uri.srid()
info['filter'] = uri.sql()
info['service'] = uri.service()
connection_settings = getPostgisConnectionSettings()
for connection in connection_settings:
if connection['database'] == info['database'] or connection[
'service'] == info['service']:
info['connection'] = connection['name']
break
return info
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_A, context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(inLayerA.dataProvider().dataSourceUri())
inLayerB = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_B, context)
ogrLayerB = GdalUtils.ogrConnectionStringFromLayer(inLayerB)
layernameB = GdalUtils.ogrLayerName(inLayerB.dataProvider().dataSourceUri())
fieldsB = self.parameterAsFields(parameters, self.FIELDS_B, context)
uri = QgsDataSourceUri(inLayerA.source())
geomColumnA = uri.geometryColumn()
uri = QgsDataSourceUri(inLayerB.source())
geomColumnB = uri.geometryColumn()
geomTypeB = inLayerB.geometryType()
sridB = inLayerB.crs().postgisSrid()
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
single = self.parameterAsBool(parameters, self.SINGLE, context)
keep = self.parameterAsBool(parameters, self.KEEP, context)
bufferdist = self.parameterAsDouble(parameters, self.BUFFER, context)
if len(fieldsB) > 0:
fieldstringB = ', '.join(["g2.{}".format(f) for f in fieldsB])
fieldstringB = ", " + fieldstringB
else:
fieldstringB = ""
if geomTypeB == QgsWkbTypes.PointGeometry:
type = "POINT"
elif geomTypeB == QgsWkbTypes.LineGeometry:
type = "LINESTRING"
else:
type = "POLYGON"
if single:
multistring = "-nlt " + type
caststring = type
st_function = "ST_Dump"
castgeom = ".geom"
else:
multistring = "-nlt MULTI" + type
caststring = "MULTI" + type
st_function = "ST_Multi"
castgeom = ""
if geomTypeB == QgsWkbTypes.PointGeometry:
if bufferdist == 0.0:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
if keep:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Intersects(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true OR (ST_Touches(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Intersects(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true AND (ST_Touches(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is false\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
elif geomTypeB == QgsWkbTypes.LineGeometry:
if bufferdist == 0.0:
if keep:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true AND ST_Touches(g1." + geomColumnA + ",g2." + geomColumnB + ") is false\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
if keep:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Intersects(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true OR (ST_Touches(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Intersects(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true AND (ST_Touches(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is false\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
if bufferdist == 0.0:
if keep:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true AND ST_Touches(g2." + geomColumnB + ",g1." + geomColumnA + ") is false\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
if keep:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Intersects(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true OR ST_Contains(g2." + geomColumnB+ ",ST_Buffer(g1." + geomColumnA + "," + bufferdist + ")) is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
sqlstring = "-sql \"SELECT (" + st_function + "(g2." + geomColumnB + "))" + castgeom + "::geometry(" + caststring + "," + str(sridB) + ") AS geom" + fieldstringB + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE (ST_Contains(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ")) is true OR ST_Overlaps(ST_Buffer(g1." + geomColumnA + "," + bufferdist + "),g2." + geomColumnB + ") is true OR ST_Contains(g2." + geomColumnB+ ",ST_Buffer(g1." + geomColumnA + "," + bufferdist + ")) is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER,
context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(
inLayerA.dataProvider().dataSourceUri())
uri = QgsDataSourceUri(inLayerA.source())
geomColumn = uri.geometryColumn()
srid = inLayerA.crs().postgisSrid()
field = self.parameterAsString(parameters, self.FIELD, context)
statsatt = self.parameterAsString(parameters, self.STATSATT, context)
stats = self.parameterAsBool(parameters, self.STATS, context)
area = self.parameterAsBool(parameters, self.AREA, context)
single = self.parameterAsBool(parameters, self.SINGLE, context)
count = self.parameterAsBool(parameters, self.COUNT, context)
dissolveall = self.parameterAsBool(parameters, self.DISSOLVEALL,
context)
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
if single:
layertype = "POLYGON"
else:
layertype = "MULTIPOLYGON"
if dissolveall:
fieldstring = ""
else:
fieldstring = "," + field
if single:
layertype = "POLYGON"
else:
layertype = "MULTIPOLYGON"
if dissolveall:
fieldstring = ""
else:
fieldstring = "," + field
if single:
querystart = '-sql "SELECT (ST_Dump(ST_Union(' + geomColumn + '))).geom::geometry(POLYGON,' + str(
srid) + ')' + fieldstring
else:
querystart = '-sql "SELECT (ST_Multi(ST_Union(' + geomColumn + ')))::geometry(MULTIPOLYGON,' + str(
srid) + ')' + fieldstring
if dissolveall:
queryend = ' FROM ' + layernameA + '"' + " -nln " + schema + "." + table + " -nlt " + layertype + " -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
else:
queryend = ' FROM ' + layernameA + ' GROUP BY ' + field + '"' + " -nln " + schema + "." + table + " -nlt " + layertype + " -lco FID=gid -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
if count:
querycount = ", COUNT(" + geomColumn + ") AS count"
else:
querycount = ""
if stats:
querystats = ", SUM(" + statsatt + ") AS sum_dissolved, MIN(" + statsatt + ") AS min_dissolved, MAX(" + statsatt + ") AS max_dissolved, AVG(" + statsatt + ") AS avg_dissolved"
else:
querystats = ""
if area:
queryarea = ", SUM(ST_area(" + geomColumn + ")) AS area_dissolved, ST_perimeter(ST_union(" + geomColumn + ")) AS perimeter_dissolved"
else:
queryarea = ""
query = querystart + querystats + queryarea + querycount + queryend
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(query)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def accept(self):
if self.mode == self.ASK_FOR_INPUT_MODE:
# create the input layer (if not already done) and
# update available options
self.reloadInputLayer()
# sanity checks
if self.inLayer is None:
QMessageBox.information(self, self.tr("Import to database"), self.tr("Input layer missing or not valid"))
return
if self.cboTable.currentText() == "":
QMessageBox.information(self, self.tr("Import to database"), self.tr("Output table name is required"))
return
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
try:
sourceSrid = self.editSourceSrid.text()
except ValueError:
QMessageBox.information(self, self.tr("Import to database"),
self.tr("Invalid source srid: must be an integer"))
return
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
try:
targetSrid = self.editTargetSrid.text()
except ValueError:
QMessageBox.information(self, self.tr("Import to database"),
self.tr("Invalid target srid: must be an integer"))
return
# override cursor
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
# store current input layer crs and encoding, so I can restore it
prevInCrs = self.inLayer.crs()
prevInEncoding = self.inLayer.dataProvider().encoding()
try:
schema = self.outUri.schema() if not self.cboSchema.isEnabled() else self.cboSchema.currentText()
table = self.cboTable.currentText()
# get pk and geom field names from the source layer or use the
# ones defined by the user
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if not self.chkPrimaryKey.isChecked() else self.editPrimaryKey.text()
if not pk:
pk = self.default_pk
if self.inLayer.hasGeometryType() and self.chkGeomColumn.isEnabled():
geom = srcUri.geometryColumn() if not self.chkGeomColumn.isChecked() else self.editGeomColumn.text()
if not geom:
geom = self.default_geom
else:
geom = None
options = {}
if self.chkLowercaseFieldNames.isEnabled() and self.chkLowercaseFieldNames.isChecked():
pk = pk.lower()
if geom:
geom = geom.lower()
options['lowercaseFieldNames'] = True
# get output params, update output URI
self.outUri.setDataSource(schema, table, geom, "", pk)
uri = self.outUri.uri(False)
providerName = self.db.dbplugin().providerName()
if self.chkDropTable.isChecked():
options['overwrite'] = True
if self.chkSinglePart.isEnabled() and self.chkSinglePart.isChecked():
options['forceSinglePartGeometryType'] = True
outCrs = QgsCoordinateReferenceSystem()
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
targetSrid = int(self.editTargetSrid.text())
outCrs = QgsCoordinateReferenceSystem(targetSrid)
# update input layer crs and encoding
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
sourceSrid = int(self.editSourceSrid.text())
inCrs = QgsCoordinateReferenceSystem(sourceSrid)
self.inLayer.setCrs(inCrs)
if self.chkEncoding.isEnabled() and self.chkEncoding.isChecked():
enc = self.cboEncoding.currentText()
self.inLayer.setProviderEncoding(enc)
onlySelected = self.chkSelectedFeatures.isChecked()
# do the import!
ret, errMsg = QgsVectorLayerImport.importLayer(self.inLayer, uri, providerName, outCrs, onlySelected, False, options)
except Exception as e:
ret = -1
errMsg = str(e)
finally:
# restore input layer crs and encoding
self.inLayer.setCrs(prevInCrs)
self.inLayer.setProviderEncoding(prevInEncoding)
# restore cursor
QApplication.restoreOverrideCursor()
if ret != 0:
output = QgsMessageViewer()
output.setTitle(self.tr("Import to database"))
output.setMessageAsPlainText(self.tr("Error %d\n%s") % (ret, errMsg))
output.showMessage()
return
# create spatial index
if self.chkSpatialIndex.isEnabled() and self.chkSpatialIndex.isChecked():
self.db.connector.createSpatialIndex((schema, table), geom)
QMessageBox.information(self, self.tr("Import to database"), self.tr("Import was successful."))
return QDialog.accept(self)
def __init__(self, iface, layer, parent=None):
QWidget.__init__(self, parent)
self.iface = iface
self.layer = layer
uri = QgsDataSourceUri(layer.source())
dbplugin = None
db = None
if layer.dataProvider().name() == 'postgres':
dbplugin = createDbPlugin('postgis', 'postgres')
elif layer.dataProvider().name() == 'spatialite':
dbplugin = createDbPlugin('spatialite', 'spatialite')
elif layer.dataProvider().name() == 'oracle':
dbplugin = createDbPlugin('oracle', 'oracle')
elif layer.dataProvider().name() == 'virtual':
dbplugin = createDbPlugin('vlayers', 'virtual')
elif layer.dataProvider().name() == 'ogr':
dbplugin = createDbPlugin('gpkg', 'gpkg')
if dbplugin:
dbplugin.connectToUri(uri)
db = dbplugin.db
self.dbplugin = dbplugin
self.db = db
self.filter = ""
self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, spatialite doesn't
self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases
self.setupUi(self)
self.setWindowTitle(
u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString()))
self.defaultLayerName = 'QueryLayer'
if self.allowMultiColumnPk:
self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values"))
else:
self.uniqueColumnCheck.setText(self.tr("Column with unique values"))
self.editSql.setFocus()
self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded)
self.editSql.setMarginVisible(True)
self.initCompleter()
# allow copying results
copyAction = QAction("copy", self)
self.viewResult.addAction(copyAction)
copyAction.setShortcuts(QKeySequence.Copy)
copyAction.triggered.connect(self.copySelectedResults)
self.btnExecute.clicked.connect(self.executeSql)
self.btnSetFilter.clicked.connect(self.setFilter)
self.btnClear.clicked.connect(self.clearSql)
self.presetStore.clicked.connect(self.storePreset)
self.presetDelete.clicked.connect(self.deletePreset)
self.presetCombo.activated[str].connect(self.loadPreset)
self.presetCombo.activated[str].connect(self.presetName.setText)
self.updatePresetsCombobox()
self.geomCombo.setEditable(True)
self.geomCombo.lineEdit().setReadOnly(True)
self.uniqueCombo.setEditable(True)
self.uniqueCombo.lineEdit().setReadOnly(True)
self.uniqueModel = QStandardItemModel(self.uniqueCombo)
self.uniqueCombo.setModel(self.uniqueModel)
if self.allowMultiColumnPk:
self.uniqueCombo.setItemDelegate(QStyledItemDelegate())
self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item
self.uniqueCombo.lineEdit().textChanged.connect(self.uniqueTextChanged) # there are other events that change the displayed text and some of them can not be caught directly
self.layerTypeWidget.hide() # show if load as raster is supported
#self.loadLayerBtn.clicked.connect(self.loadSqlLayer)
self.updateLayerBtn.clicked.connect(self.updateSqlLayer)
self.getColumnsBtn.clicked.connect(self.fillColumnCombos)
self.queryBuilderFirst = True
self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif"))
self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder)
self.presetName.textChanged.connect(self.nameChanged)
# Update from layer
# Fisrtly the SQL from QgsDataSourceUri table
sql = uri.table()
if uri.keyColumn() == '_uid_':
match = re.search('^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S)
if match:
sql = match.group(1)
else:
match = re.search('^\((SELECT .+ FROM .+)\)$', sql, re.S)
if match:
sql = match.group(1)
self.editSql.setText(sql)
self.executeSql()
# Then the columns
self.geomCombo.setCurrentIndex(self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly))
if uri.keyColumn() != '_uid_':
self.uniqueColumnCheck.setCheckState(Qt.Checked)
if self.allowMultiColumnPk:
itemsData = uri.keyColumn().split(',')
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
if item.data() in itemsData:
item.setCheckState(Qt.Checked)
else:
keyColumn = uri.keyColumn()
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
if item.data() == keyColumn:
self.uniqueCombo.setCurrentIndex(self.uniqueModel.indexFromItem(item).row())
# Finally layer name, filter and selectAtId
self.layerNameEdit.setText(layer.name())
self.filter = uri.sql()
if uri.selectAtIdDisabled():
self.avoidSelectById.setCheckState(Qt.Checked)
def accept(self):
if self.mode == self.ASK_FOR_INPUT_MODE:
# create the input layer (if not already done) and
# update available options
self.reloadInputLayer()
# sanity checks
if self.inLayer is None:
QMessageBox.critical(self, self.tr("Import to Database"), self.tr("Input layer missing or not valid."))
return
if self.cboTable.currentText() == "":
QMessageBox.critical(self, self.tr("Import to Database"), self.tr("Output table name is required."))
return
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
if not self.widgetSourceSrid.crs().isValid():
QMessageBox.critical(self, self.tr("Import to Database"),
self.tr("Invalid source srid: must be a valid crs."))
return
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
if not self.widgetTargetSrid.crs().isValid():
QMessageBox.critical(self, self.tr("Import to Database"),
self.tr("Invalid target srid: must be a valid crs."))
return
with OverrideCursor(Qt.WaitCursor):
# store current input layer crs and encoding, so I can restore it
prevInCrs = self.inLayer.crs()
prevInEncoding = self.inLayer.dataProvider().encoding()
try:
schema = self.outUri.schema() if not self.cboSchema.isEnabled() else self.cboSchema.currentText()
table = self.cboTable.currentText()
# get pk and geom field names from the source layer or use the
# ones defined by the user
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if not self.chkPrimaryKey.isChecked() else self.editPrimaryKey.text()
if not pk:
pk = self.default_pk
if self.inLayer.isSpatial() and self.chkGeomColumn.isEnabled():
geom = srcUri.geometryColumn() if not self.chkGeomColumn.isChecked() else self.editGeomColumn.text()
if not geom:
geom = self.default_geom
else:
geom = None
options = {}
if self.chkLowercaseFieldNames.isEnabled() and self.chkLowercaseFieldNames.isChecked():
pk = pk.lower()
if geom:
geom = geom.lower()
options['lowercaseFieldNames'] = True
# get output params, update output URI
self.outUri.setDataSource(schema, table, geom, "", pk)
typeName = self.db.dbplugin().typeName()
providerName = self.db.dbplugin().providerName()
if typeName == 'gpkg':
uri = self.outUri.database()
options['update'] = True
options['driverName'] = 'GPKG'
options['layerName'] = table
else:
uri = self.outUri.uri(False)
if self.chkDropTable.isChecked():
options['overwrite'] = True
if self.chkSinglePart.isEnabled() and self.chkSinglePart.isChecked():
options['forceSinglePartGeometryType'] = True
outCrs = QgsCoordinateReferenceSystem()
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
outCrs = self.widgetTargetSrid.crs()
# update input layer crs and encoding
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
inCrs = self.widgetSourceSrid.crs()
self.inLayer.setCrs(inCrs)
if self.chkEncoding.isEnabled() and self.chkEncoding.isChecked():
enc = self.cboEncoding.currentText()
self.inLayer.setProviderEncoding(enc)
onlySelected = self.chkSelectedFeatures.isChecked()
# do the import!
ret, errMsg = QgsVectorLayerExporter.exportLayer(self.inLayer, uri, providerName, outCrs, onlySelected, options)
except Exception as e:
ret = -1
errMsg = str(e)
finally:
# restore input layer crs and encoding
self.inLayer.setCrs(prevInCrs)
self.inLayer.setProviderEncoding(prevInEncoding)
if ret != 0:
output = QgsMessageViewer()
output.setTitle(self.tr("Import to Database"))
output.setMessageAsPlainText(self.tr("Error {0}\n{1}").format(ret, errMsg))
output.showMessage()
return
# create spatial index
if self.chkSpatialIndex.isEnabled() and self.chkSpatialIndex.isChecked():
self.db.connector.createSpatialIndex((schema, table), geom)
# add comment on table
supportCom = self.db.supportsComment()
if self.chkCom.isEnabled() and self.chkCom.isChecked() and supportCom:
# using connector executing COMMENT ON TABLE query (with editCome.text() value)
com = self.editCom.text()
self.db.connector.commentTable(schema, table, com)
self.db.connection().reconnect()
self.db.refresh()
QMessageBox.information(self, self.tr("Import to Database"), self.tr("Import was successful."))
return QDialog.accept(self)
def processAlgorithm(self, parameters, context, feedback):
inLayerA = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_A,
context)
ogrLayerA = GdalUtils.ogrConnectionStringFromLayer(inLayerA)
layernameA = GdalUtils.ogrLayerName(
inLayerA.dataProvider().dataSourceUri())
inLayerB = self.parameterAsVectorLayer(parameters, self.INPUT_LAYER_B,
context)
ogrLayerB = GdalUtils.ogrConnectionStringFromLayer(inLayerB)
layernameB = GdalUtils.ogrLayerName(
inLayerB.dataProvider().dataSourceUri())
#fieldsA = self.parameterAsFields(parameters, self.FIELD_A, context)
fieldsA = self.parameterAsFields(parameters, self.FIELDS_A, context)
fieldA = self.parameterAsString(parameters, self.FIELD_A, context)
fieldB = self.parameterAsString(parameters, self.FIELD_B, context)
uri = QgsDataSourceUri(inLayerA.source())
geomColumnA = uri.geometryColumn()
uri = QgsDataSourceUri(inLayerB.source())
geomColumnB = uri.geometryColumn()
sridA = inLayerA.crs().postgisSrid()
schema = self.parameterAsString(parameters, self.SCHEMA, context)
table = self.parameterAsString(parameters, self.TABLE, context)
options = self.parameterAsString(parameters, self.OPTIONS, context)
single = self.parameterAsBool(parameters, self.SINGLE, context)
if len(fieldsA) > 0:
fieldstringA = ', '.join(["g1.{}".format(f) for f in fieldsA])
fieldstringA = ", " + fieldstringA
else:
fieldstringA = ""
type = "POINT"
if single:
multistring = "-nlt " + type
caststring = type
st_function = "ST_Dump"
castgeom = ".geom"
else:
multistring = "-nlt MULTI" + type
caststring = "MULTI" + type
st_function = "ST_Multi"
castgeom = ""
sqlstring = "-sql \"SELECT (" + st_function + "(g1." + geomColumnA + "))" + castgeom + "::geometry(" + caststring + "," + str(
sridA
) + ") AS geom, g2." + fieldB + " AS " + fieldA + fieldstringA + " FROM " + layernameA + " AS g1, " + layernameB + " AS g2 WHERE ST_Intersects(g2." + geomColumnB + ",g1." + geomColumnA + ") is true\" -nln " + schema + "." + table + " -lco FID=gid " + multistring + " -lco GEOMETRY_NAME=geom --config PG_USE_COPY YES"
arguments = []
arguments.append('-f')
arguments.append('PostgreSQL')
arguments.append(ogrLayerA)
arguments.append(ogrLayerA)
arguments.append(sqlstring)
arguments.append('-overwrite')
if len(options) > 0:
arguments.append(options)
commands = ['ogr2ogr', GdalUtils.escapeAndJoin(arguments)]
GdalUtils.runGdal(commands, feedback)
return {}
def accept(self):
if self.mode == self.ASK_FOR_INPUT_MODE:
# create the input layer (if not already done) and
# update available options
self.reloadInputLayer()
# sanity checks
if self.inLayer is None:
QMessageBox.information(
self, self.tr("Import to database"),
self.tr("Input layer missing or not valid"))
return
if self.cboTable.currentText() == "":
QMessageBox.information(self, self.tr("Import to database"),
self.tr("Output table name is required"))
return
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
try:
sourceSrid = self.editSourceSrid.text()
except ValueError:
QMessageBox.information(
self, self.tr("Import to database"),
self.tr("Invalid source srid: must be an integer"))
return
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
try:
targetSrid = self.editTargetSrid.text()
except ValueError:
QMessageBox.information(
self, self.tr("Import to database"),
self.tr("Invalid target srid: must be an integer"))
return
# override cursor
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
# store current input layer crs and encoding, so I can restore it
prevInCrs = self.inLayer.crs()
prevInEncoding = self.inLayer.dataProvider().encoding()
try:
schema = self.outUri.schema() if not self.cboSchema.isEnabled(
) else self.cboSchema.currentText()
table = self.cboTable.currentText()
# get pk and geom field names from the source layer or use the
# ones defined by the user
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if not self.chkPrimaryKey.isChecked(
) else self.editPrimaryKey.text()
if not pk:
pk = self.default_pk
if self.inLayer.hasGeometryType() and self.chkGeomColumn.isEnabled(
):
geom = srcUri.geometryColumn(
) if not self.chkGeomColumn.isChecked(
) else self.editGeomColumn.text()
if not geom:
geom = self.default_geom
else:
geom = None
options = {}
if self.chkLowercaseFieldNames.isEnabled(
) and self.chkLowercaseFieldNames.isChecked():
pk = pk.lower()
if geom:
geom = geom.lower()
options['lowercaseFieldNames'] = True
# get output params, update output URI
self.outUri.setDataSource(schema, table, geom, "", pk)
uri = self.outUri.uri(False)
providerName = self.db.dbplugin().providerName()
if self.chkDropTable.isChecked():
options['overwrite'] = True
if self.chkSinglePart.isEnabled() and self.chkSinglePart.isChecked(
):
options['forceSinglePartGeometryType'] = True
outCrs = QgsCoordinateReferenceSystem()
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked(
):
targetSrid = int(self.editTargetSrid.text())
outCrs = QgsCoordinateReferenceSystem(targetSrid)
# update input layer crs and encoding
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked(
):
sourceSrid = int(self.editSourceSrid.text())
inCrs = QgsCoordinateReferenceSystem(sourceSrid)
self.inLayer.setCrs(inCrs)
if self.chkEncoding.isEnabled() and self.chkEncoding.isChecked():
enc = self.cboEncoding.currentText()
self.inLayer.setProviderEncoding(enc)
onlySelected = self.chkSelectedFeatures.isChecked()
# do the import!
ret, errMsg = QgsVectorLayerImport.importLayer(
self.inLayer, uri, providerName, outCrs, onlySelected, False,
options)
except Exception as e:
ret = -1
errMsg = str(e)
finally:
# restore input layer crs and encoding
self.inLayer.setCrs(prevInCrs)
self.inLayer.setProviderEncoding(prevInEncoding)
# restore cursor
QApplication.restoreOverrideCursor()
if ret != 0:
output = QgsMessageViewer()
output.setTitle(self.tr("Import to database"))
output.setMessageAsPlainText(
self.tr("Error %d\n%s") % (ret, errMsg))
output.showMessage()
return
# create spatial index
if self.chkSpatialIndex.isEnabled() and self.chkSpatialIndex.isChecked(
):
self.db.connector.createSpatialIndex((schema, table), geom)
QMessageBox.information(self, self.tr("Import to database"),
self.tr("Import was successful."))
return QDialog.accept(self)
def accept(self):
if self.mode == self.ASK_FOR_INPUT_MODE:
# create the input layer (if not already done) and
# update available options
self.reloadInputLayer()
# sanity checks
if self.inLayer is None:
QMessageBox.critical(self, self.tr("Import to Database"),
self.tr("Input layer missing or not valid."))
return
if self.cboTable.currentText() == "":
QMessageBox.critical(self, self.tr("Import to Database"),
self.tr("Output table name is required."))
return
if self.chkSourceSrid.isEnabled() and self.chkSourceSrid.isChecked():
if not self.widgetSourceSrid.crs().isValid():
QMessageBox.critical(
self, self.tr("Import to Database"),
self.tr("Invalid source srid: must be a valid crs."))
return
if self.chkTargetSrid.isEnabled() and self.chkTargetSrid.isChecked():
if not self.widgetTargetSrid.crs().isValid():
QMessageBox.critical(
self, self.tr("Import to Database"),
self.tr("Invalid target srid: must be a valid crs."))
return
with OverrideCursor(Qt.WaitCursor):
# store current input layer crs and encoding, so I can restore it
prevInCrs = self.inLayer.crs()
prevInEncoding = self.inLayer.dataProvider().encoding()
try:
schema = self.outUri.schema() if not self.cboSchema.isEnabled(
) else self.cboSchema.currentText()
table = self.cboTable.currentText()
# get pk and geom field names from the source layer or use the
# ones defined by the user
srcUri = QgsDataSourceUri(self.inLayer.source())
pk = srcUri.keyColumn() if not self.chkPrimaryKey.isChecked(
) else self.editPrimaryKey.text()
if not pk:
pk = self.default_pk
if self.inLayer.isSpatial() and self.chkGeomColumn.isEnabled():
geom = srcUri.geometryColumn(
) if not self.chkGeomColumn.isChecked(
) else self.editGeomColumn.text()
if not geom:
geom = self.default_geom
else:
geom = None
options = {}
if self.chkLowercaseFieldNames.isEnabled(
) and self.chkLowercaseFieldNames.isChecked():
pk = pk.lower()
if geom:
geom = geom.lower()
options['lowercaseFieldNames'] = True
# get output params, update output URI
self.outUri.setDataSource(schema, table, geom, "", pk)
typeName = self.db.dbplugin().typeName()
providerName = self.db.dbplugin().providerName()
if typeName == 'gpkg':
uri = self.outUri.database()
options['update'] = True
options['driverName'] = 'GPKG'
options['layerName'] = table
else:
uri = self.outUri.uri(False)
if self.chkDropTable.isChecked():
options['overwrite'] = True
if self.chkSinglePart.isEnabled(
) and self.chkSinglePart.isChecked():
options['forceSinglePartGeometryType'] = True
outCrs = QgsCoordinateReferenceSystem()
if self.chkTargetSrid.isEnabled(
) and self.chkTargetSrid.isChecked():
outCrs = self.widgetTargetSrid.crs()
# update input layer crs and encoding
if self.chkSourceSrid.isEnabled(
) and self.chkSourceSrid.isChecked():
inCrs = self.widgetSourceSrid.crs()
self.inLayer.setCrs(inCrs)
if self.chkEncoding.isEnabled() and self.chkEncoding.isChecked(
):
enc = self.cboEncoding.currentText()
self.inLayer.setProviderEncoding(enc)
onlySelected = self.chkSelectedFeatures.isChecked()
# do the import!
ret, errMsg = QgsVectorLayerExporter.exportLayer(
self.inLayer, uri, providerName, outCrs, onlySelected,
options)
except Exception as e:
ret = -1
errMsg = str(e)
finally:
# restore input layer crs and encoding
self.inLayer.setCrs(prevInCrs)
self.inLayer.setProviderEncoding(prevInEncoding)
if ret != 0:
output = QgsMessageViewer()
output.setTitle(self.tr("Import to Database"))
output.setMessageAsPlainText(
self.tr("Error {0}\n{1}").format(ret, errMsg))
output.showMessage()
return
# create spatial index
if self.chkSpatialIndex.isEnabled() and self.chkSpatialIndex.isChecked(
):
self.db.connector.createSpatialIndex((schema, table), geom)
# add comment on table
supportCom = self.db.supportsComment()
if self.chkCom.isEnabled() and self.chkCom.isChecked() and supportCom:
# using connector executing COMMENT ON TABLE query (with editCome.text() value)
com = self.editCom.text()
self.db.connector.commentTable(schema, table, com)
self.db.connection().reconnect()
self.db.refresh()
QMessageBox.information(self, self.tr("Import to Database"),
self.tr("Import was successful."))
return QDialog.accept(self)
def getDBLayerGeometryColumn(layer):
uri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
return uri.geometryColumn()
def __init__(self, iface, layer, parent=None):
QWidget.__init__(self, parent)
self.iface = iface
self.layer = layer
uri = QgsDataSourceUri(layer.source())
dbplugin = None
db = None
if layer.dataProvider().name() == 'postgres':
dbplugin = createDbPlugin('postgis', 'postgres')
elif layer.dataProvider().name() == 'spatialite':
dbplugin = createDbPlugin('spatialite', 'spatialite')
elif layer.dataProvider().name() == 'oracle':
dbplugin = createDbPlugin('oracle', 'oracle')
elif layer.dataProvider().name() == 'virtual':
dbplugin = createDbPlugin('vlayers', 'virtual')
elif layer.dataProvider().name() == 'ogr':
dbplugin = createDbPlugin('gpkg', 'gpkg')
if dbplugin:
dbplugin.connectToUri(uri)
db = dbplugin.db
self.dbplugin = dbplugin
self.db = db
self.filter = ""
self.allowMultiColumnPk = isinstance(
db, PGDatabase
) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't
self.aliasSubQuery = isinstance(
db,
PGDatabase) # only PostgreSQL requires subqueries to be aliases
self.setupUi(self)
self.setWindowTitle(
u"%s - %s [%s]" %
(self.windowTitle(), db.connection().connectionName(),
db.connection().typeNameString()))
self.defaultLayerName = 'QueryLayer'
if self.allowMultiColumnPk:
self.uniqueColumnCheck.setText(
self.tr("Column(s) with unique values"))
else:
self.uniqueColumnCheck.setText(
self.tr("Column with unique values"))
self.editSql.setFocus()
self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded)
self.editSql.setMarginVisible(True)
self.initCompleter()
# allow copying results
copyAction = QAction("copy", self)
self.viewResult.addAction(copyAction)
copyAction.setShortcuts(QKeySequence.Copy)
copyAction.triggered.connect(self.copySelectedResults)
self.btnExecute.clicked.connect(self.executeSql)
self.btnSetFilter.clicked.connect(self.setFilter)
self.btnClear.clicked.connect(self.clearSql)
self.presetStore.clicked.connect(self.storePreset)
self.presetDelete.clicked.connect(self.deletePreset)
self.presetCombo.activated[str].connect(self.loadPreset)
self.presetCombo.activated[str].connect(self.presetName.setText)
self.editSql.textChanged.connect(self.updatePresetButtonsState)
self.presetName.textChanged.connect(self.updatePresetButtonsState)
self.presetCombo.currentIndexChanged.connect(
self.updatePresetButtonsState)
self.updatePresetsCombobox()
self.geomCombo.setEditable(True)
self.geomCombo.lineEdit().setReadOnly(True)
self.uniqueCombo.setEditable(True)
self.uniqueCombo.lineEdit().setReadOnly(True)
self.uniqueModel = QStandardItemModel(self.uniqueCombo)
self.uniqueCombo.setModel(self.uniqueModel)
if self.allowMultiColumnPk:
self.uniqueCombo.setItemDelegate(QStyledItemDelegate())
self.uniqueModel.itemChanged.connect(
self.uniqueChanged) # react to the (un)checking of an item
self.uniqueCombo.lineEdit().textChanged.connect(
self.uniqueTextChanged
) # there are other events that change the displayed text and some of them can not be caught directly
self.layerTypeWidget.hide() # show if load as raster is supported
# self.loadLayerBtn.clicked.connect(self.loadSqlLayer)
self.updateLayerBtn.clicked.connect(self.updateSqlLayer)
self.getColumnsBtn.clicked.connect(self.fillColumnCombos)
self.queryBuilderFirst = True
self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif"))
self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder)
self.presetName.textChanged.connect(self.nameChanged)
# Update from layer
# First the SQL from QgsDataSourceUri table
sql = uri.table()
if uri.keyColumn() == '_uid_':
match = re.search(
r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$',
sql, re.S | re.X)
if match:
sql = match.group(1)
else:
match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X)
if match:
sql = match.group(1)
# Need to check on table() since the parentheses were removed by the regexp
if not uri.table().startswith('(') and not uri.table().endswith(')'):
schema = uri.schema()
if schema and schema.upper() != 'PUBLIC':
sql = 'SELECT * FROM {0}.{1}'.format(
self.db.connector.quoteId(schema),
self.db.connector.quoteId(sql))
else:
sql = 'SELECT * FROM {0}'.format(
self.db.connector.quoteId(sql))
self.editSql.setText(sql)
self.executeSql()
# Then the columns
self.geomCombo.setCurrentIndex(
self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly))
if uri.keyColumn() != '_uid_':
self.uniqueColumnCheck.setCheckState(Qt.Checked)
if self.allowMultiColumnPk:
itemsData = uri.keyColumn().split(',')
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
if item.data() in itemsData:
item.setCheckState(Qt.Checked)
else:
keyColumn = uri.keyColumn()
for item in self.uniqueModel.findItems("*", Qt.MatchWildcard):
if item.data() == keyColumn:
self.uniqueCombo.setCurrentIndex(
self.uniqueModel.indexFromItem(item).row())
# Finally layer name, filter and selectAtId
self.layerNameEdit.setText(layer.name())
self.filter = uri.sql()
if uri.selectAtIdDisabled():
self.avoidSelectById.setCheckState(Qt.Checked)
def doDropOrphanedTopoGeoms(self):
toolname = "CollectTopogeoms"
# check that a layer is selected
layer = self.iface.mapCanvas().currentLayer()
if not layer:
QMessageBox.information(None, toolname, "A TopoGeom layer must be selected")
return
# check that the selected layer is a postgis one
if layer.providerType() != 'postgres':
QMessageBox.information(None, toolname, "A PostGIS layer must be selected")
return
uri = QgsDataSourceUri( layer.source() )
# get the layer schema
schema = str(uri.schema())
if not schema:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no schema set in datasource " + str(uri.uri()) + ")")
return;
# get the layer table
table = str(uri.table())
if not table:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no table set in datasource)")
return;
# get the layer column
col = str(uri.geometryColumn())
if not col:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no column set in datasource)")
return;
layername = '"' + schema + '"."' + table + '"."' + col +'"'
try:
conn = psycopg2.connect( str(uri.connectionInfo()) )
cur = conn.cursor()
# get the layer topology
cur.execute("SELECT t.name, l.layer_id FROM topology.topology t, topology.layer l WHERE l.schema_name = %s AND l.table_name = %s AND l.feature_column = %s AND t.id = l.topology_id", (schema, table, col))
if cur.rowcount == 1:
(toponame, layer_id) = cur.fetchone()
# delete orphaned geoms...
cur.execute('DELETE FROM "' + toponame +
'"."relation" WHERE layer_id = %s AND topogeo_id NOT IN ( SELECT id("'
+ col + '") FROM "' + schema + '"."' + table + '")', (str(layer_id),))
QMessageBox.information(None, toolname, str(cur.rowcount)
+ ' orphaned topogeometry objects removed from layer ' + layername)
conn.commit()
else:
QMessageBox.information(None, toolname, layername + ' is not a topology layer (not registered in topology.layer table)')
cur.close()
conn.close()
except psycopg2.Error as e:
QMessageBox.information(None, toolname, "ERROR: " + str(e))
return
#QMessageBox.information(None, toolname, "Not implemeted yet, but we know the layer is " + schema + "." + table + "." + col + ", the topology name is " + toponame + " and layer_id is " + str(layer_id))
self.iface.mapCanvas().refresh()
