Geotools MultiPolygon to DB and back

Question

I am reading a set of data containing a large number of multypoligons. I am using the Geotools and I would like to store this list in a mysql database table.

I don't know how to store the shape in an efficient way and be able to recreate a multipolygon object.

If I get the coordinates of the multipolygon, then I get an array containing the values of all the vertices for this geometry (In the case the geometry is a composite, the array contains all the vertices for the components, in the order in which the components occur in the geometry), but I don't know how to recreate a new multipolygon with those coordinates.

Please find below what I got at.

private List<Shape> parseFile2ShapeList(File file) {

    List<Shape> shapes = new ArrayList<Shape>();
    FileDataStore myData = null;
    SimpleFeatureIterator sfit = null;
    try {
        // Extract all features
        myData = FileDataStoreFinder.getDataStore( file );
        SimpleFeatureSource source = myData.getFeatureSource();
        SimpleFeatureCollection sfc = source.getFeatures();
        sfit = sfc.features();

        // Read the features and store in a list only the ones with Venue_ID
        while (sfit.hasNext()) {
            SimpleFeature feature = sfit.next();
            String id = (String) feature.getAttribute("ID");
            MultiPolygon mulPoly = (MultiPolygon) feature.getAttribute("the_geom");
            Shape shape = new Shape(id, mulPoly);
            shapes.add(shape);
        }  
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        sfit.close();
        myData.dispose();
    }
    return shapes;
}

private boolean insertShapes(List<Shape> shapes) {
    // Insert the shapes in the DB
    boolean inserted = false;
    try (Database db = new Database()) {
        // Store in the DB all the shapes
        for (Shape shape : shapes) {
            db.getShapesDao().insertShape(shape); // What shall I store in the DB if the shape is a multipolygon? What if it is any Geometry?
        }   
        inserted = true;
    } catch (SQLException e) {
        e.printStackTrace();
        inserted = false;
    } catch (IOException e) {
        e.printStackTrace();
        inserted = false;
    }
    return inserted;
}

private Shape selectShape(String shape_id) {
    Shape shape = null;
    try (Database db = new Database()) {
        // Retrieve the shape
        shape = db.getShapesDao().getShapeById(shape_id); // How do I recreate a multipolygon (or any other Geometry inserted in the DB?)
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return shape;
}

public void main() {

    // display a data store file chooser dialog for shapefiles
    File file = JFileDataStoreChooser.showOpenFile("shp", null);
    if (file == null) {
        return;
    }

    // Read the file and load in memory the venues
    List<Shape> shapes = parseFile2ShapeList(file);
    System.out.println("Shapes parsed: " + retrieved.size());
    for (Shape shape : shapes) {
        System.out.println("\t ID: " + shape.getId() ); 
    }

    // Insert in database
    boolean inserted = insertVenues(venues);
    System.out.println("Insertion successful? " + inserted);

    // Retrieve from database
    List<Shape> retrievedShapes = new ArrayList<Shape>();
    for (Shape shape : shapes) {
        Shape retrieved = selectShape(shape.getId());
        retrievedShapes.add(retrieved);
    }
    System.out.println("Shapes retrieved: " + retrieved.size());
    for (Shape shape : retrievedShapes) {
        System.out.println("\t ID: " + shape.getId() ); 
    }
}

At the moment from a multipolygon I know how to get a polygon back (because I simply store the coordinates and I use them to create a polygon), but I don't know what to do to store and retrieve a multipolygon. In general, the optimal solution would work for any Geometry:

parse geometry obj -> store geometry to DB (with associated ID) -> (some time later...) -> retrieve geometry information (by ID) -> construct new geometry obj

The new object will be a copy of the original one, which is not available anymore.

PS: Serialising a Java object and store it in the DB is something that I would not want to do as I would like to have in the DB something somehow human readable. :)

---- edit

I am adding more information following the answer I received (thank you @user2731872!).

The database table I would like to store things in has the following schema:

