Recently I posted some tips and example script to measure distance between points using SQL Server. The obvious extension of this script would be to generate the lines between two points. I wanted to create a script that took two points from a table and using STLineFromText to create the line. The vast majority of the script is the same as the previous post, with the exception of the line to create the lines. So, here it is:
select t1.Id as ID, t1.NAMEASCII as t1_Name, t1.SOV0NAME as t1_SOV0NAME, t1.Latitude as t1_Latitude, t1.Longitude as t1_Longitude, t2.ID as t2_ID, t2.NAMEASCII as t2_Name, t2.SOV0NAME as t2_SOV0NAME, t2.LATITUDE as t2_Latitide, t2.LONGITUDE as t2_Longitude, Geography::Point(t1.Latitude, t1.Longitude, 4326).STDistance(Geography::Point(t2.LATITUDE, t2.LONGITUDE, 4326)) as Distance_Meters, Geography::STLineFromText('LINESTRING('+cast(cast(t1.longitude as float)as varchar)+' '+cast(cast(t1.latitude as float)as varchar)+','+cast(cast(t2.longitude as float)as varchar)+' '+cast(cast(t2.latitude as float)as varchar)+')', 4326) as line from dbo.Populated_Places t1 cross join dbo.Populated_Places t2 where t1.SOV0NAME = 'United States' and t2.SOV0NAME = 'United States' and t1.ID != t2.ID and t1.NAMEASCII = 'Boston'
A few notes about this script:
- I am using SQL Server 2008 R2.
- Unfortunately, you cannot pass values directly into the STLineFromText method like you can other Spatial SQL methods. When using the STLineFromText the user needs to convert the coordinate pairs into a LineString. This isn’t that big of a deal since the LineString allows a users to string together a series of coordinate pairs to create complex line objects, which is a big plus (in this example I am creating lines between two points), I had to use a cast a couple times to get the coordinate data in the correct format. I experimented with a couple different ideas and this one worked. This probably isn’t the best way to do this so if you have a better idea please post a comment!
- The line that is being generated is a geodesic line. Why is that? I am using the Geography data type and I am calling an SRID of 4326 when STLineFromText is executed. The combination of the two will create “near” geodesic lines as opposed to planar lines.
- Like in the previous post, a self cross join is being used to create the location pairs.
- There are a couple items in the where statement that need to be mentioned. The query above will not work if the coordinate pairs represent the same location, meaning STLineFromText cannot create a line where the two points represent the same space. To prevent this error I simply set the query to not select IDs that are equal. The results of this query return the distance and generates a line between all populated places in the United States from Boston. To prove this works, here is a nice little image:
Ok, that’s it. Pretty simple and pretty fast (the example query takes two seconds to run). Perhaps I’ll put together a simple script to string together multiple points into a line or multi-line object next!