locked
How to create SqlGeography from polygon? RRS feed

  • Question

  • Hi everyone, I want to create a SqlGeography from a polygon, but I don't know how to input all the points of the polygon into the SqlGeography. Could you help me to solve this? Thanks a lot.
    Tuesday, January 21, 2014 6:05 PM

Answers

  • Basically you start a new SqlGeographyBuilder, set the Srid, begin a polygon, add the polygon lines, end the figure, then end the geography.  You do have to watch for one thing though: if you do the polygon in the wrong direction you'll end up with a polygon that covers the whole surface of Earth except for the little region you actually wanted.  You can counter that by simply checking the area of the polygon and, if it's huge, rebuild the polygon in reverse order.

    The geo builder class docs can be found here:  http://msdn.microsoft.com/query/dev11.query?appId=Dev11IDEF1&l=EN-US&k=k(Microsoft.SqlServer.Types.SqlGeographyBuilder)%3bk(TargetFrameworkMoniker-.NETFramework%2cVersion%3dv4.5)%3bk(DevLang-VB)&rd=true

    • Proposed as answer by Zloth X Tuesday, January 21, 2014 10:28 PM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Tuesday, January 21, 2014 6:37 PM
  • You're restarting the geography for every point.  You want to do the begins and ends outside of the loop.

    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Tuesday, January 21, 2014 10:28 PM
  • First off it looks like you want to create a Polygon but are instead using the MultiPolygon. Second move the line

    geoBuilder.BeginGeography(OpenGisGeographyType.MultiPolygon);

    before the loop and change the type to Polygon.

    Third, move the line

    geoBuilder.EndGeography();

    to after the for loop. Now see if this works.


    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Wednesday, January 22, 2014 10:40 AM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Wednesday, January 22, 2014 10:40 AM
  • You will also have to move the begin/end figure outside of the loop as well. In the loop you would use the AddLine method.

    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Wednesday, January 22, 2014 4:23 PM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:41 PM
    Wednesday, January 22, 2014 4:23 PM
  • Right. That error is to be expected. The points are meant to be ordered in a counter-clockwise order. This is a standard in GIS. One way around this error is to first create this as an SQL Geometry. When done creating the geometry call the MakeValid method on the generated geometry. You can then convert the geometry to a geography by doing something like this:

    SqlGeographyBuilder geom = new SqlGeographyBuilder();
    
    ...
                
    SqlGeography geography = SqlGeography.STGeomFromWKB(geom.ConstructedGeography.MakeValid().STAsBinary(), 4326);


    http://rbrundritt.wordpress.com


    Wednesday, January 22, 2014 5:00 PM
  • Your converting the geoBuilder into an SQL geometry and then calculating the area of that. An SQL geometry will return the area in base units, in this case being degrees squared. What you are meant to do is replace the SqlGeographyBuilder with an SqlGeomtryBuilder. Create the geometry, make it valid, then convert it to an SQL geography. Then calculate the area on the geography. This will return an area in square meters.

    http://rbrundritt.wordpress.com

    Wednesday, January 22, 2014 6:07 PM

