Skip to content

Instantly share code, notes, and snippets.

@TheBryanMac
Created August 14, 2014 21:14
Show Gist options
  • Save TheBryanMac/a10f7063f2eed8c4ec75 to your computer and use it in GitHub Desktop.
Save TheBryanMac/a10f7063f2eed8c4ec75 to your computer and use it in GitHub Desktop.
SQL Server STIntersection Example
-- 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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment