| gvSIG desktop 1

/*

 * Created on 04-mar-2005

 *

 * gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana

 * 

 * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.

 * 

 * This program 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 program 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 program; if not, write to the Free Software

 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

 *  

 * For more information, contact:

 *

 *  Generalitat Valenciana

 *   Conselleria d'Infraestructures i Transport

 *   Av. Blasco Ib??ez, 50

 *   46010 VALENCIA

 *   SPAIN

 *

 *      +34 963862235

 *   gvsig@gva.es

 *      www.gvsig.gva.es

 * 

 *    or

 * 

 *   IVER T.I. S.A

 *   Salamanca 50

 *   46005 Valencia

 *   Spain

 * 

 *   +34 963163400

 *   dac@iver.es

 */

package com.iver.cit.gvsig.fmap.drivers.jdbc.postgis;

import java.awt.geom.Rectangle2D;

import java.io.IOException;

import java.nio.ByteBuffer;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import java.util.Hashtable;

import org.apache.log4j.Logger;

import org.postgis.PGbox2d;

import org.postgis.PGbox3d;

import com.hardcode.gdbms.engine.data.edition.DataWare;

import com.hardcode.gdbms.engine.values.Value;

import com.hardcode.gdbms.engine.values.ValueFactory;

import com.iver.andami.messages.NotificationManager;

import com.iver.cit.gvsig.fmap.DriverException;

import com.iver.cit.gvsig.fmap.core.ICanReproject;

import com.iver.cit.gvsig.fmap.core.IGeometry;

import com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver;

import com.iver.cit.gvsig.fmap.drivers.DriverAttributes;

import com.iver.cit.gvsig.fmap.drivers.IFeatureIterator;

import com.iver.cit.gvsig.fmap.drivers.jdbc.WKBParser;

/**

 * @author FJP

 *

 * TODO To change the template for this generated type comment go to

 * Window - Preferences - Java - Code Generation - Code and Comments

 */

public class PostGisDriver extends DefaultDBDriver implements ICanReproject {

    private static Logger logger = Logger.getLogger(PostGisDriver.class.getName());

    private static int FETCH_SIZE = 5000;

    private WKBParser parser = new WKBParser();

    private int fetch_min=-1;

    private int fetch_max=-1;

    private String strEPSG = null;

    private String originalEPSG = null;

    private Rectangle2D fullExtent = null;

    private String strAux;

    static {

            try {

                        Class.forName("org.postgresql.Driver");

                } catch (ClassNotFoundException e) {

                        throw new RuntimeException(e);

                }

    }    

    /**

     * 

     */

