very poor cardinality estimate on a geometry intersection

I'm intersecting a point with a set of polygons. The query is indexed, and the polygons do not overlap but the query plan seems to think that instead of 1 row, i'll return 18k rows, and this results in a bad query plan.

In particular the right most nodes of the query plan seem to think that the STPointFromText function will return a cardinality of 1000, and that the intersection of this point set with the geometry index returns 30% of the 54k rows.
(ran 1 million points through the table without finding a counter-example that actually returned more than 1 row)

The result isn't horrible in this abbreviated query, but when i join the output of this onto anything else, the high cardinality estimate forces the upstream table to be a tablescan+hashmap, even though the overall query returns 1 row. This extended query is running a few times per second, so i'm wondering how i can optimise this.

The spatial index is HHHH, for a highest resolution (over the approx 4000km longest side of the domain) of approx 80x50m, there are 56k polygons in the index, with expected minimum size of ~100m.

Note the difference between the est rows and the actual.

Estimated query plan.

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...

StackExchange Database Administrators Q#91470, answer score: 7