SQL Server STIntersection Example

SQL STIntersection.sql

-- Name: SQL Server STIntersection Example
-- Author: Bryan McIntosh

/****** Part 1: Intersect Point and Polygon ******/
DECLARE @thePolygon GEOGRAPHY, @pointIN GEOGRAPHY, @pointOUT GEOGRAPHY;
SET @thePolygon = GEOGRAPHY::STGeomFromText('POLYGON((-78.50932668617881 45.024933647425115, -78.53403351361905 44.9898648154388, -78.48446979547693 44.97239241709962, -78.45973073293072 45.007441690111115, -78.50932668617881 45.024933647425115))', 4269);
SET @pointIN = GEOGRAPHY::STGeomFromText('POINT(-78.51 45.00)', 4269);
SET @pointOUT = GEOGRAPHY::STGeomFromText('POINT(-65.00 35.00)', 4269);
SELECT @pointIN.STIntersection(@thePolygon).ToString();
SELECT @pointOUT.STIntersection(@thePolygon).ToString();
SELECT @thePolygon.STIntersection(@pointIN).ToString();

/****** Part 2: Polygon/Polygon partial overlap ******/
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))', 4269);
SET @h = geography::STGeomFromText('POLYGON((3 3, 5 3, 5 5, 3 5, 3 3))', 4269);
SELECT @h.STIntersection(@g).ToString();