Summarizing population by neighborhood using PostGIS

Question

I'm a novice with PostGIS and am currently working on a lab assignment where I have two geometric tables - one containing demographic data by blockgroup (blockgrp2010) while the other identifies various neighborhoods (nbo_hood). I'm trying to write a query that lists the population for each neighborhood. For now, I'm assuming that if the centroid of the blockgroup is inside the neighborhood, then all the population lies within that neighborhood. I drafted the query below:

select nbo_hood.name, blockgrp2010.pop10
from blockgrp2010
inner join nbo_hood on ST_Within ((ST_Centroid (blockgrp2010.geom)), nbo_hood.geom)
group by nbo_hood.name, nbo_hood.geom, blockgrp2010.pop10;

This returns a table listing the neighborhoods and corresponding population for each blockgroup. However, many of the neighborhoods are listed under multiple rows with a different population assigned (obviously because there are multiple blockgroups within the neighborhood).

How do I modify the above query (or should I start from scratch) to list each neighborhood only once with the population sum of all blockgroups?

I've tried variations of the SUM function with the SELECT statement but nothing as worked.

Answer 1

select nbo_hood.name, sum(blockgrp2010.pop10)
from blockgrp2010
inner join nbo_hood on ST_Within ((ST_Centroid (blockgrp2010.geom)), nbo_hood.geom)
group by nbo_hood.name;

If you want to list each neighborhood once, then only include a unique identifier for neighborhood (in this case, nbo_hood.name). By including the block group population the GROUP BY clause, you guarantee that the result will be summarized by each neighborhood-block group combination, neighborhood name appears multiple times in your result. (nb_hood.geom will not change the resultset, but is unnecessary.)

Once you've done that, the sum() function will work as you expect it to.

I would recommend reviewing the PostgreSQL documentation, particularly the introductory text on Aggregate Functions and the documentation on the GROUP BY clause.