Search code examples
sqlpostgresqlpostgis

Count number of points (from two datasets) contained by neighbourhood polygons using PostGIS

I have three spatial datasets:

  1. Neighbourhood polygons
  2. Point locations of police stations
  3. Point location of crimes

enter image description here

I am using PostGIS.

The code I currently have is: 

SELECT 
nc.nbh_names,
count(ps), 
count(ci)
FROM public."Neighborhood_Clusters" AS nc
LEFT JOIN public."Police_Stations" AS ps
ON ST_Contains(nc.geom, ps.geom)
JOIN public."Crime_Incidents_in_2020" AS ci
ON ST_Contains(nc.geom, ci.geom)
GROUP BY nc.nbh_names;

The output I am getting:

enter image description here

I would like my output to be a view that looks like the following:

Column 1: Neighbourhood name | Column 2: Number of police stations | Column 3: Number of crimes

There should be no more than one or two police stations per neighbourhood.

It looks like for whatever reason, if there is a police station in the neighborhood, it counts and returns the number of crimes instead.

Can someone please help correct the code so that I get the desired result?

Solution

  • Spatial joins with counts involving multiple tables can get really tricky. What you can do instead is to use a subquery for each table, and then use ST_Contains making reference to the outer query, e.g. 

    SELECT 
   nc.nbh_names,
  (SELECT count(ps.geom) FROM public.Police_Stations ps 
   WHERE ST_Contains(nc.geom, ps.geom)) AS pol,
  (SELECT count(ci.geom) FROM public.Crime_Incidents_in_2020 ci
   WHERE ST_Contains(nc.geom, ci.geom)) AS cri
FROM public.Neighborhood_Clusters nc;

    Check this db<>fiddle.

    Further reading: