Skip to content

Instantly share code, notes, and snippets.

@geobabbler
Created November 3, 2012 12:58
Show Gist options
  • Save geobabbler/4007319 to your computer and use it in GitHub Desktop.
Save geobabbler/4007319 to your computer and use it in GitHub Desktop.
Example of using a trigger in SQL Server 2008 to test spatial relationship
USE [MyDB]
GO
/****** Object: Trigger [dbo].[Locations_Insert] Script Date: 11/03/2012 08:50:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* This trigger makes sure that the data being inserted into
the Locations falls within a specific polygon stored in another table.
If it doesn't, the transaction is rolled back.
The Locations table in this example does not contain a spatial data type, only Latitude and Longitude columns
*/
ALTER TRIGGER [dbo].[Locations_Insert] ON [dbo].[Locations]
INSTEAD OF INSERT
AS
DECLARE @lat float
DECLARE @lon float
--STContains only supported by Geometry, be sure to convert Geography if needed
DECLARE @pt geometry
DECLARE @count int
SET @lat = (SELECT Latitude FROM INSERTED)
SET @lon = (SELECT Longitude FROM INSERTED)
--This example assumes latitude, longitude and polygon table are all WGS84
SET @pt = geometry::Point(@lon, @lat, 4326)
--GID is PK of polygon table, geom is the geometry column
SELECT @count = COUNT(GID) FROM my_polygon_table WHERE geom.STContains(@pt) = 1
IF (@count < 1)
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO Locations (Longitude, Latitude) SELECT Longitude,Latitude FROM Inserted
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment