Converting RD coordinates to WGS84 in PL/SQL

I am currently working on an application which uses the BAG (Basis Administratie Gebouwen) of the Dutch Kadaster (Dutch Cadastre, Land Registry and national mapping agency). When I was trying to integrate Google Maps in my OBIEE report I noticed the the chart would not work. Looking into the problem I noticed the coordinates looked a bit different than normal. It turns out that the Kadaster uses a coordinate system specific to the Netherlands. Searching on the internet I found several websites capable of converting the coordinates, but I wanted to add them to my table as a normal longitude-latitude pair. I eventually found an algorithm in Python (Dutch). I converted the code to PL/SQL and put it in a package. The functions take 1 parameter which contains the coordinates from the BAG, for example: ‘163186.737 425229.796 0.0’.

Many thanks to Thomas V. for his original post.

CREATE OR REPLACE PACKAGE bag_rd_conv
AS
   FUNCTION conv_long(p_pos VARCHAR2)
      RETURN NUMBER
      DETERMINISTIC;

   FUNCTION conv_lat(p_pos VARCHAR2)
      RETURN NUMBER
      DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY bag_rd_conv
AS
   -- Convert RD (Rijksdriehoeksysteem) coordinate to WGS84
   -- Latitude
   FUNCTION conv_lat(p_pos VARCHAR2)
      RETURN NUMBER
      DETERMINISTIC
   AS
      rdx   NUMBER;
      rdy   NUMBER;

      x0    NUMBER := 155000;
      y0    NUMBER := 463000;
      phi0  NUMBER := 52.15517440;
      lam0  NUMBER := 5.38720621;

      phi   NUMBER;
      lam   NUMBER;

      dx    NUMBER;
      dy    NUMBER;

      TYPE kt IS TABLE OF NUMBER;

      kp    kt := kt(0, 2, 0, 2, 0, 2, 1, 4, 2, 4, 10);
      kq    kt := kt(1, 0, 2, 1, 3, 2, 0, 0, 3, 1, 1);
      kpq   kt := kt(3235.65389, -32.58297, -0.24750, -0.84978, -0.06550, -0.01709, -0.00738, 0.00530, -0.00039, 0.00033, -0.00012);
   BEGIN
      rdx   := TO_NUMBER(SUBSTR(p_pos, 1, INSTR(p_pos, ' ') - 1));
      rdy   := TO_NUMBER(SUBSTR(p_pos, INSTR(p_pos, ' ') + 1, INSTR(p_pos, ' ', 1, 2) - INSTR(p_pos, ' ') - 1));

      dx    := 1E-5 * (rdx - x0);
      dy    := 1E-5 * (rdy - y0);

      phi   := 0;
      lam   := 0;

      FOR k IN 1 .. kpq.COUNT LOOP
         phi   := phi + (kpq(k) * dx ** kp(k) * dy ** kq(k));

      END LOOP;

      phi   := phi0 + phi / 3600;
      RETURN (ROUND(phi, 5));
   EXCEPTION
      WHEN OTHERS THEN
         RETURN (NULL);
   END;

   -- Convert RD (Rijksdriehoeksysteem) coordinate to WGS84
   -- Longitude
   FUNCTION conv_long(p_pos VARCHAR2)
      RETURN NUMBER
      DETERMINISTIC
   AS
      rdx   NUMBER;
      rdy   NUMBER;

      x0    NUMBER := 155000;
      y0    NUMBER := 463000;
      phi0  NUMBER := 52.15517440;
      lam0  NUMBER := 5.38720621;

      phi   NUMBER;
      lam   NUMBER;

      dx    NUMBER;
      dy    NUMBER;

      TYPE kt IS TABLE OF NUMBER;

      kp    kt := kt(0, 2, 0, 2, 0, 2, 1, 4, 2, 4, 10);
      kq    kt := kt(1, 0, 2, 1, 3, 2, 0, 0, 3, 1, 1);

      kpq   kt := kt(3235.65389, -32.58297, -0.24750, -0.84978, -0.06550, -0.01709, -0.00738, 0.00530, -0.00039, 0.00033, -0.00012);

      lp    kt := kt(1, 1, 1, 3, 1, 3, 0, 3, 1, 0, 2, 5);
      lq    kt := kt(0, 1, 2, 0, 3, 1, 1, 2, 4, 2, 0, 0);
      lpq   kt := kt(5260.52916, 105.94684, 2.45656, -0.81885, 0.05594, -0.05607, 0.01199, -0.00256, 0.00128, 0.00022, -0.00022, 0.00026);
   BEGIN
      rdx   := TO_NUMBER(SUBSTR(p_pos, 1, INSTR(p_pos, ' ') - 1));
      rdy   := TO_NUMBER(SUBSTR(p_pos, INSTR(p_pos, ' ') + 1, INSTR(p_pos, ' ', 1, 2) - INSTR(p_pos, ' ') - 1));

      dx    := 1E-5 * (rdx - x0);
      dy    := 1E-5 * (rdy - y0);

      lam   := 0;

      FOR l IN 1 .. lpq.COUNT LOOP
         lam   := lam + (lpq(l) * dx ** lp(l) * dy ** lq(l));

      END LOOP;

      lam   := lam0 + lam / 3600;

      RETURN (ROUND(lam, 5));
   EXCEPTION
      WHEN OTHERS THEN
         RETURN (NULL);
   END;

