SQL Geometry Query similar to Select by Centroid

[mpboyle]

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 trying to do this within SQL Server Management Studio, not ArcMap/ArcCatalog in order to produce a SQL view.

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!

[LanceShipman]

Take a look at the Geoprocessing Select By Location tool.  HAVE_THEIR_CENTER_IN should be what you want.

[mpboyle]

I'm trying to construct the query/view within SQL Server...not within ArcMap/ArcCatalog...should have mentioned that eariler.

My end goal is to have a SQL view comparing the geometries.

[VinceAngelo]

I would expect a polygon on polygon comparison of similar polygons to generate a
large "combinatorial expansion" issue -- If each shape slightly overlaps its neighbors
you could easily see an order of magnitude of expansion. 

I would also expect this query to be very inefficient, since there could be something on
the order of N-squared comparisons taking place (where N is the number of polygons). 
Casting one layer to point isn't  going to change the number of comparisons, but the
index will likely be more effective on the comparisons (provided you place the operators
in the right order to use the index). 

A query this ugly is better done once, with the resulting relationships saved in a table,
than forcing a view to do so much work with every query.

A Microsoft site would be a better location to discuss the particulars of the SQL-Server
implementation of geometry comparison with respect to this problem.

- V

[MarcoBoeringa]

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!

SQL Server has an STCentroid command. If you dynamically convert one of the two polygon layers to centroids, and than use that new layer as input for the STIntersects command in combination with the other polygon layer in the view definition, I think you are there...

Although I second Vince's remarks about possible performance issues, I think you may be fine if the layers don't exceed some 10000 records or so. I have run some tests with views containing STCentroid and STBuffer, and the performance was acceptable for layers up to about that size (Core I5 3GHz Desktop, locally installed SQL Server Express)

[mpboyle]

I can say the query below does what I was looking for, but as mentioned, the performance on a large dataset is poor.  When trying to execute on our entire parcel dataset (�??100,000 records) SQL Server (2008 R2) times out.  If executed on a smaller dataset, the results are as desired.

Thanks for the input from everyone!

(polygonA.Shape.STCentroid().STIntersects(polygonB.Shape) = 1)

[VinceAngelo]

This question was cross-posted to GSE -- How does you query run with 100k near-
duplicate polygons?

- V

[mpboyle]

I was the one that posted that question in GSE 🙂

As mentioned in above thread, the query performs poorly on large datasets with 2 sets of polygons.

I ran STIntersects on a similar sized set of features using our address point layer (�??90,000 records) and SQL Server 2008 R2 did not time out and executed the view.

[VinceAngelo]

Right.  Which is why you really need to be talking to Microsoft experts
about Geometry performance, or break the problem into subsets and
use a simple join based on a driver table.

- V