1

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!

Improve this question
3
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).

Improve this answer
4
  • I tried the first solution you mentioned but that seems to be giving me many more records than expected. We have around 100,000 parcels and when I execute the query, I'm getting 200,000+ records and counting. SELECT TOP (100) PERCENT ISNULL(reParcel.GPNNUM, N'') AS GPN, { fn UCASE(CASE WHEN elcCouncilDistrict.CITYCOUNCIL = '' THEN NULL ELSE RTRIM(LTRIM(elcCouncilDistrict.CITYCOUNCIL)) END) } AS CouncilDistrict FROM dbo.ELCCOUNCILDISTRICT AS elcCouncilDistrict CROSS JOIN dbo.REPARCEL AS reParcel WHERE (ISNULL(reParcel.GPNNUM, N'') <> N'') ORDER BY GPN – boyle.matt May 14 '14 at 21:20
  • you might have to throw a 'distinct' in there somewhere... we've been using this with parcel data... BIG dataset... and its been working. Let me know if you want to chat offline about this to see if we can resolve it... – DPSSpatial_BoycottingGISSE May 14 '14 at 21:23
  • we can chat offline to hopefully resolve, this would be a bonus to get this working. – boyle.matt May 14 '14 at 21:29
  • This seems to be working... polygonA.Shape.STCentroid().STIntersects(polygonB.Shape) = 1) ... however when I execute the query I get a timeout error. If I run the query on select parcels, then it returns what appear to be correct values. There are approximately 100,000 parcels, ideas why I'm getting a timeout error or ways to fix the timeout? – boyle.matt May 14 '14 at 22:15

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.