Spatial Database View giving draw error in ArcMap 10.4

[ForrestChevaillier1]

I am working with MS SQL Server 2012 and ArcMap 10.4.

I'm a bit of newbie to SQL so please bear with me.

I have created a view in my database using the following query:

CREATE VIEW dbo.intermediate

AS

SELECT MIN(pa.OBJECTID) OBJECTID, MIN(pa.INT_NAME) INT_NAME,

MIN(pa.INT_NAME) + ' INT' AS CAMPUS, MIN(pa.ISD) ISD,

geometry::UnionAggregate(pa.Shape) SHAPE

FROM dbo.pa_evw as pa

GROUP BY pa.ISD, pa.INT_NAME

Where 'dbo.pa_evw' is an archived view of a polygon layer with a category field called INT_NAME.

The query runs without an error in SQL Server Management Studio and the spatial results look how I would expect them to. However, when I bring the layer into ArcMap, I get a drawing error that says there is a "Shape integrity error."

Can anyone point me in the right direction?

[ChristianWells]

Although the query is successful in SMSS it seems that ArcMap is getting caught on the render part of the query, not the query itself. You could consider running an STIsValid() function in SMSS to see if any shapes are invalid:

CREATE VIEW dbo.intermediate
AS
SELECT MIN(pa.OBJECTID) OBJECTID, MIN(pa.INT_NAME) INT_NAME,
MIN(pa.INT_NAME) + ' INT' AS CAMPUS, MIN(pa.ISD) ISD,
geometry::UnionAggregate(pa.Shape) SHAPE,
geometry::UnionAggregate(pa.shape).STIsValid() as validate
FROM dbo.pa_evw as pa
GROUP BY pa.ISD, pa.INT_NAME

STIsValid (geometry Data Type)

Edit: The return is a boolean where 0 = False and 1 = True. Any records that are 0 would be considered invalid.

[ForrestChevaillier1]

Thank you for the suggestion.

I tried the STIsValid() function on the intermediate view and on the pa and pa_evw tables and all records returned 1 in the validate column.

[ChristianWells]

Another consideration here is that in SMSS, all shapes are stored and queried without a resolution or tolerance. However, in ArcMap we employ the use of these, which can, in some cases, truncate the coordinates therefore rendering an incomplete shape. In this case the Union Aggregate could be creating coordinates that are too granular for ArcMap to pick up.

At this point, I would recommend contacting Technical Support to review this issue.

[ForrestChevaillier1]

Christian,

Thank you for your assistance.

I ended up deleting my pa feature class and archive table, re-importing it back to the database, re-enabling archiving, and re-creating my views and they worked! So, I guess I must have corrupted that pa feature class somehow in my SQL experiments. Good thing it was just practice data!

Thanks,

Chloe

[JoshuaBixby]

Although ArcGIS Server supports SQL Server's Geometry data type, it doesn't support all Geometry subtypes.  For example, CompoundCurve and CurvePolygon are unsupported subtypes.  Check the geometry type being returned from UnionAggregate.  If GeometryCollection is what is being returned, that could be causing the problem.

[AdrianMarsden]

I found this thread just after I posted a similar question here https://community.esri.com/message/687252-sql-view-to-sanitize-geometrycollections  I'd appreciate any comments to help me with this nastiness.

[MatthieuMureau]

I am in the similar situation, UnionAggregate is problematic with some geometry. ArcGIS 10.4.1 and SQL Server 2012

To solve my problem, I use a little buffer next to the aggregate:

(geometry::STUnionAggregate(Shape)).Buffer(0.01)

I'd also appreciate any comments to help, because this request is not enough efficient for me.