OpenOffice addins for Calc

[Martin Desruisseaux <ma...@geomatys.com>]

Hello all

As mentioned during the ApacheCon in Vancouver, an addin exists that
allow to use some Apache SIS referencing services from Apache OpenOffice
spreadsheet (Calc). A first working version has been ported to the JDK8
branch in the "application/sis-openoffice" directory. This would be
offered as an alternative to "application/sis-console" for experimenting
some SIS functionalities without writing Java code. The addins add new
formulas that can be used in Calc cells. For example (formula names to
be revisited):

  * =TRANSFORM.CONVERT("EPSG:4326", "EPSG:3395", A2:B4) converts the
    coordinates in cell ranges A2:B4 from "WGS84" to "World Mercator".
  * =TRANSFORM.ACCURACY("EPSG:4326", "EPSG:3395", A2:B4) gives an
    accuracy estimation (in metres) of above transformation.
  * =GEOGRAPHIC.AREA("EPSG:3395") gives the bounding box in which "World
    Mercator" projection is valid.

The full list of functions is available in this XML file:

http://svn.apache.org/repos/asf/sis/branches/JDK8/application/sis-openoffice/src/main/unopkg/Referencing.xcu

This is just a first draft of proposed features - anything in this file
can change according feedbacks received on this mailing list. For
example I expect some function names to change (e.g. maybe rename
"CRS.AXIS" as "AXIS.NAME" - not sure yet).

Those we want to try can do the following steps:

 1. Execute "mvn install" at the root of JDK8 branch of Apache SIS project.
 2. If the sis-openoffice module has not been built (at the time of
    writing this email, it is not yet part of the multi-module build),
    go to "application/sis-openoffice" directory and execute "mvn
    install" again.
 3. If the build is successful, the "application/sis-openoffice/target"
    directory should contain an "apache-sis-0.8-jdk8-SNAPSHOT.oxt" file.
    File size is currently about 730 kb.
 4. Optional but strongly recommended: ensure that the SIS_DATA
    environment variable point to directory containing SIS data, in
    particular the EPSG geodetic dataset. For example if
    the"sis-console" application [1] as been executed, then we can do
    "export SIS_DATA=<console application directory>/data". This is
    unconvenient, but automatic installation of EPSG dataset from
    OpenOffice will be explored later. The current plan is to use the
    OpenOffice database manager [2] rather than embedding a database
    driver in the addin.
 5. Start Calc from the command line (not by clicking on the icon on the
    desktop) in order to have to keep the SIS_DATA value set in
    OpenOffice environment: "<open office installation
    directory>/program/scalc". MacOS users needs to replace "program" by
    "MacOS".
 6. In the "tools" menu, select "Extensions manager". Click on "Add" and
    browse to the "application/sis-openoffice/target" directory. Select
    "apache-sis-0.8-jdk8-SNAPSHOT.oxt".
 7. In any cell, enter =CRS.NOM("EPSG:4326") on French environment or
    =CRS.NAME("EPSG:4326") on any other language environment. If
    everything worked well, the "WGS 84" value should appear in the cell.
 8. The above "EPSG:4326" and a few other special values are guaranteed
    to work even without EPSG dataset. To test if the EPSG geodetic
    dataset is really used try =CRS.NAME("EPSG:3395"). The "World
    Mercator / WGS84" should appear in the cell.

If steps 4 and 5 are skipped, the addins will still work. But its
usefulness regarding coordinate transformations will be restricted. As a
reminder, the list of EPSG codes is at [3]. I will try to make the
installation process easier and write a better user guide later this summer.

Note: in every function argument expecting an EPSG code, it is possible
to specify instead a local path or a URL to file containing a WKT
(version 1 or 2) or GML definition of a CRS.

With the work on coverages (rasters) beginning this summer, a future
improvement for this addin may be an "=COVERAGE.EVALUATE(raster,
sampleDimension, coordinate, crs)" function. This is not scalable to a
large amount of values - OpenOffice Calc is not a database -, but can be
quite convenient when analysing relatively small (a few thousands)
amount of points.

    Martin


[1] http://sis.apache.org/command-line.html
[2]
http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdb/DatabaseContext.html
[3] http://sis.apache.org/tables/CoordinateReferenceSystems.html