[GIS] Insert Geography data into SQL Server 2008

cgeographysql server

Can someone guide me to or give me an example on how would one go about insert/query Geography data in Sql Server 2008 ( Polygon with 4 points and Point in specific) using C# EF or Linq2Sql.

I've been searching for few days now and i can't figure it out 🙁

Thanks in advance!

Best Answer

Hopefully you've found the answer you're looking for by now but I just came across this and figured I'd take a stab at answering it for others at least.

From C# here's what I do for inserting a Polygon. I'm deriving this on the fly from some working code I have but not actually testing it so forgive any typo errors.

You'll need the following assemblies. System.Data.SqlClient; System.Data.SqlTypes; System.Data.SqlServer.Types;

public void Main()
{
    // the coordinate parameters below are doubles that can gathered from wherever, you can add additional points as necessary to the POLYGON list just make sure that they from a counter-clockwise 'circle'

    string areaName = "Texas";
    string extents = string.Format("POLYGON(({0} {1}, {0} {2}, {3} {2}, {3} {1}, {0} {1}))", leftLongitude, upperLatitude, lowerLatitude, rightLongitude));

    InsertArea(areaName, extents);

}

public void InsertArea(string nameParameter, string extentsString)
{
    SqlConnection sqlConn = new SqlConnection(...)

    sqlConn.Open();

    SqlCommand sqlCom = new SqlCommand("INSERT INTO areas (name, extents) VALUES (@name, @extents)", sqlConn);

    sqlCom.Parameters.AddWithValue("@name", nameParameter);

    SqlParamater extents = new SqlParameter("@extents", SqlDbType.Udt);
    extents.UdtTypeName = "Geography";
    extents.Value = GetGeographyFromText(extentsString);

    sqlCom.Parameters.Add(extents);

    sqlCom.ExecuteNonQuery();

    sqlConn.Close();
}

public SqlGeography GetGeographyFromText(String pText)
{
    SqlString ss = new SqlString(pText);
    SqlChars sc = new SqlChars(ss);
    try
    {
        return SqlGeography.STPolyFromText(sc, 4326);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

I hope this helps someone.

Related Question