END;
/

Running SQL Developer in a Docker container

Docker is a hot topic at the moment and many have written interesting articles about how to use Docker in combination with Oracle. I especially liked Frits Hoogland’s article on installing an Oracle 12c database in a Docker container. It got me thinking about running SQL Developer in a container and how we can achieve this. There have been several other articles about this but I use a somewhat different approach than most.

I wanted to be able to use my standard setup where I run a minimal Oracle Enterprise Linux server in Virtualbox and use MobaXterm to access the server. MobaXterm is a great product because in contains everything you need to access a server including Putty, sFTP and even an Xserver. It is my favorite client as it contains everything I one portable executable. Because I run my Xserver locally I don’t have to run the complete desktop in my Virtualbox guest. If you also switch on port forwarding in your virtualbox image you can connect to localhost:2222 via ssh (I usually forward port 22 to 2222 locally, you can choose your own).

What do you need:

• A virtualbox image which runs Oracle Enterprise Linux 6.7 with Docker installed (Frits Hoogland explains how to create this in detail) and will be the Docker host system.
• MobaXterm
• Java JDK RPM
• SQL Developer RPM (you need an Oracle Technet account)

In the newly created OEL image I add an account to run SQL Developer. This account must have privileges to run Docker containers. Adding a user to the Docker group has serious security implications, see Docker daemon attack surface for details.

$ groupadd -g 54321 oinstall
$ groupadd -g 54322 dba
$ useradd -m -g oinstall -G oinstall,dba -u 54321 oracle

$ usermod -aG docker oracle

Following Frits’ lead I put my docker files in /var/lib/docker/dockerfiles where I created a folder build-sqldev-411. I also placed the downloaded java and SQL Developer rpm’s in a sub-folder called oracle-install-files. This way I can easily add the files to my container during the build. My Dockerfile looks like this:

FROM    oraclelinux:6
MAINTAINER l.parren@thedoc.nl
RUN groupadd -g 54321 oinstall
RUN groupadd -g 54322 dba
RUN useradd -m -g oinstall -G oinstall,dba -u 54321 oracle
RUN yum -y install xterm xauth libXtst
ADD oracle-install-files/jdk-8u60-linux-x64.rpm /tmp/
RUN yum -y install /tmp/jdk-8u60-linux-x64.rpm
RUN rm -f /tmp/jdk-8u60-linux-x64.rpm
ADD oracle-install-files/sqldeveloper-4.1.1.19.59-1.noarch.rpm /tmp/
RUN yum -y install /tmp/sqldeveloper-4.1.1.19.59-1.noarch.rpm
RUN rm /tmp/sqldeveloper-4.1.1.19.59-1.noarch.rpm
USER oracle
WORKDIR /home/oracle
ENV JAVA_HOME=/usr/java/latest
CMD sqldeveloper

