Beyond the Box

Using Oracle's IMPDP and EXPDP with Autodesk Enterprise Industry Models on Windows – Part 1

If you are using an Oracle database with AutoCAD Map 3D 2012 or later, this article may help you to make your first foray into the world of Oracle's Data Pump utilities. It's not intended to be an exhaustive "how to" but it will perhaps break the back of your learning experience.

And it's a fairly long topic even when trimmed down, so this is part 1 of 2 parts:

1. Using the Oracle tools
2. Using the Autodesk Tools

Background

Over the years, many of us have found Oracle's EXP and IMP utilities useful in moving database content between Oracle instances and installations. There was a time when Autodesk Topobase Administrator made use of IMP to Import what were then called Topobase 'Documents'.

With the release of Oracle 10g, Oracle Data Pump (including EXPDP and IMPDP) was announced as the heir-apparent to the IMP and EXP throne. Since that time, users of Autodesk's Topobase Administrator will possibly have noticed that this utility now insisted that IMPDP and EXPDP be used. There were fairly simple workarounds (there always seem to be!) for those that hadn't yet taken the plunge into the Oracle Data Pump world, but nonetheless the writing was on the wall.

In 2011 what was formerly Autodesk Topobase was incorporated into Autodesk's AutoCAD Map 3D 2012 Enterprise and Infrastructure Map Server 2012 (formerly Autodesk MapGuide Enterprise) products. As part of this evolution, Autodesk Topobase Administrator was renamed Autodesk Infrastructure Administrator.

In 2012 there was a further consolidation – AutoCAD Map 3D 2013 now was a single product, with no 'Enterprise' version now needed to access and use the enterprise functionality.

This product consolidation puts extremely powerful and affordable enterprise GIS software into the hands of a much, much wider range of customers. A lot of bang for your consumer buck!

Using Oracle Data Pump – Simply

When it comes to using Oracle Data Pump, a web search will yield many, many hits. My goal here is to help the user of out-of-the-box Autodesk technology to use this Oracle out-of-the-box Information Technology – to get up to speed quickly and get their information backed up or flowing smoothly between systems.

There are many EXPDP and IMPDP parameters and many variations in their application. If you have the need, you can put these parameters in a parameter file and have the utilities refer to this file at runtime.

However, my purpose here is to simplify and highlight the relevance for Autodesk Industry Models – formerly Topobase 'Documents'. Each of these Industry Models is stored in an Oracle Schema, so the examples that follow assume that we have an Oracle 11gR1 Schema set named "WATER", "SEWER", ELECTRIC" and "LAND", and that we need to use EXPDP to dump the contents for migration to an 11gR2 system (or perhaps we are taking a snapshot during a project delivery cycle).

What you should do first

IMPDP and EXPDP can use a defined a physical location (folder) referred to by a logical name and that the location is available; this location is where your hexported data will be placed, or where your imported data will be available. To define this location:

SQL> CREATE OR REPLACE DIRECTORY dmpdir AS 'E:\DBLOAD\Dump';

Note: you will require particular Oracle System Privilege – "CREATE ANY DIRECTORY" – to complete this step.

What you should do second

If you are migrating an entire Autodesk Industry Model (AutoCAD Map 3D and/or Autodesk Infrastructure Map Server) enterprise environment then Industry Model Settings might also need to be exported. The exported XML file will enable the transport of your custom (and Oracle-resident) Form and Report definitions, for example, to your target system:

A the risk of being pedantic, I should state that you will need to have installed and run your Autodesk Industry Model Setup on your target environment. I don't propose to reproduce the product documentation here.

Using EXPDP

As noted above, EXPDP enables many, many options. Here's a sample command line (assuming your Oracle Service Name is "ORCL").

E:\> EXPDP sys/<password>@ORCL as sysdba SCHEMAS=water DUMPFILE=water.dmp DIRECTORY=DMPDIR LOGFILE=water_expdp.log VERSION=11.1.0.7

Most of this is pretty obvious. I tend to run a BAT file that enables the export of multiple schemas using multiple 'cloned' commands because it provides individual LOG files and DMP files e.g.

EXPDP \"sys/<password>@ORCL as sysdba\" SCHEMAS=water DUMPFILE=water.dmp DIRECTORY=dmpdir LOGFILE=water_expdp.log VERSION=11.1.0.7
EXPDP \"sys/<password>@ORCL as sysdba\" SCHEMAS=sewer DUMPFILE=sewer.dmp DIRECTORY=dmpdir LOGFILE=sewer_expdp.log VERSION=11.1.0.7
EXPDP \"sys/<password>@ORCL as sysdba\" SCHEMAS=land DUMPFILE=land.dmp DIRECTORY=dmpdir LOGFILE=land_expdp.log VERSION=11.1.0.7

