none
Simple Spatial query

    Pregunta

  • 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.

    miércoles, 21 de marzo de 2012 11:30

Respuestas

  • 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/

    • Propuesto como respuesta Naomi N miércoles, 21 de marzo de 2012 13:52
    • Marcado como respuesta Kev Riley miércoles, 21 de marzo de 2012 14:24
    miércoles, 21 de marzo de 2012 12:45
  • 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/

    • Marcado como respuesta Kev Riley miércoles, 21 de marzo de 2012 14:23
    miércoles, 21 de marzo de 2012 14:17

Todas las respuestas

  • 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/

    • Propuesto como respuesta Naomi N miércoles, 21 de marzo de 2012 13:52
    • Marcado como respuesta Kev Riley miércoles, 21 de marzo de 2012 14:24
    miércoles, 21 de marzo de 2012 12:45
  • 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)

    miércoles, 21 de marzo de 2012 13:11
  • 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/

    • Marcado como respuesta Kev Riley miércoles, 21 de marzo de 2012 14:23
    miércoles, 21 de marzo de 2012 14:17
  • 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!

    miércoles, 21 de marzo de 2012 14:24