3-5: Add user oracle
6: Add X-window related files (I usually just add xterm so I don’t have to list packages independently)
7-9: Install Java JDK from RPM
10-12: Install SQL Developer from RPM
13-15: Set user to oracle, set the working directory and make sure the JDK can be found
16: Run SQL DeveloperThe folder structure looks like this:

$ pwd
/var/lib/docker/dockerfiles/build-sqldev-411
$ ll -R
.:
total 4
-rw-r--r--. 1 root root 697 Aug 28 22:00 Dockerfile
drwxr-xr-x. 1 root root 118 Aug 29 01:52 oracle-install-files

./oracle-install-files:
total 471316
-rw-r--r--. 1 root root 160084320 Aug 21 10:28 jdk-8u60-linux-x64.rpm
-rw-r--r--. 1 root root 322535748 Aug 21 10:29 sqldeveloper-4.1.1.19.59-1.noarch.rpm

After we have created the Dockerfile and placed the rpm’s in the right location we can generate the image:

$ cd /var/lib/docker/dockerfiles/build-sqldev-411
$ docker build -t "sqldev-411" .

Wait for the process to finish after which we can check which images are available:

$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             VIRTUAL SIZE
sqldev-411          latest              7d4cb24d9146        2 hours ago         2.037 GB
oracle-12102        latest              8ffdfb9acb44        7 days ago          12.22 GB
oraclelinux         6                   cfc75fa9f295        4 weeks ago         156.2 MB

We now have a image which contains everything we need to run sqldeveloper, so let’s give it a go:

$ docker run -ti sqldev-411

 Oracle SQL Developer
 Copyright (c) 1997, 2015, Oracle and/or its affiliates. All rights reserved.

/opt/sqldeveloper/sqldeveloper/bin/../../ide/bin/launcher.sh: line 1159: file: command not found

And nothing happens. The reason for this is that the container does not know how to connect to an Xserver, we did not install one in the virtual machine and there is none running on the client. I use MobaXterm to run a local Xserver. It will also automatically switch on X11-forwarding in a ssh session. The following statement will start SQL Developer in a Docker container:

$ docker run -ti --rm -e DISPLAY -v $HOME/.Xauthority:/home/oracle/.Xauthority --net=host sqldev-411

This setup is particularly useful in a test environment on a virtual machine. I can have several applications installed which will not interfere with each other, each can have it’s own versions of libraries and settings. I can have multiple versions of SQL Developer installed with each it’s own Java JDK in a completely isolated environment, yet on one virtual machine, saving space nd memory on my laptop. Furthermore as long as I keep my Dockerfile I can quickly rebuild the container when needed and distribute it to my colleagues.

Rittman Mead BI Forum 2015

I am at the Rittman Mead BI Forum 2015 and today we started of with a Masterclass on the updated Oracle DW + Big Data Information Management Reference Architecture. I had been reading about this but it remained very conceptual to me. Jordan Meyer and Mark Rittman managed to make it tangible and coherent for the first time for me. They showed all the parts of the architecture and how they fit together and how they should be used. Although some of the statistical and mathematical concepts that Jordan talked about were beyond me. If you get a change to see these presentations they are highly recommended.

Two more day to go.

Migrating OWB to ODI: Groovy script to update LKM DB-Link option value

