did you see NHibernate.Spatial ?
2009/11/4 Kevin Pang <[email protected]>
>
> I've come up with a couple solutions, but they don't feel all that
> great... Here's what I've gathered so far:
>
> I can add support in HQL for SQL's CONTAINS function by using a custom
> NHibernate dialect like so:
>
> public class MyDialect : MsSql2008Dialect
> {
> public MyDialect()
> {
> RegisterFunction("contains", new StandardSQLFunction
> ("contains", null));
> }
> }
>
> That will allow me to construct the following HQL query:
>
> session.CreateQuery("from Restaurant where contains
> (RestaurantName, :keywords)")
> .SetString("keywords","Sizzler")
> .List();
>
> But that doesn't solve the issue with retrieving and sorting by
> distances. It seems in order to do this I have a couple options:
>
> 1. Add a formula property to my Restaurant.hbm.xml file that will
> calculate the distance. This would look something like this:
>
> <property name="Distance" formula="dbo.DistanceBetween(@lat, @long,
> Latitude, Longitude)" type="double" />.
>
> However, this has the side effect (I think) that retrieving any
> Restaurant will run this distance calculation. I don't want this to
> happen. Retrieving a single restaurant by its ID shouldn't need to
> calculate any distances. I only care about distances when users are
> searching for restaurants near them.
>
> 2. Break everything out into a <sql-query> that will return a custom
> type (e.g. RestaurantSearchResult). This would look something like
> this:
>
> <sql-query name="RestaurantQuery">
> <return class="RestaurantSearchResult" alias="restaurants">
> <return-property name="RestaurantId" column="RestaurantId" />
> <return-property name="Distance" column="Distance" />
> </return>
>
> SELECT
> Restaurants.*,
> dbo.DistanceBetween(@lat, @long, Latitude, Longitude) AS
> Distance
> FROM Restaurants
> WHERE Contains(RestaurantName, @keywords)
> </sql-query>
>
> This seems like it should do what I want, but for some reason doesn't
> feel all that right. Any suggestions would be appreciated.
>
> Kevin
>
> On Nov 4, 3:31 am, Wayne Douglas <[email protected]> wrote:
> > you should think about using NHIbernate.Search for this :)
> >
> >
> >
> > On Tue, Nov 3, 2009 at 5:26 PM, Kevin Pang <[email protected]> wrote:
> >
> > > Let's say I'm building a restaurant review application (e.g. Yelp)
> > > using SQL Server 2005 and NHibernate. I want to allow users to search
> > > for restaurants by keywords and/or proximity to their current
> > > location. The keyword search will use the SQL Server 2005 full text
> > > search CONTAINS function, while the distance calculation will be done
> > > using a stored procedure that can calculate the distance between two
> > > lat/long points.
> >
> > > For the sake of simplicity, let's say I have a table called
> > > Restaurants with the following columns:
> >
> > > RestaurantId int
> > > RestaurantName nvarchar(100)
> > > Latitude float
> > > Longitude float
> >
> > > What's the best way to construct this query?
> >
> > > A few requirements:
> > > 1. Users can specify keywords or location or both or neither
> > > 2. The end result needs to be ranked by distance, then relevance
> > > 3. Resulting IList (or whatever we're returning) needs to have
> > > Restaurant information as well as the distance in it for displaying
> > > purposes. I'm assuming we need to create a new class just to handle
> > > the search results (e.g. RestaurantSearchResult which holds both a
> > > Restaurant instance and a Distance property)?
> >
> > --
> > Cheers,
> >
> > w://
> >
>
--
Fabio Maulo
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