    public PostGisDriver() {

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialDriver#getDriverAttributes()

     */

    public DriverAttributes getDriverAttributes() {

        return null;

    }

    /* (non-Javadoc)

     * @see com.hardcode.driverManager.Driver#getName()

     */

    public String getName() {

        return "PostGIS JDBC Driver";

    }

        /**

         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getShape(int)

         */

        public IGeometry getShape(int index) {

            IGeometry geom = null;

            boolean resul;

                try {

                setAbsolutePosition(index);

                    // strAux = rs.getString(1);                

                    // geom = parser.read(strAux);

                    byte[] data = rs.getBytes(1);                

                    geom = parser.parse(data);

            } catch (SQLException e) {

                e.printStackTrace();

            }

            return geom;

        }

        /**

     * Antes de llamar a esta funci?n hay que haber fijado el 

     * workingArea si se quiere usar.

         * @param conn

         * @param tableName

         * @param fields OJO: EL PRIMER CAMPO HA DE SER EL DE GEOMETRIA

         * @param whereClause

         */

        public void setData(Connection conn, String tableName, String fields, String whereClause, int id_FID_field)

        {

            this.conn = conn;            

            this.tableName = tableName;

            this.fields = fields;

            this.whereClause = whereClause;

            this.sqlOrig = "SELECT " + fields + " FROM " + tableName + " " + whereClause;

        this.idFID_FieldName = id_FID_field;

        getTableEPSG();

            try {

            String sqlAux = null;

            if (canReproject(strEPSG))

            {

                sqlAux = getCompoundWhere(workingArea, strEPSG);

            }

            else

            {

                sqlAux = getCompoundWhere(workingArea, originalEPSG);                

            }

            logger.info("Cadena SQL:" + sqlAux);

                st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

                // st.setFetchSize(FETCH_SIZE);

                st.execute("declare wkb_cursor binary cursor for " + sqlOrig);

                rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in wkb_cursor");

                // st.execute("begin"); 

                // bCursorActivo = true; 

                // rs = st.executeQuery(sqlOrig);

            fetch_min = 0;

            fetch_max = FETCH_SIZE-1;

            metaData = rs.getMetaData();

            doRelateID_FID();

        } catch (SQLException e) {

            NotificationManager.addError("Error al conectar a la base de datos.",e);

        }

        }

        /**

         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getFullExtent()

         */

        public Rectangle2D getFullExtent(){

            if (fullExtent == null)

            {

                try

            {

                    Statement s = conn.createStatement();                    

                    ResultSet r = s.executeQuery("SELECT extent(the_geom) AS FullExtent FROM " + tableName);

                    r.next();

                    String strAux = r.getString(1);

                    System.out.println("fullExtent = " + strAux);

                                if (strAux.startsWith("BOX3D"))

                                {

                                        PGbox3d regeom = new PGbox3d(strAux);

                            double x = regeom.getLLB().x;

                            double y = regeom.getLLB().y;

                            double w = regeom.getURT().x -x;

                            double h = regeom.getURT().y - y;

                        fullExtent = new Rectangle2D.Double(x, y, w, h);                            

                                }

                                else

                                {

                                        PGbox2d regeom = new PGbox2d(strAux);

                            double x = regeom.getLLB().x;

                            double y = regeom.getLLB().y;

                            double w = regeom.getURT().x -x;

                            double h = regeom.getURT().y - y;

                        fullExtent = new Rectangle2D.Double(x, y, w, h);                                                                    

                                }

            }

                catch (SQLException e)

                {

                    System.err.println(e.getMessage());

                }

            }

            return fullExtent;

        }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getGeometryIterator(java.lang.String)

     */

    public IFeatureIterator getFeatureIterator(String sql) throws com.iver.cit.gvsig.fmap.DriverException {

        PostGisFeatureIterator geomIterator = null;

        try {

            st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // st.setFetchSize(2000);

            if (bCursorActivo)

            {

                st.execute("CLOSE wkb_cursor_prov");

                bCursorActivo = false;

            }

                st.execute("declare wkb_cursor_prov binary cursor for " + sql);

                ResultSet rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in wkb_cursor_prov");

            // st.execute("BEGIN");

            bCursorActivo = true;

            // ResultSet rs = st.executeQuery(sql);

            geomIterator = new PostGisFeatureIterator(rs, st);

        } catch (SQLException e) {

            e.printStackTrace();

            throw new com.iver.cit.gvsig.fmap.DriverException(e);

        }

        return geomIterator;

    }

    public IFeatureIterator getFeatureIterator(Rectangle2D r, String strEPSG) throws DriverException {

        if (workingArea != null)

            r = r.createIntersection(workingArea);

        String sqlAux;

        if (canReproject(strEPSG))

        {

            sqlAux = getCompoundWhere(r, strEPSG);

        }

        else

        {

            sqlAux = getCompoundWhere(r, originalEPSG);                

        }

        System.out.println("SqlAux getFeatureIterator = " + sqlAux);

        return getFeatureIterator(sqlAux);

    }

    /**

     * Le pasas el rect?ngulo que quieres pedir. La primera

     * vez es el workingArea, y las siguientes una interseccion

     * de este rectangulo con el workingArea

     * @param r

     * @param strEPSG

     * @return

     */

    private String getCompoundWhere(Rectangle2D r, String strEPSG) {

        if (r==null)

            return this.sqlOrig;

        double xMin = r.getMinX();

        double yMin = r.getMinY();

        double xMax = r.getMaxX();

        double yMax = r.getMaxY();

        String wktBox = "GeometryFromText('LINESTRING(" + xMin + " " + yMin + ", "

                + xMax + " " + yMin + ", "

                + xMax + " " + yMax + ", "

                + xMin + " " + yMax + ")', "

                + strEPSG + ")";

        String sqlAux;

        if (getWhereClause().startsWith("WHERE")) 

            sqlAux = sqlOrig + " the_geom && " + wktBox;

        else

            sqlAux = sqlOrig + "WHERE the_geom && " + wktBox;

        return sqlAux;

    }

        /**

         * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getConnectionStringBeginning()

         */

        public String getConnectionStringBeginning() {

                return "jdbc:postgresql:";

        }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver#getFieldValue(long, int)

     */

    public Value getFieldValue(long rowIndex, int idField) throws com.hardcode.gdbms.engine.data.driver.DriverException {

        boolean resul;

            // EL ABSOLUTE NO HACE QUE SE VUELVAN A LEER LAS

            // FILAS, ASI QUE MONTAMOS ESTA HISTORIA PARA QUE

            // LO HAGA

            // System.out.println("getShape " + index);

        int index = (int) (rowIndex);

        try

        {

            setAbsolutePosition(index);

            int fieldId = idField+2;

            byte[] byteBuf = rs.getBytes(fieldId);

            if (byteBuf == null)

                return  ValueFactory.createNullValue();

            else

            {

                ByteBuffer buf = ByteBuffer.wrap(byteBuf);

                if (metaData.getColumnType(fieldId) == Types.VARCHAR)

                    return  ValueFactory.createValue(rs.getString(fieldId));

                if (metaData.getColumnType(fieldId) == Types.FLOAT)

                    return ValueFactory.createValue(buf.getFloat());

                if (metaData.getColumnType(fieldId) == Types.DOUBLE)

                    return ValueFactory.createValue(buf.getDouble());

                if (metaData.getColumnType(fieldId) == Types.INTEGER)

                    return ValueFactory.createValue(buf.getInt());

                if (metaData.getColumnType(fieldId) == Types.BIGINT)

                    return ValueFactory.createValue(buf.getLong());

                if (metaData.getColumnType(fieldId) == Types.BIT)

                    // TODO

                    return ValueFactory.createValue(rs.getBoolean(fieldId));

                if (metaData.getColumnType(fieldId) == Types.DATE)

                    // TODO

                    return  ValueFactory.createValue(rs.getDate(fieldId));

            }

        }

        catch(SQLException e)

        {

            throw new com.hardcode.gdbms.engine.data.driver.DriverException(e.getMessage());

        }

        return ValueFactory.createNullValue();

    }

    public void open()  throws com.iver.cit.gvsig.fmap.DriverException {

        /* try {

            st = conn.createStatement();

            st.setFetchSize(2000);

            if (bCursorActivo)

                close();

            st.execute("declare wkb_cursor binary cursor for " + sqlOrig);

            rs = st.executeQuery("fetch forward all in wkb_cursor");

            // st.execute("BEGIN");

            bCursorActivo = true;

        } catch (SQLException e) {

            e.printStackTrace();

            throw new com.iver.cit.gvsig.fmap.DriverException(e);

        } */

    }

    private void setAbsolutePosition(int index) throws SQLException

    {

        // EL ABSOLUTE NO HACE QUE SE VUELVAN A LEER LAS

        // FILAS, ASI QUE MONTAMOS ESTA HISTORIA PARA QUE

        // LO HAGA

        // System.out.println("getShape " + index + " fetchMin=" + fetch_min + " fetchMax=" + fetch_max);

        if (index < fetch_min)

        {

            // rs.close();

            st.execute("CLOSE wkb_cursor");

            st.execute("declare wkb_cursor binary cursor for " + sqlOrig);

            rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in wkb_cursor");

            // rs.beforeFirst();           

            // rs = st.executeQuery(sqlOrig);

            fetch_min = 0;

            fetch_max = FETCH_SIZE-1;

        }

        while (index > fetch_max)

        {

            rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in wkb_cursor");

            // rs.next();

            /* rs.afterLast();

            // forzamos una carga

            rs.next(); */

            fetch_min = fetch_max+1;

            fetch_max = fetch_min + FETCH_SIZE -1;

            // System.out.println("fetchSize = " + rs.getFetchSize() + " " + fetch_min + "-" + fetch_max);

        } 

        rs.absolute(index - fetch_min + 1);

        // rs.absolute(index+1);

    }

        /**

         * @see com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getGeometryField(java.lang.String)

         */

        public String getGeometryField(String fieldName) {

                return "ASBINARY(" + fieldName + ", 'XDR')";

        }

    /**

     * @see com.hardcode.gdbms.engine.data.driver.ObjectDriver#getPrimaryKeys()

     */

    public int[] getPrimaryKeys() throws com.hardcode.gdbms.engine.data.driver.DriverException {

        // TODO Auto-generated method stub

        return null;

    }

    /**

     * @see com.iver.cit.gvsig.fmap.drivers.VectorialJDBCDriver#getDefaultPort()

     */

    public int getDefaultPort() {

        return 5432;

    }

    /**

     * @see com.hardcode.gdbms.engine.data.driver.ObjectDriver#write(com.hardcode.gdbms.engine.data.edition.DataWare)

     */

    public void write(DataWare arg0) throws com.hardcode.gdbms.engine.data.driver.DriverException {

        // TODO Auto-generated method stub

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.core.ICanReproject#getSourceProjection()

     */

    public String getSourceProjection() {

        if (originalEPSG == null)

            getTableEPSG();

        return originalEPSG;

    }

    private void getTableEPSG()

    {

        try {

            Statement stAux = conn.createStatement();

            String sql = "SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME = '" +

                getTableName() + "';";

            ResultSet rs = stAux.executeQuery(sql);

            rs.next();

            originalEPSG = "" + rs.getInt("SRID");

            rs.close();

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.core.ICanReproject#getDestProjection()

     */

    public String getDestProjection() {

        return strEPSG;

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.core.ICanReproject#setDestProjection(java.lang.String)

     */

    public void setDestProjection(String toEPSG) {

        this.strEPSG = toEPSG;

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.core.ICanReproject#canReproject(java.lang.String)

     */

    public boolean canReproject(String toEPSGdestinyProjection) {

        // TODO POR AHORA, REPROYECTA SIEMPRE gvSIG.

        return false;

    }

    /* (non-Javadoc)

     * @see com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver#doRelateID_FID()

     */

    protected void doRelateID_FID()

    {

        hashRelate = new Hashtable();

        try {

            String strSQL = "select gid from " + getTableName();

            Statement s = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            int fetchSize = 5000;

            ResultSet r = s.executeQuery(strSQL);

            int id=0;

            int gid;

            while( r.next() ) 

            { 

              gid = r.getInt(1);

              id++;

              Value aux = ValueFactory.createValue(gid);

              hashRelate.put(aux, new Integer(gid));             

              // System.out.println("Row " + id + ":" + strAux); 

            } 

            s.close();

            numReg = id;

            /* for (int index = 0; index < getShapeCount(); index++)

            {

                Value aux = getFieldValue(index, idFID_FieldName-2);

                hashRelate.put(aux, new Integer(index));             

                // System.out.println("Row " + index + " clave=" + aux);

            }*/

/*             int index = 0;

            while (rs.next())

            {

                Value aux = getFieldValue(index, idFID_FieldName-2);

                hashRelate.put(aux, new Integer(index));

                index++;

                System.out.println("Row " + index + " clave=" + aux);                 

            }

            numReg = index; */

            // rs.beforeFirst();

        /* } catch (com.hardcode.gdbms.engine.data.driver.DriverException e) {

            // TODO Auto-generated catch block

            e.printStackTrace(); */

        } catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }          

    }

}