In April 1999, The City of Calgary selected the Esri suite of products and tools to support the revitalized GIS initiative. One of the early challenges for The City of Calgary was the undertaking of an enterprise approach to spatial data management. This presentation will cover many of the discoveries made for converting, connecting and managing spatial data in a traditional enterprise setting. The City of Calgary will discuss some practical strategies for:

• Data replication from legacy GIS to Esri
• Change management in the enterprise
• Interfacing spatial and non-spatial data: mainframe, Oracle and others
• The challenges of CAD migration
• The creating and ongoing maintenance of spatial metadata
• Performance challenges and solutions

In the wonderful and complicated world of Enterprise GIS, challenges to a successful implementation can exist not only with the GIS, but also with the Enterprise. In many ways, the current Enterprise Esri implementation underway at the City of Calgary is no different than in any other large site. In other ways it is much different, with its own unique advantages and challenges. Although there are many components to a successful Enterprise GIS implementation, this paper will discuss some of the current challenges and solutions to Enterprise GIS Data Management at the City of Calgary.

During the 1990’s the City of Calgary has been experiencing a substantial rate of growth. This has resulted in increased demands on all sectors of the City’s resources, and the need to reduce duplication of efforts, or redundancies in operations. This is true also with Information Technology services, and in the sharing and managing of data and IT resources.

The City of Calgary is the fifth largest city in Canada and the third largest municipality, serving over 850,000 citizens within a geographic area of approximately 72,000 hectares (280 square miles). There are approximately 250,000 titled ownership parcels, 5800 km (3600 miles) of paved roads, 400 km (250 miles) of paved pathway, and all associated urban infrastructure typically provided by a Canadian municipal organization.

The City of Calgary employs over 8000 staff providing a wide variety of services to its citizens.

A new Corporate Reorganization is currently underway which has seen the transformation of 22 separate Departments into 5 new Portfolios. These Portfolios and main business areas within them include:

All Portfolios have business activities which will benefit with the implementation of GIS technology. However, managing the creation, maintenance and sharing of GIS information across this broad an Enterprise is a large factor in the successful Esri Enterprise implementation currently underway.

The GIS Center of Excellence (GISCOE) currently exists in the Strategic Services Portfolio, within Information Technology Services.

The City of Calgary has been using digital mapping and CAD functionality since the early 1970’s. During the last 30 years, significant development and customization has occurred within that environment. Currently hundreds of staff throughout all City Portfolios are involved in the creation, maintenance and mapping of data using the CAD environment.

The CAD environment introduced the City of Calgary to some of the challenges in creating and sharing data in an Enterprise situation. Initially, pockets of digital mapping and CAD were developed in certain business areas which were able to realize the benefits of data quality, data management, and mass production of various map products made possible with digital mapping.

Over time, digital files were shared between business areas, but it was often difficult to determine what the most current or updated version of the data was, or where to access the files. In recent years, a CAD read-only server was created to better manage the sharing of CAD files throughout the Corporation.

The City of Calgary has been using GIS functionality since 1974 with the first application introducing point in polygon census geocoding. However, it was not until the late 1980’s that GIS was introduced as an Enterprise solution. A Corporate project was established called the Calgary Spatial Information Management (CalSIM) initiative, tasked with selecting and implementing an Enterprise GIS tool. CalSIM also dedicated a significant amount of effort towards determining Corporate data requirements and developing spatial data management policies to facilitate implementation of the GIS technology.

Consultants were hired to help evaluate the data assets of the City of Calgary at that time, plus determine what additional data was required to help meet the business activities of the various Departments. Through an extensive survey and evaluation process, data was identified and prioritized by use. Data required by three or more Departments was determined to be "core" data to the City.

It was then determined if the "core" data was actually being created maintained by some business unit within the City, or if the data needed to be collected. To provide a consistent methodology and quality for new data to be collected, the City of Calgary chose to implement a citywide Digital Aerial Survey (DAS) program to capture vector data for a significant amount of "core" surface feature data. (Updating this DAS data is now a yearly activity managed by the Engineering and Environmental Services Portfolio.)

As in any organization, stewardship of data assets was identified as a cornerstone for successful Enterprise Spatial Data management. Stewards and maintainers were designated for all the spatial datasets shared throughout the business units of the City. For the most part, stewardship was assigned to the business area most directly involved with that business service.

The concept of meta data was introduced to data stewards, and a collection process for meta data was developed, including identifying Spatial Data Maintenance Plans and transition plans for migrating maintenance of the data into the GIS environment.

