GeoTools : How to add features data into MySQL

This example shows how to add new feature data into a MySQL database.

First you have to create the mockup database and then the roads table with the following script

# Host: localhost
# Database: mokup
# Table: 'roads'
# 
CREATE TABLE `roads` (
  `width` int(8) default NULL,
  `the_geom` linestring default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

Then you can access and update the database by the following code

		//feature : type creation
		AttributeType roadWidth = AttributeTypeFactory.newAttributeType(
				"width", Integer.class);
		AttributeType geom = AttributeTypeFactory.newAttributeType("the_geom",
				LineString.class);
		FeatureType ftRoad = FeatureTypeFactory.newFeatureType(
				new AttributeType[] { roadWidth, geom  }, "roads");

		// feature : instance creation
		WKTReader wktReader = new WKTReader();
		LineString geometry = (LineString) wktReader
				.read("LINESTRING (0 0, 10 10)");
		Float width = new Float(10);
		Feature theRoad = ftRoad.create(new Object[] { width,geometry },
				"myRoad"); // the order must be compliant with the one in MySQL

		// datastore creation
		MySQLDataStoreFactory factory = new MySQLDataStoreFactory();
		Map params = new HashMap();
		params.put( "database", "mockup" );
		params.put( "dbtype", "mysql");
		params.put( "host", "localhost");
		params.put( "port", "3307");
		params.put( "user", "mockuplog");
		params.put( "passwd", "mockuppass");
		
		MySQLDataStore datastore = (MySQLDataStore) factory.createDataStore( params );
		LOGGER.info("created new datastore"); 
		//road table creation
		//datastore.createSchema(ftRoad); // Not yet implemented!!! but should be the way to do it.So create the table before
		
		FeatureStore fsRoads = (FeatureStore)(datastore.getFeatureSource("roads"));
		FeatureReader aReader = DataUtilities.reader(new Feature[] { theRoad });
		fsRoads.addFeatures(aReader);
//it is all folk!!!

Comments:

If you get a:

Exception in thread "main" java.lang.ClassCastException: org.geotools.data.jdbc.JDBCFeatureSource

the problem may not be with your code, the problem is with the SQL table definition:

CREATE TABLE roads (
   fid varchar(255) PRIMARY KEY,
   the_geom GEOMETRY
);
Posted by mores at Jul 16, 2007 14:55

Here's an updated version of the original example that seems to work with GeoTools 2.6. It is no longer necessary to create the table separately as this is now done programmatically. The code:

try {
    //feature : type creation
    AttributeDescriptor roadWidth = AttributeTypeFactory.newAttributeType("width",Integer.class);
    AttributeDescriptor geom = AttributeTypeFactory.newAttributeType("the_geom",LineString.class);
    SimpleFeatureType ftRoad = FeatureTypes.newFeatureType(new AttributeDescriptor[] { roadWidth, geom  }, "roads");
    
    // feature : instance creation
    WKTReader wktReader = new WKTReader();
    LineString geometry = (LineString) wktReader.read("LINESTRING (0 0, 10 10)");
    Float width = new Float(10);
    
    SimpleFeatureBuilder sfBuilder = new SimpleFeatureBuilder(ftRoad);
    sfBuilder.addAll(new Object[] { width,geometry });// the order must be compliant with the one in MySQL
    SimpleFeature theRoad = sfBuilder.buildFeature("myRoad");

    // datastore creation
    MySQLDataStoreFactory factory = new MySQLDataStoreFactory();
    Map<String,String> params = new HashMap<String,String>();
    params.put( "database", "name_of_database" );
    params.put( "dbtype", "mysql");
    params.put( "host", "localhost");
    params.put( "port", "3306");
    params.put( "user", "someuser");
    params.put( "passwd", "somepassword");
    JDBCDataStore datastore = factory.createDataStore(params);
    
    //road table creation
    datastore.createSchema(ftRoad);
    
    //road table population
    ContentFeatureSource fsRoads = datastore.getFeatureSource("roads");
    FeatureReader<SimpleFeatureType,SimpleFeature> aReader = DataUtilities.reader(new SimpleFeature[] { theRoad });
    while(aReader.hasNext()) {
        fsRoads.getFeatures().add(aReader.next());
    }
} catch(Exception e) {
    e.printStackTrace();
}

Posted by joshhansen at Nov 14, 2009 20:59