OWB staging mappings in general use a INSERT-SELECT-FROM statement over a DB-link. When this is migrated to ODI we get a similar mapping which uses an LKM with a select over a DB-link and an IKM which generates an insert statement with an APPEND hint. All just the way I want it. When I scheduled my mappings in a Load Plan in parallel funny things started to happen. Some mappings would run, others would fail with ORA-02019: connection description for remote database not found.
It turns out that each mapping generates a create and drop database link statement with the same name. When they are run in parallel the DB-link is created and dropped multiple times. There is a simple solution: setting the SOURCE_ACCESS_DB_LINK option for LKM. If we now create the link before any mappings are run, ODI will not generate the DB-link code, but instead use the pre-defined one.
I did not want to modify all my mappings by hand so I wrote a groovy script to do it for me.

This script works for ODI 12.1.3. In 11g the structure of the interface is slightly different so this solution won’t work.

import oracle.odi.core.OdiInstance
import oracle.odi.core.config.MasterRepositoryDbInfo
import oracle.odi.core.config.OdiInstanceConfig
import oracle.odi.core.config.PoolingAttributes
import oracle.odi.core.config.WorkRepositoryDbInfo
import oracle.odi.core.persistence.transaction.support.DefaultTransactionDefinition
import oracle.odi.domain.mapping.Mapping
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.project.OdiFolder
import oracle.odi.domain.project.OdiPackage
import oracle.odi.domain.project.finder.IOdiFolderFinder
import oracle.odi.domain.project.finder.IOdiPackageFinder

/* --------
Begin update section
Replace the follwing values with your connectivity information.
Alternatively, you can use ODI substitution APIs to fill in these parameters dynamically
----------- */

def url = "jdbc:oracle:thin:@odi1.localdomain:1521:<dbservice>" /*Master Repository: JDBC URL */
def driver = "oracle.jdbc.OracleDriver" /*Master Repository: JDBC driver */
def schema = "DEV_ODI_REPO" /*Master Repository: Database user for schema access*/
def schemapwd = "******" /*Master Repository JDBC URL */
def workrep = "WORKREP" /*Name of the Work Repository */
def odiuser = "SUPERVISOR" /* ODI User name used to connect to the repositories */
def odiuserpwd = "******" /* ODI User password to connect to the repositories */

def projectCode = "<projectcode>"
def dblinkName = "<dblink>"

/* --------
End of update section
----------- */

// Repository and ODI Instance
def masterInfo = new MasterRepositoryDbInfo(url, driver, schema, schemapwd.toCharArray(), new PoolingAttributes())
def workInfo = new WorkRepositoryDbInfo(workrep, new PoolingAttributes())
def odiInstance = OdiInstance.createInstance(new OdiInstanceConfig(masterInfo, workInfo))

// Authentication
def auth = odiInstance.securityManager.createAuthentication(odiuser, odiuserpwd.toCharArray())
odiInstance.getSecurityManager().setCurrentThreadAuthentication(auth)

// Transaction Instance
def txnDef = new DefaultTransactionDefinition()
def tm = odiInstance.getTransactionManager()
def tme = odiInstance.getTransactionalEntityManager()
def txnStatus = tm.getTransaction(txnDef)

/* Shortcuts to some finder classes*/
def fm = ((IMappingFinder) tme.getFinder(Mapping.class))         // Find Mapping

try {
    def mappingList = fm.findAll().findAll { w -> w.getProject().getCode() == projectCode && w.getName().contains('STG') }

    mappingList.each { m ->
        println("- " + m.getName())

        m.getPhysicalDesigns().each { p ->
            p.getPhysicalNodes().findAll { a -> a.getLKMName() == 'LKM Oracle to Oracle Pull (DB Link)' }.each
                    { n ->
                        println("\t Original value: " + n.getLKMOptionValue('SOURCE_ACCESS_DB_LINK').getOptionValue())
                        n.getLKMOptionValue('SOURCE_ACCESS_DB_LINK').setValue(dblinkName)
                    }
        }
    }
    // Commit transaction, Close Authentication and ODI Instance
    tm.commit(txnStatus)
    auth.close()
    odiInstance.close()
}
catch (Exception e) {

    // Commit transaction, Close Authentication and ODI Instance in Exception Block
    tm.rollback(txnStatus)
    auth.close()
    odiInstance.close()
    println(e)
}

