Spatial SQL -> MS-SQL 2008. Extract coordinates from polyline

This script shows how you can extract Lat/Long coordinates of polyline vertices :

-- -------- 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);

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

-- Extract Lat\Long coordinates of Road lines
-- save into table milano_str_coords


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


SELECT a.id, b.n,
  a.g.STPointN(b.n).Lat AS Lattiude,
  a.g.STPointN(b.n).Long AS Longitude
  INTO milano_str_coords
FROM milano_str AS a, num_seq AS b
WHERE b.n <= a.g.STNumPoints()
ORDER BY a.id, b.n;



Coordinates table will look like this:
idnLattiudeLongitude
1145.47184631040739.18233279812512
1245.4720366686659.18368096011447
1345.47289795023969.18744646655354
1445.47325808425689.19037003658003
1545.4724110833439.19503737692874
1645.4687886950999.19931978713282
1745.46711691110559.20043749291712
........



sqlexamples.info