August 1, 2016 at 1:36 pm
Yes I am.
THe datetime data is unique. I guess I could have done it that way.
I want to update the table with the spatial data so that when I load it into visual studio it can quickly grab that one column which is all I need then I'm done.
August 1, 2016 at 1:45 pm
Nusc (8/1/2016)
Yes I am.THe datetime data is unique. I guess I could have done it that way.
I want to update the table with the spatial data so that when I load it into visual studio it can quickly grab that one column which is all I need then I'm done.
create a view that provides all the necesary columns that you need in VS....you then wont have to update the table
________________________________________________________________
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 1:53 pm
Since you are on SQL 2012, have you considered using LEAD/LAG instead of doing a self-join?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 1, 2016 at 2:51 pm
drew.allen (8/1/2016)
Since you are on SQL 2012, have you considered using LEAD/LAG instead of doing a self-join?Drew
No. I will look at it later but since I've gotten this far I don't want to go back.
August 1, 2016 at 2:53 pm
I prefer to update the table in management studio. the idea is the provide as very little code in the sql query wizard for map services in SSRS.
August 1, 2016 at 2:58 pm
Nusc (8/1/2016)
I prefer to update the table in management studio. the idea is the provide as very little code in the sql query wizard for map services in SSRS.
ok...but do you have any evidence that SSRS performs better with an updated table as opposed to a view?
by updating the table you are going to have to schedule this so that it is "current"...which in itself is an additional overhead.
by the way...have you altered your rowid to order by a date time column?
________________________________________________________________
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 3:21 pm
J Livingston SQL (8/1/2016)
Nusc (8/1/2016)
I prefer to update the table in management studio. the idea is the provide as very little code in the sql query wizard for map services in SSRS.ok...but do you have any evidence that SSRS performs better with an updated table as opposed to a view?
by updating the table you are going to have to schedule this so that it is "current"...which in itself is an additional overhead.
by the way...have you altered your rowid to order by a date time column?
Perhaps I misunderstood you and I don't even know what view is.. But the idea is the load the updated table in the database in MS.
Furthermore, I have attached the output of the working code. Again I have saved the csv as a xlsx because the forum won't allow me to upload csv files.
At this point, If I can't figure out the update statement for this table then I would like to just copy and paste the last six columns in the attached file. However, when I try to load this file in sql server MS, I get an error. It worked for the example that I was trying to follow but I'm not sure what's wrong in this case.
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "segmentspatialdata" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - spatialdata_csv.Outputs[Flat File Source Output].Columns[segmentspatialdata]" failed because truncation occurred, and the truncation row disposition on "Source - spatialdata_csv.Outputs[Flat File Source Output].Columns[segmentspatialdata]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\Administrator\Desktop\spatialdata.csv" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - spatialdata_csv returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
August 1, 2016 at 3:53 pm
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?
Thanks for the info... and, yeah... depending on how you import that data, that could be a very large part of the problem.
I'll try to take another look after work unless it's already been solved for you. I don't have the time to read back in this thread just now.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2016 at 4:41 pm
This is what I am supposed to do:
https://msdn.microsoft.com/en-us/library/ee240828.aspx#LineLayer
The example they provide works in Visual Studio without having to go through Management Studio see attached file:
SELECT N'Path' AS Name, CAST('LINESTRING(
-76.5001866085881 42.4310489934743,
-76.4602850815536 43.4353224527794,
-73.4728622833178 44.7028831413324)' AS geography) as Route
When I manually select all the concactenated lat and long coords for one path it works, say emily. But I need to code it for the whole .csv file and that's the reason for all this code.
If I import the output as given in the script in MS, then I get the error above. I'm not sure what the best way to approach this is.
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "segmentspatialdata" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "Source - spatialdata_csv.Outputs[Flat File Source Output].Columns[segmentspatialdata]" failed because truncation occurred, and the truncation row disposition on "Source - spatialdata_csv.Outputs[Flat File Source Output].Columns[segmentspatialdata]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\Administrator\Desktop\spatialdata.csv" on data row 2.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - spatialdata_csv returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
My previous attempt involved creating a new column with the lat and long coords
71.92 -20.00, 14.00 24.22, ... , 41.00, -22.44
However when I tried to import it as a csv and adjusting the delimiter, it would create columns based on the number of coordinates and that was bothering because I only wanted 3 columns. So I gave up on that. I did manage to resolve it but having a set in a subquery didn't work in VS so that's why I switched to the original post.
August 2, 2016 at 6:46 pm
I will talk about the first attempt.
So the first image just combines two columns into one
UPDATE [dbo].[hurracane]
SET COORDS = [Latitude]+' '+[Longitude]
The second image combines all the rows associated with one group which would give me the format as in the original microsoft example for a line map:
How do I create an update statement for this? Is there a cleaner way to write that query? I don't like using XML path but it works. THe issue I had was that I couldn't do it and if I copied the output into a new csv and import that way it would produce columns based on the number of coordinates when I only need one column. I hope that makes sense.
August 3, 2016 at 6:47 am
I can see what you are trying to do however rather than trying to create the geometry text dynamically you should be using spatial aggregate functions.
Once you have all your data imported (I used the data from your original cleandata.xlsx file) you should covert each latitude and longitude to a point e.g.
UPDATE hurricanes SET g = geometry::STGeomFromText('POINT('+ CONVERT(VARCHAR,long) + ' ' + CONVERT(VARCHAR,lat) + ')',0);
Then to gather these points together into a collection you then use a UnionAggregate function to combine the points e.g.
SELECT name, geometry::UnionAggregate(g)
FROM hurricanes
GROUP BY name
This provides maximum flexibility as you can filter your points and group etc.
You can convert these all to linestrings via:
with lines as (
select name, g.ShortestLineTo(lead(g) over (partition by name order by rowid)) as LineToNext
from dbo.hurricanes
)
select name, geometry::UnionAggregate(LineToNext) as WholeRoute
from lines
group by name;
Hat tip: http://sqlblog.com/blogs/rob_farley/archive/2014/01/22/converting-points-to-a-path.aspx - Rob Farley's excellent article for the linestring conversion.
August 3, 2016 at 9:16 am
Hey SPiff,
there are a few issues. First the data that was provided had an incorrect column in rowID. If you filter out Willy and 2005, you will find that it only starts off at 26, that was the reason for the remaining threads discussion. Livingston recommend using ISO_TIME as the rowid.
The original data is from this website:
ftp://eclipse.ncdc.noaa.gov/pub/ibtracs/v03r08/wmo/csv/year
Pick any file.
Nevertheless, when I run
UPDATE hurricanes SET g = geometry::STGeomFromText('POINT('+ CONVERT(VARCHAR,long) + ' ' + CONVERT(VARCHAR,lat) + ')',0);
I get the following error:
Implicit conversion from data type geometry to varchar is not allowed. Use the CONVERT function to run this query.
These values are initially varchar, what's the need to convert them?
ALso the link you provided leads to this thread. http://sqlblog.com/blogs/rob_farley/archive/2014/01/22/converting-points-to-a-path.aspx
August 3, 2016 at 9:31 am
OK, to be fair I was going from the question you posted in the SSRS forum. I thought your problem is that you wanted a linestring from the coordinates - hence your concatenation in your last posts.
I don't really see the issue with the rowid, it's just an ordinal identifier. Is there supposed to be some meaning to it other then to order the datapoints in the path? Willy 1994 has rowids 1-25. You can always group by the name and year, the order will still be correct.
If you really have a problem with it you could do something like.
DENSE_RANK() OVER (PARTITION BY name, year ORDER BY rowid) AS rowid
I imported long and lat as floats - but I don't think that is the issue. I added column [g] as a geometry type to the hurricanes table, have you added a column g as a geometry? Sorry that wasn't clear.
August 3, 2016 at 9:41 am
Spiff (8/3/2016)
OK, to be fair I was going from the question you posted in the SSRS forum. I thought your problem is that you wanted a linestring from the coordinates - hence your concatenation in your last posts.
I just want to plot these points as a line path as in the website you provided using SSRS. I guess there is more than one way to do it but the default example from the MS website works. But they only provided 3 points. I need to do it for all the data.
Spiff (8/3/2016)
I don't really see the issue with the rowid, it's just an ordinal identifier. Is there supposed to be some meaning to it other then to order the datapoints in the path? Willy 1994 has rowids 1-25. You can always group by the name and year, the order will still be correct.If you really have a problem with it you could do something like.
DENSE_RANK() OVER (PARTITION BY name, year ORDER BY rowid) AS rowid
As you point out for willy 94 its 1-25 but willy 2005 is from 26-onward which should begin at 1 if I want to aggreggate each one.
Spiff (8/3/2016)
I imported long and lat as floats - but I don't think that is the issue. I added column [g] as a geometry type to the hurricanes table, have you added a column g as a geometry? Sorry that wasn't clear.
I want to try what you did. The xlsx file is supposed to be saved as a csv but the forum wont' allow me to upload csv files. I have removed it and linked to the original source data.
August 3, 2016 at 9:45 am
geometry::STGeomFromText() returns an actual geometry spatial datatype from the WKT text you supply.
If you are trying to SET that datatype to a column of VARCHAR it will try to implicitly convert the GEOMETRY datatype to VARCHAR, which will fail.
You need to add the geometry type column to your table e.g.:
ALTER TABLE hurricanes ADD g GEOMETRY;
To create the geometry column, then you can run the update against it.
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply