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 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..?