"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

Related pages


How to enable 3D support for your database with GeoServer

Overview

GeoServer app-schema online tests now includes 3D tests. This means you need to make sure your database is 3D ready before running the full app-schema online test suite.

Alternatively, if you are confident enough that you are not breaking the 3D support, you can skip the tests.

Oracle

For Oracle, you need to install SC4O package on Oracle 11g database Release 2. ** This is strictly for the app-schema online tests. Probably not required for deployments. **

I have installed SC40 on my wsrv5 test user (test3) and the Jenkins buildbot user (test4). You can probably just use the SC4O package installed for my user, or install it yourself for your user.

How to install SC4O package on a server:

If you are using wsrv5, there is no need to do this since I've already installed them. See the section on How to install SC4O package for a wsrv5 user or How to use SC4O package from another user instead.

These are the steps I did to install the package on wsrv5:

1. Make sure the user has CREATE PROCEDURE and CREATE TYPE privileges to install the package. The script actually does this by checking the user_sys_privs table:

Select * FROM user_sys_privs

In wsrv5, the users already have the permissions but they are stored in user_role_privs. I then changed the installation script to check for user_role_privs instead.

2. The script wasn't working at the time because of trailing carriage returns. I ran this script to clean the file from the carriage returns and it works:

3. Execute the following command as admin. I had to get IMT to do it for all wsrv5 users:

CALL DBMS_JAVA.GRANT_PERMISSION('test4','java.lang.RuntimePermission','getClassLoader','');

4. The final bump was this error message all over the log:

189/3 PL/SQL: Declaration ignored
189/56 PLS-00201: identifier 'MDSYS.SDO_GEOMETRY_ARRAY' must be declared
195/3 PL/SQL: Declaration ignored
195/53 PLS-00201: identifier 'MDSYS.SDO_GEOMETRY_ARRAY' must be declared
697/3 PL/SQL: Declaration ignored
697/41 PLS-00201: identifier 'MDSYS.SDO_GEOMETRY_ARRAY' must be declared
729/3 PL/SQL: Declaration ignored
729/45 PLS-00201: identifier 'MDSYS.SDO_GEOMETRY_ARRAY' must be declared

I fixed this by adding this on top of SC40_package.sql:

create or replace type sdo_geometry_array as varray (1000000) of mdsys.sdo_geometry; /

I then replaced mdsys.sdo_geometry_array occurrences to sdo_geometry_array (since I don't have admin permissions, I can't create mdsys type).

5. You should be able to run the installation script successfully now.

How to install SC4O package for a wsrv5 user:

1. Log on to the server (wsrv5 in this example).

2. Sudo -i

3. change to /home/SC40/deploy

4. Run ./install.sh with your database username and password.

How to use SC4O package from another user:

In vm args or maven parameter, specify the system property -DSC4OUser=SC4O package owner e.g. if using the SC4O package from test3:
-DSC4OUser=test3

Postgis

For postgis: Below are the steps I did to upgrade jenkins database in apacsrv4 to postgis 2:

1. Stop postgresql service.

service postgresql stop

2. I purged existing postgis, but check that it will affect noone first.

apt-get purge postgresql-8.4-postgis postgis

3. Pre requisites: GEOS 3.3.x and GDAL 1.9.1 (GDAL is only required if you want it raster enabled).

GEOS 3.3.x

wget http://download.osgeo.org/geos/geos-3.3.5.tar.bz2

tar xvfj geos-3.3.5.tar.bz2
cd geos-3.3.5
./configure
make
sudo make install
cd ..

GDAL

wget http://download.osgeo.org/gdal/gdal-1.9.1.tar.gz

tar -zvxf gdal-1.9.1.tar.gz
cd gdal-1.9.1/
./configure
make
make install

4. Build postgis 2.

I followed this info: http://postgis.refractions.net/documentation/manual-2.0/postgis_installation.html#PGInstall

wget http://www.postgis.org/download/postgis-2.0.1SVN.tar.gz
tar -xvzf postgis-2.0.1SVN.tar.gz
cd postgis-2.0.1SVN

**This is important!**

Depending on your postgis version, you may need to edit Version.config.
Check and remove "SVN" suffix from POSTGIS_MICRO_VERSION if it exists.
This was the cause of my grief because Geotools postgis plugin queries the version number, and the "SVN" suffix causes it to fail to compare as an integer to "2.0.0".
This causes the SRID to be null (since the IF condition wasn't met, and the appropriate query to get the SRID wasn't fired).

./configure (by default raster is included, so if you don't have GDAL, this will fail. If you don't want raster, specify --without-raster)
make
make install

5. Start postgres.

service postgresql start

6. Drop the existing database.

su - postgres
psql
drop database jenkins;

7. Recreate database

create database jenkins;

\q

createlang plpgsql jenkins
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/postgis.sql
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/spatial_ref_sys.sql
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/postgis_comments.sql
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/topology.sql
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/topology_comments.sql

Raster specific scripts:

psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/rtpostgis.sql
psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/raster_comments.sql

** This is important! **

The legacy script will restore legacy functions that are deprecated from 1.5. Without this, the data ref tests will fail, because the script uses some of the old functions.

psql -d jenkins -f /usr/share/postgresql/8.4/contrib/postgis-2.0/legacy.sql

8. Set ownerships.

psql
alter database jenkins owner to jenkins;

\c jenkins

alter table geometry_columns owner to jenkins; alter table spatial_ref_sys owner to jenkins; alter view geography_columns owner to jenkins;

9. Set timezone

\q
logout
perl -ni -e 'print unless /^SAT/' /usr/share/postgresql/8.4/timezonesets/Australia

su - postgres
psql
alter database jenkins set timezone_abbreviations to 'Australia';

10. Add 3D wkt.

\c jenkins

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 4979, 'epsg', 4979, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137.0,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0.0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943295],AXIS["Geodetic latitude",NORTH],AXIS["Geodetic longitude",EAST],AXIS["Ellipsoidal height",UP],AUTHORITY["EPSG","4979"]]');

Skipping 3D tests

Alternatively, you can skip the 3D tests in your pom.xml (if you're sure that you're not affecting the 3D code).

Exclude the 3D tests for app-schema-oracle-test and app-schema-postgis-test respectively:
<build>
 <testSourceDirectory>${app-schema-test-dir}/src/test/java</testSourceDirectory>
 <plugins>
     <plugin> 
         <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-surefire-plugin</artifactId>
         <configuration> 
         <!-- These tests are already run on the parent module-->
             <excludes>
                 <exclude>**/onlineTest/*.java</exclude>
                 <exclude>**/BBox3DTest.java</exclude>
                 <exclude>* **/Reprojection3DTest.java</exclude>
             </excludes>
             <argLine>-Xmx${test.maxHeapSize} -XX:MaxPermSize=${test.maxPermSize} -enableassertions ${jvm.opts} -Djava.awt.headless=${java.awt.headless} -Dapp-schema.joining=true -DtestDatabase=oracle</argLine>
         </configuration>
     </plugin>
 </plugins>
Topic revision: r5 - 04 Aug 2016, RiniAngreani
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).