Opened 14 years ago

Closed 10 years ago

#121 closed enhancement (fixed)

join external tables to geometries

Reported by: mlennert@… Owned by:
Priority: major: does not work as expected Milestone: Version 1.6.0
Component: Data Provider Version: Trunk
Keywords: Cc: neteler, jdenisgiguere, borysiasty, maplestar
Must Fix for Release: No Platform: All
Platform Version: Awaiting user input: no

Description

It would be great to be able to join an external table (postgres, dbf, text, ...) to the attributes of a geometries file (such as the join tables feature in Thuban or ArcView?). This would make cartography much easier than having to treat the attribute files directly.

Change History (27)

comment:1 Changed 14 years ago by anonymous

Owner: changed from gsherman to anonymous
Status: newassigned

comment:2 Changed 14 years ago by cavallini@…

Component: Build/InstallData Provider
Milestone: Version 1.0 Release
Type: defectenhancement

comment:3 Changed 14 years ago by anonymous

Version: HEAD

comment:4 Changed 13 years ago by neteler

Awaiting user input: unset
Cc: neteler added
Must Fix for Release: No
Platform: DebianAll

I am also interested...

comment:5 Changed 13 years ago by giohappy

It's a wish coming from many collegues of mine. It would be useful to support join and then export to a new shapefile with the enlarged attributes table.

comment:6 Changed 13 years ago by jef

Owner: anonymous deleted
Status: assignednew

comment:7 Changed 12 years ago by timlinux

Milestone: Version 1.0.0Feature Requests

comment:8 Changed 12 years ago by timlinux

Milestone: Feature RequestsVersion 1.0.0

On second thoughts this is probably handled using the enhancement type and not a fake milestone. Returning to 1.0.1 milestone, please ignore last milestone change

comment:9 Changed 12 years ago by pcav

This is available through a plugin by Carson Farmer http://www.geog.uvic.ca/spar/carson/cfarmerQgisRepo.xml It must be upgraded to 1.0 API though

comment:10 in reply to:  9 Changed 12 years ago by mlennert

Replying to pcav:

This is available through a plugin by Carson Farmer http://www.geog.uvic.ca/spar/carson/cfarmerQgisRepo.xml It must be upgraded to 1.0 API though

This is already a good start, but IIUC, it creates a new shapefile everytime to join some data. Not really ideal IMHO. Would be nice to have this more 'on-the-fly', i.e. that you can join data from another data source (dbf, RDBMS, etc) to your map within the project, without creating any new files.

Moritz

comment:11 Changed 11 years ago by jdenisgiguere

Cc: jdenisgiguere added

comment:12 Changed 11 years ago by trapanator

Milestone: Version 1.0.3Version 1.4.0

comment:13 Changed 11 years ago by wildintellect

It just occurred to me that I might know any easy(relatively) fast way to do this. If every vector layer attribute table when loaded, queried or joined (last 2 might be best) was loaded to an in::memory sqlite database. Then we could just use sqlite syntax for doing table joins. These wouldn't be persistent unless we recorded them in the project file on save (which could be done) but they would be really fast to query and would provide robust left, and inner joining. Note, I'm specifically only talking about the attribute tables which should make it easier.

comment:14 Changed 11 years ago by borysiasty

Cc: borysiasty added

comment:15 in reply to:  13 Changed 11 years ago by mlennert

Replying to wildintellect:

It just occurred to me that I might know any easy(relatively) fast way to do this. If every vector layer attribute table when loaded, queried or joined (last 2 might be best) was loaded to an in::memory sqlite database. Then we could just use sqlite syntax for doing table joins. These wouldn't be persistent unless we recorded them in the project file on save (which could be done) but they would be really fast to query and would provide robust left, and inner joining. Note, I'm specifically only talking about the attribute tables which should make it easier.

Sounds interesting. But why limit this to attribute tables only ? I would think that the general use case is linking an attribute table to another table, not linking two attribute tables.

Moritz

comment:16 Changed 11 years ago by maplestar

Cc: maplestar added

comment:17 Changed 10 years ago by pcav

Milestone: Version 1.5.0Version 1.6.0

comment:18 Changed 10 years ago by pcav

Resolution: fixed
Status: newclosed

Table join now added in trunk. Please test.

comment:19 in reply to:  18 ; Changed 10 years ago by mlennert

Replying to pcav:

