Friday, June 5, 2015

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



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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);
       }

1 comment:

Urban Growth Lab - UGLab

Proud to release a first version of the UGLab project .  UGLab is a python open source project capable to execute Urban Growth predictions f...