1

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.

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

  • I see, thanks @JGH . I will try using a materialized view since the row number is not unique in the multibuffer view. – Blue Oct 17 '17 at 0:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.