topic Re: SQL Geometry Query similar to Select by Centroid in Data Management Questions

SQL Geometry Query similar to Select by Centroid

mpboyle — Wed, 14 May 2014 19:16:56 GMT

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!

Re: SQL Geometry Query similar to Select by Centroid

LanceShipman — Wed, 14 May 2014 19:25:47 GMT

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

Re: SQL Geometry Query similar to Select by Centroid

mpboyle — Wed, 14 May 2014 19:28:18 GMT

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.

Re: SQL Geometry Query similar to Select by Centroid

VinceAngelo — Wed, 14 May 2014 22:26:20 GMT

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

Re: SQL Geometry Query similar to Select by Centroid

MarcoBoeringa — Thu, 15 May 2014 07:37:27 GMT

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)

Re: SQL Geometry Query similar to Select by Centroid

mpboyle — Thu, 15 May 2014 11:50:30 GMT

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)

Re: SQL Geometry Query similar to Select by Centroid

VinceAngelo — Thu, 15 May 2014 11:54:34 GMT

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

- V

Re: SQL Geometry Query similar to Select by Centroid

mpboyle — Thu, 15 May 2014 11:57:11 GMT

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.

Re: SQL Geometry Query similar to Select by Centroid

VinceAngelo — Thu, 15 May 2014 12:10:31 GMT

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