Passing WKT to Stored Procedure RRS feed

  • General discussion

  • I'm new to SQL Server Spatial so what I am writing about here is probably old hat to most of you.

    I have a web app I am developing in Visual Studio 2010. It incorporates the Google Earth Plugin library. One of the many functions of this app is to allow the user to draw a rectangle (rubber-band style) on Google Earth and save the rectangle in a SQL Server table.

    So the Google Earth Plugin API allowed me to handle the drawing and retrieving of the geographic coordinates of the rectangle's four corners. My C# code-behind picked up the coords and put them into a call to a SQL Server stored procedure.

    My frustration had to do with getting the WKT (well known text) just right. I did not know about the importance of the orientation - I had formatted the coordinates in a clockwise fashion when they needed to be in a counter-clockwise orientation. That was good for a couple of hours of hair-pulling. The next thing was the apostrophe shown in every single example I found that goes just before POLYGON and just after a double closing parantheses. I was passing the apostrophe to the stored procedure - dummy me - should've known better.

    Here are the key snippets...

    C# code-behind of my Default.aspx page (building the WKT-formatted string):

    string wktPoly = "POLYGON((" + nwLon + " " + nwLat + ",";
             wktPoly += swLon + " " + swLat + ",";
             wktPoly += seLon + " " + seLat + ",";
             wktPoly += neLon + " " + neLat + ",";
             wktPoly += nwLon + " " + nwLat + "))";

    Parameter definition from C# code-behind of my Default.aspx page:

    cmd.Parameters.Add("@p_wktshape", SqlDbType.VarChar);
    cmd.Parameters["@p_wktshape"].Value = wktPoly;

    Partial signature of the stored procedure:

    create procedure dbo.INSERT_DASH_AOI 
            @p_wktshape varchar(400),

    Inside a BEGIN block in the stored procedure (just a portion of the INSERT INTO statement):

    insert into DS3.dbo.DASH_AOIS (geocoords)
                    values (geography::STGeomFromText(@p_wktshape, 4326));

    Those are the pertinent pieces. I did not want to cloud this post with gobs of code that had nothing to do specifically with the issue I am discussing here.


    Wednesday, November 30, 2011 2:52 AM