The Topobase Insiders

Industry models in embedded DWG and SQL Server – Part1

Since AutoCAD® Map 3D 2012 we introduced the embedded DWG that contains the same industry models, as in Oracle, in an embedded SQLite database.  In AutoCAD® Map 3D 2013 we introduced SQL Server industry models.  Both DWG and SQL Server models contain the same data models (water, gas, wastewater and electric) and extensions, and equivalent feature rules and functionality.

The main differences between these models are that embedded DWGs are for single user while Oracle and SQL Server allow a multiuser environment.  The jobs (long transactions) functionality is only available in Oracle given that it is based on the Virtual Private Database technology.  The profile extension and 3D industry models are only available in Oracle. The plot extension is only available for Oracle and SQL Server. Note that plot extension refers to the data model extension for industry models that allows storing pre-configured plot templates in the database and generating plots based on specific display models; the plot capability itself is available in Map 3D for any FDO data source.

In order to maintain a data model, the Autodesk Infrastructure Administrator provides the same look and feel whether you work in embedded DWG, Oracle or SQL Server. 

There are only a couple of considerations when creating a data model that should be considered for both SQLite and SQL Server, and it is mainly the SELECT statements of views and label definitions.  In general, Oracle specific keywords or functions will not be recognized by the other two providers.

A general tip for SELECTs is that FDO is case sensitive so you should use consistent cases i.e. use always upper case column and table names (select p.FID, p.GEOM from MYTABLE p).

Let’s start with SQLite; below you have a list of considerations for SELECT statements:

• Use only INNER JOIN and LEFT OUTER JOIN. This last one is equivalent to the old Oracle’s syntax for outer join (+).  Note that the ISO 99 SQL Standard has suppressed the use of (+), therefore it is recommended that you use LEFT OUTER JOIN also in Oracle.
• There is no DECODE function, you need to use 'SWITCH CASE WHEN .. THEN .. WHEN .. THEN END'
• There is no NVL function, but there is a similar function called IFNULL, or use COALESCE (this one is recommended); see here for a list of SQLite functions
• There is no dual table, but you can omit the 'FROM' part of the SELECT as it is not needed, e.g.: 'select 14 + 3' instead of 'select 14 + 3 from dual'
• Use LIMIT clause at the end of the SELECT instead of ROWNUM (more info here)
• The default behavior of the LIKE operator is to ignore case for ASCII characters, which means the expression ‘a’ LIKE ‘A’ is TRUE (see here)

Regarding data types, the Autodesk Infrastructure Administrator shows in the interface the well-known Oracle data types, when you create attributes with these data types they will be matched internally with the equivalent  SQLite data types. It is important to note that SQLite does not enforce data types.

For more information refer to the following links:

SQLite language diagrams (link here)

Map 3D Help - Working with SQLite data (link here)

 

Quick Tour to Project Parsec

Project Parsec for AutoCAD Map 3D 2012 software enables users to import and map GIS geometries and attributes to AutoCAD Map 3D industry models from SDF, SQLite, and Esri SHP and geodatabase files, as well as Microsoft SQL Server and Oracle databases. This cool feature is live on Autodesk Labs here. You can try on till December 31, 2012.

In this post, I’d like to tour you the user interface of this functionality and how to convert data to Industry Model through this tool.

First, you need to install Project Parsec on AutoCAD Map 3D 2012 SP1. Once installed, a new Convert to Industry Model icon will appear in the AutoCAD Map 3D user interface on the Labs tab.

 

Open an Oracle-based or SQLite-based Industry Model, and then click Convert to Industry Model button or type MAPCONVERTTOMODEL command, you’ll see Convert to Industry Model dialog.

 

There are two kinds of source data. One is file-based sources, click button “Add file-based sources”, you can select SDF, SHP or SQLite files as source. Another is FDO Connection sources. You must first connect to the source using Data Connect.

After you add the source data, define the mapping between items in the source data and feature classes and attributes in the target industry model.

 

