Recently I started a new project in a company involving Geo-Localization. I needed to integrate in my project GPS coordinate and to calculate the distance between them. In this project I decided to use SQL Server 2008 and Fluent-Nhibernate. Sql server 2008 has a great set of data types related to Geo-Localization known as
Geography Data Type. With this fantaboulus data type you can represent point, line even polygon ad make a wide variety of manipulation with them, included calculate the distance between two point. All I needed was to store and retrieve my GPS coordinate into and from the db. I decided to use the SQL Server POINT structure. The structure of a data table was like this:
CREATE TABLE [Content](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Lat] [varchar](50) NOT NULL,
[Lon] [varchar](50) NOT NULL,
[Location] [geography] NULL,
[Messgage] [varchar](400) NULL
)
go
In this project I decided to use NHibernate as O/RM mapper and in particular I decieded to use Fluent NHibernate. For every field of the above table , except for Location, was quite easy do a mapping with Fluent because they're all basic type . But in order to map the Location field I needed to create an ad-hoc type class which I called SqlGeographyUserType. This class implemented the IUserType interface that allows you to create your own custom type.
class SqlGeographyUserType : IUserType
{
public bool Equals(object x, object y)
{
if (ReferenceEquals(x, y))
return true;
if (x == null || y == null)
return false;
return x.Equals(y);
}
public int GetHashCode(object x)
{
return x.GetHashCode();
}
public object NullSafeGet(IDataReader rs, string[] names, object owner)
{
object prop1 = NHibernateUtil.String.NullSafeGet(rs, names[0]);
if (prop1 == null)
return null;
SqlGeography geo = SqlGeography.Parse(new SqlString(prop1.ToString()));
return geo;
}
public void NullSafeSet(IDbCommand cmd, object value, int index)
{
if (value == null)
((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
else
((IDataParameter)cmd.Parameters[index]).Value = ((SqlGeography)value).STAsText().Value;
}
public object DeepCopy(object value)
{
if (value == null)
return null;
var sourceTarget = (SqlGeography)value;
SqlGeography targetGeography = SqlGeography.Point(sourceTarget.Lat.Value, sourceTarget.Long.Value,
sourceTarget.STSrid.Value);
return targetGeography;
}
public object Replace(object original, object target, object owner)
{
return DeepCopy(original);
}
public object Assemble(object cached, object owner)
{
return DeepCopy(cached);
}
public object Disassemble(object value)
{
return DeepCopy(value);
}
public SqlType[] SqlTypes
{
get { return new[] { NHibernateUtil.String.SqlType }; }
}
public Type ReturnedType
{
get { return typeof(SqlGeography); }
}
public bool IsMutable
{
get { return true; }
}
}
In this way I have been able to map SQL Server Geography data type with Fluent Nhibernate:
public class ContentMap : BaseMap<Content>
{
public ContentMap()
{
Map(m => m.Location).CustomType(typeof (SqlGeographyUserType));
Map(m => m.Lat);
Map(m => m.Lon);
Map(m => m.Message);
}
}
Next time I would like to show how to get the distance between two Geography point from SQL Server & Fluent-NHibernate . But who knows when will be? ;-)
posted @ giovedì 25 marzo 2010 03:43