Search code examples
.netsql-serversqlclientspatial-query

SQL Server PolyTab.GeometryCol.STContains(PointTab.GeometryCol) = 1 not Allowed

I have two basic tables with geometry column types in them. One has polygons and the other points, and I am trying to join them using the following basic query:

Select dbo.PointTab.*, dbo.PolyTab.Name from dbo.PointTab
Inner Join dbo.PolyTab
On
dbo.PolyTab.GeometryCol.STContains(dbo.PointTab.GeometryCol) = 1

I have tried a great many variations on this query, but always get the error:

Error Source:   .Net SQLClient Data Provider
Error Message:  Remote function reference
            'dbo.PolyTab.GeometryCol.STContains' is not allowed, and the
                   column name dbo could not be found or is ambiguous

I am running this on Windows Server 2016 with SQL Server Standard 2016. The .net system is 4.6, with the previous versions (3.5 and 2 enabled)

If I use ODBC to get into the database with my old faithful laptop running win 8.1 then it runs (without the DBO prefixes on the columns).

There are no useful references on the internet to this and I have run out of talent.

Thanks in advance

Martin

Solution

  • 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