AnsweredAssumed Answered

sql geometry group by

Question asked by ufeliciano on Nov 14, 2014
Latest reply on Nov 26, 2014 by bixb0012

2 issues...

 

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...

 

Shape
MunicipalitiesCount
xxxmunicipality15
xxxmunicipality245
xxxmunicipality395

 

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.

 

Thanks

Outcomes