[postgis-users] sorting by dinstance with hibernate criteria api

[Andreas Petersson]

hi!
im using hibernate-spatial with postgis - this is my first project with
postgis.
first of all, i wanted to hear what your approach is to this problem,
second, i need suggestions for this strange stacktrace im getting.
i wanted to do a simple search - sort by distance, using an index. it
turned out its harder than i thought.
typically i use the Criteria API from hibernate.
looking through the javadoc of hibernatespatial, i did not find a
Subclass/usages of
org.hibernate.criterion.Order, so i thought i need to roll my own.
a very simple implementation is the following: (omitting the constructor)

public class DistanceOrder extends Order {
    public String toSqlString(Criteria criteria, CriteriaQuery
criteriaQuery) throws HibernateException {
        return "distance_sphere(" + propertyName + ", GeomFromText('" +
p.toText() + "', " + p.getSRID() + ")) asc";
}


surprisingly, this did work, but the query took ages.. obviously its not
using the index.
since i am interested in the nearest 5-10 results, i decided to do the
following: create a bounding box (this is using the index) and double
its size until i match the desired number of results.
then apply the sorting on distance afterward.  - is filter the right
approach here?

this will create  bunch of queries but for the most common cases,
queries in dense cities, i will get results quickly. plus i can  create
very flexible Criteria queries.
a downside is i have to supply a factory for fresh identical queries to
execute new queries.

the class looks like this:

public class DistanceOrder extends Order {
    private final Point p;
    private final String propertyName;
    private final long needNearest;
    private final transient Provider<Criteria> freshCriteria;

    public DistanceOrder(final String propertyName, Point point, long
needNearest, Provider<Criteria> freshCriteria) {
        super(propertyName, true);
        this.p = point;
        this.propertyName = propertyName;
        this.needNearest = needNearest;
        this.freshCriteria = freshCriteria;
    }

    int boxSize = 1;

    @Override
    public String toSqlString(Criteria criteria, CriteriaQuery
criteriaQuery) throws HibernateException {
        if (freshCriteria == null) {
            throw new ImplementationException("did not expect to be
serialized..");
        }
        int lastCount = 0;
        while (lastCount < needNearest) { //recurse here
            boxSize *= 2;
            final Criteria newCriteria = freshCriteria.get();
            addBoxTocriteria(newCriteria);
            lastCount = (Integer)
newCriteria.setProjection(Projections.rowCount()).uniqueResult();
        }
        addBoxTocriteria(criteria); //the real stuff
        return "distance_sphere(" + propertyName + ", GeomFromText('" +
p.toText() + "', " + p.getSRID() + ")) asc";
    }

    private void addBoxTocriteria(Criteria newCriteria) {
        final Envelope envelope = new Envelope(p.getCoordinates()[0]);
        envelope.expandBy(boxSize);
        newCriteria.add(SpatialRestrictions.filter(propertyName,
envelope, p.getSRID()));
    }
}

the matching boudning box was found - containing 988 elements.
then the final query - restricting by last known bounding box+sorting is
being issued and here comes my problem: i get
org.postgresql.util.PSQLException: Der Spaltenindex 1 ist ausserhalb des
gültigen Bereichs. Anzahl Spalten: 0.
means: index out of range.
log4jdbc tells me that the index number 1 was set to the polygon. but no
columns were bound to the preparedStatement.

stackrace below:
do you have any idea why this is happening?
may this be an incompatibility to hibernate 3.3?

 INFO 09:44:40,102 - net.sf.log4jdbc.Slf4jSpyLogDelegator:325 - select
count(*) as y0_ from public.b_hotel this_ where (this_.coordinate &&
SRID=4326;POLYGON((-32 -32,-32 32,32 32,32 -32,-32 -32)) ) {executed in
62 msec}
ERROR 09:44:51,415 - net.sf.log4jdbc.Slf4jSpyLogDelegator:102 - 5.
PreparedStatement.setObject(1, SRID=4326;POLYGON((-32 -32,-32 32,32
32,32 -32,-32 -32)))
org.postgresql.util.PSQLException: Der Spaltenindex 1 ist ausserhalb des

gültigen Bereichs. Anzahl Spalten: 0.
    at
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:57)
    at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:121)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2114)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1238)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setPGobject(AbstractJdbc2Statement.java:1540)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1730)
    at
net.sf.log4jdbc.PreparedStatementSpy.setObject(PreparedStatementSpy.java:944)
    at
com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setObject(NewProxyPreparedStatement.java:365)
    at
org.hibernatespatial.AbstractDBGeometryType.nullSafeSet(AbstractDBGeometryType.java:154)
    at
org.hibernatespatial.GeometryUserType.nullSafeSet(GeometryUserType.java:184)
    at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:179)
    at
org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1728)
    at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1699)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1589)
    at org.hibernate.loader.Loader.doQuery(Loader.java:696)
    at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.doList(Loader.java:2228)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    at org.hibernate.loader.Loader.list(Loader.java:2120)
    at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
    at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:328)
    at quan.data.dao.booking.hotel.HotelDao.getNearest(HotelDao.java:28) 
-- 
View this message in context: http://www.nabble.com/sorting-by-dinstance-with-hibernate-criteria-api-tp22180143p22180143.html
Sent from the PostGIS - User mailing list archive at Nabble.com.