2

I have already created a raster layer in the postgres default 'public' schema and published it in GeoServer through the ImageMosaic JDBC. This is working fine but I would like to use a new custom schema in PostgreSQL instead of the public schema.

When setting up the raster using public schema, I did not specify schema in the connect.pgraster.xml.inc file and also did not create a datastore.properties file. This worked fine. See below for the original 3 xml config files :

connect.pgraster.xml.inc:

<connect>
  <!-- value DBCP or JNDI -->
  <dstype value="DBCP"/>
  <!--   <jndiReferenceName value=""/>  -->
  <username value="username" />
  <password value="password" />
  <jdbcUrl value="jdbc:postgresql://<host>:5432/dbname" />
  <driverClassName value="org.postgresql.Driver"/>
  <maxActive value="10"/>
  <maxIdle value="0"/>
</connect>

mapping.pgraster.xml.inc

<!-- possible values: universal,postgis,db2,mysql,oracle -->
<spatialExtension name="pgraster"/>
<mapping>
    <masterTable name="mosaic" >
        <coverageNameAttribute name="name"/>
        <tileTableNameAtribute name="tiletable" />
        <maxXAttribute name="maxx"/>
        <maxYAttribute name="maxy"/>
        <minXAttribute name="minx"/>
        <minYAttribute name="miny"/>
        <resXAttribute name="resx"/>
        <resYAttribute name="resy"/>
    </masterTable>
    <tileTable>
        <blobAttributeName name="rast" />
    </tileTable>
</mapping>

coverage_name.pgraster.xml

<?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="coverage_name"/>
  <coordsys name="EPSG:3857"/>
  <!-- interpolation 1 = nearest neighbour, 2 = bipolar, 3 = bicubic -->
  <scaleop  interpolation="1"/>
<axisOrder ignore="false"/>
  &mapping;
  &connect;
</config>

When attempting to set up the raster layer in a custom schema, I updated the jdbcUrl value in the connect.pgraster.xml.inc file to specify the custom schema name. I did not change the other config files (mapping.pgraster.xml.inc and coverage_name.pgraster.xml). This did not work. I then created a datastore.properties file where I also set the schema to the custom schema name. This also did not work.

Both times i got this error:

Failed to create reader from file:coverages/coverage_name.pgraster.xml and
hints Hints: LENIENT_DATUM_SHIFT = true COMPARISON_TOLERANCE = 1.0E-8
STYLE_FACTORY = StyleFactoryImpl EXECUTOR_SERVICE =
 java.util.concurrent.ThreadPoolExecutor@19268c7[Running, pool size = 0, active
threads = 0, queued tasks = 0, completed tasks = 0]
FORCE_LONGITUDE_FIRST_AXIS_ORDER = true FORCE_AXIS_ORDER_HONORING = http
FILTER_FACTORY = FilterFactoryImpl GRID_COVERAGE_FACTORY = GridCoverageFactory
TILE_ENCODING = null FEATURE_FACTORY =
org.geotools.feature.LenientFeatureFactoryImpl@1b0cd4d

See below for the updated connect.pgraster.xml.inc, the new datastore.properties file, and mosaic table setup. What else do I need to change to enable use of a custom schema?

connect.pgraster.xml.inc:

<connect>
  <!-- value DBCP or JNDI -->
  <dstype value="DBCP"/>
  <!--   <jndiReferenceName value=""/>  -->
  <username value="username" />
  <password value="password" />
  <jdbcUrl value="jdbc:postgresql://<host>:5432/dbname?currentSchema=custom_schema_name" />
  <driverClassName value="org.postgresql.Driver"/>
  <maxActive value="10"/>
  <maxIdle value="0"/>
</connect>

datastore.properties

SPI=org.geotools.data.postgis.PostgisNGDataStoreFactory
host=<host>
port=5432
database=dbname
schema=custom_schema_name
user=username
passwd=password
Loose\ bbox=true
Estimated\ extends=false
validate\ connections=true
Connection\ timeout=10
preparedStatements=true

mosaic table:

name            tiletable            minx  miny  maxx  maxy  resx  resy
coverage_name   raster_table
coverage_name   o_16_raster_table
coverage_name   o_2_raster_table
coverage_name   o_32_raster_table
coverage_name   o_4_raster_table
coverage_name   o_64_raster_table
coverage_name   o_8_raster_table
2
  • Hi, have you find solution for this problem ? Commented Nov 6, 2019 at 16:22
  • No, I was only able to use public schema
    – Chelsey L
    Commented Nov 7, 2019 at 19:42

2 Answers 2

0

I've found a solution. It seems not possible to define specific schema in the xml files. The mosaic table must be on the 'public' schema. But it's possible to set path to raster in an other schema in tiletable column on the mosaic table, like 'my_schema.raster_table', 'my_schema.o_16_raster_table' ...

0

The ImageMosaicJDBC plugin requires GDAL plugin. Please install GDAL plugin and try again. In my case it was possible to use different schema.

Here are my configs, in case someone needs them.

**** ndvi.pgraster.xml ****
<?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="ndvi"/>
  <coordsys name="EPSG:22185"/>
  <!-- interpolation 1 = nearest neighbour, 2 = bipolar, 3 = bicubic -->
  <scaleop  interpolation="1"/>
<axisOrder ignore="false"/>
  &mapping;
  &connect;
</config>

**** mapping.pgraster.xml.inc ****
<spatialExtension name="pgraster"/>
 <mapping>
  <masterTable name="rasterdata.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>

**** connect.pgraster.xml.inc ****
<connect>
  <dstype value="DBCP"/>
  <username value="postgres" />
  <password value="password" />
  <jdbcUrl value="jdbc:postgresql://localhost:5432/gisdb" />
  <driverClassName value="org.postgresql.Driver"/>
  <maxActive value="10"/>
  <maxIdle value="0"/>
  <currentSchema value="public, rasterdata"/>
</connect>

Tested on Centos8; GeoServer 2.18; PostGreSQL 12; PostGIS 3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.