I have a layer defined by a complex chunk of SQL which builds a multiline string from a bunch of single lines. This works fine. I now need to join this to a table that has all the meta data for these new lines but I could not figure out how to do the SQL.
I ended up creating a view with the name and the multigeom and performed the join on this. That works fine in PostGIS but when I try and load them in QGIS I get errors on the views because QGIS can not figure out which was the geometry column. These I can 'fix' temporarily in the 'load PostGIS layer' dialogue which tells me exactly what is wrong but I have to do this every time I attach to the data base. The DB Manager just flags the table as bad.
Is there a way to fix this once in the view? I rather like using the view as it makes it much clearer what is going on with the SQL.
For the record here is the SQL that defines the view:
SELECT f.part_of, st_multi(st_collect(f.the_geom)) AS multigeom FROM (SELECT line_master.part_of, line_master.type, (st_dump(line_master.the_geom)).geom AS the_geom FROM base.line_master) f WHERE ((f.part_of IS NOT NULL) AND ((f.type)::text = 'track seg'::text)) GROUP BY f.part_of;
Alternatively I need to define the layer in one query by joining this to a table of meta data with columns like name, description, etc by matching part_of and name.