NHibernate Spatial Query

[kev.m....@googlemail.com]

Hi Guys,

I have a query about NH Spatial. I've read the wiki and the example
unit test files that show the most common queries, and can't find what
I was looking for so I though I would ask here before I resort to
trying things in good old SQL. Basically, I want to run an HQL query
that goes somethimg like this:

SELECT TOP 100 LOCATIONS ORDER BY DISTANCE FROM THISLOCATION

Is this possible in NHibernate spatial??? If not does anyone know if
its possible (or computationally feasible) in SQL? I figure the
database would probably need to work out the distance between
"thislocation" and every other location before returning results.....
or does spatial indexing make this kind of query scalable in SQL
server 2008??

Any insight would be greatly appreciated.

Kevin.

[Ricardo Stuven]

On Jan 30, 10:18 pm, "kev.m.mul...@googlemail.com"

<kev.m.mul...@googlemail.com> wrote:
>, I want to run an HQL query that goes somethimg like this:
>
> SELECT TOP 100 LOCATIONS ORDER BY DISTANCE FROM THISLOCATION

// Get the geometry type associated to current dialect.
// In future versions of NH.Spatial this code will be
// reduced to something like "GeometryType.Of(session)".
IType geometryType = (session.SessionFactory.Dialect as
ISpatialDialect).GeometryType);

IGeometry thislocation = ...;

IList<IGeometry> result = session
.CreateQuery(@"
select l.Location
from Locations as l
order by NHSP.Distance(l.Geometry, :thislocation)
")
.SetParameter("thislocation", thislocation, geometryType)
.SetMaxResults(100)
.List<IGeometry>();

> I figure the database would probably need to work out the distance
> between "thislocation" and every other location before returning results...

> or does spatial indexing make this kind of query scalable in SQL
> server 2008??

Isaac Kunen explains this problem very well in this blog post:
http://blogs.msdn.com/isaac/archive/2008/10/23/nearest-neighbors.aspx

He says in SQL Server 2008 you have at least three approaches to the
nearest neighbors problem: the "naive", the "limited" and the "fancy".
The HQL query I presented above corresponds to the naive one and could
be very slow. A faster but limited approach in HQL would be something
like:

select l.Location
from Locations as l
where NHSP.Distance(l.Location, :thislocation) < 1000
order by NHSP.Distance(l.Geometry, :thislocation)

Note that, in SQL Server 2008, the predicate "geom1.STDistance(geom2)
< value" does use the spatial index, but the problem here is what if
the limit distance provided (in this case, 1000) is not enough?

Then comes the "fancy" approach (see the details in the blog post),
that simply can't be implemented in HQL so you have to turn to native
SQL.

I hope this helps...

Regards,
Ricardo Stuven.

[Ricardo Stuven]

Oops. The lines:

order by NHSP.Distance(l.Geometry, :thislocation)

...should be, for consistency:

order by NHSP.Distance(l.Location, :thislocation)

[kev.m....@googlemail.com]

Thanks so much for such a detailed response!!! I will try out your
suggestions and let you know how it goes!

Thanks again!

Kevin