August 2, 2016 at 11:49 am
Hi all,
has anyone created line maps using SSRS?
I have a thread that is relevant to this topic but if anyone has done this before that would be helpful.
The link is here:
http://www.sqlservercentral.com/Forums/Topic1805751-3077-4.aspx
The help file (https://msdn.microsoft.com/en-us/library/ee240828.aspx#LineLayer) has an example
SELECT N'Path' AS Name, CAST('LINESTRING(
-76.5001866085881 42.4310489934743,
-76.4602850815536 43.4353224527794,
-73.4728622833178 44.7028831413324)' AS geography) as Route
However, if you want to do this with lots of data in a csv, how should the data be imported? Should it be done in management studio as a database?
If so, how did you limit your columns of data to one column and not based on the number of lat/long coordinates?
Should the data be cleaned into a new csv for which you would just import?
If so, how did you avoid the error associated with the string after converting to geography?
I'm lost at this point. Your help is much appreciated. Thanks
August 3, 2016 at 6:55 am
I have replied in the other thread. The gist is:
Import your data as individual points e.g. (long, lat) if that is how they are recorded.
You can then convert these to Points as a geometry type:
UPDATE imported_stuff SET g = geometry::STGeomFromText('POINT('+ CONVERT(VARCHAR,long) + ' ' + CONVERT(VARCHAR,lat) + ')',0);
Once you have the data imported like that you can use spatial aggregates (SQL2012+) to combine the data.
SELECT name, geometry::UnionAggregate(g) FROM imported_stuff GROUP BY name
Doing it this way gives you the most flexibility as you can filter the points by a where clause, group them etc.
To convert these all to lines rather than collections of points:
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;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply