Search code examples
sql-servergeographysqldependency

SqlDependency and Geography SQL Server

I'm trying to get the SqlDependency object to work with geography data, but I haven't found any information about it online.

What geography functions work within the query notification system, and is there any way for me to get query notifications for something like this?

SELECT Id
  FROM dbo.Items
  WHERE 
  (geography::STGeomFromText(<Insert Geo Coords here>, 4326).STIntersects(Geography) = 1
          AND (FullText like '%Pepsi%' OR FullText like '%Coke%'))

I want to be notified when a new row is added where the Geography field is a point that is located inside a predefined region.

The regions I have are in the form of:

'POLYGON ((-84.54674 33.647809, -84.54674 33.887618, -84.289389 33.887618, -84.289389 33.647809, -84.54674 33.647809))'

Where that would be Atlanta, GA.

Solution

  • The restrictions are described in Creating a Query for Notification. There is no mention of the CLR system types (geography, geometry, hierachyid). I have described before how Query Notifications works behind the scenes and if you read that article you'll see that the requirements for Query Notifications match exactly the requirements for indexed views (and the article explains why that is no coincidence). So if you can create an indexed view like you desire, Query Notification should work. Lets try, using the MSDN example:

    CREATE TABLE SpatialTable 
    ( id int IDENTITY (1,1),
    GeogCol1 geography, 
    GeogCol2 AS GeogCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326));

INSERT INTO SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO

create view vw_test
with schemabinding 
as
select id, GeogCol1, GeogCol2
from dbo.SpatialTable
where geography::STGeomFromText('POINT(-122.35 47.656)',4326).STIntersects(GeogCol1) = 1;
go

create unique clustered index cdx_vw on vw_test (id)
go

    Msg 1982, Level 16, State 1, Line 1
    Cannot create index on view 'test.dbo.vw_test' because the view references non-deterministic or imprecise member function 'STGeomFromText' on CLR type 'Microsoft.SqlServer.Types.SqlGeography'. Consider removing reference to the function or altering the function to behave in a deterministic way. Do not declare a CLR function that behaves non-deterministically to have IsDeterministic=true, because that can lead to index corruption. See Books Online for details.

    There's you answer: Query Notifications (and all the other derivatives, like SqlDependency) will not work with geography::STGeomFromText function.