ST_Disjoint() gives unexpected result using PostGIS

Question

I am fiddling around with this dataset http://s3.cleverelephant.ca/postgis-workshop-2020.zip. It is used in this workshop http://postgis.net/workshops/postgis-intro/spatial_relationships.html.

I want to identify all the census blocks, that do not have a subway station. I thought this spatial join is rather straight forward

SELECT
  census.boroname,
  census.geom
FROM nyc_census_blocks AS census
    JOIN nyc_subway_stations AS subway
        ON ST_Disjoint(census.geom, subway.geom);

However, the result set is way too large.

Why can't I identify all the census blocks that do not have a subway station?

I want to use ST_Disjoint() and not ST_Intersects()

Answer 1

You are comparing each station to each census block and for each one returning all but one census block.

To fix this you need to union your stations together and then find the disjoint polygons.

SELECT
  census.boroname,
  census.geom
FROM nyc_census_blocks AS census
    JOIN (select st_union(geom) as geom from nyc_subway_stations) subway
        ON ST_Disjoint(census.geom, subway.geom);