Creating a shapefile with SQL Query Using Geotools Hibernate and Java

Question

Here is my context :

1] I have geometries in an postgresql database

2] My application uses JAVA/Hibernate

3] I Currently use Geotools to create a shapefile

How I do it ?

First i retrieve my results:

public List<Map<String, Object>> getObjectsToShape(String strQuery) throws BusinessException {

    Query query = entityManager.createNativeQuery(strQuery);

    org.hibernate.Query hbQuery = ((org.hibernate.jpa.HibernateQuery) query).getHibernateQuery();
    hbQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
    List<Map<String, Object>> resultList = hbQuery.list();


    return resultList;
}

And example of a query :

    SELECT car.id AS ID,  \
       car.name as NAME, \
       car.creationdate as CR_DATE, \
       concat(car.id, car.name) as EXT_NAME, \
       car.notepercentage as NOTE_PER, \
       car.codepercentage as CODE_PER, \
       (select incidence from technicalmeasures where iddb=(select 
               min(iddb) from technicalmeasures 
               where technicalmeasures.car_iddb=car.iddb)) as INCID_ANG, \
       (select rotation from technicalmeasures where iddb=(select min(iddb) from technicalmeasures where technicalmeasures.car_iddb=car.iddb)) as ROTATION, \
       (select center from tiysd where iddb=(select min(iddb) from tiysd where tiysd.car_iddb=car.iddb))   as CENTER, \
       car.quality as QUALITY, \
       0.5 as MARK, \
       ST_AsText(car.shape) as shape  \
       from car  

The query is more likely to be a view, many columns of other tables are selected, joinings, and static values.. The query itself is mandatory. The map's key is the alias of the column name. and the value is the actual data.

the results contain different types :

• The Geometry of course
• Floats
• Strings

Second, I create my shapefile using "gt-shapefile" library of Geotools

// we only need one row to create fields of the shapefile
Map<String, Object> mapObject = objectList.get(0);

Set<String> keys = mapObject.keySet();

for (String columnName : columnNames) {
   try {
       wktReader.read(String.valueOf(mapObject.get(columnName)));
   } catch (ParseException exc) {
       builder.length(100).add(columnName.toUpperCase(),String.class); // first point i can not seem to retrieve the exact type because i don't know it, and can not find any solution without being too specific 
   }
}

Then fill the shapefile :

      // Build feature
  SimpleFeatureType featureSchema = builder.buildFeatureType();
  // Feature Collection
  DefaultFeatureCollection featureCollection = new DefaultFeatureCollection();

  for (Map<String, Object> row : objectList) {

      SimpleFeatureBuilder simpleFeatureBuilder = new SimpleFeatureBuilder(featureSchema);

      for (String key : row.keySet()) {

          String value = String.valueOf(row.get(key)).trim();

          // Test if value starts with polygon on Multipolygon prefix
          if (value.startsWith("POLYGON") || value.startsWith("MULTIPOLYGON")) {
              wktReader.read(String.valueOf(value));
              simpleFeatureBuilder.set("the_geom", wktReader.read(String.valueOf(value)));
          } else {
              simpleFeatureBuilder.set(key.toUpperCase(), value);
          }

      }

      SimpleFeature simpleFeature = simpleFeatureBuilder.buildFeature(null);

      featureCollection.add(simpleFeature);

  }

My issues

• This is not a satisfying method, I have searched for almost a year to reproduce the exact same work of "pgsql2shp". I looked deeper into Geotools libraries like gt-jdbc.. but again could not find any working example. Is it possible to reproduce the exact work of pgsql2shp in Java ? with or Without Geotools ? if not, is it possible to not use hibernate and use only internal geotools librairies (a working example is very welcome)

• In case of keeping hibernate. Do you know if it is possible to keep the column order from the sqlQuery ?

Answer 1

The simple way to do this is to use the gt-jdbc-postgis module to fetch your features from the database:

    Map<String, Object> params = new HashMap<>();
    params.put("dbtype", "postgis");
    params.put("host", "localhost");
    params.put("port", 5432);
    params.put("schema", "public");
    params.put("database", "database");
    params.put("user", "postgres");
    params.put("passwd", "postgres");

    DataStore dataStore = DataStoreFinder.getDataStore(params); 

    String inputTypeName = "MyTable";
    SimpleFeatureType inputType = inputDataStore.getSchema(inputTypeName);

    FeatureSource<SimpleFeatureType, SimpleFeature> source = 
         inputDataStore.getFeatureSource(inputTypeName);

You then need to create a shapefile (using gt-shapefile) based on those features. You need the specialist dumper to change the attribute names to under 10 chars and call the geometry column the_geom:

    ShapefileDumper dumper = new ShapefileDumper(new File("./target/demo"));
    // optional set a target charset (ISO-8859-1 is the default)
    dumper.setCharset(Charset.forName("ISO-8859-15"));
    // split when shp or dbf reaches 100MB
    int maxSize = 100 * 1024 * 1024;
    dumper.setMaxDbfSize(maxSize);

    // actually dump data
    SimpleFeatureCollection fc = source.getFeatures();
    dumper.dump(fc);

Or if you went with a modern format like GeoPackage you could simply, write out the features:

    Map params = new HashMap();
    params.put("dbtype", "geopkg");
    params.put("database", "test.gkpg");

    DataStore nDatastore = DataStoreFinder.getDataStore(params);
    nDatastore.createSchema(inputType);
    SimpleFeatureStore fs = (SimpleFeatureStore)nDatastore.getFeatureSource(typeName);
    featureStore.addFeatures(fc);
    featureStore.dispose();