5

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

6

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:-) – geonerd May 7 '17 at 18:44
2

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.

1

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 LIMITed 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)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.