NHibernate.Spatial and Sql 2008 Geography type - H

问题:

I am trying to use Nhibernate with the Sql 2008 Geography type and am having difficulty. I am using Fluent Nhibernate to configure which I am fairly new to so that may be the problem as well.

First, the class I am trying to persist looks something like:

public class LocationLog : FluentNHibernate.Data.Entity
{
   public virtual new int Id {get;set;}
   public virtual DateTime TimeStamp {get;set;}
   public virtual GisSharpBlog.NetTopologySuite.Geometries.Point Location {get;set;}
}

The mapping class looks like:

public class LocationLogMap : ClassMap<LocationLog>
{
   ImportType<GisSharpBlog.NetTopologySuite.Geometries.Point>();
   Id(x => x.Id);
   Map(x => x.TimeStamp).Generated.Insert();
   Map(x => x.Location);
}

In order to use the MsSql2008GeographyDialect with Fluent Nhibernate, I have created my own configuration class:

public class Sql2008Configuration
  : PersistenceConfiguration<Sql2008Configuration, MsSqlConnectionStringBuilder>
{
   public Sql2008Configuration()
   {
      Driver<SqlClientDriver>();
   }

   public static Sql2008Configuration MsSql2008
   {
      get { return new Sql2008Configuration().Dialect<MsSql2008GeographyDialect>(); }
   }
}

so I have configuration code like:

var configuration = Fluently.Configure()
  .Database(Sql2008Configuration.MsSql2008.ConnectionString(c => c.Is(connectionString)))
  .Mappings(m => m.FluentMappings
    .AddFromAssemblyOf<LocationLog>()
);

All of this to setup the fact that I am getting the following error when trying to persist the LocationLog type to the database:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": System.ArgumentException: 24204: The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view. System.ArgumentException: at Microsoft.SqlServer.Types.SqlGeography.set_Srid(Int32 value) at Microsoft.SqlServer.Types.SqlGeography.Read(BinaryReader r) at SqlGeography::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )

I have read the following articles about how to configure and use the Nhibernate Spatial libraries:

• http://nhibernate.info/doc/spatial/configuration-and-mapping.html
• http://nhibernate.info/doc/spatial/sample-usage.html

but neither seem to help. Anybody that has experience configuring Nhibernate to use the Spatial Geography types who could provide any insights would be greatly appreciated.

回答1:

I am in the same boat, and thanks to your start I got it working (inserting and reading spatial data). For anyone else who is interested, firstly the GisSharpBlog.NetTopologySuite.Geometries.Point class is in NetTopologySuite.dll which is part of the nHibernate.Spatial download.

Secondly, as per James point, make sure you set the SRID to 4326.

And lastly, the map needs to look like this:

Map(a => a.Location).CustomType(typeof(NHibernate.Spatial.Type.GeometryType));

I am using Geography, but I read somewhere that using GeometryType may work and it does for me (I inserted some points and verified it in the database). I also read that its best to write SQL Query's for Geography so that you can use the special SQL 2008 Spatial methods (as opposed to using Criteria).

回答2:

Steve is right, you need to explicitly set a SRID for your geometry type. Looking at the NHibernate.Spatial source (which you can checkout using SVN or whatever), doing a search for SRID comes up with this buried in the code as a comment hint:

<class name="MyGeoTableA">
    <property name="MyGeoColumn">
        <type name="NHibernate.Spatial.Type.GeometryType, NHibernate.Spatial">
            <param name="srid">1234</param>
        </type>
    </property>
</class>

It looks like you need to set a parameter named SRID to whatever number you need (look it up in a SRID table). Obviously this is old-school XML configuration but fluent will have a method somewhere to add key/value string parameters. Give that a try.

---

Edit

After a bit more research, I found that trying to set a srid attribute on a column failed NHibernate's XML mapping validation, it throws an XmlSchemaValidationException. Instead, I found that geometry types in NetNopologySuite have a SRID attribute on the object themselves, and setting this makes things work. e.g.

LocationLog log = new LocationLog { Location = new Point() };
log.Location.SRID = 4326;
Session.Save(log);

There must be a better way to do this though (configure it instead of setting it all the time) but I haven't worked that out yet. If you look inside MsSql2008GeometryType class, it has a protected method called SetDefaultSRID(IGeometry) - it must be there for a reason!

回答3:

Not really an answer but questions ;-)

• Are you setting a SRID on the GisSharpBlog.NetTopologySuite.Geometries.Point object?

The default (since the point is a geometry) is 0 and will give you a SQL error when trying to persist the LocationLog.Location property as a geography. 0 is not a valid SRID for sql geography fields. You will need to specify one from the sys.spatial_reference_systems view.

• Have you tried without Fluent NHibernate?

To eliminate as many components from the problem.

回答4:

You can create your own factory with a default SRID. For example, you can create a facade for factories such as this:

public static class Default
{
    private const int DefaultSrid = 4326;

    public static readonly IGeometryFactory Factory;

    static Default()
    {
        Factory = new GeometryFactory(new PrecisionModel(), DefaultSrid);
    }
}

and use it like this:

var point = Default.Factory.CreatePoint(new Coordinate(10, 10));

instead of using the "new" keyword. You can also use the Default.Factory as a factory method in your IoC framework to create new geometries without the Default facade.

回答5:

I know this hardly be useful but in any case. After implementing all lain had said do use in your HQL queries SetParameter third IType parameter. Meaning in

 Hero hero = openSession.Get<Hero>(3);    
openSession.CreateQuery(
              "from Hero h where NHSP.Distance(h.Location,:thislocation)<1000"
               ).SetParameter("thislocation", hero.Location, new CustomType(typeof(MsSql2008GeographyType), null) ).SetResultTransformer(new DistinctRootEntityResultTransformer())
               .List();

new CustomType(typeof(MsSql2008GeographyType), null) must be passed or you get your all too famililar "System.ArgumentException: 24204".

Just spent whole night figuring that one out.