topic Spatial view using feature class and table, by joining and applying an aggregate function. in Geodatabase Questions

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

GeneSipes1 — Thu, 09 Mar 2017 22:59:05 GMT

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.

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

JoshuaBixby — Fri, 10 Mar 2017 00:16:40 GMT

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.

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

Asrujit_SenGupta — Fri, 10 Mar 2017 21:30:52 GMT

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.