Imagine that you collected geographic data in one schema and you want to provide it in some other schema. For many of us, this only happens pretty much all of the time.

For example, maybe your organization has an enterprise database which stores all the information that you have about recent, active faults. Now you want to provide that data as a WFS service with fields that conform to the ActiveFaults template being used by the AASG Geothermal Data System. Perhaps you store all your geologic map data in such a database and you want to convert it into FeatureClasses that fit into NCGMP09-style geodatabases.

Here's a nice way to convert your data formats in such a way that you only have to do it once. You can continue to make changes to your data just like you always have, and there will always be a featureclass hanging out there in the new schema which conveys the most up-to-date information that you have. The pre-requisites are:

1. Your data is in an ArcSDE Database.
2. You are at least somewhat comfortable writing SQL Queries. If you aren't, and your organization uses an SDE Database, chances are pretty good that there's someone in your organization who can help you.
3. You have command-line access to the computer that houses your SDE Database.

So what you're going to do is: First deal with your versioned data by making a multi-version view of the featureclasses and tables that you need to draw data from. Second, you'll make a standard view based on the multi-version views which conforms to the data schema that you're mapping into. Finally, you'll "trick" ArcSDE into thinking that the view is a featureclass.

Create Multi-version Views of Relevant Data Tables and FeatureClasses

If your data is versioned, you'll need to go through this step for each featureclass or related table that contains data that will be mapped into the new schema. If you skip it, then you'll find your final, transformed featureclass seems to be missing all kinds of features that you thought were part of the original featureclass.

At the command-line on the ArcSDE Server, type a command like this one:

sdetable -o create_mv_view -T contactsandfaultsview -t ContactsAndFaults -i sde:sqlserver:malachite -s malachite -D QuaternaryFaults

The parts of this command are as follows:

• sdetable: the command to administer ArcSDE tables
• -o create_mv_view: specify that you want to create a multi-version view
• -T contactsandfaultsview: give the multi-version view you're creating a name. In this case I'm making one called contactsandfaultsview
• -t ContactsAndFaults: specify the table of featureclass that you want to create a multi-version view of. In this case, ContactsAndFaults
• -i sde:sqlserver:malachite: specify the service used to connect to the ArcSDE database. In this case I am making a direct connection, but it would work through an SDE service as well. This is exactly the same as the string you use to connect to a Spatial Database through ArcCatalog.
• -s malachite: specify the server which is running the ArcSDE database
• -D QuaternaryFaults: specify the database that you're working in, in this case QuaternaryFaults

Do this once for each featureclass or table that you're going to need to map your data into the new schema. Once you've done this, if you look at your database in ArcCatalog, you should see a table show up for each of the multi-version views you created. You'll notice each of them has exactly the same attributes are the table you made them from.

Make a View that Matches the New Data Schema

Now you're going to do the hard work. You're going to write a SQL Query that looks at your multi-versioned views and builds tables that match the schema you're trying to transform into. I can't really tell you how to do this, because the SQL will be different for every single situation someone might want to map. However, once you've written it for your specific situation, the hard work is done. The views will be there and stay up-to-date with your most recent edits to the original tables and featureclasses.

In this example, I'm mapping from an NCGMP-style ContactsAndFaults featureclass into an AASG Geothermal Data System ActiveFaults-style featureclass. In order to do so, I needed to pull a few related tables in from my original NCGMP database: Glossary, DataSources, and a custom ActiveFaultData table. Here's the SQL:

