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(
           -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?


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:

    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
    pointdatatable a
    pointdatatable b
    b.ptorder = a.ptorder + 1 
    b.ptorder IS NOT NULL   
ORDER BY a.ptorder     

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.