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

Answer 2

Joining on non-intersection is tricky. An INNER JOIN gathers a set of all matching rows between the tables; in this case, a single row in census matches with every row in subway that has an ST_Disjoint geometry!

In fact, it is better to tackle this the other way around, using ST_Intersects and an EXISTS condition:

SELECT census.boroname,
       census.geom
FROM   nyc_census_blocks AS census
WHERE  NOT EXISTS (
  SELECT 1
  FROM   nyc_subway_stations AS subway
  WHERE  ST_Intersects(census.geom, subway.geom)
);

---

Alternatively, you could SELECT DISTINCT, but the JOIN is still highly inefficient.