SELECT
	CONVERT(INT, row_number() OVER (ORDER BY contactsandfaultsview.OBJECTID ASC)) AS OID,
	'http://resources.usgin.org/uri-gin/azgs/fault/' + contactsandfaultsview.ContactsAndFaults_ID + '/' AS FeatureURI,
	contactsandfaultsview.Type As Name,
	'' AS FullName,
	'' AS ParentFeatureURI,
	'' AS Label,
	'' AS Description,
	'' AS Symbol,
	contactsandfaultsview.ContactsAndFaults_ID AS OtherID,
	'' AS SpecificationURI,
	'Fault' AS FeatureType,
	'Not Given' AS GeologicHistory,
	'' AS RepresentativeAgeURI,
	'' AS YoungerAgeURI,
	'' AS OlderAgeURI,
	activefaultdataview.IntervalSinceMovement AS IntervalSinceMovement,
	'' AS FeatureTrace,
	'' AS Locatability,
	contactsandfaultsview.LocationConfidenceMeters AS PositionAccuracyMeters,
	'' AS PositionAccuracy,
	'' AS Displacement,
	activefaultdataview.SlipRate AS SlipRate,
	'' AS SlipAccumulationInterval,
	'' AS MovementType,
	'' AS MovementSense,
	'' AS DipDirection,
	'' AS DateMostRecentEvent,
	'' AS RecurrenceInterval,
	'' AS TotalSlip,
	datasourcesview.Source + ': ' + datasourcesview.Notes AS Source,
	'' AS MetadataURI,
	contactsandfaultsview.Shape AS Shape
FROM
	dbo.glossaryview 
	INNER JOIN dbo.contactsandfaultsview ON dbo.glossaryview.Term = dbo.contactsandfaultsview.Type
	INNER JOIN dbo.activefaultdataview ON dbo.glossaryview.Glossary_ID = dbo.activefaultdataview.GlossaryID
	INNER JOIN dbo.datasourceview ON dbo.contactsandfaultsview.DataSourceID = dbo.datasourcesview.DataSources_ID

Okay! After you realize how sparse my data is, we can move on. Here are some things to pay attention to:

• CONVERT(INT, row_number().....blahblahblah AS OID: This is a way to "spoof" and ObjectID. The line of code is essentially auto-generating a sequential set of integers, one for each row in the resulting view.
• For each attribute in my destination schema, I've got something AS FieldName. Where I have data, I grab it from the appropriate table. Where I don't have data, I just plop in nothing as an empty string. You could also put in Nulls or whatever is appropriate.
• The geometry information gets pulled along through the Shape attribute of the featureclass I'm mapping from. In this example, contactsandfaultsview.Shape AS Shape means I'll end up with a field called "Shape" in my view, and it will store the geometry information that was included in my contactsandfaultsview (and the ContactsAndFaults featureclass originally).
• Save the view. Give it a name. Remember that name.

Trick ArcSDE Into Thinking the View is a FeatureClass

Now, back to command-line. You'll write a command like this one:

sdelayer -o register -l ActiveFaultTemplate,Shape -e l -C OID -t GEOMETRY -R 3 -i sde:sqlserver:malachite -s malachite -D QuaternaryFaults

Here's the breakdown of what it means:

• sdelayer: This function is an ArcSDE function used to administer SDE layers
• -o register: defines the operation we wish to run. We want to register a new layer.
• -l ActiveFaultTemplate,Shape: defines the name of the view that we want to turn into a featureclass, and the name of the column in that view that contains the feature geometries
• -e l: defines an "entity mask" which (as I understand it) is kind of like specifying the type of feature (point, line, polygon, etc.) l  is for line.
• -C OID: defines the field to be used as a surrogate ObjectID. If you don't have this, you won't be able to select features in the resulting featureclass.
• -t GEOMETRY: defines the geometry storage type. I use native SQL Server geometries -- this may be different if you don't.
• -R 3: defines the spatial reference system to use. I found the right number by looking in the database's SDE_spatial_references table.
• -i sde:sqlserver:malachite: You've seen this before.
• -D QuaternaryFaults: This too.

If it succeeds you'll get a message: "Successfully Created Layer." After that you should be able to see the new featureclass in ArcCatalog. It will update as you make changes to your original tables and featureclasses. It will make it easy for you to always export your data and share it in the new schema. It will make you love life again.

Make sure to right-click the featureclass and "Register with Geodatabase" if you want to make a WFS that provides your data in the new schema. This is an awesome thing to do because now you're providing your data directly to folks as a standard web service, and whenever they connect to it, it will always be up-to-date.

Please think about that again for a second. You give someone one URL for your WFS, and every time they connect to it they get your most up-to-date information. You also are now providing that information in some kind of standardized data-exchange format, and you didn't have to change anything about your production workflows.

Now let that sink in a bit...

p.s. If you are resitant to the adoption of the NCGMP09 database format for exchanging geologic map data because you don't want to re-tool your shop, you just lost the argument. Do it.