0

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'
1
  • try it without aggregate. my ques is that you get same rows in "[INNER] JOIN ST_Contains() ". Other way to say it , cp.geom is same even if cp.cd changes (or if they are different it still return same bd.rows) – simpleuser008 Nov 1 '16 at 11:05
0

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

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.