All replies

  • Basically you start a new SqlGeographyBuilder, set the Srid, begin a polygon, add the polygon lines, end the figure, then end the geography.  You do have to watch for one thing though: if you do the polygon in the wrong direction you'll end up with a polygon that covers the whole surface of Earth except for the little region you actually wanted.  You can counter that by simply checking the area of the polygon and, if it's huge, rebuild the polygon in reverse order.

    The geo builder class docs can be found here:  http://msdn.microsoft.com/query/dev11.query?appId=Dev11IDEF1&l=EN-US&k=k(Microsoft.SqlServer.Types.SqlGeographyBuilder)%3bk(TargetFrameworkMoniker-.NETFramework%2cVersion%3dv4.5)%3bk(DevLang-VB)&rd=true

    • Proposed as answer by Zloth X Tuesday, January 21, 2014 10:28 PM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Tuesday, January 21, 2014 6:37 PM
  • Thanks for your reply. However, I couldn't make it work. It said "24301: Expected a call to BeginGeography(Polygon) or EndGeography, but AddLine was called." even though I called BeginGeography first. Could you help me to solve this? Thanks in advance. Here is my code:

    SqlGeographyBuilder geoBuilder = new SqlGeographyBuilder();
            geoBuilder.SetSrid(4326);
    
    foreach (System.Windows.Point p in newPolylinePP.Points)
                {
                    geoBuilder.BeginGeography(OpenGisGeographyType.MultiPolygon);
                    //geoBuilder.BeginFigure(p.X, p.Y);
                    geoBuilder.AddLine(p.X, p.Y);
                    //geoBuilder.EndFigure();
                    geoBuilder.EndGeography();
                }

    Tuesday, January 21, 2014 7:19 PM
  • You're restarting the geography for every point.  You want to do the begins and ends outside of the loop.

    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Tuesday, January 21, 2014 10:28 PM
  • First off it looks like you want to create a Polygon but are instead using the MultiPolygon. Second move the line

    geoBuilder.BeginGeography(OpenGisGeographyType.MultiPolygon);

    before the loop and change the type to Polygon.

    Third, move the line

    geoBuilder.EndGeography();

    to after the for loop. Now see if this works.


    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Wednesday, January 22, 2014 10:40 AM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:42 PM
    Wednesday, January 22, 2014 10:40 AM
  • I did what you said but the error is still there. It said: "24301: Expected a call to BeginFigure or EndGeography, but AddLine was called." So I changed to BeginFigure rather than AddLine but it said: "24201: Latitude values must be between -90 and 90 degrees." I checked the error detail and saw that p.X was 349.9911 and p.Y was 625.9934. My polygon is created by the user when he/she touches the map. So could you help me to solve this problem? Thanks a lot.

    Here is my code:

    SqlGeographyBuilder geoBuilder = new SqlGeographyBuilder();
                geoBuilder.SetSrid(4326);
                geoBuilder.BeginGeography(OpenGisGeographyType.Polygon);                       
    
                foreach (System.Windows.Point p in newPolylinePP.Points)
                {                
                    geoBuilder.BeginFigure(p.X, p.Y);
                    //geoBuilder.AddLine(p.X, p.Y);
                    geoBuilder.EndFigure();                
                }
                geoBuilder.EndGeography();

    • Edited by Faro Luce Wednesday, January 22, 2014 2:43 PM
    Wednesday, January 22, 2014 2:42 PM
  • You will also have to move the begin/end figure outside of the loop as well. In the loop you would use the AddLine method.

    http://rbrundritt.wordpress.com

    • Proposed as answer by Ricky_Brundritt Wednesday, January 22, 2014 4:23 PM
    • Marked as answer by Faro Luce Wednesday, January 22, 2014 4:41 PM
    Wednesday, January 22, 2014 4:23 PM
  • I have no problem when drawing polygon in counterclockwise. However, when drawing polygon in clockwise, there was an error saying "24200: The specified input does not represent a valid geography instance." Could you please help me to fix this error? Thanks a lot.
    Wednesday, January 22, 2014 4:48 PM
  • Right. That error is to be expected. The points are meant to be ordered in a counter-clockwise order. This is a standard in GIS. One way around this error is to first create this as an SQL Geometry. When done creating the geometry call the MakeValid method on the generated geometry. You can then convert the geometry to a geography by doing something like this:

    SqlGeographyBuilder geom = new SqlGeographyBuilder();
    
    ...
                
    SqlGeography geography = SqlGeography.STGeomFromWKB(geom.ConstructedGeography.MakeValid().STAsBinary(), 4326);


    http://rbrundritt.wordpress.com


    Wednesday, January 22, 2014 5:00 PM
  • Thanks for your help. Anyway, when I calculate the area, the result is pretty small, like 1/1000 or 1/10,000. Do you know what the issue is? Thanks a lot.

    Here is my code:

    SqlGeometry strkGeom = SqlGeometry.STGeomFromWKB(geoBuilder.ConstructedGeometry.STAsBinary(), 4326);
    
                            
                if (!strkGeom.STIsValid()) 
                    strkGeom = strkGeom.MakeValid();
                
                GeomArea.Header = strkGeom.STArea();

    • Edited by Faro Luce Wednesday, January 22, 2014 5:20 PM
    Wednesday, January 22, 2014 5:19 PM
  • Your converting the geoBuilder into an SQL geometry and then calculating the area of that. An SQL geometry will return the area in base units, in this case being degrees squared. What you are meant to do is replace the SqlGeographyBuilder with an SqlGeomtryBuilder. Create the geometry, make it valid, then convert it to an SQL geography. Then calculate the area on the geography. This will return an area in square meters.

    http://rbrundritt.wordpress.com

    Wednesday, January 22, 2014 6:07 PM
  • I did what you said and the result is good, but I still can't draw in clockwise.

    The error happened to this line "SqlGeography geography = SqlGeography.STGeomFromWKB(geoBuilder.ConstructedGeometry.MakeValid().STAsBinary(), 4326);". It said "24200: The specified input does not represent a valid geography instance."

    Could you please look at my code? Thanks a lot.

    SqlGeometryBuilder geoBuilder = new SqlGeometryBuilder();
                geoBuilder.SetSrid(4326);
                geoBuilder.BeginGeometry(OpenGisGeometryType.Polygon);
                geoBuilder.BeginFigure(inputPoint.First().Longitude, inputPoint.First().Latitude);
                
                foreach (Location p in inputPoint)
                {
                    geoBuilder.AddLine(p.Longitude, p.Latitude);                
                }
    
                geoBuilder.AddLine(inputPoint.First().Longitude, inputPoint.First().Latitude);
                geoBuilder.EndFigure();            
                geoBuilder.EndGeometry();
                            
                SqlGeography geography = SqlGeography.STGeomFromWKB(geoBuilder.ConstructedGeometry.MakeValid().STAsBinary(), 4326);


    • Edited by Faro Luce Wednesday, January 22, 2014 6:51 PM
    Wednesday, January 22, 2014 6:38 PM
  • That should work. Try making the geometry valid the line before turning it into a geography.


    http://rbrundritt.wordpress.com

    Wednesday, January 22, 2014 8:54 PM
  • The error is still there even though I added the validation. Do you know why? Thanks.

    Here is my code:

    geoBuilder.AddLine(inputPoint.First().Longitude, inputPoint.First().Latitude);
                geoBuilder.EndFigure();
                geoBuilder.EndGeometry();

    SqlGeometry geom = geoBuilder.ConstructedGeometry; if (!geom.STIsValid()) geom = geom.MakeValid(); SqlGeography geography = SqlGeography.STGeomFromWKB(geom.STAsBinary(), 4326);


    Wednesday, January 22, 2014 9:02 PM