def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS BB_bname, j1.bfield AS BB_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield, j2.cname AS C_cname FROM {} AS t ' +
'LEFT JOIN {} AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN {} AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id()])
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS BB_bname, j1.bfield AS BB_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield, j2.cname AS C_cname FROM {} AS t ' +
'LEFT JOIN {} AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN {} AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id()])
def buildJoin(self, originalLyr, originalLyrFieldName, joinnedLyr,
joinLyrFieldName):
"""
Builds a join bewteen lyr and joinnedLyr.
:param originalLyr: QgsVectorLayer original layer;
:param originalLyrFieldName: (str) name of the field;
:param joinnedLyr: QgsVectorLayer lyr to be joinned to originalLayer;
:param joinLyrFieldName: (str) name of the join field name (usually primary key of joinnedLyr)
"""
joinObject = QgsVectorLayerJoinInfo()
joinObject.setJoinFieldName(joinLyrFieldName)
joinObject.setTargetFieldName(originalLyrFieldName)
joinObject.setJoinLayer(joinnedLyr)
joinObject.setJoinFieldNamesSubset()
joinObject.upsertOnEdit(True) #set to enable edit on original lyr
joinObject.setCascadedDelete(True)
joinObject.setDynamicFormEnabled(True)
joinObject.setEditable(True)
joinObject.setUsingMemoryCache(True)
originalLyr.addJoin(joinObject)
def configureLayer(self, layer, context, feedback):
# rename and style the output layer here
layer.setName(self.layerName)
layer.loadNamedStyle(
os.path.join(os.path.dirname(__file__), 'styles', self.styleName))
layer.triggerRepaint()
# set up custom variable identifying the added layers
layer.setCustomProperty(NOAA_LAYER_TYPE, self.layerType)
# if both layers are available (meaning both post processors have run) then configure joins
stationsLayer = currentStationsLayer()
predictionsLayer = currentPredictionsLayer()
if stationsLayer is not None and predictionsLayer is not None:
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setJoinLayer(stationsLayer)
joinInfo.setTargetFieldName('station')
joinInfo.setJoinFieldName('station')
joinInfo.setJoinFieldNamesSubset(
['name', 'timeZoneId', 'timeZoneUTC', 'surface'])
joinInfo.setPrefix('station_')
predictionsLayer.addJoin(joinInfo)
localTimeField = QgsField('local_time', QVariant.DateTime)
predictionsLayer.addExpressionField(
'convert_to_time_zone(time, station_timeZoneUTC, station_timeZoneId)',
localTimeField)
displayDateField = QgsField('display_date', QVariant.String)
predictionsLayer.addExpressionField(
"format_date(convert_to_time_zone(time, station_timeZoneUTC, station_timeZoneId), 'MM/dd')",
displayDateField)
displayTimeField = QgsField('display_time', QVariant.String)
predictionsLayer.addExpressionField(
"format_date(convert_to_time_zone(time, station_timeZoneUTC, station_timeZoneId), 'hh:mm a')",
displayTimeField)
predictionsLayer.updateFields()
def joinTables(self):
d, f = self.lyrPair()
for k, v in d.items():
target = QgsProject.instance().mapLayer(k)
layerToJoin = QgsProject.instance().mapLayer(v)
fieldToJoin = QgsProject.instance()
symb = QgsVectorLayerJoinInfo()
symb.setJoinFieldName('id_feature')
symb.setTargetFieldName('id')
symb.setJoinLayerId(layerToJoin.id())
symb.setUsingMemoryCache(True)
symb.setEditable(True)
symb.setDynamicFormEnabled(True)
symb.setUpsertOnEdit(True)
symb.setPrefix('')
symb.setJoinFieldNamesSubset([
'ocultar', 'legenda', 'tamanhotxt', 'justtxt', 'orient_txt',
'orient_simb', 'offset_txt', 'offset_simb', 'prioridade',
'offset_txt_x', 'offset_txt_y'
])
symb.setJoinLayer(layerToJoin)
target.addJoin(symb)
def joinTables(self):
d, f = self.lyrPair()
for k, v in d.items():
target = QgsProject.instance().mapLayer(k)
layerToJoin = QgsProject.instance().mapLayer(v)
""" # tests for previous joined layers - under research
i = QgsProject.instance().mapLayers().values()
for layer in i:
fh_lyr = layer
joinsInfo = fh_lyr.vectorJoins()
if joinsInfo != 0:
QMessageBox.critical(iface.mainWindow(), "Error",
"Previously Joins already exists! Please remove joins and try again.")
break # ask for remove previous joins. use removeJoinTables()
else:
"""
# target.removeJoin(layerToJoin.id())
fieldToJoin = QgsProject.instance()
symb = QgsVectorLayerJoinInfo()
symb.setJoinFieldName('id_feature')
symb.setTargetFieldName('id')
symb.setJoinLayerId(layerToJoin.id())
symb.setUsingMemoryCache(True)
symb.setEditable(True)
symb.setDynamicFormEnabled(True)
symb.setUpsertOnEdit(True)
symb.setPrefix('')
symb.setJoinFieldNamesSubset([
'ocultar', 'legenda', 'tamanhotxt', 'justtxt', 'orient_txt',
'orient_simb', 'offset_txt', 'offset_simb', 'prioridade',
'offset_txt_x', 'offset_txt_y'
])
symb.setJoinLayer(layerToJoin)
target.addJoin(symb)
layerToJoin.startEditing()
target.triggerRepaint()
def execute_layers_join(layer, layer_field, column_header, fk_layer,
fk_field):
"""
Joins two layers with specified field.
:param layer: The destination layer of the merge.
:type layer: QgsVectorLayer
:param layer_field: The source layer of the merge.
:type layer_field: String
:param fk_layer: The foreign key layer object.
:type fk_layer: QgsVectorLayer
:param fk_field: The foreign key layer field name.
:type fk_field: String
:return:
:rtype:
"""
join = QgsVectorLayerJoinInfo()
join.joinLayerId = fk_layer.id()
join.joinFieldName = 'id'
join.setJoinFieldNamesSubset([fk_field])
join.targetFieldName = layer_field
join.memoryCache = True
join.prefix = '{} '.format(column_header)
layer.addJoin(join)
def processAlgorithm(self, parameters, context, feedback):
t_file = self.parameterAsVectorLayer(
parameters,
self.FILE_TABLE,
context
)
t_troncon = self.parameterAsVectorLayer(
parameters,
self.SEGMENTS_TABLE,
context
)
t_obs = self.parameterAsVectorLayer(
parameters,
self.OBSERVATIONS_TABLE,
context
)
t_regard = self.parameterAsVectorLayer(
parameters,
self.MANHOLES_TABLE,
context
)
g_regard = self.parameterAsVectorLayer(
parameters,
self.GEOM_MANHOLES,
context
)
g_troncon = self.parameterAsVectorLayer(
parameters,
self.GEOM_SEGMENT,
context
)
g_obs = self.parameterAsVectorLayer(
parameters,
self.GEOM_OBSERVATION,
context
)
v_regard = self.parameterAsVectorLayer(
parameters,
self.VIEW_MANHOLES_GEOLOCALIZED,
context
)
# define variables
variables = context.project().customVariables()
variables['itv_rerau_t_file'] = t_file.id()
variables['itv_rerau_t_troncon'] = t_troncon.id()
variables['itv_rerau_t_obs'] = t_obs.id()
variables['itv_rerau_t_regard'] = t_regard.id()
variables['itv_rerau_g_regard'] = g_regard.id()
variables['itv_rerau_g_troncon'] = g_troncon.id()
variables['itv_rerau_g_obs'] = g_obs.id()
context.project().setCustomVariables(variables)
# define relations
relations = [
{
'id': 'fk_obs_id_file',
'name': tr('Link File - Observation'),
'referencingLayer': t_obs.id(),
'referencingField': 'id_file',
'referencedLayer': t_file.id(),
'referencedField': 'id'
}, {
'id': 'fk_regard_id_file',
'name': tr('Link File - Manhole'),
'referencingLayer': t_regard.id(),
'referencingField': 'id_file',
'referencedLayer': t_file.id(),
'referencedField': 'id'
}, {
'id': 'fk_troncon_id_file',
'name': tr('Link File - Pipe segment'),
'referencingLayer': t_troncon.id(),
'referencingField': 'id_file',
'referencedLayer': t_file.id(),
'referencedField': 'id'
}, {
'id': 'fk_obs_id_troncon',
'name': tr('Link Pipe segment - Observation'),
'referencingLayer': t_obs.id(),
'referencingField': 'id_troncon',
'referencedLayer': t_troncon.id(),
'referencedField': 'id'
}, {
'id': 'fk_regard_id_geom_regard',
'name': tr('Link Manhole inspection - Reference'),
'referencingLayer': t_regard.id(),
'referencingField': 'id_geom_regard',
'referencedLayer': g_regard.id(),
'referencedField': 'id'
}, {
'id': 'fk_troncon_id_geom_trononc',
'name': tr('Link Pipe segment inspection - Reference'),
'referencingLayer': t_troncon.id(),
'referencingField': 'id_geom_troncon',
'referencedLayer': g_troncon.id(),
'referencedField': 'id'
}
]
relation_manager = context.project().relationManager()
for rel_def in relations:
feedback.pushInfo(
'Link: {}'.format(rel_def['name'])
)
rel = QgsRelation()
rel.setId(rel_def['id'])
rel.setName(rel_def['name'])
rel.setReferencingLayer(rel_def['referencingLayer'])
rel.setReferencedLayer(rel_def['referencedLayer'])
rel.addFieldPair(
rel_def['referencingField'],
rel_def['referencedField']
)
rel.setStrength(QgsRelation.Association)
relation_manager.addRelation(rel)
feedback.pushInfo(
'Count relations {}'.format(
len(relation_manager.relations())
)
)
joins = [
{
'layer': t_obs,
'targetField': 'id_troncon',
'joinLayer': t_troncon,
'joinField': 'id',
'fieldNamesSubset': ['ack']
}, {
'layer': g_obs,
'targetField': 'id',
'joinLayer': t_obs,
'joinField': 'id',
'fieldNamesSubset': []
}
]
for j_def in joins:
layer = j_def['layer']
join = QgsVectorLayerJoinInfo()
join.setJoinFieldName(j_def['joinField'])
join.setJoinLayerId(j_def['joinLayer'].id())
join.setTargetFieldName(j_def['targetField'])
if j_def['fieldNamesSubset']:
join.setJoinFieldNamesSubset(j_def['fieldNamesSubset'])
join.setUsingMemoryCache(False)
join.setPrefix('')
join.setEditable(False)
join.setCascadedDelete(False)
join.setJoinLayer(j_def['joinLayer'])
layer.addJoin(join)
layer.updateFields()
# load styles
styles = [
{
'layer': t_file,
'namedStyles': [
{
'file': 'itv_file_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_file_actions.qml',
'type': QgsMapLayer.Actions
}
]
}, {
'layer': t_troncon,
'namedStyles': [
{
'file': 'itv_troncon_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_troncon_table.qml',
'type': QgsMapLayer.AttributeTable
}
]
}, {
'layer': t_obs,
'namedStyles': [
{
'file': 'itv_obs_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_obs_table.qml',
'type': QgsMapLayer.AttributeTable
}
]
}, {
'layer': t_regard,
'namedStyles': [
{
'file': 'itv_regard_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_regard_forms.qml',
'type': QgsMapLayer.Forms
}, {
'file': 'itv_regard_table.qml',
'type': QgsMapLayer.AttributeTable
}
]
}, {
'layer': g_regard,
'namedStyles': [
{
'file': 'itv_geom_regard_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_geom_regard_symbology.qml',
'type': QgsMapLayer.Symbology
}
]
}, {
'layer': g_troncon,
'namedStyles': [
{
'file': 'itv_geom_troncon_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_geom_troncon_symbology.qml',
'type': QgsMapLayer.Symbology
}, {
'file': 'itv_geom_troncon_actions.qml',
'type': QgsMapLayer.Actions
}
]
}, {
'layer': g_obs,
'namedStyles': [
{
'file': 'itv_geom_obs_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_geom_obs_symbology.qml',
'type': QgsMapLayer.Symbology
}
]
}, {
'layer': v_regard,
'namedStyles': [
{
'file': 'itv_view_regard_fields.qml',
'type': QgsMapLayer.Fields
}, {
'file': 'itv_view_regard_symbology.qml',
'type': QgsMapLayer.Symbology
}, {
'file': 'itv_view_regard_labeling.qml',
'type': QgsMapLayer.Labeling
}
]
}
]
for style in styles:
layer = style['layer']
for n_style in style['namedStyles']:
layer.loadNamedStyle(
resources_path('styles', n_style['file']),
categories=n_style['type']
)
# layer.saveStyleToDatabase('style', 'default style', True, '')
layer.triggerRepaint()
# Creation de la symbologie g_obs
g_obs_rules = (
'BAA', 'BAB', 'BAC', 'BAD', 'BAE', 'BAF', 'BAG', 'BAH',
'BAI', 'BAJ', 'BAK', 'BAL', 'BAM', 'BAN', 'BAO', 'BAP',
'BBA', 'BBB', 'BBC', 'BBD', 'BBE', 'BBF', 'BBG', 'BBH',
'BCA', 'BCB', 'BCC', 'BDA', 'BDB', 'BDC', 'BDD', 'BDE',
'BDF', 'BDG'
)
g_obs_rule_descs = {
'BAA': 'Déformation',
'BAB': 'Fissure',
'BAC': 'Rupture/Effondrement',
'BAD': 'Elt maçonnerie',
'BAE': 'Mortier manquant',
'BAF': 'Dégradation de surface',
'BAG': 'Branchement pénétrant',
'BAH': 'Raccordement défectueux',
'BAI': 'Joint étanchéité apparent',
'BAJ': 'Déplacement d\'assemblage',
'BAK': 'Défaut de révêtement',
'BAL': 'Réparation défectueuse',
'BAM': 'Défaut soudure',
'BAN': 'Conduite poreuse',
'BAO': 'Sol visible',
'BAP': 'Trou visible',
'BBA': 'Racines',
'BBB': 'Dépots Adhérents',
'BBC': 'Dépôts',
'BBD': 'Entrée de terre',
'BBE': 'Autres obstacles',
'BBF': 'Infiltration',
'BBG': 'Exfiltration',
'BBH': 'Vermine',
'BCA': 'Raccordement',
'BCB': 'Réparation',
'BCC': 'Courbure de collecteur',
'BDA': 'Photographie générale',
'BDB': 'Remarque générale',
'BDC': 'Inspection abandonnée',
'BDD': 'Niveau d\'eau',
'BDE': 'Ecoulement dans une canlisation entrante',
'BDF': 'Atmosphère canalisation',
'BDG': 'Perte de visibilité'
}
g_obs_rootrule = QgsRuleBasedRenderer.Rule(None)
rendering_pass_idx = len(g_obs_rules)
for rule in g_obs_rules:
# get svg path
svg_path = resources_path('styles', 'img_obs', rule + '.svg')
# create svg symbol layer
svg_symbol_layer = QgsSvgMarkerSymbolLayer(svg_path)
svg_symbol_layer.setRenderingPass(rendering_pass_idx)
# create white square symbol layer for the backend
simple_symbol_layer = QgsSimpleMarkerSymbolLayer(
shape=QgsSimpleMarkerSymbolLayerBase.Circle,
size=svg_symbol_layer.size(),
color=QColor('white'),
strokeColor=QColor('white')
)
simple_symbol_layer.setRenderingPass(rendering_pass_idx)
# create marker
svg_marker = QgsMarkerSymbol()
# set the backend symbol layer
svg_marker.changeSymbolLayer(0, simple_symbol_layer)
# add svg symbol layer
svg_marker.appendSymbolLayer(svg_symbol_layer)
# create rule
svg_rule = QgsRuleBasedRenderer.Rule(
svg_marker, 0, 10000,
QgsExpression.createFieldEqualityExpression('a', rule),
rule
)
if rule in g_obs_rule_descs:
svg_rule.setLabel(g_obs_rule_descs[rule])
svg_rule.setDescription('{}: {}'.format(
rule,
g_obs_rule_descs[rule]
))
# add rule
g_obs_rootrule.appendChild(svg_rule)
rendering_pass_idx -= 1
g_obs_rootrule.appendChild(
QgsRuleBasedRenderer.Rule(
QgsMarkerSymbol.createSimple(
{
'name': 'circle',
'color': '#0000b2',
'outline_color': '#0000b2',
'size': '1'
}
),
0, 10000, 'ELSE', 'Autres'
)
)
g_obs.setRenderer(QgsRuleBasedRenderer(g_obs_rootrule))
feedback.pushInfo('Project has been setup')
return {}
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer(
"Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string",
"A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer(
"Point?field=id:integer&field=bname:string&field=bfield:integer",
"B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C",
"memory")
self.assertEqual(v3.isValid(), True)
tl1 = QgsVectorLayer(
"NoGeometry?field=id:integer&field=e_id:integer&field=0name:string",
"D", "memory")
self.assertEqual(tl1.isValid(), True)
tl2 = QgsVectorLayer("NoGeometry?field=id:integer&field=ena me:string",
"E", "memory")
self.assertEqual(tl2.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3, tl1, tl2])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(
df.query(),
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "BB_bname", j1."bfield" AS "BB_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'
.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), (
'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield", j2."cname" AS "C_cname" FROM "{}" AS t '
+ 'LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN "{}" AS j2 ON t."c_id"=j2."id"').format(
v1.id(), v2.id(), v3.id()))
# test NoGeometry joined layers with field names starting with a digit or containing white spaces
joinInfo3 = QgsVectorLayerJoinInfo()
joinInfo3.setTargetFieldName("e_id")
joinInfo3.setJoinLayer(tl2)
joinInfo3.setJoinFieldName("id")
tl1.addJoin(joinInfo3)
self.assertEqual(len(tl1.fields()), 4)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(tl1)
self.assertEqual(
df.query(),
'SELECT t.rowid AS uid, t."id", t."e_id", t."0name", j1."ena me" AS "E_ena me" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."e_id"=j1."id"'
.format(tl1.id(), tl2.id()))
QgsProject.instance().removeMapLayers(
[v1.id(), v2.id(), v3.id(),
tl1.id(), tl2.id()])
def test_joined_layers_conversion(self):
v1 = QgsVectorLayer("Point?field=id:integer&field=b_id:integer&field=c_id:integer&field=name:string", "A", "memory")
self.assertEqual(v1.isValid(), True)
v2 = QgsVectorLayer("Point?field=id:integer&field=bname:string&field=bfield:integer", "B", "memory")
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
tl1 = QgsVectorLayer("NoGeometry?field=id:integer&field=e_id:integer&field=0name:string", "D", "memory")
self.assertEqual(tl1.isValid(), True)
tl2 = QgsVectorLayer("NoGeometry?field=id:integer&field=ena me:string", "E", "memory")
self.assertEqual(tl2.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3, tl1, tl2])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
joinInfo.setJoinFieldName("id")
#joinInfo.setPrefix("B_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)
# add a table prefix to the join
v1.removeJoin(v2.id())
joinInfo.setPrefix("BB_")
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "BB_bname", j1."bfield" AS "BB_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
# add another join
joinInfo2 = QgsVectorLayerJoinInfo()
joinInfo2.setTargetFieldName("c_id")
joinInfo2.setJoinLayer(v3)
joinInfo2.setJoinFieldName("id")
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield", j2."cname" AS "C_cname" FROM "{}" AS t '
+ 'LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id" '
+ 'LEFT JOIN "{}" AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))
# test NoGeometry joined layers with field names starting with a digit or containing white spaces
joinInfo3 = QgsVectorLayerJoinInfo()
joinInfo3.setTargetFieldName("e_id")
joinInfo3.setJoinLayer(tl2)
joinInfo3.setJoinFieldName("id")
tl1.addJoin(joinInfo3)
self.assertEqual(len(tl1.fields()), 4)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(tl1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t."id", t."e_id", t."0name", j1."ena me" AS "E_ena me" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."e_id"=j1."id"'.format(tl1.id(), tl2.id()))
QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id(), tl1.id(), tl2.id()])
def fusionnerJointure(self, cheminCompletParcelle, jointureChoisie):
""" Selon les tables déja ouverte dans le projet : ouverture si necessaire des différents cas de délimiteurs
Jointure par QGIS """
# Vérification du projet ouverte
monProjet = QgsProject.instance()
if monProjet.fileName() == None or monProjet.fileName() == "":
monPrint("Projet en cours de création", T_WAR)
else:
monPrint("Projet ouvert {}".format(monProjet.fileName()))
root = monProjet.layerTreeRoot()
# Création du groupe jointure_date
dateMaintenant = datetime.now()
nomGroupe = MonParcellaire_JOI + " du " + dateMaintenant.strftime(
"%d %b à %Hh%M:%S")
temporaireGroupe = QgsLayerTreeGroup(nomGroupe)
# Positionner en haut de root
root.addChildNode(temporaireGroupe)
nouveauGroupe = temporaireGroupe.clone()
root.insertChildNode(0, nouveauGroupe)
root.removeChildNode(temporaireGroupe)
# Ouverture du vecteur parcelle
parcelle = QgsVectorLayer(
cheminCompletParcelle,
MonParcellaire_PAR + SEP_U + MonParcellaire_JOI, 'ogr')
monProjet.addMapLayer(parcelle, False)
nouveauGroupe.addLayer(parcelle)
# Recherche delimiteur
delimiteur, csv, nomCsv = self.rechercherDelimiteurJointure(
jointureChoisie, "No Pandas")
nomCourtJointure = os.path.basename(jointureChoisie)
monPrint(
"Délimiteur identifié {0} pour {1}".format(delimiteur,
nomCourtJointure), T_OK)
monProjet.addMapLayer(csv, False)
nouveauGroupe.addLayer(csv)
# Jointure
attributsSelectionnes = self.AttributsAJoindre_listWidget.selectedItems(
)
attributsAJoindre = []
for positionAttribut in range(len(attributsSelectionnes)):
attributsAJoindre.append(
str(self.AttributsAJoindre_listWidget.selectedItems()
[positionAttribut].text()))
#monPrint( "Attributs à joindre {}".format( attributsAJoindre))
# Liste des champs dans csv
nomColonnes, _ = self.lireAttributsJointure(jointureChoisie)
attributsAJoindreOrdonne = []
for col in nomColonnes:
if col in attributsAJoindre:
if col != MonParcellaireNomAttribut:
attributsAJoindreOrdonne.append(col)
#monPrint( "Attributs à joindre ordonné {}".format( attributsAJoindreOrdonne))
champVecteur = MonParcellaireNomAttribut
maJointure = QgsVectorLayerJoinInfo()
champCsv = self.AttributJointure_comboBox.currentText()
maJointure.setJoinFieldName(champCsv)
maJointure.setTargetFieldName(champVecteur)
maJointure.setUsingMemoryCache(True)
maJointure.setPrefix("")
maJointure.setJoinLayer(csv)
# Récupérer les champs de jointure
maJointure.setJoinFieldNamesSubset(attributsAJoindreOrdonne)
parcelle.addJoin(maJointure)
return jointureChoisie, attributsAJoindreOrdonne