And so on. There are many ways to get a slick file-processing pipeline happing via a BAT file.

What's not so obvious is the VERSION keyword; I have found invaluable for ensuring compatibility between 11gR1 and 11gR2 systems. Also note the "\" as an escape character.

The log will look something like this:

Also note that the syntax of the command could be modified to match the following example, where the logical name "dmpdir" is embedded in the file paths, replacing the need for the DIRECTORY keyword:

EXPDP \"sys/<password>@ORCL as sysdba\" SCHEMAS=land DUMPFILE=dmpdir:land.dmp LOGFILE=dmpdir:land_expdp.log VERSION=11.1.0.7

Note: If you are attempting to export data from another schema (unless you are the SYS user for example), then you will need EXP_FULL_DATABASE system Privilege.

Using IMPDP

Having moved the EXPDP DMP file to the target machine, using IMPDP is pretty similar to EXPDP.

Assuming our current DIRECTORY is set (see "What you should do first" above), we can issue a command from the Command Line like this:

E:\> IMPDP \"sys/<password>@ORCL as sysdba\" VERSION=11.1.0.7 DIRECTORY=dmpdir DUMPFILE=water.dmp LOGFILE=water_impdp.log

Note: If you are attempting to export data from another schema (unless you are the SYS user for example), then you will need EXP_FULL_DATABASE system Privilege.

Using Oracle SQL Developer

I'm going to assume you know (and love?) SQL Developer, then you will notice that your Industry Model Schema has been imported:

