Publishing PostGIS Rasters in GeoServer?

Following the configuration instructions for the PostGIS raster plugin, you should try replacing

<spatialExtension name="postgis"/>

with

<spatialExtension name="pgraster"/>

in configration file mapping.postgis.xml.inc.

I worked through several issues trying to use a postgis raster in geoserver similar to the OP. The below is how I successfully loaded my raster data.

Preconfiguration: A BNG geotiff layer, generated from FME, has been imported into postgis using raster2pgsql. The command used was:

raster2pgsql -s 27700 -I -M -C {geotiffName}.tif -t 100x100 -l 2,4,6 {schema}.{tableName} | psql -U {user} -d {database}

This created the raster table along with its pyramid tables.

Currently my server has an instance of Geoserver 2.6.2. To use Postgis rasters it requires the extension JDBC Image Mosaic which can be downloaded from http://geoserver.org/release/2.6.2/

The extension .jar was then extracted into WEB-INF/lib. This will then enable geoserver to use rasters from postgis. However to access the data several settings need configuring.

Following the instructions in: http://docs.geotools.org/latest/userguide/library/coverage/pgraster.html but slightly tweaked:

create table {schema}.MOSAIC (NAME varchar(254) not null,
     TileTable varchar(254)not null,
     minX FLOAT8,minY FLOAT8, maxX FLOAT8, maxY FLOAT8,resX FLOAT8, resY FLOAT8, primary key (NAME,TileTable))

Now insert records for a "coverage", named oek in the example, these are your raster tables: the master one and pyramid ones.

insert into {schema}.MOSAIC (name, tiletable) values ('oek', {Main raster table name});
insert into {schema}.MOSAIC (name, tiletable) values ('oek', {raster pyramid 2 table name});
insert into {schema}.MOSAIC (name, tiletable) values ('oek', {raster pyramid 4 table name});
insert into {schema}.MOSAIC (name, tiletable) values ('oek', {raster pyramid 6 table name});

Also according to the docs, for Postgis JDBC drivers < version 9 it is necessary to set a property in the database:

ALTER DATABASE dbname SET bytea_output TO 'escape'

Now Geoserver configuration:

Within your Geoserver data directory create a directory /data/coverages, this needs to contain 3 files: connect.pgraster.xml.inc, mapping.pgraster.xml.inc, oek.pgraster.xml

Follow http://docs.geotools.org/latest/userguide/library/coverage/pgraster.html for their naming and contents. Obviously adjust the database connection string and username/password settings in connect.pgraster.xml.inc

mapping.pgraster.xml.inc is where we need to reference the raster tables correctly.

<spatialExtension name="pgraster"/>
 <mapping>
  <masterTable name="MOSAIC" >
          <coverageNameAttribute name="name"/>
          <maxXAttribute name="maxX"/>
          <maxYAttribute name="maxY"/>
          <minXAttribute name="minX"/>
          <minYAttribute name="minY"/>
          <resXAttribute name="resX"/>
          <resYAttribute name="resY"/>
          <tileTableNameAtribute  name="tiletable" />
  </masterTable>
  <tileTable>
          <blobAttributeName name="rast" />
  </tileTable>
</mapping>

So this will get the values we added to the MOSAIC table, the name values are the columns of MOSAIC, which link to our raster table and pyramid tables. rast is the name of the raster column in the raster tables.

In oek.pgraster.xml make sure your SRS is correctly set, 27700 BNG in this case.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE ImageMosaicJDBCConfig [
  <!ENTITY mapping PUBLIC "mapping"  "mapping.pgraster.xml.inc">
  <!ENTITY connect PUBLIC "connect"  "connect.pgraster.xml.inc">
]>

<config version="1.0">
  <coverageName name="oek"/>
  <coordsys name="EPSG:27700"/>
  <!-- interpolation 1 = nearest neighbour, 2 = bipolar, 3 = bicubic -->
  <scaleop  interpolation="1"/>
<axisOrder ignore="false"/>
  &mapping;
  &connect;
</config

In Geoserver we can now add a new datastore.

Go to new Add new store.

Select ImageMosaicJDBC

Give it a name and the set the URL to file:coverages/oek.pgraster.xml

Save this, and fingers crossed no errors are returned. If they are, check geoserver logs which hopefully will indicate what the issue was, hopefully just a typo or naming issue.

Go to add a new layer and select the new raster data store.

Select your new layer/coverage named oek and publish it.

Then in the layer settings adjust your coordinate system and bounding boxes appropriately.

To style the raster follow docs here: http://docs.geoserver.org/latest/en/user/styling/sld-reference/rastersymbolizer.html

I added a style:

<RasterSymbolizer>
 <Opacity>1.0</Opacity>
  <ColorMap>
   <ColorMapEntry color="#ffffff" quantity="1" label="label1" opacity="1"/>
   <ColorMapEntry color="#0000ff" quantity="200" label="label2" opacity="1"/>
  </ColorMap>
</RasterSymbolizer>

This colored my rasters blue where they had a value.

You should then have a usable layer.

Disclaimer: I am no GIS expert, and a lot of what i worked through was trial and error. I have no doubt that there are better ways to do it, but for the time being I'm only working on a proof of concept to confirm if we have the capability to use raster data in postgis. Finer details like raster values and styling are things I still need to iron out. If I make any further progress I will update my answer.