CAD and GIS, the right tools for the job ( II )

Last week I got the following question; how can I link a Shape-file and an Excel-sheet and create a resulting Shape-file with just a subset of the combined attributes.

In the Shape-file was the geometry of road area’s incl. road-number, road-area-number and road-area-sub-number. In the Excel-sheet there where the same numbers and other details about the road area’s. This Excel-sheet was exported from road management software.
If the AutoCAD Map user is an senior ArcGIS user, than you’ve a challange as consultant.

We did the following in the form of a handson workshop:

AutoCAD Map FDO Create a JoinTo link both files, we use the FDO-function: Create a Join. We need to connect both as FDO-connection into AutoCAD Map and there has to be one or more common fields to use as matching columns.

To make an FDO-connection with an Excel-sheet it is easier to import the sheet into an Access-table first. Than an FDO-connection can be created to the Access-table using the following Connection String: Driver={Microsoft Access Driver (*.mdb)};DBQ=pathnamefilename.mdb When the Excel-data is inside Access, it is also easy to make the columns used to join of the same database-type ( Text or Numbers ).

In the screen below is the FDO-connection to the Shape-file selected as Primary table and the FDO ODBC-connection to the Access-table as Secondary table. For the Join we linked the following columns:
WEG_NR002 -> Weg
WEGV_NR002 -> Vak
WEGVONR002 -> Ond

In the FDO DataGrid is the combination of all columns from both sources visible. To create a resulting Shape-file with just a subset of the attributes is not an easy task in AutoCAD Map. In ArcGIS a piece of cake, is in AutoCAD Map quite a challenge.

To create a new Shape-file with in the DBF-database the content of the original file supplemented with the Excel-sheet, it has to be exported into an SDF-file first. This local geospatial database has the complete combined structure. With FDO Schema Editor it is possible to create a new Shape-file with just the columns needed. With FDO BulkCopy funtionality the content of the temporary SDF-file can be copied over into the empty Shape-file, where just a subset of the columns can be selected.

AutoCAD Map FDO Create a Join

CAD and GIS, the right tools for the job

A while ago I got the following email: “Hi Henny, I’m responsable for projects that have to do with the management and maintenance of our dunes. The spatial information is stored in an Oracle Spatial database by my GIS colleagues. We want to work with the information relevant to our maintenance work directly in an AutoCAD environment. Can you support us ?

I believe this is a common situation with a lot of companies that have both CAD and GIS-departments. Often information is exchanged using Shape or DXF files, whereas AutoCAD Map using the FDO-functionality can connect to the Oracle Spatial database directly.

We did the following in the form of a handson workshop:

We started with the connection to the ArcGIS tables in Oracle as so called Foreign Datastore. These are tables not created by AutoCAD Map and therefor without FDO-metadata. The data is displayed correctly, but working with for example the Style Editor to create thematic maps is somewhat time-consuming.

Therefor we created in a new Oracle schema the desired feature tables using FDO’s Schema Editor incl. the FDO-metadata. Herewith we could make a choice which columns contains information necessary for the management and maintenance duties and which could be skipped. After that we used BulkCopy functionality to migrated the data over from existing tables.

In the ArcGIS tables the area information from polygon geometries where calculated using database triggers after the tables got updated. In AutoCAD Map database attributes are displayed and edited using a Data Table. Here we can also add a so called calculation-column ( see image ). Herewith the area information from polygon geometries are directly available.

Different thematic maps are defined and the style definitions are stored into Layer definition files, making it easy to re-use them in existing CAD-drawings.

Create a Calculation

AutoCAD Map/FDO ESRI ArcSDE vs AutoCAD Map/FDO Oracle Spatial

AutoCAD Map/FDO <=> ESRI ArcSDE
Before AutoCAD Map can make an FDO-connection into ESRI ArcSDE server, there have to be 3 so called DLL’s ( pe.dll, sde.dll and sg.dll ) copied into the “AutoCAD Map software”/FDO/bin-folder.

Using “Map Display Manager => Data => Connect to Data” to add an ArcSDE connection in the Data Connect UI. In this connection screen the server name and instance needs to be entered and after that the username/password of a registered ArcSDE-user.

Map will research, with the ArcSDE-credentials of that user, which feature-tables are available and analyse those tables to determine the correct structure. Note: if the ArcSDE-user has full access to the database, than this analyse will take longer – take care that the user has restricted credentials only for the desired tables for a better performance.

After this analyse the list with available features will be displayed and the user can select one or mutiple tables to connect to the Map-session. Next the data is displayed, with the option to filter on location and/or attributes.

AutoCAD Map/FDO <=> Oracle Spatial
Before AutoCAD Map can make an FDO-connecton into an Oracle 9i or 10g Spatial server, there have to be an Oracle 10g Client ( or an Instant Client ) installed on the Map-machine. Besides that Map needs Oracle Workspace Manager to be installed on the Oracle server – this is used for Long Transactions and Versioning.

Using “Map Display Manager => Data => Connect to Data” to add an Oracle connection in the Data Connect UI. In this connection screen the service name needs to be entered and after that the username/password of a registered Oracle-user.

Map will search by default for FDO-schema’s ( tables with FDO-metadata ) and display these in the DataStore overview. Map can analyse also schema’s without FDO-metadata en determine the structure ( comparable with the description above for ArcSDE ).

After this the list with available features will be displayed and the user can select one or mutiple tables to connect to the Map-session. Next the data is displayed, with the option to filter on location and/or attributes.

see also: Direct access to Spatial Data using FDO-technology

ArcGIS for AutoCAD vs AutoCAD Map/FDO

ArcGIS for AutoCADArcGIS for AutoCAD is a free of charge downloadable plugin from ESRI, currently available for AutoCAD 2007, 2008 and 2009.
With this functionality AutoCAD users have access to the ArcGIS Server Map Services and the desired map will be shown as an image behind the CAD-data.
The styling of the map is done by the server and the user can get info about the GIS-attributes.
This functionality is view only and doesn’t allow editting of the ArcGIS-data by AutoCAD.

AutoCAD Map/FDO is functionality to connect directly to for example an ESRI ArcSDE server and is available for AutoCAD Map 2006, 2007, 2009 and 2010.
With this functionality AutoCAD Map users can make a selection of the GIS-feature classes they would like to connect to in their CAD-environment. These are not displayed as an image, but as vector data. That makes it possible to “snap” to them in drawing mode.
The styling of the map can be done by using AutoCAD Map Display Manager. The GIS-attributes are available in a so called Data Table, where you can select a record to highlight the corresponding object in the map ( and vise versa ).
This functionality allows you also to edit the ArcGIS-data by AutoCAD Map.

see also: Direct access to Spatial Data using FDO-technology