locked
Creating SqlGeometry/SqlGeography using CLR types RRS feed

  • Question

  •  

    Does anyone have a codesample of how to build a geometry or geography type from .NET, and/or modifying the rings/parts/point-collections later ? So far I've only been able to create instances using WKB and WKT.
    Thursday, December 27, 2007 5:46 PM
    Answerer

Answers

  •  

    This is a good discussion, thanks everybody - we are definitely aware of the usefulness of creating and modifying SqlGeometry/SqlGeography instances through an API rather than going through WKT/WKB/GML.  We're looking into solutions for this, but it's not yet clear whether they will be in Katmai.
    Wednesday, January 2, 2008 8:03 PM

All replies

  • I just did this in an SSIS script task. Basically, I have a table that has ~42000 US ZipCodes and their centerpoints as Lat/Lon. Now I want to create a collection that holds all of the zipCodes CenterPoints for each state. Here's the meat of that script task:

     

     

    SqlGeography _Geo;
    public override void PostExecute() {
        base.PostExecute();
    }
    public override void PreExecute() {
        base.PreExecute();
        _Geo = new SqlGeography();
    }
    public override void CreateNewOutputRows() {
        base.CreateNewOutputRows();
        Output0Buffer.AddRow();
    }
    public override void Input0_ProcessInput(Input0Buffer Buffer) {
        base.Input0_ProcessInput(Buffer);
        if (Buffer.EndOfRowset()) {
            Output0Buffer.RegionID = this.Variables.RegionID;
            Output0Buffer.StateGeo.AddBlobData(_Geo.STAsBinary().Buffer);
            Output0Buffer.SetEndOfRowset();
        }
    }
    public override void Input0_ProcessInputRow(Input0Buffer Row) {
        SqlBytes b = new SqlBytes(Row.point);
        SqlGeography p = SqlGeography.STGeomFromWKB(b, 4326);
        _Geo = _Geo.STUnion(p);
    }

    The real trick here seems to be the use of the .STUnion method.

    Friday, December 28, 2007 9:33 PM
    Answerer
  • You are still creating your geometry using Well-Known Binary.

    Furthermore, creating a union of two points just give you a MultiPointCollection.

    My problem is creating LineStrings, Rings, Holes and Polygons, without including a WKB/WKT converter in/between.

     

    It's not that I can't do that, but I want simplicity. In order to use WKB I would first have to create my own geometry model, and then afterwards create a WKB converter, then lastly import that using STGeomFromWKB. That is just overkill. It would be nice to be able to use SqlGeometry and SqlGeography directly instead.

    Friday, December 28, 2007 9:40 PM
    Answerer
  • You are still creating your geometry using Well-Known Binary.

     

    Yes, because SSIS doesn't natively understand the GeoTypes yet, so to fetch them for processing at that level, I have to WKB or WKT. I picked binary to reduce serialization/deserialization work and to minumize the OTW representation. Nothing to do with the problem space per set.

     

    Furthermore, creating a union of two points just give you a MultiPointCollection.

     

    Sure, which for now is close to what I want. What I really want to do now is find some way to take these points and covert them into bounding polygons. For example, I want to find the points that are edge-most then string them together. It doesn't look to me any of the supported OCG methods do that.

     

    It's not that I can't do that, but I want simplicity. In order to use WKB I would first have to create my own geometry model,

     

    I've noticed that SqlGeography does have a .Read and .Write method that a problem exposing the native storage of the types. If you can parse out how that works, you've pretty much got what you want.

     

    But I do agree, that's a lot of work.

    Friday, December 28, 2007 10:19 PM
    Answerer
  •  

    I've noticed that SqlGeography does have a .Read and .Write method that a problem exposing the native storage of the types. If you can parse out how that works, you've pretty much got what you want.

     

    How is that giving me what I want? Whether I use WKB or some other binary format doesn't make any difference. I would still have to store my data in my own geometry model and create a converter that SqlServer can parse (this time based on an internal non-documented format that could potentially change any time).

     

    I'm looking for something much much simpler, that will also give me many more compile-time errors than parsing in string or byte arrays. For instance (thought example):

    ///Create new polygon

    SqlPolygon geom = new SqlPolygon();

    PointCollection ring = new PointCollection();

    ring.Add(new Point(23,34));

    ring.Add(new Point(43,35));

    ring.Add(new Point(23,32));

    ring.Add(new Point(23,34));

    geom.ExteriorRing = ring;

    return geom as SqlGeometry;

     

    //Modify polygon by translating one vertex

    SqlGeometry ring = geom.STExteriorRing();

    ring.STPointN(34).STX += 10;

    ring.STPointN(34).STY -= 10;

     

    I know this doesn't fit with the current object model, but I just want to use simple .NET types to accomplish something like this.

     

    My main reason for this is that I want to create an embedded assembly that takes a SqlGeometry/SqlGeography as input, performs some changes to the geometry (either by modifying/adding/removing points) or by creating a new instance and returns the new geometry. Right now, as far as I can tell, I would have convert the input into my own geometry model, do the modifications, convert to WKB and then convert that back to SqlGeometry. I also want performance, so I would prefer not having the overhead of all these conversions.

     

    Bottomline: The geometry type allows me to get every single part and point, but not to set or instantiate them. I just wonder whether that is really true, and whether Microsoft have given this any thought.

    Friday, December 28, 2007 10:40 PM
    Answerer
  • I see now what you are looking for, and I agree. About the only way I can see do anything like this would be roll your types that inherit from SqlGeo* and then provide the functionality you want.

     

    ICK.

     

    There is an extended method to get the GML, which you might be able to operate on with XQuery or LINQ. But again, that's not really that much different than WKB/WKT.

     

    ICK.

     

    If they aren't giving it thought now, I'll see if I can't find Steven or Isaac next time I'm on campus and ask why.

    Friday, December 28, 2007 11:05 PM
    Answerer
  • "About the only way I can see do anything like this would be roll your types that inherit from SqlGeo* and then provide the functionality you want"

     

    Wouldn't work. The methods I need to override/extend are not marked public protected.

     

    "If they aren't giving it thought now, I'll see if I can't find Steven or Isaac next time I'm on campus and ask why"

     

    No worries. I know they read this forum regularly, but let them have their holiday :-)

    They probably already thought a bit about it (if not, now there's no excuse not to ;-), but I'm not really sure how much emphasis they put on helping us .NET developers at the moment, or whether they for now only focus on the SqlServer side of things.

     

    I would imagine that .NET is becoming one of the biggest languages used for SqlServer clients, and since the types are already there in .NET, it wouldn't take much extra work to add a few clr-only/non-sqlclr methods.

    Friday, December 28, 2007 11:13 PM
    Answerer
  • They probably already thought a bit about it (if not, now there's no excuse not to ;-), but I'm not really sure how much emphasis they put on helping us .NET developers at the moment, or whether they for now only focus on the SqlServer side of things.

     

    Two thoughts:

     

    a.) Something to sell in SQL 2011 Smile

    b.) Having done a few of my own UDTs to Yukon, having "client side" vs. "server side" type implitations is PITA. Its best of the are unified.

    Friday, December 28, 2007 11:19 PM
    Answerer
  • "What I really want to do now is find some way to take these points and covert them into bounding polygons."

     

    Is STConvexHull the function you are looking for? I know that the convex hull as a general spatial algorithm does what you describe, however I have not got as far as testing it in SQL Sever 2008 yet.

    Saturday, December 29, 2007 11:49 PM
  • it might. Right now I have that dataset as a Geography, not a geometry. Wiil try soon.

    Sunday, December 30, 2007 12:06 AM
    Answerer
  • Hi Morten,

     

    I felt exactly the same when I first looked into the .Net side of the types. http://sqlblogcasts.com/blogs/simons/archive/2007/12/09/Katmai-Spatial---First-thoughts.aspx

     

    I started a response but it got quite lengthy so I turned it into a blog post SQL Server 2008 Spatial - Using it in .Net code

    Monday, December 31, 2007 8:51 AM
  •  

    This is a good discussion, thanks everybody - we are definitely aware of the usefulness of creating and modifying SqlGeometry/SqlGeography instances through an API rather than going through WKT/WKB/GML.  We're looking into solutions for this, but it's not yet clear whether they will be in Katmai.
    Wednesday, January 2, 2008 8:03 PM
  • Thanks Steven, can you pass along an similar comment about the path-wise members of HierarchyID?

    Wednesday, January 2, 2008 8:13 PM
    Answerer