The Power of Groovy: Finding all mappings in an ODI 12c project

For a groovy script I needed a list of all mappings in a project. So far I only had to retrieve lists of objects from just one folder, but for this script I needed them all. My first thought was to look for a findByProject method for the IMappingFinder class, but for some reason all findByProject methods need an additional parameter like the folder name. Funnily there is a findByProject which only takes a project code in the IOdiPackageFinder class.

My first thought was very procedural: lets do it do old fashioned way and create a class which will traverse the folder tree of a project and build a list of all the mappings. The class I built looks like this:

import oracle.odi.domain.mapping.Mapping
import oracle.odi.domain.mapping.finder.IMappingFinder
import oracle.odi.domain.project.OdiProject


/**
 *  A class to find all Mapping objects within a project
 *  All folders are traversed recursively and the mappings are returned in a collection.
 */
class FindMappings {
    def private allMappings = null

    def processProject(odiInstance, projectCode) {
        def odiProjectsList = (odiInstance.getTransactionalEntityManager().getFinder(OdiProject.class).findByCode(projectCode))

        odiProjectsList.each { p ->
            def odiFoldersList = p.getFolders()
            odiFoldersList.each { f ->
                /* process interfaces of the current folder */
                this.listMappings(odiInstance, p.getCode(), f.getName())
                /* Process sub folders recursively */
                this.processSubFolder(odiInstance, f, p.getCode())
            }
        }

        return (allMappings)
    }

    def private listMappings(odiInstance, projectCode, folderName) {
        def mappingList = ((IMappingFinder) odiInstance.getTransactionalEntityManager().getFinder(Mapping.class)).findByProject(projectCode, folderName)

        if (allMappings == null) {
            allMappings = mappingList
        } else {
            allMappings = allMappings + mappingList
        }
    }

    /* given an odiInstance, folder and project code, we will parse all subfolders (recursively) and print the name of all interfaces found at all levels*/

    def private processSubFolder(odiInstance, Folder, projectCode) {
        def subFolderList = Folder.getSubFolders()

        if (subFolderList.size() != 0) {
            subFolderList.each { s ->
                /* process interfaces of the current folder */
                this.listMappings(odiInstance, projectCode, s.getName())
                /* Process sub folders recursively */
                this.processSubFolder(odiInstance, s, projectCode)
            }
        }
    }
}

The entry point is the processProject method which we pass a project code. The result is a collection of mappings.

I felt a bit unsatisfied by the result, although it works well I kept thinking there must be a better way to do this. Being fairly new to groovy I have not yet had the opportunity the get to know all the ins and outs of the language, but when I was looking at the documentation for collections I got thinking if I could rewrite the class to a few lines of code, and indeed it is possible.

As I mentioned before there is no findByProject method in the IMappingFinder class, however it does inherit a findAll method from its parent. So we can find all mappings in a repository as follows:

def mappingList = ((IMappingFinder) odiInstance.getTransactionalEntityManager().getFinder(Mapping.class)).findAll()

Now we’ve got a list of all mappings in the repository, but I only want the mappings in a specific project. We can select these by using the collection and object methods in combination with closures. The resulting find will look like this:

def mappingList = ((IMappingFinder) odiInstance.getTransactionalEntityManager().getFinder(Mapping.class)).findAll().findAll{w -> w.getProject().getCode() == '<projectCode>'}

This one line of code does the same as the class does I built first.

Slightly rewritten to make it a bit more readable, it could look like this:

def tme = odiInstance.getTransactionalEntityManager()            // Shortcut to transaction manager
def fm = ((IMappingFinder) tme.getFinder(Mapping.class))         // shorcut to Find Mapping

def mappingList = fm.findAll().findAll{w -> w.getProject().getCode() == '<projectCode>'}

We can keep on chaining these commands together like:

