Tuesday, February 14, 2012

NHibernate Spatial (Part 1 - Introduction)

NHibernate is a nice, tested and mature ORM. I've used it extensively since version 1.2 and it just keeps getting better and better.
It obviously provides many challenges, most of them regarding it's learning curve and some limitations, like handling spatial data at the database

Nowadays several Databases provide spatial extensions. The most notorious are probably PostGIS, Oracle Spatial and more recently SQL Server 2008. NHibernate does not natively handle this type of data but fortunately there's a library that fills this gap quite nicely: NHibernate Spatial Extensions.

In this post (or series of posts) I'm going to do a complete tutorial on setting up a SQL Server 2008 R2 database with spatial columns, mapping the tables to domain objects using NHibernate Spatial and querying them with HQL and QueryOver.

  Although NHibernate Spatial Extensions is a great project, it's being developed in "short bursts". Looking at the SVN repository we see active development through:
  • August 2008 - October 2008
  •  February 2009 - July 2009 
  • October 2010 - December 2010 
  • and finally, almost a year later, December 2011

The binary version available to download is from NHibernate 2.1 so getting the code from NH Contrib SVN repository and building it is almost a requirement.

First step:
  • Open the solution NHibernate.Spatial.vs2008.sln
  • Build it

WTF? The build fails?

I appreciate the tribute to my blog, but let's fix this.

The error states that:

I know for a fact that this project used to compile correctly so, looking at the recent commits I see this:

The extension method IsNull was removed on the changeset 1688. I don't really understand why, but I'll add it again.

Copy&Paste friendly:

/// <summary>
/// A fully compatible null checking. Use instead of " == null " expression.
/// </summary>
/// <remarks>
/// <para>
/// Using an equality to null lambda expression throws an exception in SQL Server
/// ("Invalid operator for data type. Operator equals equal to, type equals geometry.")
/// because NHibernate is generating an HQL expression like this:
/// </para>
/// <code>
///     (t.geom is null) and (null is null) or t.geom = null
/// </code>
/// <para>
/// Using this extension method, we generate just the following HQL:
/// </para>
/// <code>
///     t.geom is null
/// </code>
/// </remarks>
/// <param name="geometry"></param>
/// <returns></returns>
public static bool IsNull(this IGeometry geometry)
{ return geometry == null; }

Build again and it should... fail?

 After a quick inspection these classes are referenced from the NetTopologySuite dll. I went to the NetTopologySuite project page to download the version 1.10 of the library and saw this in the downloaded zip:

My spider senses are tingling. It seems that the good folks of the NetTopologySuite split the DLL with the provider specifics (which makes sense), the NH Spatial guys updated the NetTopologySuite reference, but didn't include the extra DLLs in the MsSql and PostGis projects. I don't really understand how this happened... Anyway, let's add the missing references:

Project NHibernate.Spatial.MsSqlSpatial.vs2008 -> add reference to NetTopologySuite.IO.MsSqlSpatial.dll

Project NHibernate.Spatial.PostGis.vs2008 -> add reference to NetTopologySuite.IO.PostGIS.dll

Build... Failed

Well, let's hope this is the last error.

Project Tests.NHibernate.Spatial.MsSql2008.vs2008 -> add reference to GeoAPI.dll

Build Succeeded.

I had planned to advance a little more in this post, but I'll wrap it up here. In my next post I'm going to create a very simple application that connects to a SQL Server 2008 database with Geography columns.

Go to part 2


  1. Hi Pedro,
    In your screen shot of the dlls shipped with the NetTopologySuite project there is one there called PowerCollections. I found that although I could NHibernate.Spatial to build without including it when I went to use it complained that that this dll was missing (in the class NHibernate.Spatial.Type.GeometryTypeBase). I found referencing it when building Spatial and then referencing it again in the end application resolved the issue.

  2. Thanks, I'm going to update the post. Anyway, in retrospective, I should have included a zip file with the compiled dll :P

  3. very useful set of posts though - I now have nhibernate.spatial up an running in my project thanks to your blogs. Thank you.

  4. Hi Pedro,
    Did you manage to build and test Spatial.Oracle (By Ricardo Stuben) latest build with NHibernate 3.2 or 3.3 ?

  5. I've built NH Spatial with NHibernate 3.2. I had to change some additional source code to make it compile, but I don't recall exactly what. Anyway, and although it compiled correctly, I only tested it on SQL 2008/2012. Not sure at all about Oracle. Sorry

  6. We've compiled the code like you and tested it with ORACLE and it turn out working on SELECT, INSERT and UPDATE statements BUT when we tried to perform a spatial criteria it failed with a "Not all variables bound" ORA exception.

    Is there anything you know about this problem ?

    Thanks in advance

  7. Probably. Did you query using HQL, LINQ or QueryOver? I'm asking this because I had some trouble, in certain cases, when using LINQ and/or QueryOver. I've posted some query examples in Part 4 of this series, where I talked about that problem.

    So, my suggestion, first try it out with HQL.

  8. We used the ICriterion and ICriteria interfaces to create a generic query engine. The exception pops when executing the .List() method for returning the result set.

  9. Hi Pedro,
    Thanks for explain and helped me to solve the implementation of NHibernate.Spatial! but I'm having issues when retrieving Spatial fields from DB,
    i get nothing but NULLs, im sure that i followed the steps one by one but still getting the same result.
    hope you can help me :), regards!

  10. Oscar, can you send me a sample project to pedro.sousa1 _at_ gmail.com?

  11. Excellent Post. Also visit http://msnetframework.com/#mono.php