By the late 1990’s it became apparent that the existing GIS platform in use at the City of Calgary did not fully address the needs of the Corporation, and Departments were struggling to develop applications. In October of 1998 the City of Calgary undertook an internal survey to determine various departmental needs for GIS functionality. The results of this survey were used to create an RFI, which was sent to several vendors including Esri. After an evaluation and benchmark process, the City of Calgary chose Esri in April of 1999. An implementation process was developed to reestablish GIS within the Corporation. This process also identified the need for a strong organizational component, and created the GIS Centre of Excellence.

The GIS Centre of Excellence (GISCOE) was established for three primary reasons.

• To reestablish credibility in GIS at the City of Calgary
• To prepare a RFI for GIS platform selection
• To facilitate the spread of GIS throughout the corporation.

Specifically GISCOE is responsible for business integration and coordination, GIS investment management, setting data standards and common development methodologies, setting strategic policies, and providing technology leadership.

During the first year of the Esri implementation GISCOE has primarily been concerned with getting the GIS up-and-running and providing quick-hit results with the Esri tools. With a Y2K freeze coming in October of 1999, it was important to demonstrate that GIS could be implemented in a quick and efficient manner.

The following broad topic areas have been the focus of GISCOE during the last 12 months:

• legacy GIS decommissioning
• developing quick hit applications
• data conversion
• development of a GISCOE web page
• creating standard symbology for SDE layers
• GIS training
• setting standards for GIS data and GIS application development
• data modeling.

A high priority of GISCOE was to decommission any existing legacy GIS applications and rebuild them in the new environment. Some GIS applications that have been rebuilt include:

• ParGIS Parks Asset Management. MapObjects applications with data creation and editing capabilities directly on SDE database.
• ESIMS Environmental sites. Mainframe extract to SDE with MapObjects viewing front end.
• ReGIS City owned land inventory. ArcView application to view and query data.

Several other applications will be decommissioned over the next two years.

In order to demonstrate "out of the box" functionality" with the Esri, and gather expertise in developing applications in the various tools a series of ‘quick hit" applications were created and demonstrated throughout the corporation.

• Intranet applications – 3 Intranet applications were built based upon the MapObjects IMS quick starter applications.
• Creation of block face polygons for census data distribution.
• Redistricting ward boundaries
• Integrating GIS with address generated by POSSE. (Public One Stop SErvice – work flow management for planning, landuse, building permits, etc.)

Prior to undertaking any GIS development or legacy decommissioning it was necessary to provide a certain amount of data in native Esri formats. As the majority of the City of Calgary’s data is sourced in MicroStation it was necessary to convert large amounts of CAD in a short time frame, and provide a methodology for daily batch updates to key datasets.

GISCOE targeted 12 major datasets, including the parcel fabric, legal base and street network for the initial conversion. After six months the GISCOE conversion team (one full time programmer, one part time and a consultant) had converted more than double the amount of data originally planned. All this data now resides in the production SDE database with approximately 200 layers.

The initial datasets converted included:

A GISCOE web page was created to share information about the Enterprise implementation of Esri tools at the City of Calgary. The Web page posts information on GISCOE activities, training, data management and white papers documenting GIS standards and methodologies. As GIS is rolled out to the desktops of people who may not have traditionally been involved with mapping or spatial data, it is essential that they be given access to as much information about the technology and the data being utilized within the GIS environment.

The Esri GIS environment is very complex with many different software packages and multiple development environments. It was necessary for the GISCOE staff to undertake a considerable amount of Esri and non-Esri training including the following courses.

• System Architecture Design for GIS
• Introduction to ArcInfo
• Customizing ArcInfo with AML
• SDE Administration
• SDE C API
• Introduction to ArcView
• Advanced ArcView
• Programming with Avenue
• Programming with MapObjects with Visual Basic
• Introduction to ArcInfo using ArcMap, ArcCatalog, and ArcToolbox (Web based)
• Intermediate Visual Basic
• PL/SQL
• SQL Plus

In addition the GISCOE team has been developing internal courses to cover topics not covered in the Esri training. For example, Converting MicroStation Data with AML, Introduction to ArcView with SDE data, and a series of data management training sessions.

GISCOE has also been actively preparing white papers to cover topics that require more discussion than that covered in the courses and in the software documentation. Some of these white papers are:

