Table Joins – A New Feature in QGIS 1.7
Table joins have been on our wish list for quite a while. After all, it is much nicer to add a join than to go through the process of creating a new shapefile with joined attribute table using “Join by Attributes”.
The time has come!
Users of QGIS trunk may have already noticed the new “Joins” tab in Layer Properties:
"Joins" tab and "Add vector join" dialog in QGIS 1.7
The join results in an extended attribute table. If there is no matching row in the joined table, the attributes will be set to NULL.
Free and Open Source GIS Ramblings 
Thanks to you for your work (blog). ;)
This is cool – Can’t wait for similar functionality with related tables
Related tables? Do you mean non-spatial tables? That should already work.
underdark;
Can you provide an example of how this works? Where do I select the table?
@darrencope: You can load non-spatial tables using “Add Vector Layer” (the naming is misleading here). The table will then be added to the layers list and you can chose it in “Add vector join” dialog.
Pingback: [QGIS 1.x] jointure de tables dans la version 1.7 | Jeux De Fille
This is really great! However, I have trouble joining numerical data. Reading in a non spatial layer (.csv), I only get string fields, even though all data is numerical. Any idea how this could be fixed? I tried creating a .csvt file with contents “String”, “Integer” but this doesn’t seem to change anything.
Workaround is to create a computed field with the integer conversion, but this is not as ‘clean’ i.m.o.
Hi Tammo,
Are you using 1.6 or current trunk? I think this problem has been addressed in the new version.
I’m using yesterday’s version of trunk (1.7.0 Trunk, 15265M).
If I add a .csvt file, integer values are loaded correctly. Maybe you could share your data for debugging purposes.
Certainly. It’s on http://www.tammo80.nl/files/testdata.csv and http://www.tammo80.nl/files/testdata.csvt
I can test it as follows: I add the layer through Layer, Add Vector Layer, testdata.csv
Then in the layer properties, in the tab Fields, only String types are shown.
Argh, never mind. The semicolon works in the csv-file, but not in the csvt-file. It should be a real comma there.
I have recently added an access database to my QGIS project and was wanting the join the database (which has no geometry) to the attribute tables of my exsisitng shapefiles. I have done it this way as I need a live link between the database and the GIS. The link has worked and is live, but when I use this table join, it doesn’t appear to work. When I create a query to select features from the database that are joined by attributes in other shapefiles, it onlyu selects them in the database and not in the linked attributes. Is there a bug with this or am I doing something fundimentally wrong?
Thanks
C
Which database are you using? I’ve only tried joining .csv files so far and querying (via attribute table) seems to work fine.
MS access 97, I have now got the join to work (I had too many null values, which I stripped out for testing purposes) so that the database information appears in the attribute table of the shapefile. When I run a query it now says ‘referneced column wasn’t found’.
I think it has worked and I think there maybe a bug with the query builder as the normal search box seems to work fine with selecting informtaion from the database stuff in the attribute table.
Hi, I am trying to do the same – non spatial join with Access tables – can you please explain how you did this? I am using QGis 1.7
Thanks!
Connecting to access database in QGIS:
1. Go to control panel/administrtive tools/data sources (ODBC)
2. Click on the user DSN tab and then ‘add’
3. Scroll down to Microsoft Access Driver (*mdb) and click ‘finish’
4. Give the connection a name then choose ‘select’ to browse to the location of the database, then click ‘ok’ to exit the dialogue and again to close the dsn window.
5. In QGIS go to add vector layer and select database option.
6. Ensure the database ‘type’ box says ‘ODBC’ and click ‘new’
7. In the name box type a name for connection
8. The host is localhost
9. The database name is the name of the dsn you created in step 4
10. All other boxes should be blank but you can tick ‘save password’ box.
11. Test the connection and make a note of any errors. When successful, click ‘ok’ to return to the ‘add vector layer’ dialogue
12. Click ‘open’ to open this connection, and if asked for password, click ‘ ok’. Choose your layer (table from database), bearing in mind it may not have any geometry.
13. To join the database to a shapefile in QGIS you need to be using version 1.7
14. Select a shapefile and right click and select ‘properties’.
15. In the ‘properties’ box select the ‘joins’ tab, and click the ‘+’ symbol this will produce an ‘add vector join’ box
16. In the ‘join layer’ select the database which has just been added to the project.
17. In ‘join field’ select the common field between the database and the shapefile.
18. For the ‘target field’ again select the common field between the shapefile and the database.
19. The cache join layer in virtual memory is checked, but not the create attribute index on join field then select ‘ok’.
20. This then should have attached the appropriate data from the database to the shapefile and search as required.
Hope this helps
C
Thank you Christina for the great step-by-step tutorial!
Wow this is great news! I have done two trainings with the staff of two government agencies in the Philippines in the last three months using version 1.5 and the limitation of the “number automatically being converted to string data” when csv is joined to attribute table was always the frustration among my trainees. I wasn’t able to figure out your advise (in one of the qgis fora) regarding the use of field calculator to address this. Anyway, when do you think is the version 1.7 going to be released?
According to the Release Checklist (http://www.qgis.org/wiki/Release_Checklists) version 1.7 is planned for April 1st.
Using .csvt files should work with previous versions too.
Can you provide a step-by-step instruction on how csvt files work? Or can you point me to a thread where this has been discussed previously? I would really want to understand this so I can advise the people I train on how to address the problem earlier mentioned. Thank you and all the best.
I tried to cover the topic in https://underdark.wordpress.com/2011/03/07/how-to-specify-data-types-of-csv-columns-for-use-in-qgis/
How do i show a table of content in Quantum GIS when I have accidently closed it.
Do you mean the layers list? It’s located in View – Panels – Layer (but this can vary from operating system to operating system).
Hi,
I just found this thread and worked my way using the latest trunk to link together several csv files: thanks a lot
Now the question: the data is originally stored in a odf (openoffice base) database. Is there any possibility to link directly this db to qgis the same way it is possible to linka an access db?
I am using Ubuntu 11.04 64bits by the way
Thanks in advance
Marc
Hi – here is my conundrum…
I have a polygon shapefile with a single column of about 15 rows.
I have also a database (which I converted into a .csv and loaded into Q). This database has 20 columns with about 1200 rows.
The column to establish the join has the same unique numbers in both the shapefile and the database BUT in the shapefile there is one unique number per row and in the database there are multiple rows of the same unique number.
ie:)
Shapefile: Database:
56JJ7834 56JJ7834
56JJ7835 56JJ7834
56JJ7836 56JJ7834
56JJ7835
56JJ7835
56JJ7836
56JJ7836
56JJ7836
I have version 1.7 WROCLAW and can join tables 1:1 but can’t figure out how to join tables in this instance…
RKLOGIST
The example did not post right… But I hope you get the gist of what I’m trying to ask…
I have a table with 1 unique number per column and a table with many of the same unique numbers in a column.
I want to join the two to be able to query the large database but see the results spatially in the polygon.
Thanks again.
Hi,
Your comment was formatted just fine in my email notification. As far as I know, joins only work 1:1 right now. Features in the geometry layer are not duplicated. That would be necessary to support 1:n joins.
The mailing list is the best place to find out whether someone is working on 1:n joins already. They certainly would be useful.
Everything is great, but the ODBC says: DSN architecture contains a disagreement between the control and administration QODBC3: Unable to connect …. can somebody help me ? win 7 64bit, QGIS 1.7 thank you
I have been able to join attribute data to a shape with with an ODBC connection as well as with a .dbf file and the attributes appear in the attribute table. But in both cases when I try to create a graduate colour ramp / thematic map the classes are all zeros. Seems it does not really read the values from the linked attrib file then. Any suggestions?
Great!
Managed to connect to an access database (quite) easily with the explanations from above.
Took me a while to discover that Win7 64bit works differently:
Step 1. there is different:
Instead of using the default “control panel/administrative tools/data sources (ODBC)”-way, with 64bit versions, you have to use C:\Windows\SysWOW64\odbcad32.exe !
Just browse there and start the exe, then all steps run as described above.
(Hint found at: http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/685eacc1-a670-42d4-8392-924230fa90cb )
Hi,
I am comparably new with all the QGIS stuff, but I enjoy the function of joining shape data and non geometric data via an ODBC connection very much. Am I doing something completely wrong, when the changes I make in QGIS are not written back into the ACcces DB (that is the source od the ODBC)?
I would appreciate a hint very much.
Great! Been waiting for this since Enceladus, Copiapo..
Tried with Dbase5 format, working good. Will try with mdb today..