SQL Error Shape.STContains is not allowed

1096
6
09-15-2016 11:07 AM
Highlighted
New Contributor III

I am trying to create a SQL view to count the number of point features in a polygon.  The following code works when run in a SQL Query, however when I try to create a view using the code I get "Shape.STContains is not allowed, and the column name dbo could not be found or is ambiguous"

SELECT POLYGON.NAME, Count(*)
from Point JOIN
POLYGON
On POLYGON.Shape.STContains(POINT.SHAPE) = 1
GROUP BY POLYGON.NAME

Any assistance would be appreciated.

Reply
0 Kudos
6 Replies
Highlighted
MVP Esteemed Contributor

It is always helpful to provide information on DBMS platform and version, and ArcGIS products and versions, if applicable.

Reply
0 Kudos
Highlighted
New Contributor III

SQL Server 2014 and Arcgis server 10.3.1

Reply
0 Kudos
Highlighted
MVP Esteemed Contributor

Are you trying to create the view from ArcCatalog or from within another database client, like SQL Server Management Studio?  If the former, have you tried it from a different database client, a non-Esri one?

Reply
0 Kudos
Highlighted
New Contributor III

Creating in SQL management studio. It displays correct results in SQL query but when try to make the view it throws that error

Reply
0 Kudos
Highlighted
New Contributor

Please tell me you managed to solve this one?

Then immediately thereafter tell me how you did it.

Have reinstalled servers and .net systems and this one has baffled me.

Thanks hugely in advance

Martin

Reply
0 Kudos
Highlighted
New Contributor

YAAYYYYYYY. Got it.

It seems that the STContains function won't operate with a DBO schema in front of the field name.

If you add the database name to the query it works. Should look like this.

USE MyDatabase

Select dbo.PointTab.*, dbo.PolyTab.Name from dbo.PointTab

Inner Join dbo.PolyTab

On PolyTab.GeometryCol.STContains(dbo.PointTab.GeometryCol) = 1