def _aliased_sql_helper(self, dbname):
queries = (
'(SELECT * FROM (SELECT * from \\"some view\\"))',
'(SELECT * FROM \\"some view\\")',
'(select sd.* from somedata as sd left join somedata as sd2 on ( sd2.name = sd.name ))',
'(select sd.* from \\"somedata\\" as sd left join \\"somedata\\" as sd2 on ( sd2.name = sd.name ))',
"(SELECT * FROM somedata as my_alias1\n)",
"(SELECT * FROM somedata as my_alias2)",
"(SELECT * FROM somedata AS my_alias3)",
'(SELECT * FROM \\"somedata\\" as my_alias4\n)',
'(SELECT * FROM (SELECT * FROM \\"somedata\\"))',
'(SELECT my_alias5.* FROM (SELECT * FROM \\"somedata\\") AS my_alias5)',
'(SELECT my_alias6.* FROM (SELECT * FROM \\"somedata\\" as my_alias\n) AS my_alias6)',
'(SELECT my_alias7.* FROM (SELECT * FROM \\"somedata\\" as my_alias\n) AS my_alias7\n)',
'(SELECT my_alias8.* FROM (SELECT * FROM \\"some data\\") AS my_alias8)',
'(SELECT my_alias9.* FROM (SELECT * FROM \\"some data\\" as my_alias\n) AS my_alias9)',
'(SELECT my_alias10.* FROM (SELECT * FROM \\"some data\\" as my_alias\n) AS my_alias10\n)',
'(select sd.* from \\"some data\\" as sd left join \\"some data\\" as sd2 on ( sd2.name = sd.name ))',
'(SELECT * FROM \\"some data\\" as my_alias11\n)',
'(SELECT * FROM \\"some data\\" as my_alias12)',
'(SELECT * FROM \\"some data\\" AS my_alias13)',
'(SELECT * from \\"some data\\" AS my_alias14\n)',
'(SELECT * FROM (SELECT * from \\"some data\\"))',
)
for sql in queries:
vl = QgsVectorLayer('dbname=\'{}\' table="{}" (geom) sql='.format(dbname, sql), 'test', 'spatialite')
self.assertTrue(vl.isValid(), 'dbname: {} - sql: {}'.format(dbname, sql))
self.assertTrue(vl.featureCount() > 1)
self.assertTrue(vl.isSpatial())
def test_SplitFeature(self):
"""Test sqlite feature can be split"""
tmpfile = os.path.join(self.basetestpath, 'testGeopackageSplitFeatures.sqlite')
ds = ogr.GetDriverByName('SQlite').CreateDataSource(tmpfile)
lyr = ds.CreateLayer('test', geom_type=ogr.wkbPolygon)
lyr.CreateField(ogr.FieldDefn('str_field', ogr.OFTString))
f = ogr.Feature(lyr.GetLayerDefn())
f.SetGeometry(ogr.CreateGeometryFromWkt('POLYGON ((0 0,0 1,1 1,1 0,0 0))'))
lyr.CreateFeature(f)
f = None
ds = None
layer = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test", 'test', u'ogr')
# Check that pk field has unique constraint
fields = layer.fields()
pkfield = fields.at(0)
self.assertTrue(pkfield.constraints().constraints() & QgsFieldConstraints.ConstraintUnique)
self.assertTrue(layer.isValid())
self.assertTrue(layer.isSpatial())
self.assertEqual([f for f in layer.getFeatures()][0].geometry().asWkt(), 'Polygon ((0 0, 0 1, 1 1, 1 0, 0 0))')
layer.startEditing()
self.assertEqual(layer.splitFeatures([QgsPointXY(0.5, 0), QgsPointXY(0.5, 1)], 0), 0)
self.assertTrue(layer.commitChanges())
self.assertEqual(layer.featureCount(), 2)
layer = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test", 'test', u'ogr')
self.assertEqual(layer.featureCount(), 2)
self.assertEqual([f for f in layer.getFeatures()][0].geometry().asWkt(), 'Polygon ((0.5 0, 0.5 1, 1 1, 1 0, 0.5 0))')
self.assertEqual([f for f in layer.getFeatures()][1].geometry().asWkt(), 'Polygon ((0.5 1, 0.5 0, 0 0, 0 1, 0.5 1))')
def test_SplitFeatureWithMultiKey(self):
"""Create SpatiaLite database"""
layer = QgsVectorLayer("dbname=%s table=test_pg_mk (geometry)" % self.dbname, "test_pg_mk", "spatialite")
self.assertTrue(layer.isValid())
self.assertTrue(layer.isSpatial())
layer.startEditing()
self.assertEqual(layer.splitFeatures([QgsPointXY(0.5, -0.5), QgsPointXY(0.5, 1.5)], 0), 0)
self.assertEqual(layer.splitFeatures([QgsPointXY(-0.5, 0.5), QgsPointXY(1.5, 0.5)], 0), 0)
self.assertTrue(layer.commitChanges())
def test_SplitTruToCreateCutEdge(self):
"""Try to creat a cut edge"""
layer = QgsVectorLayer("dbname=test.sqlite table=test_pg (geometry)", "test_pg", "spatialite")
assert(layer.isValid())
assert(layer.isSpatial())
layer.featureCount() == 1 or die("wrong number of features")
layer.startEditing()
layer.splitFeatures([QgsPointXY(1.5, -0.5), QgsPointXY(1.5, 1.5)], 0) == 0 or die("error when trying to create an invalid polygon in split")
layer.commitChanges() or die("this commit should work")
layer.featureCount() == 1 or die("wrong number of features, polygon should be unafected by cut")
def test_SplitMultipolygon(self):
"""Split multipolygon"""
layer = QgsVectorLayer("dbname=test.sqlite table=test_mpg (geometry)", "test_mpg", "spatialite")
assert(layer.isValid())
assert(layer.isSpatial())
layer.featureCount() == 1 or die("wrong number of features")
layer.startEditing()
layer.splitFeatures([QgsPointXY(0.5, -0.5), QgsPointXY(0.5, 1.5)], 0) == 0 or die("error in split of one polygon of multipolygon")
layer.splitFeatures([QgsPointXY(2.5, -0.5), QgsPointXY(2.5, 4)], 0) == 0 or die("error in split of two polygons of multipolygon at a time")
layer.commitChanges() or die("this commit should work")
layer.featureCount() == 7 or die("wrong number of features after 2 split")
def test_SplitFeature(self):
"""Create spatialite database"""
layer = QgsVectorLayer(
"dbname=%s table=test_pg (geometry)" % self.dbname, "test_pg",
"spatialite")
self.assertTrue(layer.isValid())
self.assertTrue(layer.isSpatial())
layer.startEditing()
self.assertEqual(
layer.splitFeatures(
[QgsPointXY(0.75, -0.5),
QgsPointXY(0.75, 1.5)], 0), 0)
self.assertEqual(
layer.splitFeatures(
[QgsPointXY(-0.5, 0.25),
QgsPointXY(1.5, 0.25)], 0), 0)
self.assertTrue(layer.commitChanges())
self.assertEqual(layer.featureCount(), 4)
def test_SplitFeature(self):
"""Test sqlite feature can be split"""
tmpfile = os.path.join(self.basetestpath,
'testGeopackageSplitFeatures.sqlite')
ds = ogr.GetDriverByName('SQlite').CreateDataSource(tmpfile)
lyr = ds.CreateLayer('test', geom_type=ogr.wkbPolygon)
lyr.CreateField(ogr.FieldDefn('str_field', ogr.OFTString))
f = ogr.Feature(lyr.GetLayerDefn())
f.SetGeometry(
ogr.CreateGeometryFromWkt('POLYGON ((0 0,0 1,1 1,1 0,0 0))'))
lyr.CreateFeature(f)
f = None
ds = None
layer = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test",
'test', u'ogr')
# Check that pk field has unique constraint
fields = layer.fields()
pkfield = fields.at(0)
self.assertTrue(pkfield.constraints().constraints()
& QgsFieldConstraints.ConstraintUnique)
self.assertTrue(layer.isValid())
self.assertTrue(layer.isSpatial())
self.assertEqual([f
for f in layer.getFeatures()][0].geometry().asWkt(),
'Polygon ((0 0, 0 1, 1 1, 1 0, 0 0))')
layer.startEditing()
self.assertEqual(
layer.splitFeatures(
[QgsPointXY(0.5, 0), QgsPointXY(0.5, 1)], 0), 0)
self.assertTrue(layer.commitChanges())
self.assertEqual(layer.featureCount(), 2)
layer = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test",
'test', u'ogr')
self.assertEqual(layer.featureCount(), 2)
self.assertEqual([f
for f in layer.getFeatures()][0].geometry().asWkt(),
'Polygon ((0.5 0, 0.5 1, 1 1, 1 0, 0.5 0))')
self.assertEqual([f
for f in layer.getFeatures()][1].geometry().asWkt(),
'Polygon ((0.5 1, 0.5 0, 0 0, 0 1, 0.5 1))')
def cloneToMemory(self):
curlayer = self.iface.mapCanvas().currentLayer()
selectedFeatCount = curlayer.selectedFeatureCount()
geo = QgsWkbTypes.displayString(
curlayer.wkbType()) # wkbType string name of geometry
targetLayer = QgsVectorLayer(geo, self.dlg.lineEdit_2.text(), "memory")
targetLayer.setCrs(curlayer.sourceCrs())
QgsProject.instance().addMapLayer(targetLayer, False)
root = QgsProject.instance().layerTreeRoot()
self.setStyleLayer(targetLayer)
if self.dlg.checkBoxAtrib.isChecked(): #copy attributes
curlayer_attribute_list = curlayer.fields().toList()
targetLayer_attribute_list = []
targetLayerpr = targetLayer.dataProvider()
for attrib in curlayer_attribute_list:
if targetLayer.fields().lookupField(attrib.name()) == -1:
targetLayer_attribute_list.append(
QgsField(attrib.name(), attrib.type()))
with edit(targetLayer):
for attr in targetLayer_attribute_list:
if attr.type(
) == 1: # иначе игнорируется поле с типом 1 (bool)
attr = QgsField(
attr.name(),
QVariant.String) # конвертируем bool в string
res_add = targetLayer.addAttribute(attr)
if not res_add:
print(u'Не создано поле {}'.format(attr.name()))
targetLayer.updateFields()
# for feat in curlayer.selectedFeatures(): # not work more
# targetLayer.dataProvider().addFeatures([feat]) # not work more
# ИЗ МОДУЛЯ Apend Features To layer -----------------------------------------------
# В старом варианте в QGIS3 при добавлении объектов с отличающимся набором аттрибутов
# происходила задержка с выводом сообщений в логи. Что затягивало процесс.
mapping = dict()
for target_idx in targetLayer.fields().allAttributesList():
target_field = targetLayer.fields().field(target_idx)
source_idx = curlayer.fields().indexOf(target_field.name())
if source_idx != -1:
mapping[target_idx] = source_idx
features = curlayer.selectedFeatures()
destType = targetLayer.geometryType()
destIsMulti = QgsWkbTypes.isMultiType(targetLayer.wkbType())
new_features = []
for current, in_feature in enumerate(features):
attrs = {
target_idx: in_feature[source_idx]
for target_idx, source_idx in mapping.items()
}
geom = QgsGeometry()
if in_feature.hasGeometry() and targetLayer.isSpatial():
# Convert geometry to match destination layer
# Adapted from QGIS qgisapp.cpp, pasteFromClipboard()
geom = in_feature.geometry()
if destType != QgsWkbTypes.UnknownGeometry:
newGeometry = geom.convertToType(destType, destIsMulti)
if newGeometry.isNull():
continue
geom = newGeometry
# Avoid intersection if enabled in digitize settings
geom.avoidIntersections(
QgsProject.instance().avoidIntersectionsLayers())
new_feature = QgsVectorLayerUtils().createFeature(
targetLayer, geom, attrs)
new_features.append(new_feature)
with edit(targetLayer):
res = targetLayer.addFeatures(new_features)
# ИЗ МОДУЛЯ Apend Features To layer -----------------------------------------------end
root.insertLayer(0, targetLayer)
self.iface.messageBar().clearWidgets()
self.iface.setActiveLayer(targetLayer)
curlayer.selectByIds([])
if res:
self.iface.messageBar().pushMessage(
u"Выполнено",
u"Склонировано {0}/{1} объектов".format(
len(new_features), selectedFeatCount),
duration=5,
level=0)
def processAlgorithm(self, parameters, context, feedback):
destination = self.parameterAsFile(parameters, self.DESTINATION,
context)
try:
db = postgis.GeoDB.from_name(destination)
is_geopackage = False
schema = self.parameterAsFile(parameters, self.SCHEMA, context)
except QgsProcessingException:
is_geopackage = True
schema = None
if is_geopackage:
if not destination.lower().endswith('.gpkg'):
destination += '.gpkg'
uri = destination
else:
database_uri = db.uri
info = database_uri.connectionInfo(True)
conn = psycopg2.connect(info)
cur = conn.cursor()
sql = "DROP VIEW IF EXISTS {}.{};".format(schema, self.VIEW_NAME)
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
crs = self.parameterAsCrs(parameters, self.CRS, context)
options = dict()
options['update'] = True
if is_geopackage:
options['layerOptions'] = ['FID=id']
options['fileEncoding'] = 'UTF-8'
output_layers = []
for table, geom in MAPPING.items():
# create virtual layer
if geom[0]:
vl_path = '{}?crs={}&'.format(geom[0], crs.authid())
else:
vl_path = 'None?'
csv_path = resources_path('data_models', '{}.csv'.format(table))
csv = QgsVectorLayer(csv_path, table, 'ogr')
if not csv.isValid():
csv_path = resources_path('data_models',
'{}.csv'.format(table))
raise QgsProcessingException(
tr('* ERROR: Can\'t load CSV {}').format(csv_path))
fields = []
for c_f in csv.getFeatures():
fields.append('field={}:{}'.format(c_f['name'],
c_f['typeName']))
del csv
vl_path += '&'.join(fields)
LOGGER.debug('Memory layer "{}" created with {}'.format(
table, vl_path))
vl = QgsVectorLayer(vl_path, table, 'memory')
if vl.fields().count() != len(fields):
raise QgsProcessingException(
tr('* ERROR while creating fields in layer "{}"').format(
table))
# export layer
options['layerName'] = vl.name()
if not is_geopackage:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(vl.name())
if vl.isSpatial():
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
if vl.isSpatial():
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(vl.name()))
else:
uri_string = uri_string.replace(
'table=""', 'table="{}"'.format(vl.name()))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn(vl.fields().at(0).name())
exporter = QgsVectorLayerExporter(
uri if is_geopackage else uri.uri(),
'ogr' if is_geopackage else 'postgres', vl.fields(),
vl.wkbType(), vl.crs(), True, options)
# result
if exporter.errorCode() != QgsVectorLayerExporter.NoError:
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR while exporting the layer to "{}":"{}"').
format(source, exporter.errorMessage()))
# Do create sequence
if geom[2] and not is_geopackage:
cur = conn.cursor()
sql = "CREATE SEQUENCE {}.{}_{}_seq;".format(
schema, table, geom[2])
cur.execute(sql)
conn.commit()
sql = ("ALTER TABLE {0}.{1} "
"ALTER COLUMN {2} "
"SET DEFAULT nextval('{0}.{1}_{2}_seq'::regclass);"
).format(schema, table, geom[2])
cur.execute(sql)
conn.commit()
# connection troncon_rereau_classif in geopackage
if is_geopackage:
dest_layer = QgsVectorLayer(
'{}|layername={}'.format(uri, table), table, 'ogr')
else:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(vl.name())
if vl.isSpatial():
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
if vl.isSpatial():
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(vl.name()))
else:
uri_string = uri_string.replace(
'table=""', 'table="{}"'.format(vl.name()))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn(vl.fields().at(0).name())
dest_layer = QgsVectorLayer(uri.uri(False), table, 'postgres')
if not dest_layer.isValid():
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR: Can\'t load table "{}" in URI "{}"').format(
table, source))
feedback.pushInfo('The layer {} has been created'.format(table))
output_layers.append(dest_layer.id())
# Add layer to project
context.temporaryLayerStore().addMapLayer(dest_layer)
context.addLayerToLoadOnCompletion(
dest_layer.id(),
QgsProcessingContext.LayerDetails(table, context.project(),
self.OUTPUT_LAYERS))
# Get connection
if is_geopackage:
conn = spatialite_connect(uri)
# Do create view
cur = conn.cursor()
prefix = ''
view_destination = self.VIEW_NAME
if not is_geopackage:
prefix = '{}.'.format(schema)
view_destination = '{}{}'.format(prefix, view_destination)
sql = ("CREATE VIEW {0} AS "
"SELECT r.id, r.caa, r.id_geom_regard, r.id_file, g.geom "
"FROM {1}regard r, {1}geom_regard g "
"WHERE r.id_geom_regard = g.id;").format(
view_destination, prefix)
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
if is_geopackage:
sql = ("INSERT INTO gpkg_contents "
"(table_name, identifier, data_type, srs_id) "
"VALUES ( '{0}', '{0}', 'features', {1});").format(
self.VIEW_NAME, crs.postgisSrid())
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
sql = (
"INSERT INTO gpkg_geometry_columns "
"(table_name, column_name, geometry_type_name, srs_id, z, m) "
"VALUES ('{0}', 'geom', 'POINT', {1}, 0, 0);").format(
self.VIEW_NAME, crs.postgisSrid())
feedback.pushInfo(sql)
cur.execute(sql)
conn.commit()
conn.close()
# Connexion à la couche view_regard_localized dans le Geopackage
if is_geopackage:
view_layer = QgsVectorLayer(
'{}|layername={}'.format(uri, self.VIEW_NAME), self.VIEW_NAME,
'ogr')
else:
uri = QgsDataSourceUri(database_uri)
if Qgis.QGIS_VERSION_INT >= 31000:
uri.setTable(self.VIEW_NAME)
uri.setGeometryColumn('geom')
else:
uri_string = uri.uri(True)
uri_string = uri_string.replace(
'table=""', 'table="{}" (geom)'.format(self.VIEW_NAME))
uri = QgsDataSourceUri(uri_string)
# Schema is updating the table name,
# so after search&replace
uri.setSchema(schema)
uri.setKeyColumn('id')
view_layer = QgsVectorLayer(uri.uri(False), self.VIEW_NAME,
'postgres')
if not view_layer.isValid():
source = uri if is_geopackage else uri.uri()
raise QgsProcessingException(
tr('* ERROR: Can\'t load layer {} in {}').format(
self.VIEW_NAME, source))
output_layers.append(view_layer.id())
# Add layer to project
context.temporaryLayerStore().addMapLayer(view_layer)
context.addLayerToLoadOnCompletion(
view_layer.id(),
QgsProcessingContext.LayerDetails(self.VIEW_NAME,
context.project(),
self.OUTPUT_LAYERS))
feedback.pushInfo('The data model has been created in {}'.format(uri))
return {self.DESTINATION: uri, self.OUTPUT_LAYERS: output_layers}
def create_layer(self,
parameters,
name,
is_memory,
dest_crs,
layer_style=None):
save_as = parameters.file_path
file_format = parameters.file_format
# save paramaters
serialized = base64.b64encode(
parameters.serialize(with_style=False, with_geometry=False))
# save geometry
layer = QgsVectorLayer("MultiPolygon?crs=%s" % dest_crs.authid(), name,
"memory")
pr = layer.dataProvider()
layer.startEditing()
layer.addAttribute(QgsField("params", QVariant.String))
fet1 = QgsFeature(0)
fet1.setFields(layer.fields())
fet1.setAttribute("params", str(serialized)[2:-1])
fet1.setGeometry(parameters.geometry)
pr.addFeatures([fet1])
layer.commitChanges()
# copy layer style
if layer_style is not None:
self.set_layer_style(layer, layer_style)
if is_memory:
return layer
if os.path.isfile(save_as):
# delete first if already exists
if save_as.endswith(".shp"):
QgsVectorFileWriter.deleteShapeFile(save_as)
else:
os.unlink(save_as)
# create the disk layer
QgsMessageLog.logMessage(
"Mask saving '{}' as {}".format(save_as, file_format),
"Extensions")
error = QgsVectorFileWriter.writeAsVectorFormat(
layer, save_as, "System", dest_crs, file_format)
if error[0] == 0:
QgsMessageLog.logMessage("Error = 0", "Extensions")
nlayer = QgsVectorLayer(save_as, name, "ogr")
if not nlayer.dataProvider().isValid():
QgsMessageLog.logMessage("Invalid dataProvider", "Extensions")
return None
if not nlayer.isSpatial():
QgsMessageLog.logMessage("No GeometryType", "Extensions")
return None
# force CRS
nlayer.setCrs(dest_crs)
# copy layer style
layer_style = self.get_layer_style(layer)
self.set_layer_style(nlayer, layer_style)
return nlayer
else:
raise RuntimeError(error)
return None
