Free Open Source GIS : Convert Oracle ResultSet to ESRI shapefile using GEOTOOLS v13

Ok that was a bit hard. Geootools have no clear documentation for that specific action.
So using some piece of code from this example csv2shp example and with little bit of imagination I finally managed to convert a resultset (including sdo_geometry into a shapefile)
My code is not final but if you are looking for such action it should give you a boost. It is also a bit fresh so I ll try to tidy it a bit up and update the post. If any of you have any tips or tricks to suggest you are welcome.

Before we go through the code example. Lets see the prerequisites.
(these are copy-paste from my other post --> sdo2gml)
1) You need to download sdoutl.jar and sdoapi.jar. These 2 jars may be found in the Oracle Companion CD which may be freely downloaded from oracle. Else just google it and you may found some downloading links.
2) You need to download ojdbc6.jar which may be found here ojdbc6.jar
3) Once you have these three jars you may reference them to your project.

The following geotools jars shall be referenced to your project. (geotools version 13)
--> gt-xsd-filter
--> gt-jdbc-oracle
--> gt-data
--> gt-shapefile
So lets start coding.

Fisrt we need to create a class to obtain a jdbc connection to oracle database.
class accepts 5 parameters (ip, port, dbname, user, pass) I shall give you an example at the end of this post. Lets go through the code of our first classs
(these are copy-paste from my other post --> sdo2gml)
GetOraConnection class CODE

package freeopengis.rdbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *
 * @author pt
 */
public class GetOraConnection {


    public static Connection main (String ip, String port, String db, String user, String pass)
            throws InstantiationException, IllegalAccessException{
    try {
         Class.forName ("oracle.jdbc.OracleDriver").newInstance();
        } catch (ClassNotFoundException e) {
            System.out.println("Oracle JDBC Driver missing.");
            e.printStackTrace();
        }
        System.out.println("Oracle JDBC Driver Found and Registered.");
        Connection connection = null;
        try {
            System.out.println("jdbc:oracle:thin:@"+ip+":"+port+":"+db);
            connection = DriverManager.getConnection(
                                "jdbc:oracle:thin:@"+ip+":"+port+":"+db,
                    user,
                    pass);
        
        } catch (SQLException e) {

                System.out.println("Connection Failed!");
                e.printStackTrace();
        }

        if (connection != null) {
                System.out.println("Connection succeed!");
        } else {
                System.out.println("Failed to create connection!");
        }
        return connection;
    }
}

Once we have this class we may create the class that does the job.
RsetToShape class CODE

package gis.openfreegis.rdbms;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.MultiPoint;
import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKTReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Serializable;
import java.nio.charset.Charset;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import oracle.spatial.geometry.JGeometry;
import oracle.spatial.util.GeometryExceptionWithContext;
import oracle.spatial.util.WKT;
import oracle.sql.STRUCT;
import org.geotools.data.DataUtilities;
import org.geotools.data.DefaultTransaction;
import org.geotools.data.Transaction;
import org.geotools.data.collection.ListFeatureCollection;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.data.shapefile.ShapefileDataStoreFactory;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureSource;
import org.geotools.data.simple.SimpleFeatureStore;
import org.geotools.feature.SchemaException;
import org.geotools.feature.simple.SimpleFeatureBuilder;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;

/**
 *
 * @author pt
 * These are the gtypes used by oracle
 * Note that we do not hanlde unknown geometries or collection geometries
 * as shapefiles may not hold collection or unknown geometries
 * 0 -- unknown geometry    --- IGNORE IT
 * 1 -- point               --- Point
 * 2 -- line                --- Line
 * 3 -- polygon             --- Polygon
 * 4 -- collection          --- IGNORE IT
 * 5 -- multipoint          --- MultiPoint
 * 6 -- multiline           --- MultiLine
 * 7 -- multipolygon        --- MultiPolygon
 */
