One shapefile has data -- Census demographics population per census tract. But we want to use another type of shape -- police district boundaries -- and count how many people live within those districts.
This requires using a spatial join to associate all the little shapes (Census tracts) with their containing shape (police district)
The police districts data table, which has only shape data and no population info:
https://dun2.carto.com/tables/police_districts
It looks like this when mapped:
The Census tracts data table, which contains shape data AND population info:
https://dun2.carto.com/dataset/census_tracts
Which looks like this:
When these shapefiles are joined, using query.sql (included below), we get a table of police districts (9 rows) and the aggregated demographic/pop data of their respective census tracts.
Link to Carto dataset that results from this: https://dun2.carto.com/dataset/bpd_districts_and_census
Note that it contains no geospatial data, it's just a table of census numbers, but there is a field for district_name and district_number,
which means you can join this table (bpd_districts_and_census) with the police_districts
(which does have shapefile info for the district boundaries) to make a proper map.
The JOIN condition:
FROM
census_tracts AS ct
INNER JOIN
police_districts AS pd
ON ST_CONTAINS(
pd.the_geom,
ST_Centroid(ct.the_geom)
)
This works by reducing all the Census Tracts to just their centroids, i.e. a single point, which makes it much easier for them to be "inside" or "contained" by the geometry of the respective police district.
You can read about the ST_CENTROID method here: https://postgis.net/docs/ST_Centroid.html
The reason why ST_CONTAINS (or ST_WITHIN) doesn't work with shapefiles is because the boundaries of the census tracts may not exactly fall within or overlap the containing police district:
FROM
census_tracts AS ct
INNER JOIN
police_districts AS pd
ON ST_CONTAINS(
pd.the_geom,
ct.the_geom
)
The above JOIN gets you something like this (the purple shapes represent the only tracts that are properly joined):