Table join now added in trunk. Please test.

I imagine that this is the Joins tab in the layer properties ?

A (very) quick test shows it works nicely, but we'll have to test it more thoroughly.

However, as I said in an earlier comment: what we are really looking for is the possibility of joining a layer attribute table to other data tables, not (only) to other layer attribute data tables. And IIUC, it is impossible at this stage to import a simple data table (e.g. dbf or csv) without geometries, and then join it to an existing layer attribute table, or ?

Moritz

comment:20 Changed 10 years ago by mhugent

It is possible to load geometryless tables. This includes dbfs and csvs via OGR (set Filter to 'all Files' in the dialog) as well as PostGIS tables without geometry (select the option to show geometryless tables in the postgres connection settings).

comment:21 in reply to:  20 ; Changed 10 years ago by mlennert

Resolution: fixed
Status: closedreopened

Replying to mhugent:

It is possible to load geometryless tables. This includes dbfs and csvs via OGR (set Filter to 'all Files' in the dialog) as well as PostGIS tables without geometry (select the option to show geometryless tables in the postgres connection settings).

Cool, never realised this was possible !

However, the join seems to fail once you try to map data:

1) Load a shape and a dbf file

2) Join the dbf to the shapefile attribute table

3) Go to symbology of shapefile and try to make graduated colors based on a joined attribute => all attribute values are set to NULL

Better than words: http://geog-pc40.ulb.ac.be/qgis/demo_join.gif. In the test, I use a modified version of the censusblk_swwake file from the grass book demo data: http://www.grassbook.org/data_menu3rd.php. I copied the censusblk_swwake.dbf to censusblk_data.dbf and erased most attributes from censusblk_swwake.dbf via OObase. I'll attach the files here.

Reopening the bug for now...

Moritz

comment:22 in reply to:  21 Changed 10 years ago by mlennert

Replying to mlennert:

I copied the censusblk_swwake.dbf to censusblk_data.dbf and erased most attributes from censusblk_swwake.dbf via OObase. I'll attach the files here.

Actually too big to attach, so here it is:

http://geog-pc40.ulb.ac.be/qgis/demo_data.zip

comment:23 in reply to:  19 Changed 10 years ago by lutra

However, as I said in an earlier comment: what we are really looking for is the possibility of joining a layer attribute table to other data tables, not (only) to other layer attribute data tables. And IIUC, it is impossible at this stage to import a simple data table (e.g. dbf or csv) without geometries, and then join it to an existing layer attribute table, or ?

I agree, the new Join implementation to e complete needs to allow Join also external data tables (dbf, as ftools already does, csv, etc.). It works already fine with geometryless postgres tables.

comment:24 in reply to:  20 Changed 10 years ago by lutra

Replying to mhugent:

It is possible to load geometryless tables. This includes dbfs and csvs via OGR (set Filter to 'all Files' in the dialog) as well as PostGIS tables without geometry (select the option to show geometryless tables in the postgres connection settings).

sorry, I didn't see this either (I'm browsing the trac mailing lists mails...).

comment:25 Changed 10 years ago by mhugent

Resolution: fixed
Status: reopenedclosed

Thank you for the good example data. The issue should be fixed with r15389.

comment:26 in reply to:  25 Changed 10 years ago by mlennert

Resolution: fixed
Status: closedreopened

Replying to mhugent:

Thank you for the good example data. The issue should be fixed with r15389.

Yes, great !

However, I've found another issue:

1) Load a shape and a dbf file (e.g. the attached demo data) 2) Join the two 3) Save the project 4) Close QGIS 5) Open QGIS 6) Load project

=> The join is still active. Perfect !

But:

1) Load a shape and a dbf file (e.g. the attached demo data) 2) Join the two 3) Map an indicator using Graduated symbol (e.g. Median age) 4) Save the project 5) Close QGIS 6) Open QGIS 7) Load project

=> "Handle bad layers" window opens, but I just can't get the shape file to load anymore

This only happens when I use the old symbology, not the new.

Reopening the bug as I believe that this is an integrate part of the feature that needs to work. Feel free to close again if you don't believe so.

Moritz

comment:27 Changed 10 years ago by mhugent

Resolution: fixed
Status: reopenedclosed

The old symbology issue should be fixed in r15450. Please reopen the ticket in case of further problems.

Note: See TracTickets for help on using tickets.