Highs and Lows of Moving DWG into a Database

August 6th, 2013

Let's face it, the DWG is a wild beast with a complex and messy file format.  It contains everything from polylines to xrefs to dynamic blocks to splines. 

Using the DWG to map features within a municipality can be as easy as herding cats. Taming your DWG begins with simply keeping good naming conventions for your blocks and layer names. CAD managers really need to be taskmasters to keep everyone consistent while creating DWGs.

How often do you attend a meeting and someone opens their DWG to demonstrate how something is built, and a colleague points out that the DWG is not the latest version?

Database to the Rescue

Storing your DWG in a database is not a new concept.  As far back as 2001, Autodesk released Autodesk Map 5 with Polygon and Advanced Oracle Extension service pack. This extension enabled you to push your entire DWG, including blocks, layers, and any attributes such as Object Data into an Oracle database.  It was as simple as clicking two buttons called Read and Write (see Figure 1).

Figure 1: MAPOSEWRITE button

In fact, if you received a new DWG containing all the water mains, hydrants, and valves for a new subdivision, you could simply click Write, and everything—even block scale and rotation—would quickly go into Oracle in the correct format.
Unfortunately this only works with 32-bit versions of AutoCAD® Map 3D 2014 and Oracle rather than the newer 64 bit versions.

More Databases for your DWG

In 2004, Autodesk released Autodesk Map 2005 with Feature Data Objects (FDO).  This started the trend of connecting to many different GeoSpatial data types including:

  • Autodesk SDF
  • Shapefile
  • ArcSDE
  • MapInfo
  • ODBC
  • Oracle
  • SQL Server
  • SQLite
  • PostgreSQL
  • MySQL
  • WMS/WFS

Some amazing tools to style and theme the data were now available.  You could now color-code your water mains rapidly by age or size using the style tools.  Performance increased exponentially.  Even classic GIS analysis such as spatial overlays were now possible.

FDO truly marked the shift from DWG-centric data to spatial-centric data. However, it was no longer easy to push blocks and attributes into a table. In order to push data into the database, the user would have to know the data structure inside and out.  You would have to know which AutoCAD properties went into which column in the table.  For example, your block rotation might have to be mapped to the block_rot column.

You can select each AutoCAD feature and add it to the table one feature at a time, but the FDO way ignores all the AutoCAD properties such as text value or Object Data (see Figure 2).  To load the new subdivision you will have to pick on each new item individually or export all the layers to a temporary file such as SDF first, and then use batch tools called Bulk Copy to load it into the database.  Not so pretty, is it?

Figure 2: New FDO hydrant from AutoCAD geometry
 
If you no longer want to deal with DWG, maintaining the database is quite easy with FDO. In fact, it is a very powerful spatial engine and integrates with Autodesk Infrastructure Map Server and Autodesk InfraWorks (Infrastructure Modeler) seamlessly.

Whole Hog Databases

The evolution of moving to databases really matured when Autodesk released Topobase in 2006, the Oracle-based tool for storing your DWG in Oracle.

Topobase, completely integrated into AutoCAD Map 3D and now called Industry Models for Utilities, provides a lot more tools than FDO alone. Not only does it now support SQL Server, SQLite, and Oracle, but it also can do some advanced drawing such as prompting the user for rotation when inserting a block symbol directly to the database.  Versions of the data are stored within the database and referred to as “Jobs.”  Network analysis is also “out of the box” in the industry models if the data supports it.

Another advantage of going with the Industry Models is that you don't need to reinvent the wheel.  There are whole data models for Electric & Gas, Land Development, Roads & Highways, Water & WasteWater, and any custom model you wish to create (see Figure 3).

Figure 3: Autodesk Infrastructure Administrator WasteWater Model

Returning to our example of a new DWG containing all the water mains, hydrants, and valves for a new subdivision, just as with the FDO, the Industry Models still would require adding one feature at a time.  Alternatively, like FDO, there is a batch mode called Convert to Industry Model that allows you to match each feature in an SDF or SHP to an associated feature in the Industry Model (see Figure 4).

Figure 4: Convert to Industry Model (SDF to Oracle)

Either way, you will require an intimate knowledge of the data model to bulk load data into the database with either FDO or the Industry Models; the current DWG properties or exported SDF/SHP columns will have to match up to what is in the database.  It's not as seamless as Read/Write.

Database Loading the Non-Autodesk Way

So you are probably thinking, “Why can't I have the Read and Write buttons like the Oracle Spatial Extension?” Those of us using AutoCAD Map since the stone ages have been asking that same question.

Until FDO or the Industry Models have these user-friendly read/write tools, we can look outside the Autodesk universe to see how to move our DWG into the database automatically.  A great product from Safe Software is FME Desktop (http://www.safe.com/fme/fme-technology/fme-desktop/). FME (Feature Manipulation Engine) converts, transforms, and modifies data in almost any spatial industry format.  In fact, there are few formats it does not read or write. It's amazing. From a DWG perspective, it can read the properties from a DWG and push it into the database with simple batch files or on-demand buttons. 

Properties such as block attributes and text rotation, object data and linetypes can all be captured and put in the database table of your choice. I frequently use this to batch As-Builts into SQL Server on an hourly basis so everyone has the latest version.

Again, this process is not for the faint of heart.  You not only have to have a good understanding of your database, but also a keen knowledge of how FME works.  This is a tool for the geospatial professional who also wears a database administrator's hat.

Future

Hopefully this is a “call to arms” for the Autodesk community to come up with an easier way to load whole or partial DWGs into a database with a simple click of a button. 

Until then, we can keep using the FDO and Industry Model one-feature-at-a-time techniques, which are great if all your data is edited “in-house.” For those who receive DWG updates from an outside source, it might be worth your while to research the FME Desktop product or at least learn about the Bulk Copy or Convert to Industry Model tools.

Either way, unless you are still using 32-bit AutoCAD Map 3D and only want to use Oracle, the magic of Read/Write is out of reach.

Join AUGI Today

Become part of the largest Autodesk community


About the Authors

Gordon LUCKETT

Gordon Luckett is principal of Arrow Geomatics Inc., a GIS consulting, training, and development company. Oracle Spatial and SQL Server Spatial are part of Gordon’s specialties, especially when they are combined with Autodesk and Open Source products. Much of his focus entails building web-based sites using Autodesk Infrastructure Map Server and MapGuide OpenSource.

 

Appears in these Categories