Simple Spatial query

Simple Spatial query

• Wednesday, March 21, 2012 11:30 AM

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.

All Replies

• Wednesday, March 21, 2012 12:45 PM

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:

• Wednesday, March 21, 2012 1:11 PM

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.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)```

• Wednesday, March 21, 2012 2:17 PM

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```