August 3, 2016 at 3:04 pm
Ok reset this thread.
Here is the data:
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year/
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.
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)
August 3, 2016 at 3:05 pm
Whatever mate. Goodnight.
N.b. your coordinates ([COORDS]) should be long-lat for points.
August 3, 2016 at 3:16 pm
Nusc (8/3/2016)
Ok reset this thread.Here is the data:
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year/
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.
There were three attempts to do this all 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]
Hello Nusc
assume you read my PM.
so why ignore my advice......?
I asked that YOU provide a subset of data we could all use from the source.....sorry but expecting anyone to go get it themselves is somewhat ignorant....
as you have told me this is homework...after 2mths SQL class.....no probs we all have to learn, and what you need to learn here is that without providing suitable and accurate set up scripts , then you will get few responses.
I did point you here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ as a guide.
from looking at the code you posted above, it seems that for whatever reasoning, you have decided that its your way or not at all.
why are you dropping ISO_time column...I just dont get your thoughts
Please remember that every response to your questions and multiple thread is doing this for absolutely no return, other than the benefit we all get of helpling others
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 3, 2016 at 3:33 pm
The script includes statements that removed the columns, all one needs is to run the script and that's it.
I was told to save the csv as a txt but it prompts that some of the data could be lost if stored in MS-DOS format.
I attached it anyway.
You said I didn't provide the scripts, the code is in the post verbatim
I didn't include ISO_TIME because it's easier to order by number unless there's another way.
You mentioned requirements, line layer to a map. Unfortunately the other attempt didn't work.
August 3, 2016 at 3:46 pm
...its nearly bed o'clock here in the UK and I've got a 400 mile round drive tomorrow.
hopefully someone else will help you out in the meantime.
I will keep a look out on this thread and chime in as and when I can.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 3, 2016 at 3:50 pm
Spiff (8/3/2016)
Whatever mate. Goodnight.N.b. your coordinates ([COORDS]) should be long-lat for points. Might save you a few hours if you bother to read this.
You can easily change the order. That's not the real issue at the moment.
August 3, 2016 at 7:26 pm
CREATE TABLE hurracane(
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 hurracane(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);
August 4, 2016 at 10:03 am
Nusc (7/31/2016)
IF you don't mind saving that xlsx as a csv file and importing as a flat file source so there are no differences would be great.Year , name, latitude, longitude, rowid
RIght now they're all assigned as varchar except for rowid which is assigned as an int.
Maybe that's my problem?
Oh yes, improper datatypes is a huge problem. One of my favorite T-shirts says, "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"
Making everything varying character just adds overhead, from conversions to wasted storage. In particular, if you are going to do (longitude, latitude) computations you need floating-point. Normally, I think of floating-point as being a waste in SQL, but all of the spherical trig that you use for this kind of data depends on floating-point. God bless those old Fortran math libraries !
Tables also need to have a key, by definition. What you described is basically a deck of punch cards. My other question is when she got a real key, which would be the year and the hurricane name (I cannot remember if hurricane names are reused or not). Now why do you think that are hurricane has an attribute called "row_id"? Sure sound like a physical description of physical storage that has absolutely no place in a valid logical model..
CREATE TABLE Hurricanes
(hurricane_year CHAR(4) NOT NULL,
hurricane_name VARCHAR(10) NOT NULL,
PRIMARY KEY (hurricane_year, hurricane_name),
hurricane_latitude FLOAT NOT NULL,
Hurricane_longitude FLOAT NOT NULL);
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 4, 2016 at 5:07 pm
CELKO (8/4/2016)
Nusc (7/31/2016)
IF you don't mind saving that xlsx as a csv file and importing as a flat file source so there are no differences would be great.Year , name, latitude, longitude, rowid
RIght now they're all assigned as varchar except for rowid which is assigned as an int.
Maybe that's my problem?
Oh yes, improper datatypes is a huge problem. One of my favorite T-shirts says, "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"
Making everything varying character just adds overhead, from conversions to wasted storage. In particular, if you are going to do (longitude, latitude) computations you need floating-point. Normally, I think of floating-point as being a waste in SQL, but all of the spherical trig that you use for this kind of data depends on floating-point. God bless those old Fortran math libraries !
Tables also need to have a key, by definition. What you described is basically a deck of punch cards. My other question is when she got a real key, which would be the year and the hurricane name (I cannot remember if hurricane names are reused or not). Now why do you think that are hurricane has an attribute called "row_id"? Sure sound like a physical description of physical storage that has absolutely no place in a valid logical model..
CREATE TABLE Hurricanes
(hurricane_year CHAR(4) NOT NULL,
hurricane_name VARCHAR(10) NOT NULL,
PRIMARY KEY (hurricane_year, hurricane_name),
hurricane_latitude FLOAT NOT NULL,
Hurricane_longitude FLOAT NOT NULL);
This is not an issue. This whole thing has been solved.
Thank you all for your input.
August 4, 2016 at 6:48 pm
Nusc (8/4/2016)
This is not an issue. This whole thing has been solved.
Two way street here, please. What is the solution you ended up with?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply