Hey Andrea, That's a very nifty example.
When I did this previously, I had my class doing SELECT statements from one DB, storing the data as java objects and then inserting it into another DB table. It was so messy. ;) Derrick Wong Software Engineer | ASRDC (Australian Spatial Research Data Commons) Project | CSIRO Phone: +61 8 6436 8945 [email protected] | www.csiro.au Address: ARRC (Australian Resources Research Centre), 26 Dick Perry Avenue, Kensington WA 6151, Australia PLEASE NOTE The information contained in this email may be confidential or privileged. Any unauthorised use or disclosure is prohibited. If you have received this email in error, please delete it immediately and notify the sender by return email. Thank you. To the extent permitted by law, CSIRO does not represent, warrant and/or guarantee that the integrity of this communication has been maintained or that the communication is free of errors, virus, interception or interference. Please consider the environment before printing this email. -----Original Message----- From: Andrea Aime [mailto:[email protected]] Sent: Monday, 21 June 2010 6:16 PM To: Tey, Victor (CESRE, Kensington) Cc: [email protected] Subject: Re: [Geotools-devel] postgis st_asbinary(shape) v...@csiro ha scritto: > Hi, can someone kindly advice me on this. > > I have a geometry column on postgis and would like to move that data into > oracle spatial. > > Can someone advice the best/easiest way of doing this? > > I have tried moving data out of postgis as wkt and insert into oracle > sdo_geometry(wkt) but some of the wkt exceed 4000 characters hence it failed > as it exceeds the limit allowed. I normally use GeoTools to import from shapefile to Oracle so that I dont' have to recompile OGR every time with the Oracle support (on Linux). Here is the class I use, I guess you can easily adapt it to become a postgis -> oracle transformation tool: import java.io.File; import java.io.IOException; import java.io.Serializable; 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; public class OracleImporter { public static void main(String[] args) throws IOException { String path = "/home/aaime/devel/gs2.0.x/data/release/data/shapefiles/states.shp"; ShapefileDataStore shp = new ShapefileDataStore(new File(path).toURL()); Map<Serializable, Object> params = new HashMap<Serializable, Object>(); params.put(JDBCDataStoreFactory.USER.key, "usr"); params.put(JDBCDataStoreFactory.PASSWD.key, "pwd"); params.put(JDBCDataStoreFactory.HOST.key, "localhost"); params.put(JDBCDataStoreFactory.PORT.key, OracleNGDataStoreFactory.PORT.sample); params.put(JDBCDataStoreFactory.DATABASE.key, "xe"); params.put(JDBCDataStoreFactory.DBTYPE.key, "oracle"); DataStore oracle = new OracleNGDataStoreFactory().createDataStore(params); if(oracle != null && oracle.getTypeNames() != null) System.out.println("Oracle connected"); String typeName = shp.getTypeNames()[0].toUpperCase(); if(!Arrays.asList(oracle.getTypeNames()).contains(typeName)) 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(); builder.set(attribute.toUpperCase(), source.getAttribute(attribute)); } oraStore.addFeatures(DataUtilities.collection(builder.buildFeature(null))); } t.commit(); t.close(); } } -- Andrea Aime OpenGeo - http://opengeo.org Expert service straight from the developers. ------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel ------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ Geotools-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/geotools-devel
