Buffer view that calculates numbers from a different layer

Question

Basically people draw lines and from these lines, I need to have a view that will create a buffer that will calculate potential revenue from those lines

mylinetable is a table with a portfolio_id and a line geom

mypointtable is a table with portfolio_id, units and a point geom

I've read and tried to adapt it for my code with this question but no luck so far How to create dissolved buffers in QGIS with PostgreSQL/PostGIS?

My current code (not working)

 CREATE or replace view myview AS
SELECT 
a.path[1] as gid,
sum(b.units) filter (where b.portfolio_id = a.portfolio_id) as same_portfolio,
sum(b.units) as total,
a.portfolio_id,
a.geom::geometry(Polygon, 27700) as geom 
FROM
(SELECT portfolio_id,
 (ST_Dump(ST_UNION(ST_Buffer(geom, 250)))).* as geom
FROM mylinetable group by portfolio_id
) as a left join mypointtable b on st_contains(a.geom, b.geom)
group by a.portfolio_id

The intended outcome is to sum all the units from each point from the line, and give people a visual output of the lines they are drawing on QGIS.

Both tables have a portfolio_id, I need a sum of units when portfolio_ids match with the lines and a different column of simple totals

This view needs to be loadable on QGIS that's why I'm adding the path for an id.the row number could also work

Am I missing something or..?

Answer 1

A simpler and much more performant version of your base query could be (using ST_DWithin for proximity search):

SELECT  ROW_NUMBER() OVER() AS gid,
        a.portfolio_id,
        SUM(b.units) FILTER (WHERE a.portfolio_id = b.portfolio_id) AS same_portfolios,
        SUM(b.units) AS total,
        a.geom                         -- ST_Buffer(a.geom, 250) AS geom
FROM    mylinetable AS a
JOIN    mypointtable AS b
  ON    ST_DWithin(a.geom, b.geom, 250)
GROUP BY
        a.portfolio_id, a.geom
;

Initially, there are no buffers, but from what you write I don't see where you actually need them? Nevertheless, I included the line geometry instead and added the buffer creation just for reference.

If you are concerned about points counted multiple times (e.g. if multiple lines per portfolio_id are within the threshold), use:

SELECT  ROW_NUMBER() OVER() AS gid,
        a.portfolio_id,
        SUM(b.units) FILTER (WHERE a.portfolio_id = b.portfolio_id) AS same_portfolios,
        SUM(b.units) AS total,
        a.geom                         -- ST_Buffer(a.geom, 250) AS geom
FROM    (
    SELECT  portfolio_id,
            ST_Collect(geom) AS geom   -- ST_Union(a.geom); only if you really need dissolved buffers in the output
    FROM    mylinetable
    GROUP BY
            portfolio_id
) AS a
JOIN    points AS b
  ON    ST_DWithin(a.geom, b.geom, 250)
GROUP BY
        a.portfolio_id, a.geom
;

---

You could create a View from that; you can have it sitting there as an additional layer, with additional (identical) linestrings, to the base layer and it would actually be up to date as soon as you refresh the QGIS canvas.

A more elegant way, however, could be to have an updatable View in place of the base layer itself (i.e. mylinetable), with a similar concept to the one I gave as an example to one of your former questions; people would work on that, where every new record created (or updated) during editing would be rerouted to the base table, and the View gets refreshed with all the info you need.