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