Importing esri shapefiles into DBs (Oracle, postGIS) using JAVA

Today we will walk through the process of importing shapefiles into Oracle SDO geometry type and PostGIS ST geometry type.
For the case of Oracle you have two options. You may use eitheir native oracle native  jars (sdoutl.jar and sdoapi.jar) or GEOTOOLS  java library. 

Oracle Case 1 (in my personal opinion this is the simplest way to do it)

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.

Once you have the above create a JAVA class as follows:

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

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package gis.openfreegis.rdbms; import java.io.IOException; import java.sql.DriverManager; import oracle.jdbc.OracleConnection; import oracle.spatial.util.ShapefileReaderJGeom; import oracle.spatial.util.DBFReaderJGeom; import oracle.spatial.util.ShapefileFeatureJGeom; /** * * @author pt */ public class ShapeToSDO2 { public ShapeToSDO2 ( String shploc, //location of shapefile and name without any file extension String oraTableName, //oracle table name to give String geomColName, //geometry column name to give int Srid, //srid to asign rto the table double tol, //the spatial tolerance to asign to metadata String IdCol, //unique id column int IdStart, //starting point for integer to asign to IdCol int commitFreq, //num of records to insert before commiting int printFreq, //num of records to insert to LOG boolean encFileExist, // whther .cpg file exist and you want to use it in order to encode characters String dbip, //the idb ip to connect to String dbport, // the db port String dbname, //the instance name String dbuser, // the user String dbpass // and the passwrod ) throws IOException, Exception{ ShapefileReaderJGeom shpr = new ShapefileReaderJGeom(shploc); DBFReaderJGeom dbfr = new DBFReaderJGeom(shploc); //force to use the charset encoding if encFileExist true if (encFileExist) { dbfr.readCodePage(shploc); } ShapefileFeatureJGeom sf = new ShapefileFeatureJGeom(); double minX = shpr.getMinX(); double minY = shpr.getMinY(); double maxX = shpr.getMaxX(); double maxY = shpr.getMaxY(); double minZ = shpr.getMinZ(); double maxZ = shpr.getMaxZ(); double minM = shpr.getMinMeasure(); double maxM = shpr.getMaxMeasure(); int shapeType = shpr.getShpFileType(); int shpDims = shpr.getShpDims(shapeType,maxM); //these are the metadata String dimArray = sf.getDimArray( shpDims, String.valueOf(tol), String.valueOf(minX), String.valueOf(maxX), String.valueOf(minY), String.valueOf(maxY), minZ, maxZ, minM, maxM ); //create the oracle connection OracleConnection myDbCon = (OracleConnection) DriverManager.getConnection( "jdbc:oracle:thin:@"+ dbip+":"+dbport+":"+dbname, dbuser, dbpass ); //create the table and metadata. If table exist shall be deleted sf.prepareTableForData(myDbCon,dbfr,oraTableName,IdCol,geomColName,Srid,dimArray); //and finally do the insert sf.insertFeatures(myDbCon,dbfr,shpr,oraTableName,IdCol,IdStart,commitFreq,printFreq,Srid,dimArray); //close both shp,dbf files shpr.closeShapefile(); dbfr.closeDBF(); } }

an then you may call you class like so:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

ShapeToSDO2 SHPSDO2 = new ShapeToSDO2( "C:\\myfiles\\shapefilename",//no file extension just the file name "MYNEWTABLENAME",//the table name you want to create and import the shapefile data.If exists it will be deleted and recreated "GEOM",//the geometry name to asign to the table 2100,//the SRID code as Oracle understands it 0.05,//the spatial tolerance "ID",//the Unique identifier column to create 1,//the starting point of the above column 10,//the frequency commit shall take place (every 10 insert staments) 10,//the frequency logging shall take place (every 10 insert staments) true,//if the shapefile has a .cpg file to verify the charset to use for the dbf translation. May be true or false "localhost", //the DB ip "1521",//the DB port "orcl",//the DB instance "USER",//the DB user "PASS"//the DB password );

Oracle Case 2 (using Geotools)
In this case things are a bit complicated. Although there are varius examples on the net, when it comes down to oracle none of them is working. The problem comes because oracle converts column names to capital letters, so when mathching fields between shapefile and DB column names you get errors and your job fails. To overcome the issue create a JAVA class as follows

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

/* * To change this template, choose Tools | Templates * and open the template in the editor. */ package gis.openfreegis.rdbms; import java.io.File; import java.io.IOException; import java.io.Serializable; import java.net.MalformedURLException; import java.nio.charset.Charset; import java.util.Arrays; import java.util.HashMap; import java.util.Map; import org.geotools.data.DataStore; import org.geotools.data.DataUtilities; import org.geotools.data.DefaultTransaction; import org.geotools.data.FeatureStore; import org.geotools.data.Transaction; import org.geotools.data.oracle.OracleNGDataStoreFactory; import org.geotools.data.shapefile.ShapefileDataStore; import org.geotools.feature.FeatureIterator; import org.geotools.feature.simple.SimpleFeatureBuilder; import org.geotools.jdbc.JDBCDataStoreFactory; import org.opengis.feature.simple.SimpleFeature; import org.opengis.feature.simple.SimpleFeatureType; import org.opengis.feature.type.AttributeDescriptor; import org.opengis.filter.Filter; import org.opengis.referencing.FactoryException; public class ShapeToSdo { public ShapeToSdo (String shploc) throws FactoryException, MalformedURLException, IOException { String path = shploc; ShapefileDataStore shp = new ShapefileDataStore(new File(path).toURL()); //THIS IS CRUCIAL FOR GREEK CHARS parsing shp.setCharset(Charset.forName("ISO-8859-7")); //IF NOT GREEK JUST OMMIT IT OR SET IT TO UTF_8 //shp.setCharset(Charset.forName("UTF-8")); Map<Serializable, Object> params = new HashMap<Serializable, Object>(); params.put(JDBCDataStoreFactory.USER.key, "USER"); params.put(JDBCDataStoreFactory.PASSWD.key, "PASS"); params.put(JDBCDataStoreFactory.HOST.key, "localhost"); params.put(JDBCDataStoreFactory.PORT.key, "1521"); params.put(JDBCDataStoreFactory.DATABASE.key, "orcl"); params.put(JDBCDataStoreFactory.DBTYPE.key, "oracle"); DataStore oracle = new OracleNGDataStoreFactory().createDataStore(params); if(oracle != null && oracle.getTypeNames() != null) System.out.println("Oracle connected"); System.out.println("oracle.getTypeNames()"+oracle.getTypeNames()); String typeName = shp.getTypeNames()[0].toUpperCase(); System.out.println("shp.getTypeNames()[0]===="+shp.getTypeNames()[0]); if(!Arrays.asList(oracle.getTypeNames()).contains(typeName)){ System.out.println("shp.getSchema()"+shp.getSchema()); oracle.createSchema(shp.getSchema()); } FeatureStore oraStore = (FeatureStore) oracle.getFeatureSource(typeName); oraStore.removeFeatures(Filter.INCLUDE); SimpleFeatureType targetSchema = (SimpleFeatureType) oraStore.getSchema(); SimpleFeatureBuilder builder = new SimpleFeatureBuilder(targetSchema); FeatureIterator fi = shp.getFeatureSource().getFeatures().features(); SimpleFeatureType sourceSchema = shp.getSchema(); Transaction t = new DefaultTransaction(); oraStore.setTransaction(t); while(fi.hasNext()) { SimpleFeature source = (SimpleFeature) fi.next(); for(AttributeDescriptor ad : sourceSchema.getAttributeDescriptors()) { String attribute = ad.getLocalName(); String passAtrr = attribute.toUpperCase(); builder.set(passAtrr, source.getAttribute(attribute)); } oraStore.addFeatures(DataUtilities.collection(builder.buildFeature(null))); } t.commit(); t.close(); } }

And then you may call it like so:

1	ShapeToSdo SHPSDO = new ShapeToSdo("C:\\myfiles\\myshapefile.shp");

POSTGIS (using Geotools)
First of all you need to include the PostGIS Plugin within your project.
If you are using maven add the following tag within your pom.xml file

<dependency>
  <groupId>org.geotools.jdbc</groupId>
  <artifactId>gt-jdbc-postgis</artifactId>
  <version>13.0</version>
  <type>jar</type>

</dependency>

Then create a java class and name it ShapeToPostGIS as follows:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package gis.openfreegis.rdbms;

import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.net.MalformedURLException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.FeatureSource;
import org.geotools.data.FeatureStore;
import org.geotools.feature.FeatureCollection;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.referencing.CRS;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.ReferenceIdentifier;
import org.opengis.referencing.crs.CoordinateReferenceSystem;

/**
 *
 * @author pt
 */
public class ShapeToPostGIS {
    public ShapeToPostGIS () throws FactoryException, MalformedURLException, IOException, ClassNotFoundException, SQLException {
    String shapeFileLoc = "C:\\various_tests\\myfile.shp"; //location of the shapefile
    String postGISTblName = "MYSHAPETABLE";//name to give to the newly created table 
    String shapeEPSG = "EPSG:2100";//the epsg to use if not found from shpefile
        try {

            // shapefile loader
            Map<Object,Serializable> shapeParams = new HashMap<Object,Serializable>();
            shapeParams.put("url", new File(shapeFileLoc).toURL());
            shapeParams.put( "charset", "ISO-8859-7" );//for greek chars
            DataStore shapeDataStore = DataStoreFinder.getDataStore(shapeParams);       

            // feature type
            String typeName = shapeDataStore.getTypeNames()[0];
            FeatureSource<SimpleFeatureType,SimpleFeature> featSource = shapeDataStore.getFeatureSource(typeName);
            FeatureCollection<SimpleFeatureType,SimpleFeature> featSrcCollection = featSource.getFeatures();
            SimpleFeatureType ft = shapeDataStore.getSchema(typeName);

            // feature type copy to set the new name
            SimpleFeatureTypeBuilder builder = new SimpleFeatureTypeBuilder();
            builder.setName(postGISTblName);
            builder.setAttributes(ft.getAttributeDescriptors());
            builder.setCRS(ft.getCoordinateReferenceSystem());

            SimpleFeatureType newSchema = builder.buildFeatureType();

            // management of the projection system
            CoordinateReferenceSystem crs = ft.getCoordinateReferenceSystem();

            // test of the CRS based on the .prj file
            Integer crsCode = CRS.lookupEpsgCode(crs, true);

            Set<ReferenceIdentifier> refIds = ft.getCoordinateReferenceSystem().getIdentifiers();
            if ( ( (refIds == null) || (refIds.isEmpty() ) ) && (crsCode == null) ) {
                CoordinateReferenceSystem crsEpsg = CRS.decode(shapeEPSG);
                newSchema = SimpleFeatureTypeBuilder.retype(newSchema,crsEpsg);
            }

            Map postGISParams = new HashMap<String,Object>();
            postGISParams.put("dbtype", "postgis");         //must be postgis
            postGISParams.put("host", "localhost");         //the name or ip address of the machine running PostGIS
            postGISParams.put("port",5444);                 //the port that PostGIS is running on (generally 5432)
            postGISParams.put("database", "gisapps");       //the name of the database to connect to.
            postGISParams.put("user", "gis-user");          //the user to connect with
            postGISParams.put("passwd", "gis-user-pwd");    //the password of the user.
            postGISParams.put("schema", "myschema");        //the schema of the database
            postGISParams.put("create spatial index", Boolean.TRUE);
            DataStore dataStore = null;
            try {
            // storage in PostGIS
            dataStore = DataStoreFinder.getDataStore(postGISParams);
            } catch (Exception e){
            System.out.println("problem with datastore:"+e);
            }
           
            if (dataStore == null) {
             System.out.println("ERROR:dataStore is null");
            } 
            
            dataStore.createSchema(newSchema);
            FeatureStore<SimpleFeatureType,SimpleFeature> featStore = (FeatureStore<SimpleFeatureType,SimpleFeature>)dataStore.getFeatureSource(postGISTblName);
            featStore.addFeatures(featSrcCollection);
           

        } catch (IOException e) {
            System.out.println("ERROR:"+e);
        }

       System.out.println("Perfect, works as a charm!!!!!");

}
}

Finally, to call your class just create a new class and call:

ShapeToPostGIS SHPTOPOSTGIS = new ShapeToPostGIS();

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); }