使用NHibernate和SQL Server Geography进行距离高级搜索 [英] Advanced search with distances using NHibernate and SQL Server Geography
问题描述
我在存储库中有一个现有的高级搜索方法,该方法可以检查FormCollection
是否存在搜索条件,如果存在,则将条件添加到搜索中,例如
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>();
}
我还进行了基本的距离搜索,以查找每个住宅与搜索条件之间的距离.查询的HBM为
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>
我必须定义一个计算距离的函数,因为在地理函数中无法让NHibernate逃避结肠:
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();
所以我的问题是;如何将两者结合在一起(或从头开始),以便可以过滤高级搜索结果,使其仅包括距离搜索位置10英里以内的住所?
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?
更新我已经尝试通过以下代码使用NHibernate.Spatial:
UPDATE I have tried using NHibernate.Spatial with the following code:
criteria.Add(SpatialExpression.IsWithinDistance(
"Coordinate", new Coordinate(latitude, longitude), 10));
但SpatialExpression.IsWithinDistance
返回了System.NotImplementedException
.
推荐答案
创建一个投影,该投影实际上会在结果中添加一个新的距离列,该距离列是由UDF计算得出的,然后对其添加了限制:
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));
更新
n.b.尽管在我发布该消息时一定有效,但是它不再起作用. NHibernate不喜欢'.'. dbo之后,并说
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
无法解析属性:Residences.Domain.Residence的dbo".
"could not resolve property: dbo of: Residences.Domain.Residence".
如果我删除"dbo".我得到
If I remove the 'dbo.' I get
'GetDistance'不是公认的内置函数名称."
"'GetDistance' is not a recognized built-in function name."
这篇关于使用NHibernate和SQL Server Geography进行距离高级搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!