[Geotools-gt2-users] converting a shapefile to MySQL

[Mike H. \(SBC\) <mik...@sw...>]

All,

I am trying to read in a shapefile and write out the features to a MySQL
table that i have already created. I am usingthe following code sample, but
i get the exception shown below. Is there some sort of size limit with the
geometry column in MySQL that i have exceeded? or is there a problem with my
code. I thought it might be related to the number of features so i tried
iterating through the collection and doing a commit after each call to
addFeatures(), but i think this problem is more related to the geometry data
itself.

If you are know of a better or more correct way to perform this conversion
then please let me know.

Thanks
Mike


            URL myURL = new URL("file:///shapefiles/timeznp020.shp");
            ShapefileDataStore store = new ShapefileDataStore(myURL);
		FeatureSource source = store.getFeatureSource(name);
            FeatureResults fsShape = source.getFeatures();
            // get the schema definition
            FeatureType ft = source.getSchema();
            collection = fsShape.collection();

    		// datastore creation
    		MySQLDataStoreFactory factory = new MySQLDataStoreFactory();
    		Map params = new HashMap();
    		params.put( "database", "junkdb" );
    		params.put( "dbtype", "mysql");
    		params.put( "host", "localhost");
    		params.put( "port", "3306");
    		params.put( "user", "mike");
    		params.put( "passwd", "password");

    		MySQLDataStore datastore = (MySQLDataStore) factory.createDataStore(
params );
    		FeatureStore newFeatureStore =
(FeatureStore)(datastore.getFeatureSource("timeznp020"));
     		FeatureReader aReader = DataUtilities.reader(collection);
    		newFeatureStore.addFeatures(aReader);


  --------------------------------------------------------------------------
-------------------------------

2837 features found in shapefile.
Jun 19, 2005 8:24:30 PM org.geotools.data.jdbc.JDBCTextFeatureWriter
doInsert
SEVERE: SQL Exception writing geometry columnPacket for query is too large
(3111857 > 1048576)
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (3111857
> 1048576)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1554)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1540)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1005)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
	at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:1984)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1248)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1194)
	at
org.geotools.data.jdbc.JDBCTextFeatureWriter.doInsert(JDBCTextFeatureWriter.
java:94)
	at
org.geotools.data.jdbc.JDBCFeatureWriter.write(JDBCFeatureWriter.java:219)
	at
org.geotools.data.InProcessLockingManager$1.write(InProcessLockingManager.ja
va:330)
	at
org.geotools.data.jdbc.JDBCFeatureStore.addFeatures(JDBCFeatureStore.java:33
5)
	at ShapeReader2Mysql.main(ShapeReader2Mysql.java:193)
Jun 19, 2005 8:24:30 PM org.geotools.data.jdbc.JDBCFeatureWriter close
WARNING: Feature writer calling close when queryData is  already closed
Ops! Something went wrong :-(
org.geotools.data.DataSourceException: SQL Exception writing geometry
columnPacket for query is too large (3111857 > 1048576)
	at
org.geotools.data.jdbc.JDBCTextFeatureWriter.doInsert(JDBCTextFeatureWriter.
java:107)
	at
org.geotools.data.jdbc.JDBCFeatureWriter.write(JDBCFeatureWriter.java:219)
	at
org.geotools.data.InProcessLockingManager$1.write(InProcessLockingManager.ja
va:330)
	at
org.geotools.data.jdbc.JDBCFeatureStore.addFeatures(JDBCFeatureStore.java:33
5)
	at ShapeReader2Mysql.main(ShapeReader2Mysql.java:193)
Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too
large (3111857 > 1048576)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1554)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:1540)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1005)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1109)
	at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1070)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:2027)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:1984)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1248)
	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1194)
	at
org.geotools.data.jdbc.JDBCTextFeatureWriter.doInsert(JDBCTextFeatureWriter.
java:94)
	... 4 more