Hi Bart,
On 18 Sep 2008, at 09:55, Saelen Bart wrote:
> I have a question about Hibernate Spatial.
> I'm building an application that should be able to query some data.
> I'm using Hibernate and especially Hibernate Criteria to build the
> queries based on the user input. Now I want to use Hibernate
> Spatial to incorporate 2 queries from an older application. This
> older application is using JDBC on a Postgis DB. I want to
> transform these 2 queries into Hibernate (Spatial) Criteria. So I
> can use them together with the other criteria I already have. But I
> have no idea how to transform these queries. So my questions is:
> how do I transform the following queries to Hibernate Criteria?
>
> query 1: Find the x-amount of object closest to a reference object.
>
> private static String SEARCHNEAREST_QUERY = "SELECT geoid,
> poisubtype, name, Distance((select geom from poi_poi where
> geoid=?),geom) FROM poi_poi WHERE poisubtype=? order by 4 limit ?";
>
> with parameters: geoId , outputCategory, amount
> geoId is an id used to identify each object
> outputCategory is a category filter
> amount is the number of objects to return
>
First, let me remark that this question is nothing specific about
Hibernate Spatial. Hibernate Spatial provides you access to the
distance function through HQL, but that is all.
Now, I don't think you can use HQL or the Query interface because of
the subselect in the select clause. And you cant' use Criteria
because of the parameters. So it's probably best to split the query:
(I'm assuming class POI with properties geom, geoid, subType and name)
1) retrieve the POI
POI poi = session.get(POI.class, id);
2) retrieve the distance using HQL
Query q = session.createQuery("select geoid, subType, name, distance
( geom, ?) from POI");
Type geometryType = new CustomType(GeometryUserType.class, null);
q.setParameter(0, poi.getGeom(), geometryType);
q.setMaxResults(limit);
List results = q.list();
> query 2: Find the x-amount of object closest to a reference object
> within a certain radius.
>
> private static String SEARCHINRADIUS_QUERY = "SELECT geoid,
> poisubtype, name, Distance((select geom from poi_poi where
> geoid=?),geom) FROM poi_poi WHERE poisubtype=? and geom && Expand
> ((select geom from poi_poi where geoid=?),?) AND Distance((select
> geom from poi_poi where geoid=?),geom) < ? order by 4 limit ?";
>
> with parameters: geoId, outputCategory, geoId, radius, geoId,
> radius, amount
If I understand correctly, you want to search within a certain radius
of a specified POI for other POI's and report their properties and
the distance to the specified POI.
You can use the techniques above but also add and
Restrictions.sqlRestrictions
I would break that also up in:
1) retrieve the specified POI (as above)
POI poi = session.get(POI.class, id);
2) create a filter Geometry
Geometry filter = new Envelope ( poi.getX() - radius, poi.getX() +
radius, poi.getY() - radius, po.getY() + radius);
3) create the query
String hql = "select geoid, subType, name, distance( geom, ?) from
POI where subtype = ? and within (geom, ?)";
Query q = session.createQuery(hql);
Type geometryType = new CustomType(GeometryUserType.class, null);
q.setParameter(0, poi.getGeom(), geometryType);
q.setParameter(1, subType);
q.setParameter(2, filter, geometryType);
q.setMaxResults(limit);
Hope this helps,
Karel Maesen
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
