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?
Best Answer
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:
Load some test data like this:
Then this query produces the segments using WKT: