Simple Spatial query

Question

Probably a simple question, but I'm just starting to 'play' with spatial data, and can't get my head round this.

Given the example data

DECLARE @Towns TABLE
    (
      name VARCHAR(255) ,
      location GEOGRAPHY 
    );
INSERT INTO @Towns VALUES

('Leeds', 'POINT(-1.58333 53.8)'),
('Preston', 'POINT(-2.7166667 53.766667)'),
('Altrincham', 'POINT(-2.35 53.383333)')

SELECT towns.name ,
       towns.location
FROM @Towns AS towns

How do I 'draw'  a line between each point.

I know I can hard code a line such as

   geography::STGeomFromText('LINESTRING(-1.58333 53.8, -2.7166667 53.766667)',4326)

or

   geometry::STGeomFromText('LINESTRING(-1.58333 53.8, -2.7166667 53.766667)',0)

But I thought I could read the data from the table (self joined) to get the data needed to draw the lines.

Answer 1

You're not the first person to be caught out by this, but there is no inbuilt method to create a LineString from a series of Points.

There are a couple of alternatives:

• You could construct the WKT of the LineString dynamically using string manipulation functions, or a SELECT FOR XML statement, say, and then pass that to STGeomFromText().
• Or, you can construct a SQLCLR function that accepts a TVP containing the Points in the order in which they are to be added to the LineString, and use the AddLine() method of the SqlGeographyBuilder() class to construct a geography instance and return that.

For more discussions, try:

http://social.msdn.microsoft.com/Forums/sa/sqlspatial/thread/827c1dec-1d30-4534-b7cc-b88821849207

http://social.msdn.microsoft.com/Forums/sk/sqlspatial/thread/25fdcb43-4790-4110-8c23-dadc6071b3f8

http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/0ff0e3ec-fcb3-4aaa-9fbc-dbca6186d717

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

You're not the first person to be caught out by this, but there is no inbuilt method to create a LineString from a series of Points.

That's good to know!

I had tried to dynamically build the WKT, but was getting errors, so thought it wasn't a valid approach.....

DECLARE @Towns TABLE
    (
      name VARCHAR(255) ,
      location GEOGRAPHY 
    );
INSERT INTO @Towns VALUES

('Leeds', 'POINT(-1.58333 53.8)'),
('Preston', 'POINT(-2.7166667 53.766667)'),
('Altrincham', 'POINT(-2.35 53.383333)')

SELECT towns.name ,
       towns.location,
       geography::Parse(
		   '''LINESTRING(' +
		   replace(replace(towns.location.STAsText(),'POINT (', ''),')','')
		   + ', '+
		   replace(replace(towns2.location.STAsText(),'POINT (', ''),')','')
		   +')'''
       )
FROM @Towns AS towns
join @Towns as towns2 on towns.name <> towns2.name

gives me the error

Msg 6522, Level 16, State 1, Line 12
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24114: The label 'LINESTRING(-2.71666 in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.
System.FormatException: 
   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)

Answer 3

It looks like you might have gotten a bit carried away with your single quotes.... try this instead:

DECLARE @Towns TABLE
    (
      name VARCHAR(255) ,
      location GEOGRAPHY 
    );
INSERT INTO @Towns VALUES

('Leeds', 'POINT(-1.58333 53.8)'),
('Preston', 'POINT(-2.7166667 53.766667)'),
('Altrincham', 'POINT(-2.35 53.383333)')

SELECT towns.name ,
       towns.location,
       geography::Parse(
		   'LINESTRING(' +
		   replace(replace(towns.location.STAsText(),'POINT (', ''),')','')
		   + ', '+
		   replace(replace(towns2.location.STAsText(),'POINT (', ''),')','')
		   +')'
       )
FROM @Towns AS towns
join @Towns as towns2 on towns.name <> towns2.name

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 4

It looks like you might have gotten a bit carried away with your single quotes.... try this instead:

Excellent thanks!  Too much trying to build the string to display, rather than work!