[Qgis-developer] table join

[Barry Rowlingson]

On Sun, Nov 29, 2009 at 7:07 AM, Paolo Cavallini <cavallini at faunalia.it> wrote:
> Hi all.
> There is an user willing to donate some money to solve the long standing table join
> issue:
> https://trac.osgeo.org/qgis/ticket/121
> Is there a dev willing to take this, and other users willing to donate some more to
> help speeding things up?
> In case, please contact me.

(ok if I post!?)

This has been nagging me since I saw the post, so I think I'll reply
here. I'm not sure why it bothers me, possibly because Arc/Info could
do this kind of thing waaay back when. I once connected a line
coverage with an Ingres database, in a dynamic relate. I can't
remember the syntax now...

 Anyway, the logical process is that of connecting a feature with a
row-set of attributes via a relate. The problem with doing something
like this in Qgis is that attributes appear in a lot of places, and
making related attributes available in those places could be a lot of
work. Labelling, symbology, selection, querying, saving and loading,
attribute data, metadata etc. Not impossible, but a big job. And some
API changes.

 I guess things would work like this. You load in a vector map layer.
Then you choose a 'relate' option for that layer (a new tab in the
layer properties dialog) and create a new relation. The relation
definition would have a name, the source map layer, the related table,
the joining mechanism (which could be a column name from the map and
one from the related table) and options for handling the join (what to
do if there's no related item). The related table could potentially be
any tabular data source (DB, file, map layer attributes).

 Let's make a concrete example. I have a UK county polygon map with
id, name, area attributes. I have a MySQL DB table of county name and
population. I have a CSV file of Europe-wide workforce numbers broken
down by age and sex together with county ids for UK counties.

 I'd create one relate called 'pop' that linked the polygon map to the
MySQL DB via the county name fields, and another called 'workforce'
that linked the polygon map to the CSV file via the county id fields.
I tell it to fail if there's counties with no matching entries in the
related tables, but luckily everything is completely matched. The
extra non-UK entries in the workforce CSV aren't an issue because my
map is only UK.

 I have a vague memory that Arc/info used % signs somewhere in
relates, so perhaps to get attribute 'foo' from relate 'bar' we could
have bar%foo in dialogs? Then a symbology drop-down would have, along
with the attributes of the UK shapefile mentioned above, also have
entries for the relates. So something like: workforce%M16-20,
workforce%M21-30 and so on. My problem with this is that % is probably
a valid character in attribute names and will break stuff. Seems like
a kludge.

 Is that the kind of thing your donor is willing to stump up for? Of
course if we all use Sqlite and PostGIS we can do all this in the DB
with VIEWS. So my real suggestion is 'Do it all in the DB with VIEWS'.

Barry