August 3, 2016 at 9:53 am
Nusc (8/3/2016)
--snip
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.
Rename your .CSV to .TXT and then attach it. Problem solved.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 3, 2016 at 9:53 am
Okay it works so far. How do I save the output into the table so that I can load it in visual studio?
MY issue with using these geography statements is that it won't recognize the string, this is why I avoided it.
Here is the example of one output:
name | WholeRoute
ABELE| 0x00000000010424000000CDCCCCCCCC8C554033333333333328C00000000000A05540CDCCCCCCCCCC27C0D6277DD2279D5540and so on
I can save the output to a new file and import it but I want to know in general if you can update tables with subqueries.
August 3, 2016 at 9:54 am
What won't recognise the string? You can use spatial queries in a report dataset, the SSRS maps require spatial types.
If you need to save the results you can just do:
select ... into hurricane_routes ...
if you need to you can convert geoms back to WKT using SELECT g.STAsText() from hurricanes..
August 3, 2016 at 10:03 am
I get the same errors as I did with linestring in my previous attempts.
THe whole point of doing is this is to load this data into SSRS.
August 3, 2016 at 11:31 am
wtf indeed. What are you doing with the Import Export wizard now?
You can't export a spatial datatype to a csv - it's a binary format. You don't need to. Create a datasource to the table you've made from SSRS.
SSRS maps consume spatial datatypes anyway, a csv would be no use.
August 3, 2016 at 11:36 am
Okay then going back to my previous attempt, how would I introduce the INSERT statement for this JOIN?
http://www.sqlservercentral.com/Forums/FindPost1806043.aspx
I asked how to update the table but then the discussion diverted somewhere...
August 3, 2016 at 12:18 pm
Spiff (8/3/2016)
If you need to save the results you can just do:select ... into hurricane_routes ...
If you want to to an INSERT or SELECT INTO on a CTE, you just do it in the last query.
WITH cte AS (...)
SELECT *
INTO my_new_table
FROM cte
You could just use the spatial query in a report dataset anyway.
August 3, 2016 at 12:37 pm
If I insert that JOIN using:
WITH cte AS (JOIN STATEMENT)
I get the error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
I have attached a screen of what I need a new table.
I can't use spatial query if I can't load the data into a new table.
August 3, 2016 at 1:12 pm
I don't know why you need all that. The query I provided creates the whole linestring - of all the points. If you really need just a linestring to the next point - the top part of query where it uses g.ShortestLineTo(lead(g).... does that for you.
You can use your query directly in a report dataset if you want, or just create a view, you don't need to put it in a table.
Why can't you just create the table and stick an INSERT INTO at the top?
August 3, 2016 at 1:25 pm
Spiff (8/3/2016)
I don't know why you need all that. The query I provided creates the whole linestring - of all the points. If you really need just a linestring to the next point - the top part of query where it uses g.ShortestLineTo(lead(g).... does that for you.You can use your query directly in a report dataset if you want, or just create a view, you don't need to put it in a table.
Why can't you just create the table and stick an INSERT INTO at the top?
Because I want lines. I don't want individual points - I don't see how it shows up on SSRS.
This is what I want:
SELECT N'Path' AS Name, CAST('LINESTRING(
-76.5001866085881 42.4310489934743,
-76.4602850815536 43.4353224527794,
-73.4728622833178 44.7028831413324)' AS geography) as Route
But as you can see this is only 3 points, I would need to do it based on the dataset.
In addition, I don't know how to assign a rowid to that data properly without resetting back to 1 for each Name, even though it was included in the original data source you downloaded which I have removed.
I don't want to use ISO_TIMe because it's easier to order with rowid as INT.
WHere can I find a reference for creating views? Is this in VS or MS?
What are you talking about here?
Why can't you just create the table and stick an INSERT INTO at the top?
August 3, 2016 at 2:06 pm
I don't want to use ISO_TIMe because it's easier to order with rowid as INT.
well I disagree.....on the assumption that you wish to plot the course of the hurricane, how else do you wish to do it other than by time.?
________________________________________________________________
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 2:29 pm
THe microsoft example can plot a path with 3 points
SELECT N'Path' AS Name, CAST('LINESTRING(
-76.5001866085881 42.4310489934743,
-76.4602850815536 43.4353224527794,
-73.4728622833178 44.7028831413324)' AS geography) as Route
In my case I would just add : Year Lat Long Name.
I don't need time other than year to isolate each one and by name.
August 3, 2016 at 2:30 pm
J Livingston SQL (8/3/2016)
I don't want to use ISO_TIMe because it's easier to order with rowid as INT.
well I disagree.....on the assumption that you wish to plot the course of the hurricane, how else do you wish to do it other than by time.?
+1
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 3, 2016 at 2:38 pm
My first post -
Spiff (8/3/2016)
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.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;
The clue was I called everything "lines".
Change geometry::UnionAggregate(LineToNext)
to geometry::UnionAggregate(LineToNext).STAsText() and you will see.
August 3, 2016 at 2:51 pm
I get the result in the attached image.
I am pretty close to getting this done but my issue involves storing these tables into new tables. That's the biggest issue at the moment.
Look at the output for damnit2.jpg, Can you tell me where the insert statement goes when you have a subquery?
I could try and use the query after it has been stored:
update [dbo].[hurracane]
set trajtectory=geography::STGeomFromText('LINESTRING('+[COORDS]+')', 4326)
The other option was to store this
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
Viewing 15 posts - 46 through 60 (of 69 total)
You must be logged in to reply to this topic. Login to reply