2

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';
2

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.

4
  • However, your query does work, and is perfectly valid! If you get no result rows, you two geoms don't touch.
    – geozelot
    Jul 29 at 8:57
  • Yes, I figured that when checking the spatial relation with your query. That returns false there too. Crappy input data ;)
    – Stophface
    Jul 29 at 9:24
  • @Stophface remember that these spatial relations are narrowly defined rules on the geometric predicates, e.g. ST_Touches may just check differently than expected. Digging into the DE-9IM and its application in the set of relationship functions helps tremendously in bug-tracking. Apart from that, coordinate inprecision is a common issue, introduced by the fact that a value can hold 15 digits of precision, and is subject to floating point rounding.
    – geozelot
    Jul 29 at 10:14
  • That DE-9IM is a valuable source. Thanks a lot for the hint!
    – Stophface
    Jul 29 at 11:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.