locked
Code diagnosis please! Simple geographyBuilder Polygon insert/update => does not represent a valid geography instance. RRS feed

  • Question

  • Hi,

    Could anybody tell me why this simple attempt to add a Polygon to an SQL Server 2008 table field with type "geography" throws the following exception:
    [ArgumentException: 24200: The specified input does not represent a valid geography instance

    My Code: (note that I have similar code that successfully ads a point to the same table, so my DB basics should be ok):

        protected void DoPolygon()
        {
            try
            {
                SqlGeographyBuilder geographyBuilder = new SqlGeographyBuilder();

                SqlGeography geography;

                geographyBuilder.SetSrid(4326);

                // state what type of geography object that I to create
                geographyBuilder.BeginGeography(OpenGisGeographyType.Polygon);

                List<GoogleLatLng> lstPoints = new List<GoogleLatLng>();
                lstPoints.Add(new GoogleLatLng(48.049241580151, 7.72816300392151));
                lstPoints.Add(new GoogleLatLng(47.9876454318799, 7.85703778266907));
                lstPoints.Add(new GoogleLatLng(48.1398668399212, 7.16331124305725));
                lstPoints.Add(new GoogleLatLng(48.593529576211, 7.67631953125));

                geographyBuilder.BeginFigure(lstPoints[0].Latitude, lstPoints[0].Longitude);

                GoogleLatLng[] gglArr = lstPoints.ToArray();
                int len = gglArr.Length;
                for (int idx = 1; idx < len; idx++)
                {
                    geographyBuilder.AddLine(gglArr[idx].Latitude, gglArr[idx].Longitude);
                }
                //SqlGeography Polygon requires the first and last points of the polygon to be identical
                //[FormatException: 24306: The Polygon input is not valid because the start and end points of the ring are not the same. Each ring of a polygon must have the same start and end points.]
                geographyBuilder.AddLine(gglArr[0].Latitude, gglArr[0].Longitude);

                // close the figure and geography class
                geographyBuilder.EndFigure();

                geographyBuilder.EndGeography();

                // get the geography builder to return the sqlgeography type
                geography = geographyBuilder.ConstructedGeography;

                // set the command text
                String pKeyName = "_RUID";
                Guid pKeyVal = new Guid("12da1e40-3836-4dc0-b577-93b1fcf3a582");
                String tblName = "rc_Resources";
                String colName = "_Area_Polygon_UDT";

                String sqlUpdateCmd = String.Format("UPDATE {0} SET {1} = @pGeoPolygon WHERE {2} = '{3}';", tblName, colName, pKeyName, pKeyVal);

                SqlConnection sqlConn = new SqlConnection(rcShared.RcRDBConnStr);
                sqlConn.Open();

                SqlCommand sqlCommand = new SqlCommand(sqlUpdateCmd, sqlConn);

                sqlCommand.CommandType = CommandType.Text;

                sqlCommand.Parameters.Add(new SqlParameter("@pGeoPolygon", geography) { UdtTypeName = "Geography" });

                sqlCommand.ExecuteNonQuery();
                sqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }


    Many thanks for any tips!
    Richard


    Hubert-Associates
    Sunday, February 22, 2009 9:35 PM

Answers

  •  At a guess, I'd say that you've got the wrong ring orientation... your list of lat/longs should be as follows:

    lstPoints.Add(new GoogleLatLng(48.593529576211, 7.67631953125));  
    lstPoints.Add(new GoogleLatLng(48.1398668399212, 7.16331124305725));  
    lstPoints.Add(new GoogleLatLng(47.9876454318799, 7.85703778266907));  
    lstPoints.Add(new GoogleLatLng(48.049241580151, 7.72816300392151));  


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Sunday, February 22, 2009 10:02 PM
    Answerer

All replies

  •  At a guess, I'd say that you've got the wrong ring orientation... your list of lat/longs should be as follows:

    lstPoints.Add(new GoogleLatLng(48.593529576211, 7.67631953125));  
    lstPoints.Add(new GoogleLatLng(48.1398668399212, 7.16331124305725));  
    lstPoints.Add(new GoogleLatLng(47.9876454318799, 7.85703778266907));  
    lstPoints.Add(new GoogleLatLng(48.049241580151, 7.72816300392151));  


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Sunday, February 22, 2009 10:02 PM
    Answerer
  • Thanks tanoshimi, for taking a look. Should have known it was my data :-) Surprises me that the error message regarding the "last point must be same as first" was crystal clear, whereby the one "not a valid geography" is pretty ambiguous. The behavior is actually an unexpected plus, but may cause me some problems since the binary arrays of google LatLongs that I currently accept do not care what the "polygon" looks like. So, I can use this exception to indicate that the data is not of best quality.

    Thanks again for your help, I'd never have looked there!

    Richard

    Hubert-Associates
    Monday, February 23, 2009 8:20 AM
  • No probs!

    Sometimes you get a more specific error (something like "this geography instance is invalid because it exceeds a single hemisphere.... a common reason for this is incorrect ring orientation.") whereas sometimes you get more generic error - I'm not sure why.

    If you're looking for a way to programmatically fix potential problems with clockwise/anticlockwise geography polygons, check out Ed's post here: http://blogs.msdn.com/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, February 23, 2009 8:39 AM
    Answerer
  • Great Tip, it is time to order your book ;-)
    R

    Hubert-Associates
    Monday, February 23, 2009 9:01 AM
  • @ H-A. I order'd Tanoshimi's book a few months back too, after seeing how little I knew (and still do) about all this GIS stuff.

    sometimes it brings my brain to mash.

    for myself, i've spent months and months just trying to import various data files into the dabase (which i'm still no closer than where i was, months and months ago). After that, i spend most of my time trying to get my spatial queries performant 'cause my sql sqls are poor.
    -Pure Krome-
    Thursday, April 23, 2009 12:19 AM