public class RsetToShape {
    //set the charset for the dbf generated
    public static final Charset USER_CHARSET = Charset.forName("UTF-8");
    //set directory where shapefile should be generated
     public static final String WORKDIR = "C:\\openfreegis\\data\\";
    /**
     *
     * @param rset {ResultSet}  the resultset executed from query
     * @param geometrytype {String} 
                               the type of geometries you want to generate.
                               Possible values are "Point"||"Line"||"Polygon"||null
                              If passed as null then the geometry of first row
                              should give the geometry type.
     * @param {String}shpname 
                             The name of file without any extension (.shp)
                              If passed as null the timestamp shall be used
     * @param {Boolean} zip 
                               true or false depending if you want shapefile files (.dbf,.shp etc)
                               to be zipped within a single zip file
     * @throws SQLException
     * @throws IOException
     * @throws GeometryExceptionWithContext
     * @throws ParseException
     * @throws SchemaException
     */
    public RsetToShape (ResultSet rset, String geometrytype, String shpname,Boolean zip) throws SQLException, IOException, GeometryExceptionWithContext, ParseException, SchemaException {
    System.out.println("geometrytype===="+geometrytype);
    //check the input parameters
    String ShpFileName;
    if (shpname==null){
    ShpFileName = new SimpleDateFormat("yyyyMMddhhmm").format(new Date());
    } else {
    ShpFileName = shpname;
    }
    String geomType = "";
    int geomtype = 0; //this is to set the type for the shapefile
    int parsingGeomType = 0;//this is the type of geomtery parsed from resultset
    if (geometrytype == null){
    geomtype = 0;
    }
    else if (geometrytype.equals("Polygon")){
    geomtype = 7;
    }
    else if (geometrytype.equals("Line")){
    geomtype = 6;
    }
    else if (geometrytype.equals("Point")){
    geomtype = 5;
    }
    else{
    geomtype = 0;
    }
    ResultSetMetaData rsmd=rset.getMetaData();
    int numOfColumns = rsmd.getColumnCount();
    List<SimpleFeature> features = new ArrayList<SimpleFeature>();
    SimpleFeatureBuilder featureBuilder = null;
    SimpleFeatureType N_TYPE = null;
    WKT wkt = new WKT();
    String typeElemsStr = "";
    int counter = 0;
    int sridCode = 0;
   
    while (rset.next()) {
        counter = counter +1;
        System.out.println("processing row "+counter);
        SimpleFeature feature = null;
        List<String> attrsNames = new ArrayList<String>();
        List<String> attrsTypes = new ArrayList<String>();
        List<String> attrsVals = new ArrayList<String>();
        /*
         * We handle the first row independenly
         * so we build the SimpleFeatureType from the first row
         * this means column names and geometry type
         * should be getted from first row unless you have supplied
         * the geomtry type you want to parse
         */
        if (counter ==1){
            JGeometry j_geom = null;
           
            for (int i=1;i<numOfColumns+1;i++){
            String colName = rsmd.getColumnName(i);
            String columntype = rsmd.getColumnTypeName(i);
                    if (columntype == "NUMBER"){
                         typeElemsStr = typeElemsStr + colName +":Double,";
                         attrsNames.add(colName);
                         attrsTypes.add("Double");
                         attrsVals.add(rset.getString(rset.getMetaData().getColumnName(i)));
                    }
                    if (columntype == "VARCHAR2"){
                         typeElemsStr = typeElemsStr + colName +":String,";
                         attrsNames.add(colName);
                         attrsTypes.add("String");
                         attrsVals.add(rset.getString(rset.getMetaData().getColumnName(i)));
                    }
                    if (columntype.equals("MDSYS.SDO_GEOMETRY")){
                        STRUCT st = (oracle.sql.STRUCT) rset.getObject(i);
                        j_geom = JGeometry.load(st);
                        if (geomtype == 0){//set it if not supplied
                        System.out.println("geomtery must be setted as it is not clarified");
                        geomtype = j_geom.getType();
                        }
                        parsingGeomType = j_geom.getType();
                        sridCode = j_geom.getSRID(); 
                        //polygon and multipolygons may be in the same shape file
                        if (geomtype == 3 || geomtype == 7){
                        geomType = "MultiPolygon";
                        }
                        //point and MultiPoint may be in the same shape file
                        if (geomtype == 1 || geomtype == 5){
                        geomType = "MultiPoint";
                        }
                         //Line and MultiLine may be in the same shape file
                        if (geomtype == 2 || geomtype == 6){
                        geomType = "MultiLine";
                        }
                    
                    }
                 }
                typeElemsStr = "the_geom:"+geomType+":srid="+sridCode+","+typeElemsStr;
                System.out.println("typeElemsStr===="+typeElemsStr);
                N_TYPE = createFeatureType(typeElemsStr);
                featureBuilder = new SimpleFeatureBuilder(N_TYPE);
                String wktRepres = new String(wkt.fromJGeometry(j_geom));
                GeometryFactory geometryFactory1 = JTSFactoryFinder.getGeometryFactory( null );
                WKTReader reader = new WKTReader( geometryFactory1 );
                Geometry geom = getGeometryObj(parsingGeomType,reader,wktRepres);
                feature = featureBuilder.buildFeature("0");
                feature.setAttribute("the_geom", geom);   
                 for (int g=0;g<attrsNames.size();g++){
                       if (attrsTypes.get(g).equals("Double")){
                           if (attrsVals.get(g).equals(null)){
                           feature.setAttribute(attrsNames.get(g), 0);   
                           }
                           else{
                           feature.setAttribute(attrsNames.get(g), Double.parseDouble(attrsVals.get(g)));
                           }
                       }
                       if (attrsTypes.get(g).equals("String")){
                       feature.setAttribute(attrsNames.get(g), attrsVals.get(g));
                       }
                   }
            features.add(feature);
            }
            //go through the rest of rows
            else {
            Geometry geom = null;
            for (int r=1;r<numOfColumns+1;r++){
            String colName = rsmd.getColumnName(r);
            String columntype = rsmd.getColumnTypeName(r);
                if (columntype == "NUMBER"){
                attrsNames.add(colName);
                attrsTypes.add("Double");
                attrsVals.add(rset.getString(rset.getMetaData().getColumnName(r)));
                }
                if (columntype == "VARCHAR2"){
                attrsNames.add(colName);
                attrsTypes.add("String");
                attrsVals.add(rset.getString(rset.getMetaData().getColumnName(r)));
                }
                if (columntype.equals("MDSYS.SDO_GEOMETRY")){
                STRUCT st = (oracle.sql.STRUCT) rset.getObject(r);
                JGeometry j_geom = JGeometry.load(st);
                int geotype = j_geom.getType();
                String wktRepres = new String(wkt.fromJGeometry(j_geom));
                GeometryFactory geometryFactory1 = JTSFactoryFinder.getGeometryFactory( null );
                WKTReader reader = new WKTReader( geometryFactory1 );
                geom = getGeometryObj(geotype,reader,wktRepres);
               
                }
                feature = featureBuilder.buildFeature(counter+"");//trick it to get it as String
                feature.setAttribute("the_geom", geom);
            }
            for (int s=0;s<attrsNames.size();s++){
              if (attrsTypes.get(s).equals("Double")){
               feature.setAttribute(attrsNames.get(s), Double.parseDouble(attrsVals.get(s)));
               }
              if (attrsTypes.get(s).equals("String")){
              feature.setAttribute(attrsNames.get(s), attrsVals.get(s));
              }
            }
        features.add(feature);
        }
    }
    System.out.println("features length is===="+features.size());
    File file = new File(WORKDIR+ShpFileName+".shp");
    ShapefileDataStoreFactory dataStoreFactory = new ShapefileDataStoreFactory();
    Map<String, Serializable> params = new HashMap<String, Serializable>();
    params.put("url", file.toURI().toURL());
    params.put("create spatial index", Boolean.TRUE);
    ShapefileDataStore newDataStore = (ShapefileDataStore) dataStoreFactory.createNewDataStore(params);
    //set the disired charset
    newDataStore.setCharset(USER_CHARSET);
    newDataStore.createSchema(N_TYPE);
    /*
    * Write the features to the shapefile
    */
    Transaction transaction = new DefaultTransaction("create");

    String typeNameShp = newDataStore.getTypeNames()[0];
    SimpleFeatureSource featureSource = newDataStore.getFeatureSource(typeNameShp);

        if (featureSource instanceof SimpleFeatureStore) {
            SimpleFeatureStore featureStore = (SimpleFeatureStore) featureSource;
            /*
             * SimpleFeatureStore has a method to add features from a
             * SimpleFeatureCollection object, so we use the ListFeatureCollection
             * class to wrap our list of features.
             */
            SimpleFeatureCollection collection = new ListFeatureCollection(N_TYPE, features);
            featureStore.setTransaction(transaction);
            try {
                featureStore.addFeatures(collection);
                //commit the transaction
                transaction.commit();
                    if (zip == true){
                     //now put all the generated files into one zip file
                    FileOutputStream fos = new FileOutputStream(WORKDIR+ShpFileName+".zip");
                    ZipOutputStream zos = new ZipOutputStream(fos);
                    String file1Name = ShpFileName+".shp";
                    String file2Name = ShpFileName+".dbf";
                    String file3Name = ShpFileName+".shx";
                    String file4Name = ShpFileName+".prj";
                    addToZipFile(WORKDIR, file1Name, zos);
                    addToZipFile(WORKDIR, file2Name, zos);
                    addToZipFile(WORKDIR, file3Name, zos);
                    addToZipFile(WORKDIR, file4Name, zos);
                    zos.close();
                    fos.close();
                    }
               
            } catch (Exception problem) {
                problem.printStackTrace();
                transaction.rollback();
               
            } finally {
                features.clear();
                rset.close();
                transaction.close();
            }

        } else {
            System.out.println(typeNameShp + " does not support read/write access");
            System.exit(1);
        }
    System.exit(0);
    }
    /**
     * @param s {String}
     * @return {SimpleFeatureType}
     * @throws SchemaException
     */
     private static SimpleFeatureType createFeatureType(String s) throws SchemaException {
         s=s.substring(0, s.length()-1);
         final SimpleFeatureType SF_TYPE = DataUtilities.createType("Location",
                s
        );
        return SF_TYPE;  
     }
     /**
      *
      * @param geomtype {int}
      * @param reader {WKTReader}
      * @param wktRepres {String}
      * @return {Geometry}
      * @throws ParseException
      */
     private static Geometry getGeometryObj(int geomtype, WKTReader reader, String wktRepres) throws ParseException{
         System.out.println(geomtype);
            Geometry geometryRet = null;
            //Multipolygon
            if (geomtype == 7){
            MultiPolygon geometry = (MultiPolygon) reader.read(wktRepres);
            geometryRet = geometry;
            }
            //Polygon
            if (geomtype == 3){
            Polygon geometry = (Polygon) reader.read(wktRepres);
            geometryRet = geometry;
            }
            //Multiline
            if (geomtype == 6){
            MultiLineString geometry = (MultiLineString) reader.read(wktRepres);
            geometryRet = geometry;
            }
            //Line
            if (geomtype == 2){
            LineString geometry = (LineString) reader.read(wktRepres);
            geometryRet = geometry;
            }
             //MultiPoint
            if (geomtype == 5){
            MultiPoint geometry = (MultiPoint) reader.read(wktRepres);
            geometryRet = geometry;
            }
            //Point
            if (geomtype == 1){
            Point geometry = (Point) reader.read(wktRepres);
            geometryRet = geometry;
            }
     return geometryRet;
     } 
    
