How to add information about other tables to a buffer view in Postgresql?

Question

So basically I'm creating a view of a buffer that fulfills some conditions, and this buffer needs to have data from point attributes in another table

This is my query that doesn't work:

CREATE OR REPLACE VIEW schema1.buffer_50_view AS 
SELECT a.siteid,
a.name,
sum(select count from schema2.point_table b where count >= 50 and 
st_dwithin(a.geom, b.geom, 100))  as sum_points,
st_buffer(p.geom, 100::double precision) AS buffer_50
FROM (select * from schema1.site_table p where site_progress = 100) a

it doesnt accept the sum line, and I have to add more lines like that, how to make it work?

Answer 1

Depending on how big your data are, you might want to do this as a materialized view and refresh it on a schedule rather than as a view.

SELECT a.site_id, ST_Buffer(a.geom, 100) AS geom, count
FROM (
  SELECT sites.site_id, sites.geom, count(*) as count
  FROM schema1.site_table sites
  JOIN schema2.point_table pts
  ON ST_DWithin(sites.geom, pts.geom, 100)
  AND sites.site_progress = 100
  GROUP BY sites.site_id, sites.geom
  ) a
WHERE a.count > 50;

Answer 2

This is what I would do:

  SELECT 
  a.siteid,
  a.name,
  a._sumpoints
 FROM ( SELECT schema1.site_table.*,
 (SELECT count (*) as count FROM schema2.pointtable WHERE 
 st_dwithin(a.geom, st_buffer(b.geom, 100))) as sum_points
 FROM schema1.site_table
 order by sum_points) a

Test this then add a filter clause filter (WHERE put your definition of the filter) filter1

  SELECT 
  a.siteid,
  a.name,
  a._sumpoints

  FROM ( SELECT schema1.site_table.*,
  (SELECT count (*) as count FILTER (WHERE count>= 50) filter1 FROM 
  schema2.pointtable WHERE st_dwithin(a.geom, st_buffer(b.geom, 100))) 
  as sum_points
  FROM schema1.site_table
  order by sum_points) a