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();
can you show the outtputs screen shots
ReplyDeleteOutput is just rows within the DB. Do you mean the log????
DeleteThis comment has been removed by the author.
ReplyDeleteproblem on postgis source code - error on this line: "import com.ianturton.cookbook.utilities.GenerateRandomData;"
ReplyDeleteThats correct. The missing class is just creating random data instead of using a real shapefile. Sorry for a(it is just copy/paste from the source provided) I ll try to make a working example and update the post. Thanks for mentioning.
DeleteOk I have updated the post using a working example for the POSTGIS case. If you face any problems let me know. I have tested locally and it works as a charm. Thanks for mentioning.
Delete