     /**
      *
      * @param fileName {String}
      * @param zos {ZipOutputStream}
      * @throws FileNotFoundException
      * @throws IOException
      */
     public static void addToZipFile(String workdir, String fileName, ZipOutputStream zos) throws FileNotFoundException, IOException {

        System.out.println("Writing '" + fileName + "' to zip file");

        File file = new File(workdir+fileName);
        FileInputStream fis = new FileInputStream(file);
        ZipEntry zipEntry = new ZipEntry(fileName);
        zos.putNextEntry(zipEntry);

        byte[] bytes = new byte[1024];
        int length;
        while ((length = fis.read(bytes)) >= 0) {
            zos.write(bytes, 0, length);
        }

        zos.closeEntry();
        fis.close();
    }
}

Once all the above hav gone fine we are ready to use our new class and finally do the f***ing job. So you may create a method and call it like following

String dbip = "192.31.128.1";
    String dbport = "1521";
    String dbname = "orcl";
    String dbuser = "USERNAME";
    String dbpass = "PASS";
//This is just a sample query. You may use any kind of spatial query.
    String sqlquery =   "SELECT SDO_GEOM.SDO_INTERSECTION(GRNM.GEOM, BKM.GEOM, 0.05) GEOM"+
                        " FROM COLA_A BKM, COLA_B GRNM WHERE GRNM.ID<3 AND "+
                        " SDO_GEOM.SDO_INTERSECTION(GRNM.GEOM, BKM.GEOM, 0.05) IS NOT NULL ";

 try {
           Connection con = GetOraConnection.main(dbip,dbport, dbname, dbuser, dbpass);
           if (con !=null){
           Statement stmt = null;
            try {
                stmt = con.createStatement ();
            } catch (SQLException e) {
                System.out.println("ERROR:"+e.getMessage());
            }
            ResultSet rset = null;
            try {
                rset = stmt.executeQuery (sqlquery);
            } catch (SQLException e) {
                System.out.println("ERROR:"+e.getMessage());
            }
            if( rset != null )
            {
           //Here it is. This is our call to our new class
            RsetToShape shp = new RsetToShape(rset,null,null,true);
          //So if you want to call it given the filename and the geometries you 
          // want  to parse, you should call it like following
           RsetToShape shp1 = new RsetToShape(rset,"Polygon","myshapefile",true);

         
            stmt.close();
            rset.close();
            con.close();
            }
          
           }
       } catch (InstantiationException ex) {
           Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
       } catch (IllegalAccessException ex) {
           Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
       }

Free Open Source GIS

Friday, June 5, 2015

Convert Oracle ResultSet to ESRI shapefile using GEOTOOLS v13

1 comment:

Urban Growth Lab - UGLab

Pages

Search This Blog