3

I have two attribute tables in qgis:

  1. One table has around 20 million records
  2. The second table has around 40k records.

I make a join (under Layer-Properties->Joins) in 2nd table and get some data from table 1 and put that into table 2 based on a single Id column. Now I want to verify the data that it is correctly joined but when i put an id into feature search it gets hang as table 1 is very large.

Is there any efficient way available to deal with such a large data set?

I have tried to write a script to verify data but still after running the whole day it is unable to produce any result still running and my QGIS project seems hang:

layerUpdateName = "Extra" #Name of the layer whose data want to update
layerUpdateColumn = "toid" #Column name to update

layerGetName = "Data_Tbl" #Name of the layer from data will be get
layerGetColumn = "TOID" #Cloum name from where value will be taken

dataMatchColumnName = "LOTID" 

#getting the desired layer
if QgsProject.instance().mapLayersByName(layerUpdateName):
    layerUpdate = QgsProject.instance().mapLayersByName(layerUpdateName)[0]; 

if QgsProject.instance().mapLayersByName(layerGetName):
    layerGet = QgsProject.instance().mapLayersByName(layerGetName)[0];
a = 0

for lg in layerGet.getFeatures():
    a += 1
    for lu in layerUpdate.getFeatures():    
        if lu[layerUpdateColumn] == lg[layerGetColumn]:
            print (lu[layerUpdateColumn])
            break;
  • 4
    you use a nested for loop which creates 40.000*20.000.000 iterations, that's quite a lot! Did you try a "real" join, it's under Layer-Properties->Joins. What file format do you have? May be a different (database) format could be faster (geopackage)? – Andreas Müller Oct 9 '19 at 8:19
  • @AndreasMüller Yes i have join the two table in same way and i want to verify the data after joining. – Muhammad Faizan Khan Oct 14 '19 at 5:04
  • @AndreasMüller I have .gdb file – Muhammad Faizan Khan Oct 14 '19 at 5:24
  • To clarify, are you updating existing columns or adding new columns? Using the join function in QGIS suggests adding new columns, but then you talk about updating a column. – hexamon Oct 14 '19 at 7:32
  • I am not updating any column just make a join and get some column. where did i write it? – Muhammad Faizan Khan Oct 14 '19 at 9:10
3
+25

How about filtering the joined layer for null values? Right click the joined layer and choose filter. QGIS will ask you to create a virtual layer to filter since it is a join-layer. Then you can filter the virtual layer with:

"nameOfColumnThatShouldHaveData" IS NOT NULL

I created a small test data set where I joined the column "data" from the layer "table" onto the point layer "joinedLayer". See the images below for the process.

Right click and filter the joined layer

enter image description here

Press yes to create a virtual layer

enter image description here

Right click the new, virtual layer and press filter again

enter image description here

Filter out null values in the columns added by the join ("table_data" in my case, since it was prefixed in the join process)

enter image description here

And here is the result tables. As you can see, the virtual layer has less features than the joinedLayer, since the NULL values are filtered out. You can also see the differing feature count in the layer list.

enter image description here

You should get the 40 000 rows that has the new column with data in it. You can right click the virtual layer and select "Show feature count" to make sure it is 40 000.

Not sure if this is impacted by the large number of rows, but the process works for a small dataset.

This is not a pyQGIS answer of course, but I got the impression in the comment discussion that pyQGIS was not a requirement.

Another caveat is that if the input join data contains null values, you will filter out those as well of course, thus filtering out rows that were actually joined. You could string together a compound filter to check all joined columns if some have data and some have null like so:

("column1" is not null) AND ("column2" is not null) ...

... and so on.

| improve this answer | |
  • I didn't fine layer filter option in joins layer – Muhammad Faizan Khan Oct 14 '19 at 9:31
  • It's in the layer list. So after you join the data, right click (in the layer list) the layer that you updated. There you should see "Filter..." – hexamon Oct 14 '19 at 9:34
  • Sorry i am new to qgis and still unable to locate your option. Can you give some hints in image format? – Muhammad Faizan Khan Oct 14 '19 at 10:04
  • Updated the answer with pictures. – hexamon Oct 14 '19 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.