I want to do the following as illustrated on the microsoft website:
add a line layer to map
https://msdn.microsoft.com/en-us/library/ee240828.aspx#LineLayer
They give an example with 3 points.
I want to do the same with the dataset:
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year/
or use:
CREATE TABLE hurracane2(
Year VARCHAR(4) NOT NULL
,Name VARCHAR(10) NOT NULL
,Latitude VARCHAR(10) NOT NULL
,Longitude VARCHAR(10) NOT NULL
,RowID INTEGER NOT NULL
);
INSERT INTO hurracane2(Year,Name,Latitude,Longitude,RowID) VALUES
('1987','EMILY','9.8','-51.3',104),('1987','EMILY','10.4','-53',105),('1987','EMILY','10.9','-54.7',106),('1987','EMILY','11.4','-56.4',107)
,('1987','EMILY','12','-58',108),('1987','EMILY','12.4','-59.7',109),('1987','EMILY','13.1','-61.3',110),('1987','EMILY','13.7','-63.1',111)
,('1987','EMILY','14.5','-64.7',112),('1987','EMILY','15.1','-66.3',113),('1987','EMILY','15.9','-67.7',114),('1987','EMILY','16.7','-69.1',115)
,('1987','EMILY','17.8','-70.4',116),('1987','EMILY','18.3','-71',117),('1987','EMILY','19','-71.5',118),('1987','EMILY','20','-72.3',119)
,('1987','EMILY','20.9','-72.8',120),('1987','EMILY','22','-73',121),('1987','EMILY','23.2','-73',122),('1987','EMILY','24.4','-72.7',123)
,('1987','EMILY','26','-72',124),('1987','EMILY','28','-70.5',125),('1987','EMILY','30.2','-68',126),('1987','EMILY','32.3','-64.7',127)
,('1987','EMILY','32.4','-64.6',128),('1987','EMILY','35','-60',129),('1987','EMILY','38','-55',130),('1987','EMILY','41.2','-49',131)
,('1987','EMILY','44.8','-42.5',132),('1987','EMILY','49','-36',133),('1993','EMILY','19.9','-52.6',43),('1993','EMILY','20.5','-53.6',44)
,('1993','EMILY','21.3','-54.8',45),('1993','EMILY','22.3','-56',46),('1993','EMILY','23.2','-57.1',47),('1993','EMILY','24.3','-57.8',48)
,('1993','EMILY','25.4','-58.6',49),('1993','EMILY','26.7','-59.5',50),('1993','EMILY','27.6','-60',51),('1993','EMILY','28','-60.3',52)
,('1993','EMILY','27.9','-60.5',53),('1993','EMILY','28','-60.4',54),('1993','EMILY','28.2','-60.4',55),('1993','EMILY','28.3','-60.7',56)
,('1993','EMILY','27.9','-61',57),('1993','EMILY','27.4','-61.2',58),('1993','EMILY','26.9','-61.7',59),('1993','EMILY','26.6','-62.4',60)
,('1993','EMILY','26.4','-63',61),('1993','EMILY','26.3','-63.5',62),('1993','EMILY','26.4','-64.4',63),('1993','EMILY','26.6','-65.2',64)
,('1993','EMILY','27','-66.1',65),('1993','EMILY','27.4','-66.9',66),('1993','EMILY','28','-67.6',67),('1993','EMILY','28.6','-68.2',68)
,('1993','EMILY','29.3','-68.8',69),('1993','EMILY','30','-69.2',70),('1993','EMILY','30.6','-69.7',71),('1993','EMILY','31.2','-70.2',72)
,('1993','EMILY','31.5','-70.8',73),('1993','EMILY','31.8','-71.4',74),('1993','EMILY','32','-72.2',75),('1993','EMILY','32.4','-73',76)
,('1993','EMILY','32.9','-73.8',77),('1993','EMILY','33.6','-74.7',78),('1993','EMILY','34.5','-75.2',79),('1993','EMILY','35.2','-75.1',80)
,('1993','EMILY','35.6','-74.9',81),('1993','EMILY','36.6','-74.4',82),('1993','EMILY','37.5','-72.7',83),('1993','EMILY','38.2','-70.7',84)
,('1993','EMILY','39','-68.5',85),('1993','EMILY','39.2','-66',86),('1993','EMILY','39.2','-63.6',87),('1993','EMILY','39','-61.4',88)
,('1993','EMILY','38.6','-59.6',89),('1993','EMILY','38.1','-58.3',90),('1993','EMILY','37.5','-57.7',91),('1993','EMILY','36.9','-57.5',92)
,('1993','EMILY','36.4','-57.6',93),('1993','EMILY','36','-57.6',94),('1993','EMILY','35.8','-57.5',95),('1993','EMILY','36.1','-57.2',96)
,('1993','EMILY','36.7','-56.9',97),('1993','EMILY','37.4','-56.4',98),('1993','EMILY','38','-55.7',99),('1993','EMILY','38.7','-54.8',100)
,('1993','EMILY','39','-53',101),('1993','EMILY','39.3','-51.1',102),('1993','EMILY','39.8','-49.4',103),('1999','EMILY','11.5','-53.6',25)
,('1999','EMILY','11.5','-53.8',26),('1999','EMILY','11.6','-53.9',27),('1999','EMILY','12.1','-53.9',28),('1999','EMILY','12.6','-54.2',29)
,('1999','EMILY','12.8','-54.8',30),('1999','EMILY','13.2','-55.2',31),('1999','EMILY','13.8','-55.7',32),('1999','EMILY','14.3','-56.2',33)
,('1999','EMILY','15','-56.6',34),('1999','EMILY','15.8','-57',35),('1999','EMILY','17','-57.1',36),('1999','EMILY','18','-57',37)
,('1999','EMILY','19','-57',38),('1999','EMILY','20','-57',39),('1999','EMILY','21.1','-56.6',40),('1999','EMILY','22.4','-56.7',41)
,('1999','EMILY','23.8','-56.7',42),('2005','EMILY','10.7','-42.4',134),('2005','EMILY','10.8','-43.4',135),('2005','EMILY','10.9','-44.4',136)
,('2005','EMILY','11','-45.4',137),('2005','EMILY','11','-46.8',138),('2005','EMILY','11','-48.5',139),('2005','EMILY','11','-50.2',140)
,('2005','EMILY','11','-52',141),('2005','EMILY','11','-53.7',142),('2005','EMILY','11.1','-55.4',143),('2005','EMILY','11.2','-57.2',144)
,('2005','EMILY','11.4','-58.9',145),('2005','EMILY','11.6','-60.2',146),('2005','EMILY','11.9','-61.5',147),('2005','EMILY','12','-61.8',148)
,('2005','EMILY','12.4','-63.2',149),('2005','EMILY','12.9','-64.9',150),('2005','EMILY','13.3','-66.7',151),('2005','EMILY','13.7','-68.4',152)
,('2005','EMILY','14.1','-70.1',153),('2005','EMILY','14.5','-71.8',154),('2005','EMILY','14.9','-73.4',155),('2005','EMILY','15.4','-75',156)
,('2005','EMILY','15.9','-76.5',157),('2005','EMILY','16.4','-78',158),('2005','EMILY','17.1','-79.5',159),('2005','EMILY','17.7','-81.2',160)
,('2005','EMILY','18.3','-82.8',161),('2005','EMILY','18.9','-84.3',162),('2005','EMILY','19.5','-85.8',163),('2005','EMILY','20.3','-87.3',164)
,('2005','EMILY','20.3','-87.4',165),('2005','EMILY','21.3','-88.9',166),('2005','EMILY','22','-90.3',167),('2005','EMILY','22.6','-91.5',168)
,('2005','EMILY','23.2','-92.8',169),('2005','EMILY','23.7','-94',170),('2005','EMILY','24.1','-95.1',171),('2005','EMILY','24.4','-96.1',172)
,('2005','EMILY','24.6','-96.9',173),('2005','EMILY','24.8','-97.6',174),('2005','EMILY','25','-98.7',175),('2005','EMILY','25','-99.7',176)
,('2005','EMILY','25','-100.5',177),('2005','EMILY','25','-101.1',178),('2011','EMILY','14.9','-61.4',1),('2011','EMILY','15.1','-62.5',2)
,('2011','EMILY','15.4','-63.6',3),('2011','EMILY','15.7','-64.8',4),('2011','EMILY','16','-66.2',5),('2011','EMILY','16.3','-67.7',6)
,('2011','EMILY','16.6','-69.1',7),('2011','EMILY','16.8','-70.3',8),('2011','EMILY','16.9','-70.7',9),('2011','EMILY','16.9','-71.3',10)
,('2011','EMILY','17.3','-72.2',11),('2011','EMILY','17.9','-73.4',12),('2011','EMILY','18.8','-74.6',13),('2011','EMILY','19.8','-75.5',14)
,('2011','EMILY','21','-76.3',15),('2011','EMILY','22.4','-76.9',16),('2011','EMILY','23.6','-77.4',17),('2011','EMILY','24.6','-77.9',18)
,('2011','EMILY','25.6','-78.1',19),('2011','EMILY','26.4','-78.2',20),('2011','EMILY','27.2','-78.1',21),('2011','EMILY','28.4','-77.4',22)
,('2011','EMILY','29.7','-76.4',23),('2011','EMILY','30.7','-74.8',24);
select *
FROM [dbo].[hurracane]
There were three attempts to do this, two failed because I don't know how to store the tables into a new table.
First attempt involved this:
DROP COLUMN Basin, Sub_basin, Nature, Center, ISO_time, Serial_num, track_type, Num, [Wind(WMO)], [Pres(WMO)], [Wind(WMO) Percentile], [Pres(WMO) Percentile]
ALTER TABLE [dbo].[hurracane]
DROP COLUMN ISO_TIme
SELECT *
from [dbo].[hurracane]
ALTER TABLE [dbo].[hurracane]
ADD rowid int
WITH cteRank AS
(
SELECT
indexid = ROW_NUMBER() OVER (PARTITION BY [Name], [Season] ORDER BY [Name])
,RowID
FROM [dbo].[hurracane]
)
UPDATE cteRank
SET RowID = indexid
;
SELECT
a.rowid,
a.Season,
a.Name,
a.Latitude,
a.Longitude,
b.rowid,
b.Season,
b.Name,
b.Latitude,
b.Longitude,
CAST('LINESTRING(' + a.Longitude +' '+ a.Latitude +', '+
b.Longitude +' '+ b.Latitude + ')' AS GEOGRAPHY) AS segmentspatialdata
into hurricane_routes3
FROM
hurracane a
LEFT OUTER JOIN
hurracane b
ON
b.rowid = a.rowid + 1
AND b.season = a.season
AND b.name = a.name
WHERE
b.rowid IS NOT NULL
ORDER BY a.rowid
Second attempt involved this:
select *
from [dbo].[hurracane]
select *, ([Latitude]+' '+[Longitude]) as COORDS2
from [dbo].[hurracane]
ALTER TABLE [dbo].[hurracane]
ADD COORDS2 varchar(50)
UPDATE [dbo].[hurracane]
SET COORDS2 = [Latitude]+' '+[Longitude]
SELECT *
FROM [dbo].[hurracane]
SELECT
Name, [Season],
STUFF(
(SELECT DISTINCT ',' + COORDS2
FROM [dbo].[hurracane]
WHERE Name = a.Name AND [Season] = a.[Season]
FOR XML PATH (''))
, 1, 1, '') AS Coordinates
FROM [dbo].[hurracane] AS a
GROUP BY Name, [Season]
The issue in the second attempt involved using sets in a subquery. The statement below is incorrect.
update [dbo].[hurracane]
set trajtectory=geography::STGeomFromText('LINESTRING('+[COORDS]+')', 4326)
For now, I just want to be able to store the output (Season, Name, Segmentspatialdata) from both sets of code so I can test to see if I can import the data in SSRS. I have attached screenshots of the output from one set of code because i'm a new user i can't upload two.