For some attributes, you can use the Expression Builder when mapping attribute values. For example, you can convert units, perform calculations, or concatenate values in the source when mapping to attribute values in the target industry model.

To apply a filter to the source data, right-click the source and click Filter source. If necessary, you can change the coordinate system assigned to the source data. Right-click the source and click Set coordinate system.

 

When you have completed the mapping for a source, you can save the mapping as a profile and later reload the profile easily for future conversions.

 

To validate topologies, check Validate all topologies option. After the source data is converted, all topology errors will be listed out in the topology explorer.

When you have finished creating the mapping between the source and the target industry model, click Convert to execute conversion. Your data will be converted smartly to industry model data!  

Easy? Have a Try!

Map 3D 2012 New: Industry Model in File

Good news to Industry Model users!
In Map 3D 2012 Release, Industry Model functionalities could be used with file-based industry model drawings. Those drawings are regular DWG files that contain an industry model in SQLite. What you see are just *.dwg, *.dwt files.

How to work with those file-based industry model drawings?

You could find some sample industry model drawing templates under folder %\AppData\Local\Autodesk\AutoCAD Map 3D 2012\R18.2\enu\Template\Industry Templates.  Moreover, you could customize drawing template or drawing files in Autodesk Infrastructure Administrator by yourself. There are two ways: create based on sample DWT and create new DWT. And we will show you how to do it step by step:

Create based on sample industry model DWT:

1. Select File->Open (See Picture1). Open an existing sample DWT.
2. Use Administrator functionalities in the same way as in Oracle provider
3. Click File->Save/ Save as to save it as DWT, DWG files.  You could also save it as SQLite files.

In this case, the DWT, DWG created will have configured form and an embedded display model.

Create new DWT:

1. Select File->New. 
2. In create new industry model, select modules, set units and spatial settings. Click OK to create.
3. After creating it, configure it with Administrator functionalities and then save it.  

       

 Picture 1 :New file-based industry model dwt file in Administrator

In Map 3D, you could new DWG with file-based industry model dwt files, or open an existing file though double click.  Steps are the same as work with a regular DWG file. 
And here are some tips and tracks you may be interested in:

1. If you assign Global Coordinate System in the file, CS of its industry model will be changed at the same time. But the coordinates of features will not be transformed with the CS change.In create new industry model, select modules, set units and spatial settings. Click OK to create.
2. Most Oracle-based Industry Model functions are supported in files-based industry models drawings.  And do not forget to save after work!

Not supported Oracle-based Industry Model Functions

In 2012 release, some IM features are not supported in file-bases industry model drawings, see list as below.
• User, User Group
• Job
• Profile Designer, Profile Manager
• 3D support
• Plot
• TB2TB
• Schema Conversion
• Security Admin
• Server Administrator
• Spatial Export
• Polygon/Line definition report
• Gas module 

Google, Bing and the Position Finder

Within Topobase there are plenty of possibilities to find features. The most powerful way is probably using the Topobase Forms. Here you can search for almost everything. Either with a simple filter, an SQL filter, a geographic filter or a projection through foreign keys. Another easy way is the Advanced Generate Graphic. This button can be found under the Generate Graphic button in the Home - Display ribbon. Within the Advanced Generate Graphic dialog we put a control called Position Finder. The position finder is a one to many steps guide for finding a feature. For instance the user wants to search for an address, then he starts the address search, selects first the street and in the next step he selects the right building number. After he has done that he generates the graphic around this feature.

I like the concept of the position finder because it is a guided process where the search will give the user in 100% of the cases the correct result. If he searches for a Hydrant, he'll find it.

Why not taking the position finder out of the Advanced Generate Graphic dialog and place it somewhere more prominent. We could put it for instance into an AutoCAD palette set. Do you also think that's a good idea? Watch the movie to get an impression how this looks like:

If you need this gimmick NOW, just go ahead and download it here (109.9K).

