Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
PostGIS has a makeline function that converts points given by some order into lines. Does such a function exist in SQL Server? Can't find it myself, hence this is a comment not an answer. But I'm no SQL server person. postgis.refractions.net/pipermail/postgis-users/2007-September/… for the PostGIS technique. –  Spacedman Sep 27 '12 at 8:37
    
Some discussion here seems to imply there's no readymade function like in PostGIS, but you can write something: social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/… –  Spacedman Sep 27 '12 at 8:40

1 Answer 1

up vote 1 down vote accepted

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     
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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