How to draw Spatial Line from Spatial Points in SQL Server

     In this post, I want to show you how easy it is to draw a spatial line by using spatial points. To make the following demo to work, you must have SQL Server 2017 or later. The reason is, I will use the new system functions STRING_AGG and CONCAT_WS. There are not Spatial functions and you can draw a spatial line by using spatial points. They will make this process easy. You can read about these new function in my older post here.

     I downloaded Hurricane data from NOAA for free. Dataset has the location of the hurricane eyes in latitude and longitude. By knowing the location, its pretty easy to display these points as Spatial data (geography). I wanted to connect these points to each other and create a line, by doing that I could add a buffer around the line and make a spatial range search and find if I have any customer under this line.

    Here what my data looks like.

  Latitude and Longitude comes in string. I created a new column to store this information as Spatial Data type then convert string to geography data type by using the following query.

ALTER TABLE Hurricanes2005 add LocationGeog geography 
UPDATE Hurricanes2005 set LocationGeog = geography::Point(Latitude, Longitude, 4326)

     I have created a new spatial column named LocationGeog and populate it by using Latitude and Longitude information. You can use Long and Lat names to see point's Latitude and Longtitudes.

     Now, I am ready to convert these valid points into a spatial line. Spatial Line format is LineString(point1, point2, .....)  I need to pick all the points of Katrina and merge them together. There should be an empty space between each latitude and longitude. Also, there should be a comma between each point. Here is an example of a valid Spatial Line.

LINESTRING(5 6, 6 5, 7 8)

     If you have SQL Server 2017 or later, you can use two new functions of SQL Server 2017. I am going to use String_Agg function to insert comma between each points and Concat_WS function to add space between each Longitude and Latitude.

SELECT geography::STGeomFromText('LINESTRING(' + STRING_AGG(CONCAT_WS(' ', LocationGeog.Long, LocationGeog.Lat),',') + ')',4326) 
FROM Hurricanes2005 WHERE name ='KATRINA'

This returns me the path of Katrina as you can see in the following screenshot.