Sample SQL code to demonstrate linear referencing in SQL Server

LinearReferencingExample.sql

--example use of the CreateLinearReferenceFeature function
--see http://geographika.co.uk/linear-referencing-in-sql-server-2008 for details
--can be run directly in Management Studio once the Register.sql
--script has added the SQLSpatialTools.dll to the database

DECLARE @original_line AS geometry
--create a line feature
SET @original_line = geometry::STGeomFromText('LINESTRING (313401 232075,
313401.6885 232075.662,
313419.0265 232036.7775, 313434.913 232000.2635, 313456.0535 231952.438,
313471.65765098558 231916.45611721367)',0)

PRINT @original_line.STLength()
--the original feature length is 174.859

DECLARE @cut_line AS geometry
--get a subline from 20m along to 25m along the feature
SELECT @cut_line = dbo.CreateLinearReferenceFeature(@original_line, 20,25)
PRINT @cut_line.STLength()
--the cut feature has a length of 5m

--show the features in the same results window
DECLARE @results TABLE (GEOM Geometry);

INSERT INTO @results SELECT @original_line
INSERT INTO @results SELECT @cut_line

SELECT * FROM @results