The org.apache.sis.referencing.factory.sql.epsg package in the non-free:sis-epsg module provides SQL scripts for installing a local copy of the EPSG geodetic dataset. This dataset provides definitions for thousands of Coordinate Reference Systems (CRS), together with parameter values for thousands of Coordinate Operations between various pairs of CRS. EPSG is maintained by the International Association of Oil and Gas Producers (IOGP) Surveying & Positioning Committee and is subject to EPSG terms of use. Because of incompatibilities between EPSG terms of use and Apache 2 license, the EPSG geodetic dataset is not distributed by default with Apache SIS. A copy of the dataset is provided in a separated module in a separated source code repository. The Maven identifier of that module is org.apache.sis.non-free:sis-epsg and the source repository is located at http://svn.apache.org/repos/asf/sis/data/non-free/sis-epsg. The EPSG scripts are copied in that module with identical content, but in a more compact format.

This org.apache.sis.referencing.factory.sql.epsg package in core:sis-referencing module contains only tools for maintaining the non-free:sis-epsg module. This package is provided only in the test directory, not in the main directory, because the org.apache.sis.referencing.factory.sql.epsg package name is reserved by the non-free:sis-epsg module. The core:sis-referencing module should not distribute anything in packages owned by other modules. However it is okay to use those package names in directories that are not part of the distribution, like tests. We put those tools here for easier maintainance when the core of Apache SIS is modified.

How to apply EPSG geodetic dataset updates

This page explains how to convert the SQL scripts published by EPSG into the more compact form used by Apache SIS. This conversion applies only to the data types, the integrity constraints and the way the SQL scripts are written. No data value should be altered. Steps to follow:

  1. Download the latest SQL scripts for PostgreSQL from http://www.epsg.org.

  2. Unzip in the directory of your choice (replace "9_0" by the appropriate version number in the ZIP filename), and remember the path to that directory:

    unzip epsg-v9_0sql-PostgreSQL.zip
    cd epsg-v9_0sql-PostgreSQL
    export EPSG_SCRIPTS=$PWD
  3. If a copy of the original SQL scripts (as downloaded from EPSG) for the previous version is still available, and if the following commands report no difference, then jump to step 4.

    diff EPSG_v9_0.mdb_Tables_PostgreSQL.sql EPSG_v8_9.mdb_Tables_PostgreSQL.sql
    diff EPSG_v9_0.mdb_FKeys_PostgreSQL.sql  EPSG_v8_9.mdb_FKeys_PostgreSQL.sql

    Otherwise:

    1. Move to the directory which contains the Apache SIS scripts:

      cd <SIS_HOME>/non-free/sis-epsg/src/main/resources/org/apache/sis/referencing/factory/sql/epsg/
    2. Overwrite Tables.sql and FKeys.sql with the new SQL scripts (replace "9_0" by the appropriate version number in the commands show below). Do not overwrite Data.sql and Indexes.sql:

      cp $EPSG_SCRIPTS/EPSG_v9_0.mdb_Tables_PostgreSQL.sql Tables.sql
      cp $EPSG_SCRIPTS/EPSG_v9_0.mdb_FKeys_PostgreSQL.sql  FKeys.sql
    3. Open the Tables.sql file for edition:

      • Keep the header comments that existed in the overwritten file.
      • In the statement creating the coordinateaxis table, add the NOT NULL constraint to the coord_axis_code column.
      • In the statement creating the change table, remove the UNIQUE constraint on the change_id column and add a CONSTRAINT pk_change PRIMARY KEY (change_id) line instead.
      • In the statement creating the epsg_datum table, change the type of the realization_epoch column to DATE.
      • Change the type of ellipsoid_shape, reverse_op, param_sign_reversal show_crs, show_operation and all deprecated fields from SMALLINT (or sometime VARCHAR(3)) to BOOLEAN.
      • Change the type of every table_name columns from VARCHAR(80) to epsg_table_name.
      • Change the type of coord_ref_sys_kind column from VARCHAR(24) to epsg_crs_kind.
      • Change the type of coord_sys_type column from VARCHAR(24) to epsg_cs_kind.
      • Change the type of datum_type column from VARCHAR(24) to epsg_datum_kind.
      • Suppress trailing spaces and save.

      Usually this results in no change at all compared to the previous script (ignoring white spaces), in which case the maintainer can just revert the changes in order to preserve the formatting.

    4. Open the FKeys.sql file for edition:

      • At the end of all ALTER TABLE statement, append ON UPDATE RESTRICT ON DELETE RESTRICT.
      • suppress trailing spaces and save.

      In most cases this results in unmodified FKeys.sql file compared to the previous version.

  4. Open the org.apache.sis.referencing.factory.sql.epsg.DataScriptFormatter class located in the test directory of the sis-referencing module and run the main method of that class.

  5. Upgrade the FACTORY.VERSION value defined in the org.apache.sis.referencing.report.CoordinateReferenceSystems class.