NHibernate Spatial (Part 4 - Querying)

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

In my previous NH Spatial post I've shown a concrete end-to-end example of how to use NHibernate Spatial on an ASP.NET MVC project.
Anyway, there's still much to be said about using this library and I've received some hits on my blog from people who were searching for how to do specific spatial operations with NH Spatial. So, this blog post will be pretty much practical, showing some spatial queries using NH Spatial on SQL Server 2008 R2, taking into account some variables in the process:

• Geography (always assuming SRID 4326)  and Geometry data types
• HQL and LINQ
• NHibernate version

So, here's the flow of the thing: I'm going to pick some of the main spatial operations and show how you may execute them using NHibernate Spatial.

But first, regarding NHibernate version, the current "official" release is 3.2.0.4000. Unfortunately NHibernate Spatial does not compile with it. I've fixed those compilation errors locally (pretty simple) but it's not enough. Some of the spatial operations don't register the Sql parameters correctly and I believe it's a bug in NHibernate. I've narrowed it down to the NHibernate.Loader class, but haven't invested much trying to solve it. So, for the sake of this post, I'm going to use NHibernate 3.1.0.4000 (which was also the version that I've used in my previous post). Anyway, if you expect to deal mostly with HQL you can risk the 3.2.0.4000 version, as the problem I described is mostly related with QueryOver and LINQ.

Regarding the session object, in one of my previous posts I've shown how to create a valid session factory for spatial operations in SQL Server. I'll show examples for both geometry and geography. Typically one will not mix geometries and geographies in the same application.

geometry:

Fluently.Configure()
                .Database(
                    MsSqlConfiguration.MsSql2008
                        .ConnectionString("<connection string>")
                        .Dialect<MsSql2008GeometryDialect>())
                        .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DistrictMap>())
                .BuildSessionFactory();

geography:

Fluently.Configure()
                .Database(
                    MsSqlConfiguration.MsSql2008
                        .ConnectionString("<connection string>")
                        .Dialect<MsSql2008GeographyDialect>())
                        .Mappings(m => m.FluentMappings.AddFromAssemblyOf<DistrictMap>())
                .BuildSessionFactory();

 

Also, and assuming Fluent NHibernate for the mappings, map each property according to their custom type.

geometry:

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

geography:

Map(x => x.AreaWithGeography).CustomType<Wgs84GeographyType>();

The Wgs84GeographyType class was created by me in one of my previous posts so that the mappings could be done by code using FluentNHibernate. Otherwise we would have to use XML mappings to set the correct SRID.

Now, let's start querying:

Area

Returns the total surface area of a geometry instance

Geometry - Yes
Geography - Yes (the computed value will be in square meters instead of square units)

Example:
Show the ID and calculated area of all Districts that have more than 0.5 units of area.


HQL

var result = session.CreateQuery(@"select NHSP.Area(d.Area) 
                                   from District d 
                                   where NHSP.Area(d.Area) > 0.5")
                    .List();

LINQ

var result = session.Query<District>()
                    .Where(d => d.Area.Area > 0.5)
                    .Select(d => new { d.Id, TotalArea = d.Area.Area })
                    .ToList();

Buffer

Returns a geometric object that represents the union of all points whose distance from a geometry instance is less than or equal to a specified value.It's like stretching the polygon so that it grows by a specific amount.

Geometry - Yes
Geography - No

Example:
Get a  0.1 units buffer on the district "Lisboa".

HQL

var result = session.CreateQuery(@"select NHSP.Buffer(d.Area, 0.1) 
                                   from District d 
                                   where d.Name = 'Lisboa'")
                    .UniqueResult();

LINQ
Does not work

Intersects

Returns true if a geometry instance intersects another geometry instance. Returns false otherwise.

Geometry - Yes
Geography - Yes

Example #1:
Get all points inside a rectangle. Very useful for example if you have a map view and only want to fetch the visible points.

Common code:

var boundingBox = Geometry.DefaultFactory
                          .CreatePolygon(
                               Geometry.DefaultFactory
                                       .CreateLinearRing(new[]
                                       {
                                           new Coordinate(-9.5, 40), 
                                           new Coordinate(-9.5, 36),
                                           new Coordinate(-7, 36), 
                                           new Coordinate(-7, 40),
                                           new Coordinate(-9.5, 40)
                                       }), null);

Note: the geography data type has two major restrictions regarding polygons: ring orientation and spanning across hemispheres. Don't create a polygon that spans across hemispheres and follow the draw pattern that I've shown above.

HQL

var result = session.CreateQuery(@"select p 
                                   from Place p, District d where 
                                   NHSP.Intersects(p.Point, :boundingBox) = 1")
                     .SetParameter("boundingBox", boundingBox , 
                                       NHibernateUtil.Custom(
                                           typeof(MsSql2008GeometryType)))
                     .List();

QueryOver

var result = session.QueryOver()
                    .WhereSpatialRestrictionOn(p => p.Point)
                    .Intersects(boundingBox)
                    .List();

LINQ
Does not work. Basically whenever the parameter is not a property from the same or another entity, LINQ will fail to call the Custom Type to get the correct SQL to execute. For example, if I was referencing the property "Area" from the entity "District" it would work.
Badass bug, and I'm not exactly sure of how to solve it. In HQL, as one can explicitly state the custom type of parameter, this is not an issue.


Example #2:
Let's make things more interesting. We'll get all places inside the districts that have an area larger than 0.5 units.

HQL

var result4 = session.CreateQuery(@"select p 
                                    from Place p, District d where 
                                    NHSP.Area(d.Area) > 0.5 and
                                    NHSP.Intersects(p.Point, d.Area) = 1")
                     .List();

LINQ (I normally use the lambda syntax, but this syntax is simpler for this example)

var query = from p in session.Query()
            from d in session.Query()
            where d.Area.Area > 0.5 && p.Point.Intersects(d.Area)
            select p;

var result = query.ToList();

Centroid

Returns the geometric center of a geometry instance consisting of one or more polygons

Geometry - Yes
Geography - No

Example:
Get the centroid of all the districts

HQL

var result = session.CreateQuery(@"select NHSP.Centroid(d.Area) from District d")
                    .List();

LINQ

var result = session.Query()
                    .Select(d => d.Area.Centroid)
                    .ToList();

Distance

Returns the shortest distance between a point in a geometry instance and a point in another geometry instance.

Geometry - Yes
Geography - Yes

Example #1:
Get all the places that are within a certain distance from the centroid of the districts

HQL

var result = session.CreateQuery(@"select p 
                          from Place p, District d where
                          NHSP.Distance(p.Point, NHSP.Centroid(d.Area)) < 0.1
                    .List();

LINQ

var query = from p in session.Query<Place>()
            from d in session.Query<District>()
            where p.Point.Distance(d.Area.Centroid) < 0.1
            select p;

var result11 = query.ToList();

Example #2: 
Get the 5 nearest  places from a specific point

Common code:

var point = new Point(-8.5, 39.0);

HQL

var result = session.CreateQuery(@"select p from Place p 
                                   order by NHSP.Distance(p.Point, :point)")
                    .SetParameter("point", point, 
                        NHibernateUtil.Custom(typeof(MsSql2008GeometryType)))
                    .SetMaxResults(5)
                    .List();

LINQ
Does not work. Same problem as before.

Enough for now. I'll probably pick the NHibernate Spatial topic in another post.

If I manage to find some time I'll try to contact Ricardo Stuven so that I may commit some of the corrections and improvements that I've implemented locally. NHibernate Spatial is a great project, and it's a pity that it hasn't got much attention lately from its developers.