Spatial SQL -> MS-SQL 2008. Split Polylines to simple lines

This script shows how you can split polyline to simple lines using polyline vertices as a "cutter" object. Input table is Lines, output table simple_lines.

-- =================================================================
-- split Polylines to simple lines
-- =================================================================

-- -------- create sequential numbers table -----------------


IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[num_seq]') AND type IN (N'U'))
DROP TABLE [dbo].[num_seq];

SELECT TOP 1000 IDENTITY(int,1,1) AS n
INTO num_seq
FROM MASTER..spt_values a, MASTER..spt_values b;

CREATE UNIQUE CLUSTERED INDEX idx_1 ON num_seq(n);

-- ------------------------------------------------------------

-- create empty table for splited lines output
IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[simple_lines]') AND type IN (N'U'))
DROP TABLE [dbo].[simple_lines]
GO

CREATE TABLE [dbo].[simple_lines](
  [seg_id] [int] IDENTITY(1,1) NOT NULL,
  [LineID] [int] NOT NULL,
  [n] [int] NOT NULL,
  [geog] [geography] NULL
) ON [PRIMARY]
GO

IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[Vertice_Points]') AND type IN (N'U'))
DROP TABLE [dbo].[Vertice_Points]
GO

SELECT a.LineID, b.n, a.geog.STPointN(b.n) AS geog
  INTO dbo.Vertice_Points
FROM dbo.Lines AS a, num_seq AS b
WHERE b.n <= a.geog.STNumPoints()
GO

-- -------------------------------------------------------
-- Agregate intersection points to Points-Collection
-- using agregate function GeographyCollectionAggregate()
-- -------------------------------------------------------

IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[points_collection]') AND type IN (N'U'))
DROP TABLE [dbo].[points_collection]
GO

SELECT a.LineID, dbo.GeographyCollectionAggregate(a.geog) AS g
   INTO points_collection
FROM dbo.Vertice_Points AS a
GROUP BY a.LineID
GO

-- -----------------------------------------------------
-- split polylines by points collection object
-- -----------------------------------------------------

IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[tmp_lines]') AND type IN (N'U'))
DROP TABLE [dbo].[tmp_lines]
GO

SELECT a.LineID, a.geog.STDifference(b.g.STBuffer(0.005)) AS g
  INTO tmp_lines
FROM dbo.Lines AS a INNER JOIN dbo.points_collection AS b
ON a.LineID = b.LineID
GO

-- -----------------------------------------------------
-- extract simple lines
-- -----------------------------------------------------

INSERT INTO dbo.simple_lines (LineID, n, geog)
SELECT a.LineID, b.n, a.g.STGeometryN(b.n) AS geog
FROM dbo.tmp_lines AS a, num_seq AS b
WHERE b.n <= a.g.STNumGeometries()
GO


-- drop temp tables
IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[tmp_lines]') AND type IN (N'U'))
DROP TABLE [dbo].[tmp_lines]
GO
IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[points_collection]') AND type IN (N'U'))
DROP TABLE [dbo].[points_collection]
GO
IF EXISTS ( SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N'[dbo].[Vertice_Points]') AND type IN (N'U'))
DROP TABLE [dbo].[Vertice_Points]
GO





sqlexamples.info