es.emergya.bbdd.dao.RoutingHome.java Source code

Java tutorial

Introduction

Here is the source code for es.emergya.bbdd.dao.RoutingHome.java

Source

/*
 * Copyright (C) 2010, Emergya (http://www.emergya.es)
 *
 * @author <a href="mailto:jlrodriguez@emergya.es">Juan Lus Rodrguez</a>
 * @author <a href="mailto:marias@emergya.es">Mara Arias</a>
 *
 * This file is part of GoFleet
 *
 * This software is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * As a special exception, if you link this library with other files to
 * produce an executable, this library does not by itself cause the
 * resulting executable to be covered by the GNU General Public License.
 * This exception does not however invalidate any other reasons why the
 * executable file might be covered by the GNU General Public License.
 */
package es.emergya.bbdd.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import org.appfuse.dao.hibernate.GenericDaoHibernate;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.CustomType;
import org.hibernate.type.Type;
import org.hibernatespatial.GeometryUserType;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.Point;

import es.emergya.bbdd.bean.Routing;
import es.emergya.utils.LogicConstants;

@Repository("routingHome")
public class RoutingHome extends GenericDaoHibernate<Routing, Long> {

    private final static String table = "Routing";
    private final static String id = "id";
    private final static String the_geom = "the_geom";
    private final static String source = "source";
    private final static String target = "target";
    private final static String cost = "cost";
    private final static String reverse_cost = "reverse_cost";
    private final static String rule = "rule";
    private final static String to_cost = "to_cost";
    public final static Integer SRID = LogicConstants.getInt("SRID", 4326);

    public enum funcion {
        SIMPLE, SHOOTING_STAR
    };

    public RoutingHome() {
        super(Routing.class);
    }

    @Override
    public Routing get(Long id) {
        try {
            return super.get(id);
        } catch (Throwable t) {
            log.error("Estamos buscando un objeto que no existe", t);
            return null;
        }
    }

    /**
     * Devuelve la lista de ids de la ruta desde vertice_origen a
     * vertice_destino.
     * 
     * Utiliza la funcion shooting_star
     * 
     * @param origin
     * @param goal
     * @return
     */
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    private List<Long> shortest_path_shooting_star(final Long origin, final Long goal) {
        final List<Long> lista = new ArrayList<Long>();
        try {
            Session currentSession = getSession();
            CallableStatement consulta = currentSession.connection()
                    .prepareCall("{call shortest_path_shooting_star(?,?,?,?,?)}");

            consulta.setString(1,
                    "SELECT " + id + "::integer as id, " + source + "::integer as source, " + target
                            + "::integer as target, " + cost + " as cost," + reverse_cost + " as reverse_cost, "
                            + "ST_X(ST_StartPoint(" + the_geom + ")) as x1," + "ST_Y(ST_StartPoint(" + the_geom
                            + ")) as y1," + "ST_X(ST_EndPoint(" + the_geom + ")) as x2," + "ST_Y(ST_EndPoint("
                            + the_geom + ")) as y2," + rule + " as rule, " + to_cost + " as to_cost FROM " + table
            // + " order by " + id
            );
            consulta.setInt(2, origin.intValue());
            consulta.setInt(3, goal.intValue());
            consulta.setBoolean(4, true);
            consulta.setBoolean(5, true);
            log.trace(consulta);
            ResultSet resultado = consulta.executeQuery();

            while (resultado.next())
                lista.add(resultado.getLong("edge_id"));

        } catch (Exception e) {
            log.error("No se pudo calcular la ruta", e);
        }

        return lista;
    }

    /**
     * Devuelve la lista de num calles que encajan con el patrn
     * 
     * @param pattern
     * @param num
     * @return list of streets
     */
    @SuppressWarnings("unchecked")
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    public List<String> find(final String pattern, final Integer num) {
        List<String> lista = new ArrayList<String>();
        try {
            lista = getSession().createCriteria(Routing.class)
                    .setProjection(Projections.distinct(Projections.property("name"))).setMaxResults(num)
                    .add(Restrictions.ilike("name", pattern)).addOrder(Order.asc("name")).list();

        } catch (Exception e) {
            log.error("Error al buscar las calles :" + pattern, e);
        }

        return lista;
    }

    /**
     * Devuelve la primera calle que encuentre con este nombre:
     * 
     * @param pattern
     * @param num
     * @return list of streets
     */
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    public Routing find(final String calle) {
        Routing res = null;
        try {
            res = (Routing) getSession().createCriteria(Routing.class).add(Restrictions.eq("name", calle))
                    .add(Restrictions.isNotNull("geometria")).addOrder(Order.asc("id")).setMaxResults(1)
                    .uniqueResult();

        } catch (Exception e) {
            log.error("Error al buscar las calles :" + calle, e);
        }

        return res;
    }