• What is the Difference between CAD and GIS, and What is Topology?
• Comparison of Esri Vector Data Models.
• Introduction to AML for Existing Programmers.
• Converting MicroStation to Esri formats.

In a large organization the size of the City of Calgary it is essential to set data standards. GISCOE has been actively pursuing standards for our Esri implementation. Some standards are imposed at the database (SDE) level such as layer name and column definitions.

In some cases the limitations of the Esri tools impose their own set of standards that must be adopted to support all Esri products successfully. For example ArcView dBase files only supports a ten character column name and ArcInfo attribute tables only support a 16 character column names. SDE does not directly support integers over 9 digits.

As a CAD centric site, the City of Calgary users are accustom to standard symbology on enterprise data sets. To accommodate this functionality for ArcView users is was necessary to customize the ArcView SDE connection routine (DBAccess extension) to automatically set ArcView legends and display scales upon load from SDE.

Currently, very little spatial data is still actually being created and maintained in the Esri environment. Until this creation and maintenance function is transferred to the Esri environment, conversion and replication of data to the SDE is one of the significant challenges to Enterprise GIS Data Management at the City of Calgary.

As in most large GIS implementations, a significant component of the implementation is converting existing data into the new GIS format. This is also the case at the City of Calgary. Much of the spatial data in existence at the City currently exists in MicroStation (dgn) format. Until the creation and maintenance of this data is actually migrated to the new environment, the data will need to be converted from the current dgn format to ArcInfo coverages which are then loaded into the SDE database as a batch process.

Fortunately for GISCOE the City’s MicroStation data was of extremely high quality. In addition to this the ArcInfo AML conversion process worked better and was easier to implement than first expected. This allowed GISCOE to convert more than double the amount of data originally targeted for phase one implementation.

Key datasets, such as legal base, parcel fabric and street network are updated nightly using an AML process. Thus our SDE database is up to date the day following the MicroStation edit. It will be necessary to maintain this batch replication for a while, perhaps several years, until spatial data maintenance can be migrated into the Esri environment. The City of Calgary has a significant investment in MicroStation, both in trained staff and MDL customization. It would be impossible to move into the Esri editing environment at one time.

The basic order of GIS conversion and replication is:

1. As part of the regular MicroStation maintenance process, an Oracle table is updated with the tile (section) number whenever a tile is edited. This process was in place prior to the introduction of GIS conversion.
   
2. The modified MicroStation tiles are converted to coverage. A Batch process that runs at night that checks the Oracle table and only converts the modified tiles. Each feature is flagged with the tile number. When the coverage is loaded into SDE this tile number is loaded as an attribute.
   
3. The development SDE layer is checked for the Tile ID and the affected features are deleted from SDE.
   
4. The coverage is loaded into the development SDE instance with AML LAYERIMPORT or SDE COV2SDE. This is also a nightly batch process.
   
5. Error log files are created and checked the following morning. If errors occurred during the conversion, the development SDE layer is backed out and re-imported from the previous night’s export file.
   

6. Shapefiles are generated and exported from SDE. A batch process creates and stores these shapefiles on a shared access server. This is necessary to support ArcView queries that are not fully supported on SDE layers.
   
7. The Development layer is exported for nightly SDE backup. This is independent of a nightly Oracle backup. This is also a batch process running every night.
   
8. Finally the exported development layers is import into production through an additional batch process that occurs every night.

The result is that a MicroStation file that is edited on Monday will be converted to coverage and loaded into the SDE development instance on Monday night. On Tuesday night the layer is exported from SDE development and imported to SDE production. The delay of one day is required to ensure GIS quality. This process allows GISCOE to maintain a production SDE server that is never modified during working hours.

The topological structure of ArcInfo coverages has allowed the GISCOE conversion team to flag errors encountered during conversion and report these to the MicroStation data stewards. Many of these errors were unknown or would have been very difficult to locate in the CAD world. The result of this has been to reverse translate some converted datasets back to MicroStation to act as the new source. While it would be preferable to maintain the data directly in the GIS environment this is not possible at the current time.

During the conversion process several log files are generated that report any topology errors encountered. The log files are then checked every morning. This has allowed GISCOE to maintain a high degree of confidence in the conversion process. Before and after the SDE import, a batch SQL script runs to check Oracle record counts within the SDE database. If the record count difference exceeds an imposed limit the SDE is checked.

During the previous ten months that the conversion has been operating in a batch mode the only errors that have occurred were due to network or server failures.

