Friday, March 23, 2012

NHibernate Spatial (Part 4 - Querying)


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.

11 comments:

  1. Hi,
    first thanks for your post series

    i have follow part1 in order to compile Nh spatial ..thats ok

    but my test fail...

    i have this

    using NetTopologySuite.Geometries;
    namespace Core
    {
    public class District
    {
    public virtual int Id { get; set; }
    public virtual Polygon Area { get; set;
    public virtual string Name { get; set; }
    }
    }

    using Core;
    using FluentNHibernate.Mapping;

    namespace TestNHSpatial
    {
    public class DistrictMap :ClassMap
    {
    public DistrictMap()
    {
    ImportType();

    Id(x => x.Id);
    Map(x => x.Name);
    Map(x => x.Area).CustomType();
    }
    }
    }

    and this

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

    finally

    var cfg = new OrderingSystemConfiguration();

    var configuration = Fluently.Configure()
    .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connString)
    .Dialect())
    .Mappings(m => m.AutoMappings.Add(
    AutoMap.AssemblyOf(cfg)))
    .BuildConfiguration();

    var exporter = new SchemaExport(configuration);

    exporter.Drop(false, true);

    exporter.Create(true, true);



    I have read this in order to compile NH spatial for Nhibernate 3.1

    http://build-failed.blogspot.it/2012/02/nhibernate-spatial-part-2.html

    I have also read this

    NHibernate.Spatial and Sql 2008 Geography type - How to configure

    but the same code for me don't compile... I have this

    using NetTopologySuite.Geometries;
    namespace Core
    {
    public class District
    {
    public virtual int Id { get; set; }
    public virtual Polygon Area { get; set; }
    public virtual string Name { get; set; }
    }
    }

    using Core;
    using FluentNHibernate.Mapping;

    namespace TestNHSpatial
    {
    public class DistrictMap : ClassMap
    {
    public DistrictMap()
    {
    ImportType();

    Id(x => x.Id);
    Map(x => x.Name);
    Map(x => x.Area).CustomType();
    }
    }
    }

    and this

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

    finally

    var cfg = new OrderingSystemConfiguration();

    var configuration = Fluently.Configure()
    .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connString)
    .Dialect())
    .Mappings(m => m.AutoMappings.Add(
    AutoMap.AssemblyOf(cfg)))
    .BuildConfiguration();

    var exporter = new SchemaExport(configuration);

    exporter.Drop(false, true);

    exporter.Create(true, true);

    i have this error...

    NHibernate.MappingException : An association from the table District refers to an unmapped class: NetTopologySuite.Geometries.Polygon

    thanks for all

    ReplyDelete
  2. I have read you blog post in order to compile NH spatial for Nhibernate 3.1 and thats work fine

    but when i try to test a sample project build failed...

    I have also read this
    http://stackoverflow.com/questions/6059292/geospatial-point-mapping-in-fluent-nhibernate


    but the same code for me don't compile... I have this

    using NetTopologySuite.Geometries;
    namespace Core
    {
    public class District
    {
    public virtual int Id { get; set; }
    public virtual Polygon Area { get; set; }
    public virtual string Name { get; set; }
    }
    }

    using Core;
    using FluentNHibernate.Mapping;

    namespace TestNHSpatial
    {
    public class DistrictMap : ClassMap
    {
    public DistrictMap()
    {
    ImportType();

    Id(x => x.Id);
    Map(x => x.Name);
    Map(x => x.Area).CustomType();
    }
    }
    }

    your suggest class...

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

    finally set up configuration...

    var cfg = new OrderingSystemConfiguration();

    var configuration = Fluently.Configure()
    .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connString)
    .Dialect())
    .Mappings(m => m.AutoMappings.Add(
    AutoMap.AssemblyOf(cfg)))
    .BuildConfiguration();

    var exporter = new SchemaExport(configuration);

    exporter.Drop(false, true);

    exporter.Create(true, true);

    i have this error...

    NHibernate.MappingException : An association from the table District refers to an unmapped class: NetTopologySuite.Geometries.Polygon

    thanks in advance

    ReplyDelete
  3. sorry for typing error

    Map(x => x.Area).CustomType();
    is
    Map(x => x.Area).CustomType();

    but dont compile...

    thanks

    ReplyDelete
  4. I've answered your question on Stackoverflow. As I've said there, if you need, send me a test project to pedro.sousa1 _at_ gmail.com

    ReplyDelete
  5. :(
    is Map(x => x.Area).CustomType();

    dont compile

    sorry

    ReplyDelete
  6. You shouldn't have a compilation error there.

    Anyway, and unrelated, I've seen that you're using a SchemaExport instruction to generate the database schema. You'll need something additional on the mappings to have that working correctly.

    You have to put a:

    .CustomSqlType("GEOGRAPHY"); on the fluentNHibernate mappings.

    ReplyDelete
  7. ok now works :)

    thank you very much!!!

    ReplyDelete
  8. @Pedro
    sir, you rock. thank you for this blog post :)

    ReplyDelete
  9. Hello!

    Thank you very much for this mini-series. It has served as a great kick-starter for myself.

    Your solution works well, however I have come across a small issue and I was wondering if you are able to shed more light on how to solve it. I use the NHibernate profiler (NHProf) and it complains about the use of GeoAPI.Geometries.IGeometry in a custom mapping:

    "the custom type 'GeoAPI.Geometries.IGeometry' handled by 'Wgs84GeographyType' is not Serializable"

    Do you know how I might overcome this warning?

    Kind regards,
    Øyvind

    ReplyDelete
    Replies
    1. Øyvind, Did you ever find an answer to this question?

      Delete