Checking if boundaries of two geometries touch using PostGIS

postgispostgresqlspatial-join

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 have two geometries in the same table nyc_neighborhoods.
I want to know if the nyc_neighborhoods geometry with the name Tremont touches the nyc_neighborhoods geometry with the name South Bronx.

How would I do that with the ST_Touches() Function?

SELECT * FROM nyc_neighborhoods AS neighbor1
JOIN nyc_neighborhoods AS neighbor2
ON ST_Touches(neighbor1.geom, neighbor2.geom)
WHERE neighbor1.name = 'Tremont' AND neighbor2.name = 'South Bronx';

Best Answer

Simply move the function into the SELECT - it returns a BOOLEAN:

SELECT ST_Touches(neighbor1.geom, neighbor2.geom) AS touches
FROM   nyc_neighborhoods AS neighbor1
CROSS JOIN    -- verbose but preferred, you could use a , <comma> here
       nyc_neighborhoods AS neighbor2
WHERE  neighbor1.name = 'Tremont' AND neighbor2.name = 'South Bronx'
;

A plain CROSS JOIN between tables is a delicate business as it returns a cartesian product between them - here however, the explicit filter conditions limit the compared set to one row each.

Related Question