| gvSIG desktop 1

/*

 * Created on 03-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.io.InputStream;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Enumeration;

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

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 testPostGis {

      public static void main(String[] args) 

      { 

      /*    System.err.println("dburl has the following format:");

          System.err.println("jdbc:postgresql://HOST:PORT/DATABASENAME");

          System.err.println("tablename is 'jdbc_test' by default.");

          System.exit(1); */

      // Tarda casi 28 segundos en recuperar el tema de provincias!!

      String dburl = "jdbc:postgresql://localhost/latin1";

      String dbuser = "postgres";

      String dbpass = "aquilina";

      String dbtable = "vias";

     java.sql.Connection conn; 

        try 

        { 

            System.out.println("Creating JDBC connection...");

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

            Enumeration enumDrivers = DriverManager.getDrivers();

            while (enumDrivers.hasMoreElements())

            {

                System.out.println("Driver " + enumDrivers.nextElement().toString());

            }

            conn = DriverManager.getConnection(dburl, dbuser, dbpass);

            // magic trickery to be pgjdbc 7.2 compatible

            // This works due to the late binding of data types in most java VMs. As

            // this is more a demo source than a real-world app, we can risk this

            // problem.

            /* if (conn.getClass().getName().equals("org.postgresql.jdbc2.Connection")) {

                ((org.postgresql.Connection) conn).addDataType("geometry", "org.postgis.PGgeometry");

                ((org.postgresql.Connection) conn).addDataType("box3d", "org.postgis.PGbox3d");

            } else {

                ((org.postgresql.PGConnection) conn).addDataType("geometry", "org.postgis.PGgeometry");

                ((org.postgresql.PGConnection) conn).addDataType("box3d", "org.postgis.PGbox3d");

            } */

            conn.setAutoCommit(false);

          /* 

          * Create a statement and execute a select query. 

          */

            // String strSQL = "select ASBINARY(the_geom) as geom, nom_provin from " + dbtable;

            String strSQL = "select ASBINARY(the_geom) as geom from " + dbtable;

            // strSQL = "select ASTEXT(the_geom), nom_provin as geom from " + dbtable;

            /* String strSQL = "SELECT gid, rd_3, rd_5, rd_6, rd_10, rd_11, rd_12, rd_13, rd_14,"; 

            strSQL = strSQL + " rd_15, rd_16, kilometers, cost, metros, AsText(force_2d(the_geom)) FROM vias"; 

            strSQL = strSQL + " WHERE TRUE";

            */

            // PreparedStatement s = conn.prepareStatement(strSQL);

            long t1 = System.currentTimeMillis();

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

                        // s.execute("begin");

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

            // s.setFetchSize(5);

            int fetchSize = 200000;

                        s.execute("declare wkb_cursor binary cursor for " + strSQL);

                        // ResultSet r =        s.executeQuery("fetch forward " + fetchSize + " in wkb_cursor");

            ResultSet r =   s.executeQuery("fetch forward all in wkb_cursor");

            // ResultSet r = s.executeQuery(strSQL);

                        long t2 = System.currentTimeMillis();

                        System.out.println("Tiempo de consulta:" + (t2 - t1) + " milisegundos");

                        t1 = System.currentTimeMillis();

                        WKBParser parser = new WKBParser();

                        long id=0;

                        /* for (int i=1; i< 100; i++)

                        {

                            r.absolute(i);

                            System.out.println("Row " + i + ":" + r.getString(2));

                        }

                        r.beforeFirst();

            for (int i=1; i< 100; i++)

            {

                r.absolute(i);

                System.out.println("Row " + i + ":" + r.getString(2));

            } */

          while( r.next() ) 

          { 

            /* 

            * Retrieve the geometry as an object then cast it to the geometry type. 

            * Print things out. 

            */ 

              // Object obj = r.getObject(2);

              byte[] arrayByte = r.getBytes(1);

              IGeometry gp = parser.parse(arrayByte);

              // String strAux = r.getString(1);

              // int id = r.getInt(2);

              // System.out.println("Fila " + id + ":" + obj.toString());

              id++;

              // Geometry regeom = PGgeometry.geomFromString(obj.toString());

            // PGgeometry geom = (PGgeometry)obj; 

            /* int id = r.getInt(2);

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

            System.out.println(geom.toString()); */ 

              // System.out.println("provin=" + r.getString(2));

              if ((id % fetchSize) == 0)

              {

                  r =   s.executeQuery("fetch forward " + fetchSize + " in wkb_cursor");

              }

          }

          s.close(); 

          conn.close();

                        t2 = System.currentTimeMillis();

                        System.out.println("Tiempo de recorrido:"  + (t2 - t1) + " milisegundos. NumRegistros recorridos" + id);

        } 

        catch( Exception e ) 

        { 

          e.printStackTrace(); 

        }  

      }

}