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.
Further to Russell's Answer
It's unfortunate that you have you locations stored in the wrong data type. The easiest way to get an accurate distance between your is to cast the geometries as geographies.
You will need to confirm the the Lat is stored in the Y and the Long is stored in the X of the geometry, otherwise you will most likely end up with inaccurate distances.
If you are doing a Nearest Neighbour query you will need to take into account that 1º North/South is different than 1º East/West and the distance returned from a geometry containing Lat/Long will not reflect this. Generally this is unlike to cause an issue, but you may end up with nearest neighbours in the wrong order.
To demonstrate this
CREATE TABLE #TestGeomVsGeog (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Geom Geometry,
Geog Geography
);
INSERT INTO #TestGeomVsGeog (Geom, Geog)
VALUES
(Geometry::Point(-57,-25,4326), Geography::Point(-25,-57,4326)), -- AnchorPoint
(Geometry::Point(-58,-25,4326), Geography::Point(-25,-58,4326)), -- 1 degree East
(Geometry::Point(-57,-26,4326), Geography::Point(-26,-57,4326)), -- 1 degree South
(Geometry::Point(-57,-25.9999,4326), Geography::Point(-25.9999,-57,4326)) -- Closest Point
;
SELECT a.ID FromID, b.ID ToID,
a.geom.STDistance(b.geom) GeomDegreeDistance,
a.geog.STDistance(b.geog) GeogMetreDistance,
ROW_NUMBER() OVER (ORDER BY a.geom.STDistance(b.geom)) GeometryOrder,
ROW_NUMBER() OVER (ORDER BY a.geog.STDistance(b.geog)) GeographyOrder
FROM (SELECT * FROM #TestGeomVsGeog WHERE ID = 1) a,
(SELECT * FROM #TestGeomVsGeog WHERE ID != 1) b
DROP TABLE #TestGeomVsGeog
This returns the following
FromID ToID GeomDegreeDistance GeogMetreDistance GeometryOrder GeographyOrder
----------- ----------- ---------------------- ------------------ ---------------- ----------------
1 2 1 100949.861402787 2 1
1 4 0.9999 110769.324596193 1 2
1 3 1 110780.403395342 3 3
As you can see the Geometry distance will end up with the wrong point as the nearest neighbour.
Depending on the amount of data you are dealing with I would suggest creating copy of the original into temporary table (using geography) to do the processing on, or lobby to have the original table datatype changed to a geography.
Best Answer
Try using brackets around
LatandLong: