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.

About these ads
34 comments
  1. not a spam said:

    Thanks to you for your work (blog). ;)

  2. Drew said:

    This is cool – Can’t wait for similar functionality with related tables

    • underdark said:

      Related tables? Do you mean non-spatial tables? That should already work.

      • darrencope said:

        underdark;

        Can you provide an example of how this works? Where do I select the table?

      • underdark said:

        @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.

  3. Tammo said:

    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.

    • underdark said:

      Hi Tammo,
      Are you using 1.6 or current trunk? I think this problem has been addressed in the new version.

  4. Tammo said:

    I’m using yesterday’s version of trunk (1.7.0 Trunk, 15265M).

    • underdark said:

      If I add a .csvt file, integer values are loaded correctly. Maybe you could share your data for debugging purposes.

  5. Tammo said:

    Argh, never mind. The semicolon works in the csv-file, but not in the csvt-file. It should be a real comma there.

  6. Christina said:

    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

    • underdark said:

      Which database are you using? I’ve only tried joining .csv files so far and querying (via attribute table) seems to work fine.

      • Christina said:

        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’.

      • Christina said:

        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.

    • Francois said:

      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!

      • Christina said:

        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

      • underdark said:

        Thank you Christina for the great step-by-step tutorial!

  7. pinoy_geographer said:

    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?

  8. monde said:

    How do i show a table of content in Quantum GIS when I have accidently closed it.

    • underdark said:

      Do you mean the layers list? It’s located in View – Panels – Layer (but this can vary from operating system to operating system).

  9. Marc said:

    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

  10. JHARR said:

    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

    • JHARR said:

      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.

      • underdark said:

        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.

  11. radovan said:

    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

  12. Tania said:

    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?

  13. Bernd said:

    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 )

  14. Sven said:

    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.

  15. sekjen said:

    Great! Been waiting for this since Enceladus, Copiapo..
    Tried with Dbase5 format, working good. Will try with mdb today..

Follow

Get every new post delivered to your Inbox.

Join 3,246 other followers

%d bloggers like this: