6

Which steps should be taken to build the spatialindex table inside PostgreSQL when using a JNDI connection?

Is the table built by GeoNetwork or does it need to be created manually?


What I have tried so far:

For building the spatialindex table in GeoNetwork (3.0.6) in the Postgres database as opposed to using a shapefile internally, I switched database connection to JNDI, as recommended in the advanced configuration docs.

Creating JNDI connection first

To do so, I had to change the database type used in srv.xml to <import resource="../config-db/jndi-postgres-postgis.xml"/> and configure my datasource in context.xml like so:

<Resource 
        name="jdbc/geonetwork"
        type="javax.sql.DataSource" 
        auth="Container" 
        driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://localhost:5432/geonetwork" 
        username="geonetwork_user" 
        password="" 
        maxActive="20" 
        maxIdle="20" 
        maxWait="200"
        initialSize="20"
        removeAbandoned="true" 
        removeAbandonedTimeout="120" 
        logAbandoned="true" 
        testOnBorrow="true" 
        defaultReadOnly="false" 
        defaultAutoCommit="false" 
        validationQuery="SELECT 1" 
        timeBetweenEvictionRunsMillis="10000"
        minEvictableIdleTimeMillis="1800000" 
        testWhileIdle="true" 
        numTestsPerEvictionRun="3" 
        poolPreparedStatements="true" 
        maxOpenPreparedStatements="1200" 
        provideDataStore="true"  --> tried this to build spatialindex table
/>

I added the provideDataStore parameter according to this section. Actually it looks like it must go into the /config-db/jndi-postgres-postgis.xml file, but this is rejected by the application.

I adapted some configuration in the /config-db/jndi-postgres-postgis.xml file as mentioned here:

 <bean id="jdbcDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
        <property name="jndiName" value="java:/comp/env/jdbc/geonetwork"/>
        <property name="cache" value="true"/>
        <property name="exposeAccessContext" value="false"/>
 </bean>
 <bean id="jpaVendorAdapterDatabaseParam" class="java.lang.String">
        <constructor-arg value="POSTGRESQL"/>
 </bean>

With this configuration, I was able to switch the database connection to JNDI.

Build spatialindex into Postgres

But nevertheless, it does not build the spatialindex into Postgres.

I tried:

  • Restarting tomcat
  • Harvesting new datasets
  • Playing around with the provideDataStore parameter
  • Creating the table spatialindex myself to make GeoNetwork use it on restart

The table is defined according to the definition from the 2.10 branch's create-db-postgis.sql file:

CREATE TABLE spatialindex
 (
    fid int not null,
    id  varchar(250),
    primary key(fid)
  );
CREATE INDEX spatialindexNDX1 ON spatialindex(id);
SELECT AddGeometryColumn('spatialindex', 'the_geom', 4326, 'MULTIPOLYGON', 2 );
CREATE INDEX spatialindexNDX2 on spatialindex USING GIST(the_geom);

But my spatialindex.shp file in the data directory still gets altered instead of the database table.

Is there anything else neccessary to make GeoNetwork build the spatialindex table in Postgres?

1

As a workaround I figured out how to make GeoNetwork create the spatialindex table in PostGIS (not using JNDI). This approach is taken from here.

  • In srv.xml choose postgres.xml as database configuration
  • Configure postgres.xml to contain this:
<import resource="defaultJdbcDataSource.xml" />
<import resource="postgis-alternate-datasource.xml" />

<bean id="jpaVendorAdapterDatabaseParam" class="java.lang.String">
    <constructor-arg value="POSTGRESQL"/>
</bean>

<bean id="jdbcDriverClassName" class="java.lang.String">
    <constructor-arg value="org.postgis.DriverWrapper"/>
</bean>

<bean id="jdbcURL" class="java.lang.String">
    <constructor-arg value="jdbc:postgresql_postGIS://${jdbc.host}:${jdbc.port}/${jdbc.database}"/>
</bean>
  • Configure jdbc.properties accordingly.

On next GeoNetwork restart, it will automatically create the spatialindex table. Then, I triggered a new indexing from the admin interface to populate the table.

However, this approach is not optimal from my perspective as it is said to be deprecated in the 2.10 docs already and wants the database configuration inside the application files, as opposite to the JNDI configuration.

0

I also didn't figure out how to do it with JNDI, but wanted to document my current approach. First, here is the approach with JNDI, which does not lead to success in the current Geonetwork versions 3.6 and 3.7.

JNDI (no success)

Ensure you have a JNDI resource configuration in the Tomcat servlet container connecting to your Postgres/Postgis database:

docker-entrypoint.sh

...
        echo '
<Context>
  <Resource name="jdbc/geonetwork"
   auth="Container"
   type="javax.sql.DataSource"
   username="'${POSTGRES_DB_USERNAME}'"
   password="'${POSTGRES_DB_PASSWORD}'"
   factory="org.apache.commons.dbcp.BasicDataSourceFactory"
   driverClassName="org.postgis.DriverWrapper"
   url="jdbc:postgresql_postGIS://'${POSTGRES_DB_HOST}':5432/'${db_gn}'"
   maxActive="66"
   maxIdle="12"
   minIdle="12"
   removeAbandoned="true"
   removeAbandonedTimeout="3600"
   logAbandoned="true"
   testOnBorrow="true"
   defaultAutoCommit="false"
   validationQuery="SELECT 1"
   defaultTransactionIsolation="2"
   accessToUnderlyingConnectionAllowed="true"
   poolPreparedStatements="true"
   maxOpenPreparedStatements="1200"
  />
</Context>' > "${CATALINA_HOME}"/conf/context.xml
...

Comment out the default database configuration and enable the pre-built JNDI database configuration:

sed -i -e 's|<import resource="../config-db/h2.xml"/>|<!--<import resource="../config-db/h2.xml"/> -->|g' ./geonetwork/WEB-INF/config-node/srv.xml
sed -i -e 's|<!--<import resource="../config-db/jndi-postgres-postgis.xml"/> -->|<import resource="../config-db/jndi-postgres-postgis.xml"/>|g' ./geonetwork/WEB-INF/config-node/srv.xml

If Tomcat boots normally with Geonetwork, JNDI (which uses JDBC) will run.

Now the question posed by the questioner is where to set the parameter provideDataStore to true so that Geonetwork no longer uses the shapefile to store spatial information (bounding boxes etc.) I have not made the following approach (which works under Geonetwork 2.10.4) work.

docker-entrypoint.sh

        # Not working 1 (no effect):
        sed -i -e 's|</default>|</default>\
  <resources>\
    <resource enabled="true">\
      <name>main-db</name>\
      <provider>jeeves.resources.dbms.JNDIPool</provider>\
      <config>\
        <context>java:/comp/env</context>\
        <resourceName>jdbc/geonetwork</resourceName>\
        <url>jdbc:postgresql_postGIS://'${POSTGRES_DB_HOST}':5432/'${db_gn}'</url>\
        <provideDataStore>true</provideDataStore>\
      </config>\
    </resource>\
  </resources>|' "${CATALINA_HOME}"/webapps/geonetwork/WEB-INF/config.xml

        # Not working 2 (error):
#       sed -i -e 's|<property name="exposeAccessContext" value="false"/>|<property name="exposeAccessContext" value="false"/>\
#  <property name="provideDataStore" value="true"/>|' "${CATALINA_HOME}"/webapps/geonetwork/WEB-INF/config-db/jndi-postgres-postgis.xml

JDBC only (works out-of-the-box)

Comment out the default database configuration and enable the pre-built JDBC database configuration:

sed -i -e 's|<import resource="../config-db/h2.xml"/>|<!--<import resource="../config-db/h2.xml"/> -->|g' ./geonetwork/WEB-INF/config-node/srv.xml
sed -i -e 's|<!--<import resource="../config-db/postgres-postgis.xml"/> -->|<import resource="../config-db/postgres-postgis.xml"/>|g' ./geonetwork/WEB-INF/config-node/srv.xml

jdbc.properties (increased maxActive and increased maxOpenPreparedStatements)

jdbc.basic.removeAbandoned=true
jdbc.basic.removeAbandonedTimeout=120
jdbc.basic.logAbandoned=true
jdbc.basic.maxActive=64
jdbc.basic.maxIdle=${jdbc.basic.maxActive}
jdbc.basic.initialSize=${jdbc.basic.maxActive}
jdbc.basic.maxWait=200
jdbc.basic.testOnBorrow=true
jdbc.basic.timeBetweenEvictionRunsMillis=10000
jdbc.basic.minEvictableIdleTimeMillis=1800000
jdbc.basic.testWhileIdle=true
jdbc.basic.numTestsPerEvictionRun=3
jdbc.basic.poolPreparedStatements=true
jdbc.basic.maxOpenPreparedStatements=1200
jdbc.basic.validationQuery=SELECT 1
jdbc.basic.defaultReadOnly=false
jdbc.basic.defaultAutoCommit=false

Use standard docker-entrypoint.sh (needs Docker) via https://github.com/geonetwork/docker-geonetwork and maybe see my pull request.

After starting, a table called spatialindex should appear in the geonetwork database. Via harvesting or via the settings tools, the index can be regenerated and the table should be filled.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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