1) I have 2 polygon feature classes in my SQL Server database. I want to know how many of our small polygons (parcels) are contain in the bigger polygon (municipalities) based on the centroid, but the result I want is a spatial view I can add graphically to my map. The desired map would be something like...
The problem is that I don't know how to create the spatial view to add to the map because I received the error message...
"The type "geometry" is not comparable. It cannot be used in the GROUP BY clause."
when running the statement...
select b.shape,COUNT(a.shape) as shape
from admin.PARCEL a, admin.MUNICIP b
where b.SHAPE.STContains(a.Shape.STCentroid())=1 and a.PIN like '328-02%'
group by b.shape;
Tried the STUnion approach but the result is redundant geometries (municipality geometry for each parcel polygon it contains).
2) Our second concern is that we want to create an indexed view to test the statement above when succesful, and from what I understand the statement can't use subqueries to get created. I ran the group by statement without the shape and it took almost 17 hours, so we want to see if any performance improvement is detected. The parcel feature class is update daily by syncronizing a replica.
Any ideas or suggestions are welcome.