All the GISCOE AML conversion development has followed a set of guidelines for code format and modularity. This has allowed GISCOE to rapidly convert new datasets based upon existing AML templates and methodologies.

While the initial import of MicroStation data into ArcInfo is a relatively simple process, the amount of processing required by individual datasets can vary considerably. Post import processing can range for simple building of topology to processing multiple MicroStation imports and multiple GIS overlays to achieve the desired results. In many cases it has been possible to get much more GIS useable data out of a CAD dataset than originally expected.

The Enterprise GIS implementation is not happening in isolation of many other Corporate Information Technology activities, and must compete for technical architecture resources with many other major IT initiatives underway at the City. GIS implementation must fit within the existing hardware and network environment already in place.

The current Oracle/SDE configuration used at the City of Calgary is comprised of SDE 3.0.2.2 and Oracle 8.1.5 on Digital Unix 4.0e operating system. During the first half of 2000 the environment will be upgraded to support Oracle 8.1.5 and ArcSDE 8.0

The Esri database environment consists of 4 Oracle instances: Production, Development, Application Development and Test. This Production instance currently shares the UNIX server with a production PeopleSoft instance which is not an ideal setup. The remaining instances are on a separate UNIX server. All SDE instances reside in the Compaq HA Cluster configuration which provides automatic server fail-over capability. In the HA cluster configuration, the failure of one UNIX server will trigger the automatic recovery of those affected Oracle instances onto one of the available Compaq HA machines.

Existing Oracle security mechanisms are deployed to service the security needs of the SDE environment. Unique Oracle userids are established and provided with the required SDE layer access privileges (select, update, delete, insert) through existing Oracle security mechanisms.

Currently only one schema owner has been deployed to provide the layer creation needs of the GIS environment. As more business units take on the stewardship responsibility for providing their own data to the SDE environment, corresponding schema owners will be required to service and secure the needs of these groups within the Oracle environment.

The Oracle database has daily cold backups, which are maintained for an indefinite length of time. In addition there are weekly Oracle exports. SDE export files are created nightly and maintained for several weeks. One monthly export is archived indefinitely. The SDE exports allow GISCOE to recover on a SDE layer basis, which would not be possible with Oracle backups alone.

SDE performance has been strictly monitored to prevent server overload. Performance benchmarking and historical statistics are generated on a frequent basis with PATROL performance software. Currently there are over 100 ArcView and several ArcInfo installations. By mid-summer there will also be an addition 500 connections from a MapObjects application.

With an inexperienced and rapidly growing user community at the City of Calgary it is necessary to be proactive when it comes to database tuning and performance. Testing has indicated that the largest SDE performance gain can be had by simply restricting the layer minimum and maximums view scales. Many users can simultaneously hit a large SDE layer at an appropriate scale with little impact on the database. However, just one user hitting a large layer at full scale, can maximize the CPU to 100%. While the CPU spike is only for a short duration for a single user, several users making the same query could literally bring the database to its knees.

MapObjects applications do not represent a large threat to performance because the applications can restrict the theme view scales through coding. However, ArcView users represent a potential problem. As an open system, ArcView allows users to make any query they want and view data at any scale they want. In an Enterprise installation, an open system rolled out to hundreds of inexperienced users is a recipe for disaster.

To minimize this performance risk, GISCOE has rewritten the DBAccess extension to set the view map units and prevent layers from displaying at full scale. A metadata table has been added to the SDE schema which has a record for each layer. The table also stores a minimum and maximum scale setting, and a CLOB (Character Large OBbject) which contains an ArcView legend file. When the user loads a layer from SDE the theme minimum and maximum view scales are automatically set and the Oracle CLOB is unloaded to a local ArcView legend file, which then sets the theme legend. The scripts used to create Oracle metadata table and modify DBAccess extension have been uploaded to the Esri ArcScripts web page. The script is named Automatic Default Legends for SDE Themes.

GISCOE has also implemented all the standard SDE tuning suggestions such as tuning layer grids, eliminating extents, setting consistent x,y offset and xyscale values throughout the database, setting layer extents and performing standard Oracle tuning.

By combining the SDE and Oracle tuning with the ArcView layer scale restrictions we are confident that we can achieve a 1 to 2 second maximum response time, within the targeted scale limits, fir every layer in the database. This has been achieved without implementing turbo layers. Due to the City’s nightly batch update schedule turbo layers do not appear to be appropriate.

