Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS)

Question

I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point.

I want to create a line layer that draws lines between my points based on the order field.

According to http://technet.microsoft.com/en-us/library/ee240828.aspx#LineLayer I need to do something like this:

SELECT N'Path' AS Name
    , CAST(
        'LINESTRING(
           -76.5001866085881 42.4310489934743,
           -76.4602850815536 43.4353224527794,
           -73.4728622833178 44.7028831413324)' 
        AS geography) as Route

But how do I go from my data to something like that?

Answer 1

Below is a simplistic, single-query way to do it as a list of small 2 point line segments in one query, without using cursors. There's a lot more that can be done if you build and store your lines in another table with a cursor and the .STUnion method and your point spatialdata field.

Assumptions and changes: 1. includes city and state. 2. to be more realistic I stored lat and long as decimal(10,7), so, unfortunately this example converts lat and long back to varchar to build the line of Well Known Text (WKT). 3. I renamed order to ptorder since order is a reserved word. 4. Only one path or line exists in the source data table and cities not on the path have a NULL ptorder. 5. ptorder is a sequence without gaps, or else the query gets far more complicated. 6. I'm avoiding cursors. 7. I'm assuming your data looks like this:

5,    42.37, -71.03,  'Boston', 'MA' 
3,    39.75, -104.87, 'Denver', 'CO' 
2,    33.93, -118.40, 'Los Angeles', 'CA'
4,    25.82, -80.28,  'Miami Intl', 'FL' 
NULL, 40.77, -73.98,  'New York', 'NY'
1,    33.43, -112.02, 'Phoenix', 'AZ'

Load some test data like this:

CREATE TABLE pointdatatable (
    ptorder             INT, 
    lat                 DECIMAL(10,7), 
    long                DECIMAL(10,7), 
    pointspatialdata    GEOGRAPHY,    
    city                VARCHAR(20),
    state               CHAR(2) )

INSERT INTO pointdatatable (ptorder, lat, long, pointspatialdata, city, state) VALUES
    (5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA'), 
    (3, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'), 
    (2, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'), 
    (4, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'), 
    (NULL, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'), 
    (1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ')

Then this query produces the segments using WKT:

SELECT 
    a.ptorder,
    a.lat,
    a.long,
    a.city,
    b.ptorder,
    b.lat,
    b.long,
    b.city AS nextcity,
    CAST('LINESTRING(' + CAST(a.long AS VARCHAR) +' '+ CAST(a.lat AS VARCHAR) +', '+ 
        CAST(b.long AS VARCHAR) +' '+ CAST(b.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
FROM 
    pointdatatable a
LEFT OUTER JOIN 
    pointdatatable b
ON     
    b.ptorder = a.ptorder + 1 
WHERE 
    b.ptorder IS NOT NULL   
ORDER BY a.ptorder