I'm using Hibernate 3 with the following Criteria Query:

List businesses = session.createCriteria(Business.class)
.add(Restrictions.like("name", likeCriteria))
.addOrder(Order.desc("currentRating"))
.list();

Based on my mappings below how can I add a distance calc in here for the latitude/longitude. I'm VERY concerned with speed. Do I need a Projection?

<hibernate-mapping>
<class name="com.chaos.value.Business" table="fc_business" lazy="false">
<id name="id" column="biz_id">
<generator class="native"/>
</id>
<property name="name" column="biz_name"/>
<property name="currentRating" column="current_rating"/>
<many-to-one name="address" class="com.chaos.value.Address" column="address_id" cascade="all"/>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="com.chaos.value.Address" table="fc_address" lazy="false">
<id name="id" column="address_id">
<generator class="native"/>
</id>
<property name="latitude"/>
<property name="longitude"/>
<property name="line1"/>
<property name="line2"/>
<property name="city"/>
<property name="state"/>
<property name="zipcode"/>
<property name="phone1"/>
<property name="phone2"/>
<property name="fax"/>
<property name="url"/>
</class>
</hibernate-mapping>

I'm on mysql 4.1 with an eventual move to 5.

I'm not clear on what you want. You want to do a query for businesses within a given distance of a certain latitude/longitude?

Well, that's really up to the database, isn't it? Also, computing true great-circle distances between lat/long points is pretty complex GIS stuff to get exactly right.

I guess I'm wondering what in the world this has to do with Hibernate? Perhaps the question to ask yourself is, how would you do this without Hibernate? If you don't have an answer, Hibernate definitely won't help!

Cheers,
Rob

Yes I want to do exactly this. Currently I get the resultset then calculate the great circle distance on each item. Great circle distance is a a complex calc but just a calc given two points nontheless.




This does have to do with hibernate because what I really want is a resultset (from the database) containing Businesses within this distance.

Thanks,
Mark

So right now you are calculating great-circle distance outside the database?


This does have to do with hibernate because what I really want is a resultset (from the database) containing Businesses within this distance./quote]
You seem to have missed my point. Right now you are calculating distances outside the database. You say that you want the database to do it (so you can do geoqueries based on distance to a point).

That is not Hibernate's problem! Hibernate knows nothing about spatial queries or spatial indexing. You have to configure the database itself to store your geopoints and handle your spatial queries.

For instance, from this page on MySQL geoqueries:



If your database can't do something like that, then Hibernate's not going to be able to either.

Even if your database does support geoqueries, I'm not sure HQL does! You can probably do some magic with UserTypes, though.

Cheers,
Rob

Right ... I see your point! Good article on spatial queries.

Thanks,
Mark

You're welcome. If you found it helpful, could I trouble you to rate my answer positively? I feel like a karma whore(tm Slashdot) but there doesn't seem to be a way to privately message you :-D
Cheers!
Rob

You can do this in hibernate and I don't see how this is not hibernate's problem.
You want a property called distance calculated on the basis of a formula.
<property name="distance" formula="my_distance_calc_sql" type="double" />

If you do not have all information required for calculating distance during mapping time, then you will have to use a native sql query and populate your object yourself. For instance, if it requires some query parameters, I don't know of any other way.

According to this article:

http://forums.mysql.com/read.php?23,409 ... #msg-43079

mysqll can do something like this ...

mysql> SELECT
-> x2.zcta
-> FROM ZCTA x1, ZCTA x2
-> WHERE x1.zcta = '21236'
-> AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
-> + COS(x1.lat_radians) * COS(x2.lat_radians)
-> * COS(x2.long_radians - x1.long_radians)) * 3956 <= 5;


I don't want some super spatial database calcs within some crazy polygonal boundary. mysql has extensions for this if I did.

Seems that I should be able to get hibernate to help me here. I don't want resultsets that have 10,000 items when in fact only 100 are within my distance.

Well, then it looks like you want to write some Hibernate customized SQL queries. Just write exactly that same SQL, but invoke it through the session's executeSQLQuery() method.

If that's not good enough, then I'm kind of wondering what exactly it is that you think Hibernate should be doing to make this easier? What is the HQL you want to be able to write?

piperatom, you're a bit confused... spatial distance is not a property of a single object, it is a function taking two objects and returning a distance. So a distance <property> would make no sense. And yes, it is definitely not Hibernate's job to make the database do filtering based on distance!

Cheers,
Rob

Ok I just wasn't thinking ... this was fairly easy to do and exactly what I needed:


String sql = "from Business biz where biz.name like '%auto%' and "+
" acos( " +
" sin(:lat) * sin(biz.address.latitude/57.29) + " +
" cos(:lat) * cos(biz.address.latitude/57.29) * " +
" cos(biz.address.longitude/57.29 - :lon) " +
" ) * 3956 < :distance ";

Query query = session.createQuery(sql)
.setInteger("distance", 2)
.setDouble("lat", lat)
.setDouble("lon", lon);
Iterator results = query.list().iterator();


It's fast and if I want I can set the max resultset size and the offset into the resultset.

Great! :-)

Would you consider rating my answer accordingly?

Thanks!
Cheers,
Rob

If you found this thread useful ... perhaps you can help resolve this issue

http://forum.hibernate.org/viewtopic.php?t=950170