locked
Manipulating Geometries via C# RRS feed

  • Question

  • At some point will we be able use the Microsoft.SqlServer.Types and C# (VB, whatever) to programatically create or manipulate a geometry such as add/remove vertices, add/remove "holes" in polygons, or change types?  

     

    For example, assume I have a geometry that is a polygon containing holes.  If all I want is a polygon representing the ExteriorRing of the existing polygon I can call STExteriorRing but it returns a linestring rather than a polygon.  Right now the workaround I have is to build a string representation of the linestring's point collection as a polygon and create a new geometry using STGeomFromText.  Is there a more efficient (or convenient) way to achieve the conversion from a "closed" linestring to polygon?  Can inner rings be deleted somehow?

     

    PS - Will there be more ".net like" ways to traverse for the various collections rather than the 1-based "N" arrays (STPointN, STGeometryN, STInteriorRingN, etc)?  It seems as most other collections now days from other Microsoft objects are 0 based and offer a Count method or enumerator of some sort.

     

    Thanks,

    Kirk

     

    Tuesday, March 4, 2008 4:38 PM

Answers

  • Hi Kirk,

     

    We're definitely aware of the usefulness of programmatically working with the types in .NET code.  There was another thread about this topic that you might find worth reading:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2621826&SiteID=1

     

    We're looking at this, but as you might imagine it's difficult to find a solution that works well in both SQL Server and the client api. 

    Wednesday, March 5, 2008 6:40 PM
  • "what is the best way of creating a shape (GIS object) from the data?"

     

    WKB is much faster than WKT (both in and out), but there is no support for Z and M with WKB (WKT inconsistently supports this). The newest SFS standard adds support for Z and M but SQL Server is implementing the previous standard.

     

    If you need to work with geometries from .NET, try NetTopologySuite. It supports WKB in and out, and you will have easy access to manipulating geometries.

     

    Personally I created a converter for the SqlGeometry and SqlGeography datatypes, so I can avoid the overhead of converting into WKB when querying the database, but unfortunately there isn't a similar option when submitting back to the server since you can only construct SqlGeometries using WKB or WKT.

    Wednesday, March 12, 2008 6:19 AM
    Answerer
  • Sorry I don't have any code I can share, but it's fairly straightforward.

    First check the geometry type using Geometry.InstanceOf or STGeometryType.

    Then you can use the following methods to query the number of sub-types in your geometry: STNumGeometries, STNumInteriorRings, STNumPoints.

    To get the objects, use

    STGeometryN, STPointN (PointCollections/Rings/Lines), STExteriorRing (Polygons), STInteriorRingN (Polygons),  STX (Points), STY (Points).

     

     

     

    Wednesday, March 12, 2008 4:48 PM
    Answerer

All replies

  • I have also a performance question. Having a geometry object from database, what is the best way of creating a shape (GIS object) from the data? Now I'm using geom.STAsBinary() to get WKB and then pass it to the parsing method that will create a shape. I don't know what is the wkb creation implementation, maybe I should traverse the geometry by myself recognizing types, etc.? What is optimal way of parsing the geometry into GIS shape object?
    Wednesday, March 5, 2008 1:39 PM
  • Hi Kirk,

     

    We're definitely aware of the usefulness of programmatically working with the types in .NET code.  There was another thread about this topic that you might find worth reading:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2621826&SiteID=1

     

    We're looking at this, but as you might imagine it's difficult to find a solution that works well in both SQL Server and the client api. 

    Wednesday, March 5, 2008 6:40 PM
  •  

    The reason for the 1-based index is for standards compliance. That is how the OGC specification is, and if you look at the naming of of the methods, they also sound like they are 1-based.
    Saturday, March 8, 2008 10:10 PM
    Answerer
  • "what is the best way of creating a shape (GIS object) from the data?"

     

    WKB is much faster than WKT (both in and out), but there is no support for Z and M with WKB (WKT inconsistently supports this). The newest SFS standard adds support for Z and M but SQL Server is implementing the previous standard.

     

    If you need to work with geometries from .NET, try NetTopologySuite. It supports WKB in and out, and you will have easy access to manipulating geometries.

     

    Personally I created a converter for the SqlGeometry and SqlGeography datatypes, so I can avoid the overhead of converting into WKB when querying the database, but unfortunately there isn't a similar option when submitting back to the server since you can only construct SqlGeometries using WKB or WKT.

    Wednesday, March 12, 2008 6:19 AM
    Answerer
  • "Personally I created a converter for the SqlGeometry and SqlGeography datatypes, so I can avoid the overhead of converting into WKB when querying the database"

    can you share some code? I'm interested in interpreting multi polygons with holes and collections.
    Wednesday, March 12, 2008 9:32 AM
  • Sorry I don't have any code I can share, but it's fairly straightforward.

    First check the geometry type using Geometry.InstanceOf or STGeometryType.

    Then you can use the following methods to query the number of sub-types in your geometry: STNumGeometries, STNumInteriorRings, STNumPoints.

    To get the objects, use

    STGeometryN, STPointN (PointCollections/Rings/Lines), STExteriorRing (Polygons), STInteriorRingN (Polygons),  STX (Points), STY (Points).

     

     

     

    Wednesday, March 12, 2008 4:48 PM
    Answerer