To install the tool you have to copy and paste the file Topobase.Insiders.PositionFinder.dll to your Topobase Client 2010 bin directory (e.g. C:\Program Files\Autodesk Topobase Client 2010\bin). Start Topobase Client, open a workspace and type NETLOAD in the command window, select the file Topobase.Insiders.PositionFinder.dll. In order to load the file automatically with Topobase Client you can type TBPFRegister in the command window (TBPFUnregister removes it from the registry). To start the Position Finder type TBPF. Or just place a new button in the ribbon:

Oracle Data Import - Domains

In the previous post I explained how to enable a Topobase document for import with the Oracle data import tool (here). 

In this article you will see how to link domains and their values.  In the SIMROSEWATER source schema the material of the pipe is stored in the MATERIAL table.  For each of these values there should be a corresponding value in the Topobase domain table WA_MATERIAL_TBD, and if there is no corresponding value, you will have to add it.  The Oracle data import allows you to add missing values in the target domain tables.

Open the Oracle data import tool. Select the mapping definition that you created in the previous post, and click Edit. The Edit Mapping Definition window is displayed.

Select the Domains tab.  Select MATERIAL under Source Domains for SIMROSEWATER, and select WA_MATERIAL_TBD under Final Domains for ATORAMIG. Click Link Domains.

The icon shows that the tables are linked. The link is listed under Linked Domains.

 

Now you will map the values of the domain. Select the Domain Values tab. 

First, you need to relate the ID and VALUE of the domain table to the primary key and description of the source table (MAT_CODE and MAT_NAME).  Click Domain Settings.

Select the MAT_CODE Source Key Column and select the MAT_NAME Source Caption Column.  Retain the default values for the target columns (ID and VALUE).

Click Close.  The Link Domain Values area displays the values of the MATERIAL table listed by MAT_CODE – MAT_NAME. 

 

Before linking domain values, you need to analyze your data and know which entries you need to create in the Topobase domain table. It is recommended that you copy first the new values to the domain table.  Select the new values in the source table and Click Copy.

Now you can use the Autolink functionality to match values automatically in the source and target tables.  This process works in three levels: 

• The string is exactly the same
• Any of the words in the source string are contained in the target string
• Using the soundex algorithm

Autolink matches the column with the first match in the alphabetically ordered list of target values.

Click Autolink and the values are linked automatically.

Afterwards, you can review the linked values, unlink any incorrect values or link manually values that were not automatically linked.  The resulting links should appear as shown in the figure below:

 

In the next post I will show you how to link feature classes and attributes.

Oracle Data Import

Autodesk Topobase 2010 provides a tool to migrate data among Oracle schemas. The source schema could be any Oracle schema; the target schema is a Topobase schema.

The user interface of the tool displays the tables of the source and target schemas; therefore it is easy for the user to link them.  The lookup tables or domains can also be easily linked. The tool provides automatic options to link the tables and fields.  As a result, the tool creates SQL statements for the migration, creates SQL Loader files or makes the inserts through the API. 

In this first post, I’d like to show you how to enable a Topobase document for import and I will explain you the interface of the tool.  In the next posts, I will show you how to link domains and feature classes.

First, you need to enable the Oracle data import extension in the document.  In Topobase Administrator, select the document, and in the Document Settings toggle on Oracle data import extension.

Click Save.  The document is updated and the system tables for the import (TB_MIG_*) are created in the document.

Now you have an additional option in the Import menu. Select Import > Oracle Data. The Source Document window is displayed, the target document is your current document (i.e. ATORAMIG).  Enter the name of the source document for the import (in this example SIMROSEWATER).

Click Ok.  In the Import Oracle Data window, click New and enter a name for the mapping definition.

In the individual tabs you can link the domains and feature classes.  On the left-side you see the source tables displayed and on the right-side you see the target feature classes.  You have the option to link, unlink, do automatic links or copy the tables, attributes or domains in the target document.

 

In the next posts I will show you examples on how to link domains and feature classes.

1-Click Maintenance

We created a new Topobase plug-in for you! It's called 1-Click Maintenance and can be downloaded below.
 
The tool performs some very basic maintenance checks and if possible tries to fix the found issues.
As you will see, most of these checks can already be executed within the Topobase Client or Administrator, but not all of them in one single place. And that’s exactly the goal of this plug-in. I hope it can also help you maintaining your Topobase documents on a regular basis!