// Find all mappings in project DEMO which start with 'DEM'
def mappingList = fm.findAll().findAll { w -> w.getProject().getCode() == 'DEMO' }.findAll {w -> w.getName()[0..2] == 'DEM'}

// Find all mappings in project DEMO which name contains STG
def mappingList = fm.findAll().findAll { w -> w.getProject().getCode() == 'DEMO' }.findAll {w -> w.getName().contains('STG')}

Amazing power.

Using IntelliJ IDEA as development tool for ODI Groovy scripts

I find the Groovy editor in ODI quite limiting when editing my scripts. I know there must be a multitude of IDE’s which are suitable for editing Groovy but I found InteliJ IDEA on Google and liked the look and feel of the tool, so I wondered if I could use it to make editing ODi Groovy scripts easier.

Download the IntelliJ IDEA Community Edition which is free from the following link and install.

Start InitelliJ IDEA:

Select Project structure  so we can setup the defaults for our ODI Groovy projects. The first thing we have to define is the SDK. Select Project on the left and create the New button to create a new SDK and select JDK. In the select window that follows select the home folder of the SDK you use to run ODI. I use jdk1.7.0_45 because I now this works for ODI 12.1.3, I have had some issues with other patch levels.

Next we need to setup the ODI Libraries. Click Libraries on the left and then click the green + sign at the top of the empty box. Again a select window will open where you can add the library paths by clicking the green +. At the top you can enter a name for the Library, I chose odilib. Add the following paths to the library (the version numbers might vary):

• %ODI_HOME%\odi\sdk\lib
• %ODI_HOME%\oracle_common\modules\oracle.jdbc_12.1.0
• %ODI_HOME%\oracle_common\modules\oracle.jsf_2.1

Now setup is complete and we can start creating projects. Make sure to select Groovy for the language, the Groove library can remain empty, then finish creating the project.

I have chosen to create a module for each of my scripting sub-projects, you can choose to organize your projects any way you want, but this works for me. Create a new Module by right clicking on the project name and then selecting New->Module. The New Module dialog appears and again choose Groovy as your language and select the Groovy Library we defined before (in my case odilib). Finish creating the Module.

Within a Module we can create several types of source files, but for scripting I use a Groovy Script type. To create it right-click the module name and select New->Groovy Script. This will open an editing window for your new script.

These are the features of IntelliJ IDEA I particularly like:

• The out of the box support for Groovy
• Code->Reformat Code
• Code->Optimize Imports
• The automatic import of classes
• The very helpful information the editor gives when writing code
• Version control
• and many, many more…

Of course there are many IDE’s which have similar functionality. You will just have to use the one that works best for you.

Migrating OWB to ODI: ORA-02292 integrity constraint (DEV_ODI_REPO.FK_MAP_EXPR_REF_3) violated - child record found

I have run into a persistent error when trying to migrate my OWB mappings to ODI. I use this simple mapping for test purposes:

When I migrate this to ODI I get the following result:

So far so good and exactly as expected. Now Expression_0 is no longer needed and has to be removed, so select the object, press delete and save. The object disappears, but the icon stays highlighted. Pressing the icon again seems to save the changes. Close and reopen the mapping, and lo and behold the expression_0 object is still present. Now delete it again and press the save all button instead and…..

Unable to save MAPPING_3 (ODI-10143: Error while accessing the ODI repository.   
ORA-02292 integrity constraint (DEV_ODI_REPO.FK_MAP_EXPR_REF_3) violated - child record found

I never used to get this error before, but I think I might just have pressed the save button and not noticed the object did not disappear. This looks like a bug in the migration utility to me.
There is a workaround, but that requires me to modify the original OWB mapping, I have not yet found a way to solve the problem in ODI.
The workaround is as follows:

• either remove the expression in OWB before the migration
• or replace the expression by a constant (you can still use expressions in a constant and since it the input for a pre-mapping it will only be evaluated once)