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.

  • I have managed to work around this by doing the join in the layer definition using the DB Manager. I.e. instead of creating a view "Tracks" that did the join between the view with the geom and the table with meta data I used the DB Manager sql window to do the join and then load the table. Interestingly when I "create view" it won't let me load the view. Which comes back to the original assertion that information must be getting lost somewhere – Russell Fulton Jul 9 '17 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.