[Qgis-developer] table join

[Alex Mandel]

Barry Rowlingson wrote:
> 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

I had contemplated this a little recently along similar lines. SQLite
has a concept of IN-Memory databases. What if we created the procedure
to put joins/relates (Any sql relationship) into an in memory DB, that
way the procedure just needs to be re-run on project load. It would make
accessing the "joined" tables fast and allow querying against them.

The Arc model has some really good and some really annoying features,
particularly poor handling of many to many and the lack of a full SQL
operations as join operators.

Alex