mysql> describe shape_table;
+-----------------+----------------------+------+-----+---------+----------------+
| Field           | Type                 | Null | Key | Default | Extra          |
+-----------------+----------------------+------+-----+---------+----------------+
| id              | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| shape_id        | varchar(32)          | NO   |     | NULL    |                |
| shape           | text                 | NO   |     | NULL    |                |
+-----------------+----------------------+------+-----+---------+----------------+

There are other columns to this table, but for the moment they are not relevant. In any case, given the input file, I would like to store every geometry (shape column) in a different row.

In my program, given a shape_id I would like to retrieve the associated information about the shape (shape column) and then construct the geometry.

Answer 1

(not tested) Geotools has an MySQL Data Store: http://docs.geotools.org/latest/javadocs/org/geotools/data/mysql/MySQLDataStoreFactory.html

also documetned here: http://docs.geotools.org/latest/userguide/library/jdbc/mysql.html

You should be able to take the FeatureCollection you read from SHP and push it to the new datastore. Thus,

SimpleFeatureCollection sfc 

is what you want to pass to the SimpleFeatureSource source that you define for this datastore. Something like

DataUtilities.source( collection );

as per the user guide: http://docs.geotools.org/latest/userguide/library/main/collection.html

Hope this helps.

Answer 2

The solution that I found is to use WKTWriter and WKTReader classes. This is what I wrote in my DAO class:

static private String INSERT_SHAPE = "INSERT INTO shape_table(shape_id, shape) VALUES (?, ?)";

static private String GET_SHAPE_BY_ID = "SELECT shape FROM shape_table WHERE shape_id = ?";


public void insertShape(String shape_id, Geometry shape) throws SQLException {
    try (PreparedStatement s = connection.prepareStatement(INSERT_SHAPE)) {
        s.setString(1, shape_id);
        WKTWriter writer = new WKTWriter();
        String wkt = writer.write(shape);
        s.setString(2, wkt);
        s.executeUpdate();
    }
}

public Geometry getShapeById(String shape_id) throws SQLException, ParseException {
    String wkt = null;
    Geometry shape = null;
    WKTReader reader = new WKTReader();
    try (PreparedStatement s = connection.prepareStatement(GET_SHAPE_BY_ID)) {
        s.setString(1, shape_id);
        ResultSet rs = s.executeQuery();
        if (rs.next()) {
            wkt = rs.getString("shape");
            shape = reader.read(wkt);
        }
        return shape;
    }
}

This solution satisfies me, as in the DB table I can see the whole geometry and store the rest of the information in a table that is already created in a specific way. Of course I am interested in hearing your comments.

Answer 3

You are working at too low a level and thus are making it hard on yourself. GeoTools is designed (for most users/uses) to work with DataStores which abstract the details away and features which handle geometry and attributes for you. So your problem breaks in to two parts - 1st read in the shapefile and 2nd write the features to the database. You have succeeded with the first step, the second is a bit trickier but fairly easy.

Get a connection to the DB (I've used PostGIS as I have it installed but MySql should work the same way):

params.put("user", "geotools");
params.put("passwd", "geotools");
params.put("port", "5432");
params.put("host", "127.0.0.1");
params.put("database", "geotools");
params.put("dbtype", "postgis");
dataStore = DataStoreFinder.getDataStore(params);

Then send the features to the dataSource:

SimpleFeatureSource featureSource = dataStore
.getFeatureSource(schema.getName().getLocalPart());
if (featureSource instanceof SimpleFeatureStore) {
    SimpleFeatureStore featureStore = (SimpleFeatureStore) featureSource;
    featureStore.setTransaction(transaction);
    try {
        List < FeatureId > ids = featureStore.addFeatures(features);
        transaction.commit();
    } catch (Exception problem) {
        problem.printStackTrace();
        transaction.rollback();
    } finally {
        transaction.close();
    }
    dataStore.dispose();
    return true;
} else {
    dataStore.dispose();
    System.err.println("Database not writable");
    return false;
}

There is a bit of messing about the handle creation of new tables if needed and such like which you can see in the full code, which creates a new table if needed or appends to the existing one.