    /**
     * Devuelve la lista de ids de la ruta desde vertice_origen a
     * vertice_destino
     * 
     * @param origin
     * @param goal
     * @return
     */
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    private List<Long> getSimpleGid(final Long origin, final Long goal) {
        final List<Long> lista = new ArrayList<Long>();
        try {
            Session currentSession = getSession();
            CallableStatement consulta = currentSession.connection().prepareCall("{call shortest_path(?,?,?,?,?)}");
            consulta.setString(1, "SELECT id, " + source + "::int4, " + target + "::int4, " + "ST_length2d("
                    + the_geom + ")::float8 as cost FROM " + table);
            consulta.setLong(2, origin);
            consulta.setLong(3, goal);
            consulta.setBoolean(4, false);
            consulta.setBoolean(5, false);
            log.trace(consulta);
            ResultSet resultado = consulta.executeQuery();

            while (resultado.next())
                lista.add(resultado.getLong("edge_id"));
        } catch (Exception e) {
            log.error("No se pudo calcular la ruta", e);
        }

        return lista;
    }

    /**
     * Devuelve null si hay algn error.
     * 
     * @param origen
     * @param destino
     * @return
     */
    @SuppressWarnings("unchecked")
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    public MultiLineString calculateRoute(final Point origen, final Point destino, final funcion f) {
        log.trace("calculateRoute(" + origen + ", " + destino + ", " + f + ")");
        MultiLineString resultado = null;
        try {
            Long origin = getVertex(origen, false);
            Long goal = getVertex(destino, true);
            List<Long> ids = new ArrayList<Long>(0);

            if (origin != null && goal != null)
                switch (f) {
                case SIMPLE:
                    ids = getSimpleGid(origin, goal);
                    break;
                case SHOOTING_STAR:
                    ids = shortest_path_shooting_star(origin, goal);
                    break;
                }

            if (ids.size() > 0) {

                Session currentSession = getSession();
                final Criteria criteria = currentSession.createCriteria(Routing.class)
                        .add(Restrictions.in("id", ids)).setProjection(Projections.property("geometria"));
                log.trace(criteria);
                List<Object> lineas = criteria.list();
                List<LineString> lineStrings = new LinkedList<LineString>();

                for (Object m : lineas) {
                    if (m instanceof MultiLineString)
                        for (int i = 0; i < ((MultiLineString) m).getNumGeometries(); i++)
                            lineStrings.add((LineString) ((MultiLineString) m).getGeometryN(i));
                    else if (m instanceof LineString)
                        lineStrings.add((LineString) m);
                    else
                        log.error("Devuelto alto extrao: " + m);
                }

                resultado = new MultiLineString(lineStrings.toArray(new LineString[0]), new GeometryFactory());

                resultado.setSRID(4326);

                if (log.isTraceEnabled())
                    log.trace("Resultado: " + resultado);
            }
        } catch (Throwable t) {
            log.error("Error al calcular la ruta", t);
            resultado = null;
        }

        return resultado;
    }

    /**
     * Devuelve el id del vrtice ms cercano, calculado segn la tabla de
     * routing.
     * 
     * @param p
     * @param end
     * @return
     */
    @Transactional(readOnly = true, rollbackFor = Throwable.class)
    private Long getVertex(Point p, boolean end) {
        log.trace("getVertex(" + p + ", " + end + ")");
        Long res = -1l;
        try {
            Session currentSession = getSession();
            String point = "Start";
            if (end) {
                point = "End";
            }

            Type geometryType = new CustomType(GeometryUserType.class, null);
            Query q = currentSession.createQuery(
                    "select " + id + " from " + table + " order by ST_Distance(ST_SETSRID(ST_POINT(ST_X(ST_" + point
                            + "Point(" + the_geom + ")),ST_Y(ST_" + point + "Point(" + the_geom + "))), " + SRID
                            + ")" + ",ST_SETSRID(?, " + SRID + ")) asc");
            q.setParameter(0, p, geometryType);
            log.trace("SRID " + p.getSRID());
            q.setMaxResults(1);
            log.trace(q);
            res = (Long) q.uniqueResult();
        } catch (Throwable t) {
            log.error("Error al calcular el vertice mas cercano" + t, t);
        }
        log.trace(res);

        return res;
    }

}