notes from a passionate developer





This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

Microsoft.SqlServer.Types and SQL Server 2012, inconsistency

Been working a bit with Microsoft SQL Server 2012 and spatial features using the Geography data type. I'm using it from ADO.Net hence I make use of the Microsoft.SqlServer.Types library. Didn't take long to get annoyed about the API in there. Let's have a look. There´s a type: SqlGeography; that you can use as a parameter against SQL Server. First I created a simple polygon consisting of some coordinates (don't know where on earth since it's taken from MSDN).

Due to lack of building a polygon from actual instances SqlGeography representing points we need to do it the textual way:

var polygon = new SqlChars("POLYGON((
    -122.358 47.653, 
    -122.348 47.649, 
    -122.348 47.658, 
    -122.358 47.658, 
    -122.358 47.653))".ToCharArray());

Now we could create a standard parameter for use in our SQL:

var coordsP = (SqlParameter)cmd.CreateParameter();
coordsP.ParameterName = "pCoords";
coordsP.Direction = ParameterDirection.Input;
coordsP.SqlDbType = SqlDbType.Udt;
coordsP.UdtTypeName = "geography";
coordsP.Value = SqlGeography.STPolyFromText(polygon, 4326);

There´s a method: SqlGeography.Point(latitude, longitude, srid); that you could use to create a simple point, it would look something like this:

var point = SqlGeography.Point(
    47.653, //Latitude 
    -122.358, //Longitude
    4326); //SRID

Lets have a look at the data we inserted using the polygon created above:

    Coords.STAsText() Polygon,
    Coords.STPointN(1).STAsText() Point1,
    Coords.STPointN(1).Lat Point1Latitud,
    Coords.STPointN(1).Long Point1Longitude
from LocationSpatials


  -122.358 47.653,
  -122.348 47.649,
  -122.348 47.658,
  -122.358 47.658,
  -122.358 47.653

POINT (-122.358 47.653)



Tell me you spot it! These kind of inconsistencies drives me crazy! If I in code define a point with Latitude first and Longitude as the second argument, I sure as hell would expect that to be the layout of the STAsText methods as well. Also, isn't it cutym to have it Latitude, Longitude and not Longitude, Latitude? I hope I'm wrong, otherwise the person doing the STAsText method must have been drunk.


View Comments