Spatial view using feature class and table, by joining and applying an aggregate function.

586
2
03-09-2017 02:59 PM
Highlighted
New Contributor III

I have a feature class and a stand alone table, that can be related one to many. I am trying to create a spatial view by joining the two, and applying a count aggregate function. I keep getting errors having to to with the Geometry field in the feature class table, not working with the group by clause. Here is an example. 

SELECT FC.MAXIMOID_PF,FC.Shape,FC.OBJECTID,COUNT(TBL.MAXIMOID),TBL.DATE_
FROM trCenterline as FC
LEFT OUTER JOIN trStreetSweeps as TBL
ON FC.MAXIMOID_PF = TBL.MAXIMOID
GROUP BY FC.OBJECTID,FC.MAXIMOID_PF,FC.Shape,FC.ObjectID,TBL.MAXIMOID,TBL.DATE_

Here is the error....

I am running ArcMap 10.3 and I am trying to query an SDE database. 

Tags (3)
Reply
0 Kudos
2 Replies
Highlighted
MVP Esteemed Contributor

It isn't working because SQL Server Geometry or Geography types cannot be used in a GROUP BY clause.  What version of SQL Server are you using?  If 2012 or higher, you can use Static Aggregate Geometry Methods to incorporate "grouped" spatial data into query results. 

Highlighted
MVP Regular Contributor

Before directly creating Views using ArcGIS Desktop, try creating the view at the Database end, make sure it gives the desired result there...then create it using ArcGIS Desktop if needed.