very poor cardinality estimate on a geometry intersection
Sounds like you want to get rowgoals to play their part on the query - so try using TOP(1), maybe with testing to avoid NULLs (in case of non-matching SRIDs). That way you can get the "nearest neighbour" functionality to kick in. I know you're using Contains, but you want to use a method that tells the QO that you're only going to get a single row back.

http://blogs.lobsterpot.com.au/2014/08/14/sql-spatial-getting-nearest-calculations-working-properly/ might have a few tips...