# avg dist between points within polygons

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

The issue is that line 9 only knows FOO. It does not have access to p, t1, or t2 anymore. Try:

``````DISTANCES AS (
SELECT FOO.cell, FOO.geom <-> FOO.other_geometry 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)
``````
• I see how that works, thanks. I got it to work but it crashed my computer:-) May 7, 2017 at 18:44

Thanks Micky. I actually ended up with something simpler once I realized that the district zone # was in all of the tables, that eliminated the need to do a spatial limit by the district polygon. I ended up with:

``````SELECT DISTINCT t2.gid, name, t2.cell as school_cell,
avg(ST_DISTANCE (t1.geom, t2.geom))
OVER (PARTITION BY name) AS dist_avg_m
FROM n.table1 t1, n.table2 t2
WHERE t1.cell = t2.cell
ORDER BY t2.cell
``````

but these are great suggestions and I now have them in my toolbox.

I feel that you have over complicated your query for what you appear to want as a result and as a consequence it will not give you the result you are after.

I created a small test set of data to run your query (incorporating @underdarks fix) over to work out what it was doing and the results where quite strange.

Your query returned less rows, lower counts and what appears to the minimum distance for the small set of test data I generated. I think this is due to the `LIMIT`ed subquery and some other bits and pieces.

Rather than trying to fix your query, the following simple query should give you the result that you want.

``````SELECT d.cell,
count(*) num,
round(avg(s.geom <-> b.geom)::NUMERIC ,1) avg_dist
FROM mesa.schldist_cellpop AS d  -- For each district
INNER JOIN mesa.schools2 AS s   ON ST_INTERSECTS(d.geom, s.geom) -- join to the school
INNER JOIN mesa.school_pop AS b ON ST_INTERSECTS(d.geom, b.geom) -- and the buildings
GROUP BY d.cell
``````

I've used intersects rather than contains because if you have buildings on the boundary I though it would be better to include it in the averages on both sides rather than not at all. If you have multiple schools in a district you will need to change the query to deal with that in an appropriate manner.

The sample data I used was generated by the follow CTE statements

``````-- Some Sample data to work on
WITH schools2 AS (
-- three school points A B and C.  One for each district
SELECT *
FROM (VALUES
(1,'A', 'POINT(5 5)'::Geometry),
(2,'B', 'POINT(15 5)'::Geometry),
(3,'C', 'POINT(5 15)'::Geometry)
) S(gid,name, geom)
),
school_pop AS (
-- some random points as buildings.  Could be polygons, but points will do for the example
SELECT ('POINT(' || (random()*20) || ' ' || (random()*20)||  ')')::Geometry AS geom
FROM generate_series(1,20) B(geom)
),
schldist_cellpop AS (
-- Three districts each with a school.  2 small, 1 large
SELECT *
FROM (VALUES
(1,'D1','POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'::Geometry),
(2,'D2','POLYGON((10 0, 20 0, 20 20, 10 20, 10 0))'::Geometry),
(3,'D3','POLYGON((0 10, 10 10, 10 20, 0 20, 0 10))'::Geometry)
) D(gid,cell,geom)
)
``````