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