NHibernate Spatial (Part 2)

Part 1 - Introduction
Part 2 - First Demo
Part 3 - Improving
Part 4 - Querying
Part 5 - NHibernate 3.3.1+

Well, now that we've got a working NHibernate Spatial dll (from my previous post) let's put it to some good use.

I want to create a relatively simple example that uses spatial data with NH Spatial. For this tutorial I'm going to create a SQL Server 2008 database table with all of the Portuguese districts. Each district will have it's area stored in the database.

 There should be 18 polygons which fill the entire country and do not overlap (obviously).



I'll display a Bing map on a webpage. When the user clicks on a point of the map the district that contains that point will be fetched and the polygon will be displayed on the map with a certain color.



All of the queries will be done with NHibernate using NHibernate Spatial for the GIS operations, in this case the intersection between the click-coordinate and the corresponding district area.










Technologies used:

• Bing Maps / Javascript
• ASP.NET MVC 3
• C# 4.0
• NHibernate 3.1 + NHibernate Spatial + FluentNHibernate
• SQL Server 2008 R2 

Let's get started by declaring our "District" class.

The district has an Id, a name and a geographic area

ID: int
Name: string
Area:  <geographic area>

Now, here's our first bottleneck. What kind of data type should we use to describe the "geographic area"? NHibernate Spatial uses GeoAPI for the datatypes, which is a library that was created to improve the interoperability between GIS projects by providing several data types for GIS operations. In this case, as we want to represent an area, we'll use the type "IPolygon". So, our class definition will be:

using GeoAPI.Geometries;

public class District
{
    public virtual int Id { get; set;}
    public virtual IPolygon Area { get; set;}
    public virtual string Name{ get; set; }
}

Well, in practice there are several districts in Portugal that are composed of several polygons, forming  MultiPolygons (which are supported by GeoAPI). Therefore we should be using the generic datatype IGeometry. But, for this example, I've simplified the portuguese district areas to make them simple polygons, so the IPolygon data type suits just fine.

Now let's define the table representation. SQL Server supports both Geography and Geometry columns. Geography uses an Ellipsoid earth model and Geometry uses a planar coordinate system. Geometry is simpler to use and provides more spatial operations, but Geography is more precise when thinking on world coordinates and distance/area calculations, particularly for large distances. For this example both would do, but there is an extra step required if one opts for Geography.

Let's create a table named "District"

Note: The Id should be an "Identity

 I've created a sql file with the insert statements for these districts.

Now, and for mapping our domain object to the database, we can use a XML configuration or code-based configuration. I hate the verbosity and redundancy of the NHibernate XML file configuration so I'll use FluentNHibernate instead.

The mapping can be defined as:

using FluentNHibernate.Mapping;
using NHibernate.Spatial.Type;

public class DistrictMap : ClassMap<District>
{
    public DistrictMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.Area).CustomType<MsSql2008GeographyType>();
    }
}

Unfortunately this isn't enough for the geography type. With geometry one would define the mapping as:

Map(x=>x.Area).CustomType<MsSql2008GeometryType>();

and we would be set to go. Geography, on the other hand, requires something called an SRID. I'm not going to delve into many details (check the link) but you can picture SRID as the identifier of the coordinate system used. The most common one is 4326, usually referred as WGS84, made popular by it's use on GPS coordinates (lat: [-90;90],  lon:[-180;180]). We'll use that SRID on this example.

The problem is that, by default, the SRID on the geography type defined by NHibernate Spatial is 0 (zero), which is invalid.

To set a correct SRID there are three choices:

• Use NHibernate XML configuration to set a custom parameter.

<property name="Area" column="Area">
    <type name="NHibernate.Spatial.Type.MsSql2008GeographyType, 
            NHibernate.Spatial.MsSql2008">
        <param name="srid">4326</param>
    </type>
</property>

• Change the NHibernate Spatial source-code so that MsSql2008GeographyType has default SRID 4326 instead of 0. 

• Define a subclass of MsSql2008GeographyType that sets the correct SRID

using NHibernate.Spatial.Type;
using System;

[Serializable]
public class Wgs84GeographyType : MsSql2008GeographyType
{
    protected override void SetDefaultSRID(GeoAPI.Geometries.IGeometry geometry)
    {
        geometry.SRID = 4326;
    }
}

The first option is viable if you do the NH mappings using XML.
The second is tempting but not recommended at all. Forget that I mentioned it :)
I'm going to stick with the third option. After declaring the class just use it as a custom type on the FluentNHibernate mapping.

using FluentNHibernate.Mapping;

public class DistrictMap : ClassMap<District>
{
    public DistrictMap()
    {
        Id(x => x.Id);
        Map(x => x.Name);
        Map(x => x.Area).CustomType<Wgs84GeographyType>();
    }
}

We're ready to go. I'm not going to show the details on creating a SessionFactory or the ISession as this is not the scope of this post, so I'll assume that you have the ISession object initialized and ready to use.

Just a quick check using NHibernate QueryOver.

var districts = session.QueryOver<District>().List();

If you place a breakpoint after the query you can see that the polygons where correctly loaded from the database, and considered as working IPolygon instances.

Looks good, but let's try a spatial operation to see if it works. Based on the requirements of this example lets try to find a polygon based on a coordinate.

Let's harcode a coordinate. In this case we will use coordinate (lon:-9, lat:39). It should return the district "Lisboa" (or Lisbon if you prefer the International name)















The NHibernate QueryOver instruction will be:

var district = session.QueryOver<District>()
                    .WhereSpatialRestrictionOn(d => d.Area)
                    .Intersects(new Point(-9.0, 39.0))
                    .SingleOrDefault();

when you run it you get:

Unable to cast object of type 'NHibernate.Dialect.MsSql2008Dialect' to type 'NHibernate.Spatial.Dialect.ISpatialDialect'.

That's because we're missing something really important: we need a dialect that is able to talk "spatial" with the database. NHibernate Spatial provides a few, and we'll use MsSql2008GeographyDialect.

My NHibernate NHibernate session factory was configured like this:

public static ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2008
            .ConnectionString("server=.;uid=user;pwd=pass;database=database"))
        .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DistrictMap>())
        .BuildSessionFactory();
}

And yes, I know that I shouldn't have the connection string hardcoded :)

Anyway, I was missing the Dialect instruction, which should be placed at the database level, looking like this:

public static ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2008
            .ConnectionString("server=.;uid=user;pwd=pass;database=database")
            .Dialect<MsSql2008GeographyDialect>()
        .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DistrictMap>())
        .BuildSessionFactory();
}

Let's try our query again.

Lisboa was returned as expected. Perfect! So, the NHibernate Spatial stuff is sort-of complete.

What are we missing to create the fully working example?

• Display a map on the web-page
• Handle the click button on the map to trigger an Ajax request to the server
• Create an MVC Action that receives a coordinate as a parameter and executes the above query, returning a JSON object with the polygon coordinates.
• Receive the coordinates of the polygon in Javascript and draw it using the Bing Maps API.

 But you'll have to wait for my next post :)

---

Go to Part 3