The City of Calgary has operated a large mainframe environment for many years. Another major initiative is underway to decommission this legacy environment by 2^nd quarter of 2002. This involves replacing 218 applications, some of which will now be "spatially enabled", or rebuilt as GIS applications.

The separation between spatial (CAD) data and tabular business data (mainframe) continues to be a challenge to maximizing the value of the data assets at the City of Calgary. Integrating newly developed or purchased applications with GIS will enable much better use of the data. Reengineering the business processes for the simultaneous creation of both the spatial and attribute data related to the business feature will increase the quality of the data being produced in the process.

In some cases, there is nothing common in both environments to allow the joining of the data. The use of free form addresses in the mainframe environment also complicates the task of integrating spatial and tabular data.

The organizational structures in existence at the City of Calgary makes sharing information about the GIS implementation a critical component to managing the Enterprise GIS data. It is very important that easy access to meta data and mechanisms for managing changes to data be in place for the GIS user community. GISCOE has created a Home WEB Page as a point of entry for staff at the City to obtain GIS related information.

Two of the key components of the data management infrastructure are the GIS meta data environment and the GIS Data Change Control mechanism.

In a large GIS implementation, managing changes to any of the data components of the implementation is a key requirement in ensuring success. With the creation and maintenance of data occurring in so many places within the organization, and in a different spatial data creation environment, managing of changes to the source data is imperative.

The current mapping and spatial data management environment is not GIS centric. GIS is not driving the business, but rather GIS is being introduced as a tool that can help business units manage their business, as it should be.

Within the GISCOE Home Page, a link was established to a GIS Data Change Control mechanism. GIS users can notify GISCOE of errors or questions they may have of the data being provided. Data stewards can notify GISCOE of any changes they are planning to the source data, which can then be passed on to the GIS user community. This Data Change Control function allows the GIS user community to interact with GISCOE, and the data GISCOE is providing in the GIS environment.

A second major Enterprise data management "infrastructure" developed in GISCOE is the populating and publishing of meta data for GIS data.

GISCOE is utilizing the ArcCatalog component of ArcInfo8 as the meta data repository for data provided in SDE. GISCOE staff are currently populating valid meta data (in conjunction with the source data stewards), using a custom City of Calgary stylesheet. This information is then exported from ArcCatalog and posted on the meta data component of the GISCOE WEB page.

The ArcCatalog is proving to be an excellent meta data environment, and can be used until full ArcInfo8 implementation is possible.

Due to the City of Calgary’s batch replication environment it will be difficult to implement the GeoDatabase. Until we are able to move our maintenance functions into the GIS and out of MicroStation we will be unable to take full advantage of the GeoDatabase. We do expect to migrate the maintenance of some datasets within the next year, however, we intend to move with caution. Third party extension to Arc 8 such as ArcParcel, ArcSurvey and ArcFM are among the solutions we will be evaluating.

As previously mentioned, while the Enterprise Esri implementation currently underway at the City of Calgary is probably similar to many other large GIS implementations, there are unique advantages and challenges facing the City. Managing the data resources in the Enterprise implementation has its own unique challenges and advantages as well. Some of these include:

1. The current spatial data maintenance environment at the City of Calgary is primarily MicroStation. Although the many years of use and development within this environment has provided the City with extremely high quality data assets, it will take time and money to migrate the maintenance to the Esri environment. This is especially true for those business areas with hundreds of staff already trained in MicroStation, and with substantial development already in place for producing those spatial datasets.

Conversion of data from MicroStation to SDE is not difficult, but the conversion and replication activities required to provide quality, up-to-date data to the SDE adds a large layer of complexity to managing the GIS data being provided.

2. Stewardship of the massive amount of data required in the GIS environment is spread throughout many business areas within the City of Calgary. While it makes sense to have data originating in the business area most closely associated with the data, it is not always easy or possible for that business area to anticipate all the other Corporate requirements for the data they are providing. Current budget allocation processes do not necessarily allow adequate resources for an Enterprise view of the data to be provided by a specific business area.
3. Enterprise GIS implementation must compete with many other major Information Technology initiatives underway at the City of Calgary. This applies not only for staff resources, but also for technical architecture requirements such as servers, access to GIS data from remote locations, and what Esri products are best suited to the particular business needs of a Portfolio. This also affects how the data is provided and what performance implications will exist with the chosen Esri tools.

The Esri Enterprise implementation is well underway at the City of Calgary. GIS is definitely reestablished, and spatial data management "infrastructure" is being created to help facilitate the best use of the extensive data assets in existence at the City.

Author Information