Advanced search with distances using NHibernate and SQL Server Geography

Question

I've got an existing advanced search method in a repository that checks a FormCollection for the existence of search criteria, and if present, adds a criterion to the search e.g.

public IList<Residence> GetForAdvancedSearch(FormCollection collection)
{
  var criteria = Session.CreateCriteria(typeof(Residence))
    .SetResultTransformer(new DistinctRootEntityResultTransformer());

  if (collection["MinBedrooms"] != null)
  {
    criteria
      .Add(Restrictions.Ge("Bedrooms", int.Parse(collection["MinBedrooms"])));
  }

  // ... many criteria omitted for brevity

  return criteria.List<Residence>();
}

I've also got a basic distance search to find how far each residence is from the search criteria. The HBM for the query is

<sql-query name="Residence .Nearest">
  <return alias="residence" class="Residences.Domain.Residence, Residences"/>
  <return-scalar column="Distance" type="float"/>
  SELECT R.*, dbo.GetDistance(:point, R.Coordinate) AS Distance
  FROM Residence R
  WHERE Distance < 10
  ORDER BY Distance
</sql-query>

I had to define a function to calculate the distance, as there was no way to get NHibernate to escape the colons in the geography function:

 CREATE FUNCTION dbo.GetDistance
 (
  @firstPoint nvarchar(100), 
  @secondPoint GEOMETRY
 )
 RETURNS float
 AS
 BEGIN
  RETURN GEOGRAPHY::STGeomFromText(
  @firstPoint, 4326).STDistance(@secondPoint.STAsText()) / 1609.344
 END

And the repository calls the named query thus:

return Session.GetNamedQuery("Residence.Nearest")

.SetString("point", String.Format("POINT({0} {1})", latitude, longitude)) .List();

So my question is; how do I combine the two (or start from scratch), so I can filter the advanced search results to include only residences within 10 miles of the search location?

UPDATE I have tried using NHibernate.Spatial with the following code:

criteria.Add(SpatialExpression.IsWithinDistance(
    "Coordinate", new Coordinate(latitude, longitude), 10));

but SpatialExpression.IsWithinDistance returned a System.NotImplementedException.

Answer 1

Have you seen the NHibernate.Spatial project? This may provide an easy solution to your problem.

The alternative is to create your own implementation of ICriterion - this is not too tricky if you derive from AbstractCriterion and you target your particular database platform. This would then allow you to combine your distance function with other criteria.

Answer 2

Create a projection that, in effect, adds a new distance column to the results, which is calculated by called the UDF, and then add a restriction to it:

var query = String.Format(
    "dbo.GetDistance('POINT({0} {1}', Coordinate) AS Distance", 
    latitude, longitude);
criteria
    .Add(Restrictions.Le(Projections.SqlProjection(
        query, 
        new [] {"Distance"}, 
        new [] {NHibernateUtil.Double}), 10));

UPDATE

n.b. Although this must have worked when I posted it, it doesn't work any more. NHibernate doesn't like the '.' after dbo, and says

"could not resolve property: dbo of: Residences.Domain.Residence".

If I remove the 'dbo.' I get

"'GetDistance' is not a recognized built-in function name."