ST_Contains Aggregation Problem I am using the postgis function ST_Contains to find all of the census blocks in different counties, the counties have been split by congressional district boundaries. I am trying to find the population of each county part that has been split. I used this sql statement...

  SELECT cp.state, cp.countyfp, cp.county, cp.cd, sum(bd.pop00)
  FROM countyparts2008_split_v2 AS cp
  JOIN us_block_2000_with_data AS bd
    ON ST_Contains(cp.geom, bd.geom)
    AND cp.state = '01'
  GROUP BY cp.state, cp.countyfp, cp.county, cp.cd


and i get this result

"01";"115";"St. Clair";"04";60306
"01";"125";"Tuscaloosa";"06";161992
"01";"125";"Tuscaloosa";"07";161992
"01";"115";"St. Clair";"03";60306
"01";"115";"St. Clair";"06";60306
"01";"073";"Jefferson";"04";647119
"01";"025";"Clarke";"01";27178
"01";"025";"Clarke";"07";27178
"01";"037";"Coosa";"02";10411
"01";"101";"Montgomery";"03";221731
"01";"103";"Morgan";"04";108021
"01";"107";"Pickens";"04";20143
"01";"103";"Morgan";"05";108021
"01";"125";"Tuscaloosa";"04";161992
"01";"037";"Coosa";"06";10411
"01";"107";"Pickens";"01";20143
"01";"107";"Pickens";"06";20143
"01";"107";"Pickens";"07";20143
"01";"107";"Pickens";"03";20143
"01";"037";"Coosa";"03";10411
"01";"073";"Jefferson";"06";647119
"01";"073";"Jefferson";"07";647119
"01";"101";"Montgomery";"02";221731


The numbers for each county part should be different, so for example each St. Clair entry should have a different population total (the last column). I figure I am aggregating it wrong or something, but not sure exactly.

I also tried this sql query

SELECT cp.state, 
       cp.countyfp,
       cp.cd, 
       (SELECT SUM(pop00) 
          FROM us_block_2000_with_data AS bd
         WHERE ST_Contains(cp.geom, bd.geom)
         GROUP BY cp.cd, countyfp, state)
  FROM countyparts2008_split_v2 AS cp
 WHERE cp.state = '01'
It's difficult without the data but let's recreate a mental experiment.

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
 ( 1, 2, true ),
 ( 1, 3, true ),
 ( 1, 4, false)
) AS t(cp, bd, contains);


Now... What happens if I join this table to itself on condition that returns true more than once.

SELECT *
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 contains | cp | bd | cp | bd 
----------+----+----+----+----
 t        |  1 |  2 |  1 |  3
 t        |  1 |  2 |  1 |  2
 t        |  1 |  3 |  1 |  3
 t        |  1 |  3 |  1 |  2
 f        |  1 |  4 |  1 |  4
(5 rows)


Now this may make perfect sense in this form, but if you conceal a table from the result..

SELECT f1.*
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 cp | bd | contains 
----+----+----------
  1 |  2 | t
  1 |  2 | t
  1 |  3 | t
  1 |  3 | t
  1 |  4 | f


Now you're going to be confused. So the problem here is that you have multiple cases where one cp.geom contains numerous bd.geoms

Now how does that work if you add a GROUP BY..

SELECT f1.*, sum (f2.bd)
FROM foo AS f1
JOIN foo AS f2
  USING (contains)
GROUP BY f1.cp, f1.bd, f1.contains
 cp | bd | contains | sum 
----+----+----------+-----
  1 |  3 | t        |   5
  1 |  2 | t        |   5
  1 |  4 | f        |   4


And, that's precisely your problem. Here you're getting a sum() of 5. Which is understood given the above tables that produced that.

So if you want to debug this check the condition ST_Contains(cp.geom, bd.geom) output cp.geom and bd.geom using ST_AsText()