August 1, 2016 at 9:39 am
Nusc (8/1/2016)
J Livingston SQL (8/1/2016)
here is your spread sheet data for "Emily" setup for others to use.you will see that "Emily" does have a rowid of 1`......
No, not for year 2005 there is no rowid of 1. I don't understand why.
you need to partition by "year" & "name" then
edit in bold
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 10:32 am
No. If you open the csv file and sort by year and name that's still not true.
That's something I need to fix. Furthermore, I need to change the data type of latitude and longitude but I keep getting errors:
SELECT cast([Year] as decimal(4,2)) as [Year] FROM [dbo].[cleandata]
SELECT cast([Latitude] as decimal(4,2)) as [Latitude] FROM [dbo].[cleandata]
SELECT cast([Longitude] as decimal(4,2)) as [Longitude] FROM [dbo].[cleandata]
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
August 1, 2016 at 10:43 am
Nusc (8/1/2016)
No. If you open the csv file and sort by year and name that's still not true.That's something I need to fix. Furthermore, I need to change the data type of latitude and longitude but I keep getting errors:
SELECT cast([Year] as decimal(4,2)) as [Year] FROM [dbo].[cleandata]
SELECT cast([Latitude] as decimal(4,2)) as [Latitude] FROM [dbo].[cleandata]
SELECT cast([Longitude] as decimal(4,2)) as [Longitude] FROM [dbo].[cleandata]
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
suggest you change
indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])
to this
ROW_NUMBER() OVER(PARTITION BY NAME, YEAR ORDER BY Latitude)
why are you changing to decimals?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 11:29 am
why are you changing to decimals?
Because of this working example which reads in lat and long as decimal:
This is precisely what I need to do.
August 1, 2016 at 11:34 am
J Livingston SQL (8/1/2016)
suggest you change
indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])to this
ROW_NUMBER() OVER(PARTITION BY NAME, YEAR ORDER BY Latitude)
I still get the following error:
(88407 row(s) affected)
Msg 6522, Level 16, State 1, Line 12
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
Maybe if I fix the data type first it will work. Do you know why I got the error in the previous message?
August 1, 2016 at 11:34 am
Nusc (8/1/2016)
why are you changing to decimals?
Because of this working example:
This is precisely what I need to do.
looking at that example...they are casting to varchar from a decimal...you already have lat & long as varchar...so you can remove the cast from the posted code
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 11:37 am
Nusc (8/1/2016)
J Livingston SQL (8/1/2016)
suggest you change
indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])to this
ROW_NUMBER() OVER(PARTITION BY NAME, YEAR ORDER BY Latitude)I still get the following error:
(88407 row(s) affected)
Msg 6522, Level 16, State 1, Line 12
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
Maybe if I fix the data type first it will work. Do you know why I got the error in the previous message?
please post your revised code that cause this error
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 11:46 am
J Livingston SQL (8/1/2016)
Nusc (8/1/2016)
J Livingston SQL (8/1/2016)
suggest you change
indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])to this
ROW_NUMBER() OVER(PARTITION BY NAME, YEAR ORDER BY Latitude)I still get the following error:
(88407 row(s) affected)
Msg 6522, Level 16, State 1, Line 12
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24206: The specified input cannot be accepted because it contains an edge with antipodal points. For information about using spatial methods with FullGlobe objects, see Types of Spatial Data in SQL Server Books Online.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
Maybe if I fix the data type first it will work. Do you know why I got the error in the previous message?
please post your revised code that cause this error
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
FROM
hurracane a
LEFT OUTER JOIN
hurracane b
ON
b.rowid = a.rowid + 1
WHERE
b.rowid IS NOT NULL
ORDER BY a.rowid
August 1, 2016 at 11:56 am
J Livingston SQL (8/1/2016)
suggest you change
indexid = ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Name])
I was going to mention that it's pointless to use the same expression in both the PARTITION BY and ORDER BY clauses of a single OVER clause. By definition, each of the rows within a partition have exactly the same value for each of the partition expressions, so ordering on any of those expressions is superfluous.
This can be seen by running the following code:
SELECT *,
ROW_NUMBER() OVER(PARTITION BY v.grp ORDER BY v.grp, v.seq) AS a,
ROW_NUMBER() OVER(PARTITION BY v.grp ORDER BY v.grp DESC, v.seq) AS d
FROM (
VALUES(1, 10), (1,22), (1,35), (2,11), (2,27), (2,36)
) v(grp, seq)
The two row numbers differ in one being sorted "ascending" and the other being sorted "descending" on the partition expression. As you can see, they give exactly the same results, because the order on the partition expression is superfluous.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2016 at 12:12 pm
try
LEFT OUTER JOIN
hurracane b
ON
b.rowid = a.rowid + 1
AND b.season = a.season
AND b.name = a.name
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 12:24 pm
A quick side note: it's hurricane, not hurracane, I think.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 1, 2016 at 12:28 pm
Phil Parkin (8/1/2016)
A quick side note: it's hurricane, not hurracane, I think.
but in this thread its also been called "cleandata" and the column "year" is now called "season". 😛
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2016 at 12:37 pm
J Livingston SQL (8/1/2016)
Phil Parkin (8/1/2016)
A quick side note: it's hurricane, not hurracane, I think.but in this thread its also been called "cleandata" and the column "year" is now called "season". 😛
That's what we call 'Agile' round here :w00t:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 1, 2016 at 1:17 pm
J Livingston SQL (8/1/2016)
try
LEFT OUTER JOIN
hurracane b
ON
b.rowid = a.rowid + 1
AND b.season = a.season
AND b.name = a.name
THis works. Thank you. Been staring at this for the last 5 days.
My problem now is to import the new column segment spatial data.
How do I embed the join statement which generated the spatial data within the subquery of an update statement?
Below is clearly wrong but what is the general idea?
ALTER TABLE [dbo].[hurracane]
ADD segmentspatialdata geography
WITH cteRank2 AS
(SELECT
a.rowid,
a.Season,
a.Name,
a.Latitude,
a.Longitude,
b.rowid,
b.Season,
b.Name,
b.Latitude,
b.Longitude,
spatialdata =
CAST('LINESTRING(' + a.Longitude +' '+ a.Latitude +', '+
b.Longitude +' '+ b.Latitude + ')' AS GEOGRAPHY)
,segmentspatialdata
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
)
UPDATE cteRank2
SET segmentspatialdata = spatialdata
;
August 1, 2016 at 1:32 pm
hmmm...been trying to second guess what you are trying to do......are you lokking to plot the track of hurricanes?
if so...then I think you need to get data that provide a date/time of the lat/lon data and then you can use that to create your rowid correctly (eg ORDER BY "datetime")
I found some csv data here that seems similar to yours but includes a column called "ISO-time)....this is what I would use for the RowID code.
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year
I had a play for Emily/2005 and the spatial results in SSMS appeared different, you may wish to take look.
as for your more recent question...why do you need to update anything....just present the data as is (???)
if I get a chance later I will post my thoughts based on the data I found above.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 16 through 30 (of 69 total)
You must be logged in to reply to this topic. Login to reply