The tool is implemented as a plug-in for the Topobase Administrator and it works with the official Topobase 2010 version. The tool runs against any Topobase document, no matter which modules or extensions are applied. It also works against job-enabled and job-disabled documents.

But before you run the plug-in, please make sure no user is connected to this document. And make sure you have an up-to-date backup of your documents before you start fixing the issues.
 
You find all the detailed information about the tool and how to run it in the readme:

Download Readme (884.3K)

Download Plug-in (136.5K)

Maybe you know other pitfalls within a Topobase document which are not yet addressed by this tool? If yes, you will be able to implement new checks! I will share the full source code of this tool soon. Stay tuned! 

Disclaimer (we give no support for this tool):
AUTODESK PROVIDES THIS PROGRAM "AS IS" AND WITH ALL FAULTS.
AUTODESK SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTY OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR USE.  AUTODESK, INC. DOES NOT WARRANT THAT THE OPERATION OF THE PROGRAM WILL BE UNINTERRUPTED OR ERROR FREE.

Label Regeneration

One of the very powertul features of Topobase is label definitions. Labels were the subject of a couple of previous posts Topobase Labels Part 1: Configuration and Topobase Labels Part 2: Storage. Label Definitions describe how Topobase will generate label text, how labels get created automatically, and (for me the coolest aspect) how Topobase regenerates the label text.

For labels where the text is completely derived from the feature being labeled, regeneration is straight forward... if the feature changes, update the label text.

But Topobase allows you to specify an arbitrary SQL statement to get the label text. This provides a powerful way to generate concatenations of various pieces of information from multiple feature classes.

So how does it know what feature changes can affect the label? The answer is Regeneration Definitions. These are special feature rules that trigger a label update.

So let's take a typical example. We have conductors that need to be labeled with their circuit name. The CONDUCTOR feature class is related to the CIRCUIT feature class with a many-to-one relationship. So the label text for the CONDUCTOR references the CIRCUIT class, and the SQL looks something like this:

select circuit.NAME_NUMBER
from EL_CIRCUIT circuit, EL_CONDUCTOR conductor
where conductor.FID = $id and conductor.FID_CIRCUIT = circuit.FID

Now, we need to set up a regeneration definition that specifies that the label text needs to be updated when the circuit name changes. Click the Regeneration Definition button at the bottom of the Label Properties dialog and create a new rule. Choose the CIRCUIT class and the NAME_NUMBER property to tell Topobase that this class and property should be monitored. Finish off by accepting the default query that gets the affected conductor FID values given a change to the circuit class, which looks something like this:

select distinct EL_CONDUCTOR.FID
from EL_CONDUCTOR
where EL_CONDUCTOR.FID_CIRCUIT = $id

And that's it. Topobase adds a trigger to the CIRCUIT table that finds the affected conductors and updates their labels when the circuit name changes. This is part of the RegenerateLabel server side feature rule group. Easy, no?

Do you store data in Oracle Spatial ?

If yes, you can use them with Topobase Client and Topobase Web (for editing or viewing). Topobase 2009 has a new administrator function that allows you to convert an Oracle schema to a Topobase Document.

This converter will analyze the original schema and create the necessary meta information that is used by Topobase. During the whole process, the user is guided and is informed about the problems, so that he/she can fix them.

Surveyor

I remember, I was 14 years old and two guys came to our home with a theodolite. They explained to me that they had to survey our new house and showed me a map.
It looked like a funny job so I decided to study this matter and in 2002 I finally obtained a "Swiss Surveyor License". Fine, very fine... but I never used it...
Except for the design of the new Topobase Survey module. With this tool, you can compute the field measurement and use the results in your Topobase documents.
It's a very complete application that fullfils the surveyor workflow: from field to GIS. An advanced least square adjustment computation, field code list management, distribution in GIS repository, generation of network (measurement and point) plan, usage of existing coordinates, quality and reliability improvements etc, etc...