(You may find that there are Oracle Views which need correcting or recompiling. I'm assuming that you can handle this one on your own! J)

In Part 2 we'll look at using Autodesk Infrastructure Administrator to do your post-import housekeeping.

 

Application Integration with Autodesk Infrastructure Map Server

As an enterprise GIS tool, it is rare that you would ever implement an AIMS system in isolation. Typically, organisations require (1) that AIMS is fed with data from disparate external systems and/or (2) that users have a way of linking the AIMS user interface within, to and/or from the user interfaces of those external systems. These are 2 of the most common types of AIMS integration and today we are going to look at the latter; user interface integration. The functionality that we are aiming at is the ability to navigate from one system to the other - for example, selecting a feature in AIMS and then opening the corresponding record for that feature in an Asset Management system. Or conversely from the Asset Management system we would like to open AIMS automatically, already zoomed to the location of the current asset.

What Comes Out Of The Box

Fortunately, we get most of this functionality "out of the box", but what we get depends a little on whether we are using Industry Models, or more specifically, the "Infrastructure Application Extension" (why is my brain still yelling "TB Web"??). Perhaps a little matrix will help:

	Without Application Extension	With Application Extension
Navigate from external system to AIMS	No	Yes! Using UrlAction.aspx
Navigate from AIMS out to the external system	Yes! Using 'URL Activated for Feature' in Infra Studio	Yes! Using 'URL Activated for Feature' in Infra Studio

So really the only major functionality gap is navigating into AIMS from an external system (if you don't use the Infrastructure Application Extension). That's going to require some code, so lets knock over the easier ones first and come back to this later.

Configuring the Out of The Box Tools

Navigating from AIMS to an External System

Using the 'URL Activated for Feature' functionality is fairly simple. We just need to build up a URL based on some constant text together with some injected properties from the feature, to create a URL like this:

http://myassetsystem/gotoAsset.php?FeatureID=111380&AssetType=Parcel

Then, using Infrastructure Studio, we would create an expression that looks like this:

concat(concat('http://myassetsystem/gotoAsset.php?AssetID=', URLENCODE( ID )),'&AssetType=Parcel')

There is more info on defining expressions here and more specifically for creating URL links here.

You may think that I am assuming your external application is web based and not a desktop app. More on that in a minute.

Navigating to AIMS from an External System

For those running the Application Extension, there is not much config to do on the AIMS side to get up and running with UrlAction. You'll just need to configure your external application to build up the URL that AIMS is expecting. All the options are well documented, so I'll refrain from regurgitating them here. I will point out that you'll need the FID(s) for the feature(s) you are navigating to, so you may have some work to do if they're not available to your external application. Mileage may also vary if you have implemented your own AIMS login, such those as we have done for customers that require Active Directory integration. Email me if you'd like more information on that topic.

Navigate To AIMS Without Application Extension

So, how do we get from our external system into AIMS when you can't use UrlAction? Our solution was to develop a Fusion widget that looks at the http URL parameters that have been passed in when the map first loads. If the widget finds a certain set of parameters then it will go looking for features based on the parameter values, then set the current zoom/selection to any features that it finds. There are 2 major components to the widget: the Javascript running on the client browser and a PHP file for doing server side work.

Why a widget? This is a fairly common requirement, so something that is reusable and simple to deploy ticks the right boxes.

Widget installation instructions

1. Download AcUrlLink and unzip it to <webExtensionsInstallPath>\www\fusion\widgets.
   Note: You'll need to restart Infrastructure Studio after step 1 if it was open
2. Using Infrastructure Studio, add the widget to the desired Fusion Layout. 

How it works

Lets step through how the widget works once it is installed. The first thing that needs to happen is to add some parameters to the normal URL that would open AIMS:

&action=zoom&layer=Parcels&attribute=ID&attvalue=106520

Here is a breakdown of each parameter:

action – what the appl will do when loaded, should always be 'zoom'

layer – The map layer to look for features in

attribute – The attribute on layer to look in

attvalue – the value for attribute to look for

So in the previous example were are looking on the Parcels layer where ID = 106520

With these parameters added, the full URL to open AIMS might look something like:

http://localhost/mapserver2013/fusion/templates/mapguide/aqua/index.html?ApplicationDefinition=Library%3a%2f%2fSamples%2fSheboygan%2fFlexLayout%2fAqua.ApplicationDefinition&action=zoom&layer=Parcels&attribute=ID&attvalue=106520

Once this URL is hit, Fusion loads and initialises all of the widgets by calling their initialize method. In this method we have some code that adds an event handler to the loading of the map:

this.getMap().registerForEvent(Fusion.Event.MAP_LOADED, OpenLayers.Function.bind(this.onMapLoadedDoStuff, this));

So now the 'onMapLoadedDoStuff' method will be called once the map is loaded. This method contains the code that will check the URL to see if the action parameter is 'zoom' and to retrieve the criteria for the feature to zoom to:

if(this.getParameterByName("action") == 'zoom')

{

Fusion.ajaxRequest('widgets/AcUrlLink/AcUrlLink.php', {

onSuccess: OpenLayers.Function.bind(this.urlLinkComplete, this),

onFailure: OpenLayers.Function.bind(this.urlLinkFail, this),

parameters: {

'mapname': this.getMapName(),

'session': this.getMapSessionID(),

'layer': this.getParameterByName("layer"),

'attribute': this.getParameterByName("attribute"),

'attvalue': this.getParameterByName("attvalue"),

'seq': Math.random()

}

});

}

Here we are getting the URL parameter values, plus the Map and Session information and passing it to the PHP code on the server which will interrogate the Layer for Features via the API. The result it receives is a XML selection set, which can then be applied on the client side (via urlLinkComplete method which is called after the request successfully completes):

urlLinkComplete: function(r) {

…

var map = Fusion.getMapById();

map.setSelection(r.responseText,true);

…

},

This call to 'map.setSelection' both sets the selection and zooms to the feature. This is about all the widget needs to do. There are a couple of helper methods I haven't posted here (you can see those in the full source code).

Next, let's look at what is happening in the server side PHP. In this case I'll let the code comments do the explaining:

//Get the layer object for layer we are selecting on

$mglayer = $map->GetLayers()->GetItem($layer);

//build up filter string from parameters passed in

$filter = "$attribute = '$attvalue'";

$featureSrvc = $siteConnection->CreateService(MgServiceType::FeatureService);

//apply the filter to the layer to get a set of features back

$queryOptions = new MgFeatureQueryOptions();

$queryOptions->SetFilter($filter);

$layerResId = new MgResourceIdentifier($mglayer->GetFeatureSourceId());

$featureClassName = $mglayer->GetFeatureClassName();

$featureReader = $featureSrvc->SelectFeatures($layerResId, $featureClassName, $queryOptions);

//create a selection set based on the returned features

$selection = new MgSelection($map);

$selection->AddFeatures($mglayer, $featureReader, 0);

//return the selection as XML

echo $selection->ToXml();

And that's about it!

Now we can link to and from AIMS with and without the Industry Application Extension.

Navigate from AIMS to Desktop Applications

So far we have only looked at linking to an application via http URL link. What are the options if our external application is desktop based and not web based? The answer is to still use URL links, but define a custom protocol handler for the desktop based application (assuming it doesn't already have one). You've no doubt used these before but perhaps have never had to create one. You know how Microsoft Outlook opens up when you click a link prefixed with 'mailto:'? This is an example of a protocol handler. Even when you are invoking a link prefixed with 'http' you are invoking a protocol handler that has mapped the 'http' protocol to your web browser application. You'll need to create a new handler that maps a string, e.g. 'asset', to your desktop based Asset System client application. Then, whenever a URL is invoked that begins with 'asset:', your computer will know to open your asset application rather than open the link in a web browser. There's plenty of information for how to do this on the 'net, here is a great article to get you started.

Final words

Not using Fusion yet? It's time to make the move. If you need some help getting started then check out the documentation here.