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.

• Luffydude: Derp I forgot about the st_dwithin. You're right about the collect as well, can't double or triple count those points. Thank you so much you're a SQL hero!! The buffer geom would just be a visual aid so that they can see what their lines are collecting. I'll need to reread your previous answer, I initially thought views were unupdatable
• geozelot: @Luffydude updateable Views are mighty, but a little tricky to set up. there are more options to avoid duplication, e.g. join the line layer and the View (without geometry) in QGIS or add those sum columns to the line layer and update them whenever sth. changes (again with a trigger). in this particular case it's rather a matter of taste than must-dos I guess, but in many DB designs (statistical) aggregations are commonly outsourced into e.g. Views, to avoid physically storing highly dynamic data (amongst other and more practical reasons).
• Luffydude: very interesting, will definitely give it a go! Just one more thing regarding this view. When I try to join a 3rd layer (another point layer), the query just takes forever to load. If I remove my 2nd layer and keep the 3rd one it works fine. I'm wondering if there's anything in the query that prevents joining both point layers at the same time
• geozelot: @Luffydude index? ...I think that might be suitable for another question; that query should work with more joins, but, as always, it depends; on table size, type of join, join syntax and so on...and a look at the EXPLAIN ANALYZE <query> is very useful.