SQL Geometry Query similar to Select by Centroid

Question

Does anyone know if it's possible to query SQL geometry by something like 'centroid of polygon'...similar to ArcMap's select by location?

I'm using SQL Server 2008 R2 as the DBMS.

I have two polygon layers that are not exactly coincident and if I use STIntersects on the target polygon I get two different values where the polygons are not coincident. I was wondering if there is a query that can be used instead of STIntersects that would be similar to selecting the centroid of the polygon...?

Thanks in advance!

Answer 1

In SQL Server I use:

 select * from polygonA, polygonB
where
    polygonA.shape.STIntersects.STCentroid().shape(polygonB.shape) = 1)

Which is basically converting PolygonA to points, and intersecting them with PolygonB

But I've also found luck with

 select * from polygonA, polygonB
where
    polygonA.shape.STWithin(polygonB.shape) = 1)

...and that works when you want to use the equivalent of the CONTAINS_CLEMENTINI method of the ArcGIS Select by Location tool (ie. you have odd shaped polygons where the centroid would fall OUTSIDE itself when converted to point).