How to insert raster in PostGIS from java JDBC

Question

I have been looking for a solution to store dynamically generated raster data into PostGIS from java and PostGIS JDBC.

I am working with geotools toolkit but i am unable to find any documentation that fit my needs.

Here is what I've tried:

I am currently importing com.vividsolutions.jts.geom.GeometryCollection using java jdbc and preparedStatement like so:

PreparedStatement p = 
 conn.prepareStatement("INSERT INTO myGeometryTable (geom) VALUES (?)");
p.setObject(1, (PGgeometry) convertToPGgeometry(geometryCollection));
p.executeQuery();

So I though it would be the same for raster, but there is no PGraster type. My raster object is a GridCoverageLayer that implement RasterLayer.

I got a simple table created with this sql command

CREATE TABLE myRasterTable ("rid" serial PRIMARY KEY,"rast" raster);
PreparedStatement p = 
 conn.prepareStatement("INSERT INTO myRasterTable (rast) VALUES (?)");
-- PGraster DOES NOT EXIST
p.setObject(1, (PGraster) myRasterType);
p.executeQuery();

Almost every solution to store raster in postgis involve using raster2pgsql. raster2pgsql create a sql file that serialize a raster image (geotiff in my case). The output is like so:

INSERT INTO "public"."test" ("rast") VALUES ('01000001000000F ... I think it is Hex format '::raster);
CREATE INDEX "test_rast_gist" ON "public"."test" USING gist (st_convexhull("rast"));

I don't know if I can do the same using java

• Ian Turton: GeoTools doesn't (currently) support storing rasters in PostGIS (and also doesn't recommend storing Geometries that way either)

Answer 1

You need to translate your raster into PostGIS compatible WKB Raster data.

We wrote an ImageIO driver that translate RenderedImage into WKB raster InputStream.

That driver is part of a raster storage and processing support on embedded java database H2.

This is a work in progress feature, we hope to merge that in the H2 database master branch in some times.

You can find this driver here (MPL 2.0,EPL 1.0 license):

https://github.com/nicolas-f/h2database/tree/georaster_patch/h2/src/main/org/h2/util/imageio/

You can find a sample of transfering an image through JDBC here:

https://github.com/nicolas-f/h2database/blob/georaster_patch/h2/src/test/org/h2/test/db/TestGeoRaster.java#L802

It is working the same way with PostGIS.

I will update this answer as soon as all will be merged (if Thomas Mueller agree to merge)