Multibuffer in Postgis to view in QGIS

Question

I want to create a multibuffer view based on a point view in Postgis AND view the result in QGIS.

The points are in a view called "location"

1. To create the multibuffer I create a sequence in Postgis "multibuffer_vid_seq". This will be used to have unique ID.

2. Creating the multibuffer

    SELECT nextval('multibuffer_vid_seq'::regclass) AS bid,
     loc.id, 
     st_buffer(loc.geom, 100::double precision) AS geom 
    FROM location loc 
    GROUP BY loc.id, loc.geom 
   UNION ALL 
    SELECT nextval('multibuffer_vid_seq'::regclass) AS bid,
     loc.id, 
     st_difference(st_union(st_buffer(loc.geom, 500::double precision)), 
     st_buffer(loc.geom, 100::double precision)) AS geom 
    FROM location loc 
    GROUP BY loc.id, loc.geom 
   UNION ALL 
    SELECT nextval('multibuffer_vid_seq'::regclass) AS bid,
     loc.id, 
     st_difference(st_union(st_buffer(loc.geom, 1000::double precision)), 
     st_buffer(loc.geom, 500::double precision)) AS geom 
    FROM location loc 
    GROUP BY loc.id, loc.geom;
   

This seems to work fine: I end up with a view having a unique ID, the location ID and the multibuffer geom named geom.

QGIS sees the layer and I can bring it in the layer panel. However no geometry show up. It is empty.

I don't understand why so as doing a select in Postgis does return some proper records with a geom.

Answer 1

Using an ever changing ID is a bad idea. QGIS will query the DB several times, fetching sometimes just the ID, sometimes the entire row corresponding to the chosen IDs, sometimes ID+geom. Since the retrieved ID doesn't exist the next time you query the DB, nothing is returned.

Your alternative here is to use a different primary key whose values will always correspond to the same row. One option is to use the row number - provided the fields you supply in the sort order can uniquely identify each record. Another option is to change your view for a materialized view