3

I have a table of 40 polygons, they contain name, id and the geometry. I have a second table of addresses with it's own geometry represented as points. I have aggregated the 40 polygons using geometry::UnionAggregate.

I want to be able to return the id of the polygon for one point in particular from my table of addresses. The following is my code but I get a true value (1) for each of the 40 polygons. I was expecting 39 nulls and '1' for the polygon that actually contains the point. Is this because it is now treating the aggregated polygons as one entity now so therefore they all contain the point?

I am new to spatial queries and may be missing something blindingly obvious but would appreciate some help.

declare @n geometry
set @n = (select geometry::UnionAggregate(sp_geometry) from Polygons)
declare @p geometry
set @p = (select sp_geometry from PointData);

select n.id from Polygons n, pointdata p
where @n.MakeValid().STWithin(@p) = 1
kvorobiev
  • 5,012
  • 4
  • 29
  • 35
SSingh
  • 153
  • 3
  • 13

1 Answers1

2

The UnionAggregate will be one polygon that is the union of all of the polygons in your table (do @n.ToString() and prove it to yourself). As such, you've lost the fidelity of which polygon(s) intersect with your point. You need to test against the individual polygons. I'd do it like this:

select *
from dbo.Polygons as poly
join dbo.PointData as point
   on poly.STIntersectects(point.sp_geometry) = 1;
Ben Thul
  • 31,080
  • 4
  • 45
  • 68