Building on the comments provided by @John M, I've contrived the following example of how to build a geometric index, and then query the index using the STEquals function:
USE tempdb; DROP TABLE dbo.G1; DROP TABLE dbo.G2; CREATE TABLE dbo.G1 ( G1ID INT NOT NULL CONSTRAINT PK_G1 PRIMARY KEY CLUSTERED IDENTITY(1,1) , Shape1 GEOMETRY ); CREATE SPATIAL INDEX SIndx_G1_Shape1 ON dbo.G1(Shape1) WITH (BOUNDING_BOX = (0, 0, 9999, 9999)); CREATE TABLE dbo.G2 ( G2ID INT NOT NULL CONSTRAINT PK_G2 PRIMARY KEY CLUSTERED IDENTITY(1,1) , Shape2 GEOMETRY ); CREATE SPATIAL INDEX SIndx_G2_Shape2 ON dbo.G2(Shape2) WITH (BOUNDING_BOX = (0, 0, 9999, 9999)); DECLARE @c NVARCHAR(50); SET @c = ''; DECLARE @LoopCount INT; SET @LoopCount = 0; WHILE @LoopCount < 5000 BEGIN SET @c = 'LINESTRING(2 ' + CONVERT(NVARCHAR(10), @LoopCount) + ', ' + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, 4 ' + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)'; IF @LoopCount < 10 PRINT (@c); INSERT INTO dbo.G1 (Shape1) SELECT geometry::Parse(@c); SET @c = 'LINESTRING(' + CONVERT(NVARCHAR(10), @LoopCount) + ' ' + CONVERT(NVARCHAR(10), @LoopCount) + ', ' + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, ' + CONVERT(NVARCHAR(10), @LoopCount + 2) + ' ' + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)'; IF @LoopCount < 10 PRINT (@c); INSERT INTO dbo.G2 (Shape2) SELECT geometry::Parse(@c); SET @LoopCount = @LoopCount + 1; END SELECT TOP(10) * FROM dbo.G1; SELECT TOP(10) * FROM dbo.G2; SELECT * FROM dbo.G1 INNER JOIN dbo.G2 ON G1.Shape1.STEquals(G2.Shape2) = 1;
