I have a table of points (schools t1) and a table of small polygons (buildings t2). They are within large polygons (p) that represent districts. I am trying to get the average distance from a school to all of the buildings withing the same district. I am really new at this stuff and found a post that seemed to have the answer but I can't quite get it. What am I doing wrong?
WITH SCHOOLS AS ( SELECT p.gid, p.cell, t1.name, code2, pub_pri2, enrollment, t1.geom as wkb_geometry FROM mesa.schools2 t1 LEFT JOIN mesa.schldist_cellpop p ON ST_CONTAINS (p.geom, t1.geom) ORDER BY p.gid), DISTANCES AS ( SELECT p.cell, t1.geom <-> t2.geom as dist FROM (SELECT distinct on(t1.gid) t1.gid, p.cell, t1.geom, (SELECT t2.geom FROM mesa.school_pop t2 ORDER BY t2.geom<->B.wkb_geometry LIMIT 1) AS other_geometry FROM mesa.schldist_cellpop p, SCHOOLS B, mesa.school_pop t2, mesa.schools2 t1) AS FOO) SELECT distinct p.cell, count(p.cell) over(partition by p.cell), round(avg(distance) over(partition by p.cell)::NUMERIC, 1) FROM DISTANCES WHERE DISTANCES IS NOT NULL
The error I am getting is ERROR: missing FROM-clause entry for table "p" LINE 9: SELECT p.cell, t1.geom <-> t2.geom as dist FROM