none
How to Create a Line in a Spatial Type (Geometry or Geography)

    Question

  • I have a series of points and I wish to create lines in a geography column from them. That is to say, I want to create a set of lines each between two discrete points. I can see how to create a point by supplying the lat/long co-ordinates, but there doesn't seem to be an equivalent for creating lines.

    Of course, there is

        geography::STLineFromText('LINESTRING(0 0, 10 10)', 4326)

    for example, but this is quite cumbersome, and in its simplest form requires literals to be supplied. And anyway, I have my point data in two geography
    columns, so intuitively I would expect to be able to write something like

        geography::STLineFromPoints(point1, point2, 4326)

    Can anyone direct me to an elegant technique for doing this, ideally without
    resorting to strings and parsing?

    TIA

    Thursday, August 18, 2011 12:19 AM

Answers

  • What you're describing sounds like the spatial builder API. Here's some simple examples: http://blogs.msdn.com/b/davidlean/archive/2008/10/30/sql-2008-spatial-samples-part-n-5-of-n-sql-builder-api.aspx and you can even do more interesting things with it http://blogs.msdn.com/isaac/archive/2008/05/30/our-upcoming-builder-api.aspx. You can write in-server .NET code (SQLCLR) that uses the API and serves as a wrapper for what you're suggesting.

    You can also create spatial types from XML (GML) with the GeomFromGML (available with both geometry and georaphy) static method.

    Hope this helps. Cheers, Bob Beauchemin, SQLskills

    Thursday, August 18, 2011 2:16 AM
  • By "native", I assume you mean a T-SQL function? I do know what you mean, and I used to feel the same way. Despite the fact that SQLCLR has been around for 6 years now, many developers (and even more DBAs) are still a bit uncomfortable with coding their own CLR functions.

    However, you will learn to embrace SQLCLR - and when you do you'll find you can do much more with SqlGeography and SqlGeometry than you can with T-SQL and the "native" methods exposed by the geometry and geography datatypes; Reprojection, triangulation, routefinding, clustering, as well as your example of programatically creating or extending geometries - all can be done pretty simply in CLR procedures. I think I've already got a function somewhere that does what you want - if I have a dig around and find it I'll let you know.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, August 18, 2011 10:15 AM
    Answerer
  • I tried an experiment, with a simple CLR function, to see what performance was like, and was quite surprised to find that calling a CLR function doubles the time to execute my query. For comparison, my original statement is

        SELECT geography::STGeomFromText('LINESTRING(' + Point1 + ', ' + Point2 + ')', 4326) FROM MyTable

    and my new statement is

        SELECT dbo.CreateLine(Point1, Point2) FROM MyTable

    This is the CreateLine function:

    public static SqlGeography CreateLine(SqlGeography startPoint, SqlGeography endPoint)
    {
     SqlGeographyBuilder constructed = new SqlGeographyBuilder();

     constructed.SetSrid(4326);

     constructed.BeginGeography(OpenGisGeographyType.LineString);

     constructed.BeginFigure(startPoint.Lat.Value, startPoint.Long.Value);

     constructed.AddLine(endPoint.Lat.Value, endPoint.Long.Value);

     constructed.EndFigure();

     constructed.EndGeography();

     return constructed.ConstructedGeography;
    }

    Previoulsy, my CLR functions have been quite fast, so I don't know why this one should suddenly be so slow. The original call takes 3 seconds to return 32,000 rows. With the new function it takes 6 seconds.

    Any thoughts on how I can improve this?

    Charles

     


    Friday, August 19, 2011 8:34 AM

All replies

  • What you're describing sounds like the spatial builder API. Here's some simple examples: http://blogs.msdn.com/b/davidlean/archive/2008/10/30/sql-2008-spatial-samples-part-n-5-of-n-sql-builder-api.aspx and you can even do more interesting things with it http://blogs.msdn.com/isaac/archive/2008/05/30/our-upcoming-builder-api.aspx. You can write in-server .NET code (SQLCLR) that uses the API and serves as a wrapper for what you're suggesting.

    You can also create spatial types from XML (GML) with the GeomFromGML (available with both geometry and georaphy) static method.

    Hope this helps. Cheers, Bob Beauchemin, SQLskills

    Thursday, August 18, 2011 2:16 AM
  • Hi Bob

    I found this when I first starting looking for a solution, but put it to one side in the hope of finding something native. Having been unsuccessful, I then couldn't find the link again, so thanks for pointing me back to it. I suppose I'm surprised that there isn't native support for this kind of basic stuff (as I would see it), but I guess it is easy enough to add it, once one knows how.

    Much appreciated.

    Cheers

    Charles

    Thursday, August 18, 2011 9:23 AM
  • By "native", I assume you mean a T-SQL function? I do know what you mean, and I used to feel the same way. Despite the fact that SQLCLR has been around for 6 years now, many developers (and even more DBAs) are still a bit uncomfortable with coding their own CLR functions.

    However, you will learn to embrace SQLCLR - and when you do you'll find you can do much more with SqlGeography and SqlGeometry than you can with T-SQL and the "native" methods exposed by the geometry and geography datatypes; Reprojection, triangulation, routefinding, clustering, as well as your example of programatically creating or extending geometries - all can be done pretty simply in CLR procedures. I think I've already got a function somewhere that does what you want - if I have a dig around and find it I'll let you know.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, August 18, 2011 10:15 AM
    Answerer
  • Hi Tanoshimi

    I'm quite comfortable with SQLCLR, I just thought more would have been "built-in", from the outset. I would be very interested in any function you have already, though.

    I would also be interested in any reprojection functions. Once I have loaded my points, lines and polygons, I will occasionally need to expose WGS84 long/lat points as WGS84 X/Y points, and I realise that is not built-in either.

    Thanks

    Charles

    Thursday, August 18, 2011 12:53 PM
  • I tried an experiment, with a simple CLR function, to see what performance was like, and was quite surprised to find that calling a CLR function doubles the time to execute my query. For comparison, my original statement is

        SELECT geography::STGeomFromText('LINESTRING(' + Point1 + ', ' + Point2 + ')', 4326) FROM MyTable

    and my new statement is

        SELECT dbo.CreateLine(Point1, Point2) FROM MyTable

    This is the CreateLine function:

    public static SqlGeography CreateLine(SqlGeography startPoint, SqlGeography endPoint)
    {
     SqlGeographyBuilder constructed = new SqlGeographyBuilder();

     constructed.SetSrid(4326);

     constructed.BeginGeography(OpenGisGeographyType.LineString);

     constructed.BeginFigure(startPoint.Lat.Value, startPoint.Long.Value);

     constructed.AddLine(endPoint.Lat.Value, endPoint.Long.Value);

     constructed.EndFigure();

     constructed.EndGeography();

     return constructed.ConstructedGeography;
    }

    Previoulsy, my CLR functions have been quite fast, so I don't know why this one should suddenly be so slow. The original call takes 3 seconds to return 32,000 rows. With the new function it takes 6 seconds.

    Any thoughts on how I can improve this?

    Charles

     


    Friday, August 19, 2011 8:34 AM