Integrating ArcSDE with Oracle Spatial/Oracle DBMS:  

Accessing Enterprise Geosciences Data through ArcGIS

 –Gulf of Mexico Example

 

 

 

 

Ning Li

 

 

 

 

 

GIS Workshop Summer 2004

 July 31, 2004

 

 

---

 

 

---

 

Abstract

 

This project conducted configuration of ESRI ArcSDE with Oracle database at the Geographic Information System Program Lab, University of Texas at Dallas.  It conducted ArcSDE implementation and investigated the methods in loading, storing and retrieving Geographic and oil and gas production data in Oracle DBMS via ArcSDE.  A preliminary Geosciences database is created in Oracle and ArcGIS is used as a tool to display, retrieve and analyze the Gulf of Mexico oil and gas production data downloaded from U.S. Minerals Management Services.  In order to improve the ArcGIS performance and enhance the data analyzing capability, Oracle Materialized Views are created to record the aggregated data based on the raw production data.

 

Introduction

 

Data integration, data sharing and data quality control are challenging issues that today’s Geosciences community are facing, particularly in the upstream oil and gas Exploration and Production (E & P) industry in which the scientists and engineers are dealing with spatial data daily.  Geographic information system has been used as an effective tool in the oil and gas searching activities since 1988 (Watson and Witherbee, 1988; Witherbee and Watson, 1988) .  ArcGIS is rapidly accepted as a standard GIS tool in the E & P community.  The integration of ArcGIS with Enterprise DBMS software has emerged as a promising solution for solving some of the problems in the industry.  This project conducts a preliminary research on this topic by starting with a less complex Geosciences database in Oracle DBMS, loading necessary spatial data including map, well, and production data into the database, and integrates ArcGIS with Oracle via ArcSDE.  Map displaying and data retrieval from ArcGIS is used to test the integrated system.  The methodology utilized in this project mainly involves ArcSDE configuration with Oracle, logical and physical implementation of the Geosciences database, Data Aggregation/Summarization inside Oracle, ArcGIS data retrieval, and displaying on the system.

 

 

Problem Statement

 

The IT/Data Management Teams in today’s Oil and Gas upstream industry are facing significant challenges in managing spatial Geoscience data and providing quality services to their Geoscientists due to the reality of segregated data sources and various data format that are typical for this industry.  Managing explosive data due to the deployment of new exploration technology, such as seismic and electronic well logging is another contributing factor to the challenge.  Furthermore, the merger activities among oil companies in the U.S. in the past few years worsening the problems.

 

The recent advances and standardization in the GIS and Database Technology shed a light on the solution in providing integrated data management services, data quality control, and allowing knowledge sharing among Geoscientists.  These days, data Management professionals in the industry are conducting or proposing GIS portal implementation on their companies’ master repository databases.  The key to the success of those projects heavily rely on the seamless integration between the GIS analyzing tools and the back end relational DBMS, and how well DBMS can handle spatial data.  Due to the facts that ESRI is the major vendor in GIS application and ArcGIS become standard GIS tool in the E & P industry, and Oracle DBMS servers are widely used in the oil and gas industry, this project investigates the methods to integrate ArcGIS with Oracle DBMS or perhaps Oracle Spatial features by using ArcSDE. 

 

Literature Review

 

Geographic Information System has been used in Oil and Gas exploration study for about sixteen years since Watson and Witherbee (1988) first reported their research on GIS application in Oil and Gas Database Management.  The rapid development of GIS technology has attracted more and more people in the Oil and Gas industry to seriously apply this tool in their natural resource searching activities (Moore, 1991; Miller, 1992; Froomer etc, 1993; Boltze and Rosenbaum, 1994; Ulmasvai, 1996; Comet etc, 1996; Boltze and Rosenbaum, 1996; Grace and Walsh, 1997; Park, 1997; Panetta etc, 1998; Nagihara etc 1999).  This application is gradually accepted as a valuable tool in the Oil and Gas searching activities along the maturity of the application. 

 

Due to the complex nature of Oil and Gas exploration and production activities, the Geoscience data collected and accumulated in the industry tend to be in a variety of formats, they can be structured, unstructured, and semi-structured (Hoeveland et al., 2004).  Those E & P data, such as geologic map, seismic section, wells and Geoscience documents could be scattered in a lot of different computers in E & P companies.  Data sharing and data integration are major issues in the E & P companies (Moore, 2004; Pfitzinger, 2004).  To resolve the problems, many companies and software vendors are promoting standard data model/master data repository concept and GIS portal for the benefits of data integration, data exchange, data sharing/knowledge sharing and data analysis (Meroney, 2004; Grise, 2004; Hopkins, 2004).  In order to standardize the data management, Public Petroleum Data Model Association (PPDM) sponsored by the industry has been actively building Petroleum Data Model and PPDM Spatial for ArcGIS (PPDM Association Spring Conference 2004, Grise, 2004).  In the mean time, a separate ArcGIS Geology Data Model has also being developed by U.S., Canada and other countries Government Agencies and ESRI (Grise and Brodaric, 2004). 

 

In responding the industry’s need, software vendors have added Enterprise GIS features by providing the DBMS gateway tool ArcSDE (ESRI, 2002, 2003), and Spatial features within DBMS, such as Oracle Spatial (Oracle, 2002).  According to ESRI, the first ArcSDE was introduced in 1999 as version 8.0.1 (See detail info at arcsde_life_cycle.pdf).  According to the most recent data management conference presented in the E & P community (8^th International Conference on Petroleum Data Integration, e-Commerce and Data Management, 2004), the integration of GIS and DBMS technology is just in the early stage with promising potential in the E & P industry.  

 

Several projects have been reported on integrating ArcGIS with Oracle database in the industry and government agencies. These projects include:

·        Accessing Business Critical Information through a GIS web Portal: ConocoPhilips Inc. – 8^th International Conference on Petroleum Data Integration, e-Commerce and Data Management, 2004

·        U.S.G.S. National Oil & Gas Assessment On-line (NOGA Online), Leveraging ArcIMS and the Worldwide Web, 2002 ESRI Petroleum User Group

·        Public Petroleum Data Model (PPDM), PPDB Spatial, GIS Geology Model

 

Integrating ArcGIS with RDBMS will greatly benefit the industry, considering ArcGIS and Oracle are the dominant technologies that are widely used in the industry.  This will provide

·        Better data integration and data sharing

·        Better user access and privileges control

·        More data access to difference facets of geosciences data

·        Better application scalability

·        Better backup and recovery techniques

·        Better SQL data manipulating capability inside RDBMS than in ArcGIS

 

Data Description

 

U.S. Department of Energy reports that Gulf of Mexico Region is the most important region for U.S. Oil and Gas production that is consisting about 40% of U.S. oil reserve (United States Country Analysis Brief).  U.S. Minerals Management Services provides free oil and gas production data and Geographic Mapping data for the Gulf of Mexico Region. (http://www.gomr.mms.gov/homepg/pubinfo/freeasci/freedesc.html).  Abundant data in the Gulf of Mexico region are available to the public that is ideal for this project need.

 

Data used in this project include

·        Geographic Map data

These files were downloaded from the MMS Geographic Mapping Data . The original data format is in Arc Info e00 format so data conversion to shapefile format was conducted using ArcToolBox. The shapefiles used in this project are

district_boundary (mms_district_bnd_metadata.htm)

mms_actls_plg (mms_activels_metadata.htm).

To give an orientation about Gulf of Mexico, a shapefile for the Gulf Coast States (states_met.zip) were downloaded from http://sdms.nwrc.gov/pub/ngom/ngom.html

·        Borehole ASCII file

This borehole ASCII file is downloaded from the MMS web site Well Information and Data.  The borehole ASCII format is described in detail at MMS web site: Summary File for Boreholes, and then Borehole file is converted to ArcGIS shapefile for map displaying.

·        Oil and Gas production data

Oil and Gas production data from 1996 to Mar, 2004 were downloaded from MMS web site: Production Information and Data and the information about those production data is available from Summary File for OGOR

 

Methodology

 

1.      Configuring ArcSDE Service

1.1  Working in the Oracle side that needs Oracle DBA privileges.

       The following procedure is based on ArcSDE Administration for Oracle (2001),   

       ArcSDE Configuration and Tuning Guide for Oracle (ESRI, 2002),

Get appropriate user accounts setup in UTD lab, including Oracle DBA privi1eges for the database that going to be used for this project.  In this case GEODB is selected as the project database.  In the mean time, the windows user login account at least should have Administration priviledges.  If ArcSDE has already been installed earlier, as in this study case, then the user account should be the same user account used in the first installation.

1.1.1        Determine to use the existing GEODB database in windows 2000 server host named Bruce. Oracle RDBMS version is 9.2.0.1

1.1.2        Create SDE tablespace in GEODB database

1.1.3    Create Login role and Data_Owner role in GEODB database based on ESRI documents. The two roles contain numerous system privileges that allow ArcSDE to create necessary objects inside Oracle Database.

o       Data_owner (for specific privileges see data_owner_role.doc)

o       Login role (for specific privilege see Login_role.doc)

o       Grant execute on dbms_pipe and dbms_lock to public role in GEODB

 

1.1.4        Create SDE user account in GEODB and set SDE tablespace as SDE user’s default tablespace and then grant Data_owner and Login role to user SDE.

Other privileges (see detail in Other privileges granted to sde user.doc) due to the first time SDE objects creation sdesetupora9i.exe failed

           

1.2      Configuring ArcSDE Service from the Windows server side

1.2.1        Architecture of the ArcSDE

In UTD lab, the current windows server Bruce has multiple Oracle databases set up and there is already an ArcSDE default home and SDE service called dpddata_SDE.  This sde service default home directory is I:\arcsde\ArcSDE\ora9iexe and it is set up for DPDDATA database.  In order to set up a second SDE service, a new sdehome directory needs to be set and some files in the system need to be modified.  The architecture of the system is similar as described in Figure 1.

1.2.2        Create a new SDEHOME directory. The default SDEHOME is created

during ArcSDE software installation. Copy the default SDEHOME directory folder I:\arcsde\ArcSDE\ora9iexe to a new directory I:\arcsde\ora9i.

1.2.3    Define the new SDE Service name GEODB_SDE

1.2.4    Configure %SDEHOME%\etc\service.sde file by adding a new entry

            GEODB_SDE 5152 

1.2.5        Add a new line GEODB_SDE 5152/tcp to the windows services file \System32\drivers\etc\services

 

Fig. 1. ArcSDE Architecture in the Server Side.

 

1.2.6        Create the new ArcSDE service GEODB_SDE by issuing command

                          sdeservice –o create –p sde –l bruce –H I:\arcsde\ora9iexe

                           –d ORACLE9I,GEODB –I geodb_sde        

                          Note: For help on sdeservice command type sdeservice –h from the

                           command prompt to see the meaning of those options.

1.2.7        Check the new SDE service by issue sdeservice –o list from the command prompt

1.2.8        Make sure the ArcSDE DBA password is the same as the SDE user

            password in Oracle

1.3      Create sde data dictionary in GEODB database

            by running sdesetupora9i.exe in the new %SDEHOME%\bin\directory to create

            SDE objects in GEODB database

1.4      Startup geodb_sde service by

            sdemon –o start –I geodb_sde

1.5      Connect ArcCatalog with GEODB.

Open ArcCatalog -> Expand Database Connections _Add Spatial Database Connection to setup the connection between ArcCatalog and GEODB database.

 

 

                     Fig. 2. Connecting Spatial Geodatabase from ArcCatalog

 

2.      Loading Geographic data into Oracle database as SDE Layer

This is done by right clicking on the selected shapefile that you want to export and then select export->shapefile to geodatabase and then export the file to Oracle GEODB (Figs. 3, 4).

 

 

          Fig. 3. Loading shapefile to Oracle database via Database Connection

 

Fig. 4. ArcSDE layers displayed in ArcMap.

 

3.      Load Geographic Data into Oracle Database as Oracle Spatial Format

The purpose of Oracle spatial data exercise is to understand how Oracle Spatial manages spatial data and if ArcGIS can access Oracle Spatial data.  Based ArcSDE Configuration and Tuning Guide for Oracle (ESRI, 2003), ArcGIS should be able to access both Oracle Spatial table data and ArcSDE layers from Oracle database, but the configuration need to be planned first before SDE metadata is created inside Oracle. SDE’s default layer format is preconfigured inside the %SDEHOME%\ect\dbtune file. If the preconfigured DBTUNE paraters are loaded into SDE’s meta data DBTUNE table, SDE allows to export the DBTUNE table, reconfigure the DBTUNE parameters in %SDEHOME%\ect\dbtune file. However, my account was not able to export the DBTUNE data so I can’t reconfigure the DBTUNE parameters to let ArcSDE read Oracle spatial tables. Below shows the steps how shapefiles are loaded into Oracle spatials.

 

3.1  Convert e00 files to Coverages via ArcTool box: Blocks, District_Boundary, Active_leases. Then convert these files into shapefiles.

3.2  Create a user account called MMS inside Oracle GEODB database to host Oracle Spatial tables and production data from MMS.

3.3  Follow the instruction from Oracle provided document (using_shp2sdo.txt) to convert the ESRI shapefile into Oracle formatted files.

3.3.1        Run shp2sdo.exe to convert ESRI shapefile into Oracle readable files.

 

3.3.2        Oracle spatial table creation scripts and SQL*Loader control files are automatically generated (Fig 5). (active_ls.sql, load_active_ls.txt; create_boreholes.sql, load_borehole.txt; create_distr_bnd.ctl, load_distr_bnd.txt; create_gom_sts.sql,load_gom_sts.txt).

Note: some of the .ctl files contain real data some are not. This depends on the options used when executing shp2sdo.exe file

 

Fig. 5. shp2sdo file generated scripts (see bottom of the two lines)

 

3.3.3        Create the Oracle spatial tables and load the data by using scripts created in step 3.3.2.  Again, follow the instruction from Oracle provided specific instruction on how to use the generated scripts and load the data into Oracle Spatial (using_shp2sdo.txt).

 

3.3.4        Update the GEOM column in Oracle spatial tables to set SDO_SRID

Example:

UPDATE BOREHOLES A SET A.GEOM.SDO_SRID=8307;

Note: SRID: Each coordinate system is identified by a unique number

          (SRID). An unique spatial reference ID number.

SDO_SRID Unique Spatial Reference ID, 8307 = WGS 84

Some common coordinate systems include:

 

                        Geodetic

                        -NAD83 (SRID=8265)

                                -WGS84(SRID=8307)

 

3.4.5        Update USER_SDO_GEOM_METADATA table to set SDO_GEOMETRY data type values for each spatial table in the database.

Example:

update user_sdo_geom_metadata set srid=8307

                        where table_name='BOREHOLES'

                        and column_name='GEOM';

 

Adjust TOLERANCE and coordinate system bounds if required (almost always required for geodetic data)

 

update user_sdo_geom_metadata a

                        set a.diminfo=

                        mdsys.sdo_dim_array(

                        mdsys.sdo_dim_element('X', -100, -80, 0.0000005),

                        mdsys.sdo_dim_element('Y', 20, 32, 0.0000005))

                        where table_name='BOREHOLES'

                        and column_name='GEOM';

 

3.4.6        Validate layers by using Oracle provided VALIDATE_LAYER_WITH_CONTEXT PL/SQL procedure

                                begin

                        sdo_geom.validate_layer_with_context

                        ('BOREHOLES', 'GEOM', 'VALIDATION_RESULTS

                        end;

                         /

 

3.4.7        R-Tree indexing for 2 dimensional data. Oracle requires the Oracle spatial table being indexed to improve performance.

                        create index boreholes_indx on boreholes(geom)

                          indextype is mdsys.spatial_index

                        parameters('layer_gtype=point tablespace=mms_index');

3.4.8        Launch Index Advisor to see how the rendering performed and adjust the Geographic Boundary

 

 

4.      Loading business data into the database via SQL Loader

4.1  Create attribute table: partitioned production table and load the data via SQL*Loader (See the PRODUCTION table creation script at create_production.sql )

4.2  Create staging table so that the data could be loaded exactly in the same format as the original data (create_production_stage.sql) and then load the data into staging table (load_prod_stage.txt)

The reason we want to create a staging table is that the original data does not have

a primary key column. From relational database data integrity and performance

consideration, a primary key WELL_PRD_CD column is added in the

PRODUCTION table.

4.3  Load the data from production_stage table to production table (prod_stage_to_prod.sql)

 

5.      Create Materialized View for manipulating and summarizing the original data to overcome less flexible data manipulating features in ArcGIS (see scripts in create materialized view log on production.doc, create materialized view log on production.doc). Note: Materialized View will need to use diskspace.

 

create materialized view log on production

with sequence, rowid

(well_prd_cd, lease_nmb, complet_nm, prod_dt, days_on_prd,

product_cd, month_o_prd, month_g_prd, month_wtr_prd,

api_well_nmb, well_stat_cd, area_blk_bot,

operator_nm, field_nm_cd, inject_vol, prd_interv_cd,

first_prod_dt, unit_agt_nmb,

unit_aloc_suf, year)

including new values;

 

 

create materialized view lease_yearly_mv

pctfree 0 tablespace mms_mv

storage(initial 8k next 8k pctincrease 0)

build immediate

refresh complete

enable query rewrite

as select lease_nmb, sum(month_o_prd) oil, sum(month_g_prd) gas, sum(month_wtr_prd)water, decode(sum(month_o_prd), 0, 99999, sum(month_g_prd)/sum(month_o_prd))"gas/oil", decode(sum(month_wtr_prd), 0, 99999, sum(month_o_prd)/sum(month_wtr_prd)) "oil/water", decode(to_number(to_char(prod_dt, 'YYYY')),1996,1996,1997,1997,1998,1998,1999,1999,2000,2000,2001,2001,2002,2002,2003,2003) Year from production

group by api_well_nmb,  decode (to_number(to_char(prod_dt, 'YYYY')),1996,1996,1997,1997,1998,1998,1999,1999,2000,2000,2001,2001,2002,2002,2003,2003)

order by lease_nmb, year

 

      Note: a. The Materialized View lease_yearly_mv will be

               refreshed if the mater data table changed.

            b. There is another simpler SQL coding for this purpose but

                this one seems run faster.

 

The Materialized View will aggregate PRODUCTION table data so that data can be displayed and manipulated easily in ArcMap, that is, let the data manipulation work done inside database if large amount of data are involved in calculation.

 

For Example, as we know, the PRODUCTION data is recorded by month for each well with associated leasing number.  To answer the question ‘What is the annual oil and gas production with each lease area in the past 8 years?’ You need to sum the monthly production and then group by lease and year.

 

It is not easy to use GROUP BY in ArcGIS calculation, nor with more than one

      GROUP BY parameters.  However, it is easy to do it by SQL functions provided

      in Oracle.

 

      What if a few records changed in the raw PRODUCTION table due to data update?

 

Oracle Materialized view can be updated automatically so your summary data is still going to be correct when you use it.  In ArcGIS, you need to recalculate by the hard way.

 

6.      Joining tables and creating Oracle View before retrieving the raw data table into ArcGIS.

 

Like Materialized View, using join and create view inside Oracle will be quite useful in future GIS data analysis study when the relational database model is getting complex.  Unlike Materialized View, View does not utilize diskspace and is based on the parent/master table.  ArcGIS join function at this moment does not allow user to filter out much unneeded attribute in business table during the join. However, this is the strength of RDBMS.  Oracle allows users to create View to join tables and filter out information the users do not use or do not need to know in return save user’s time and could be widely used by other users.  In this project, due to the fact that the project is in the preliminary stage that no other major Geosciences attribute tables being used, pre-ArcGIS data joining is not applied.  

 

Results

 

The MMS schema created inside GEODB contains all of the Oracle Spatial Tables GOM_STATES, DISTR_BND, BOREHOLES, ACTIVE_LS, MMS_BLOCKS, business attribute table PRODUCTION, and numerous Materialized Views. The Materialized Views summarized each well’s annual oil, gas, water production and oil/water, gas/water ratios (WELL_YEARLY_MV), the oil, gas, water annual and total production and oil/water, gas/water ratios based on each lease (LEASE_YEARLY_MV, LEASE_TOTAL_MV).  Nine leases were found having total oil production over 50 million barrels oil production from 1996 to 2003 with Shell has a most successful lease, lease_number G07963. From 1996 to 2003, Shell lease G07963 produced impressive 292,226,569 barrels of oil (table 1).  The two maps in the next two pages show well distribution and the total oil and gas production at lease level.

 

Table 1. Top Lease and Operators with Total Oil > 50 million barrels 1996-03

Lease_Number	Operator	Oil (barrels)	Gas (oil equiv)
G05868	Shell	104,009,703	171110885
G07493	Shell	70,267,188	405838457
G07963	Shell	292,226,569	26,067,967
G08241	Shell	73,849,718	195,562,039
G09771	BP	57,634,562	81,863,407
G12119	ChevronTexaco	54,649,138	58,904,075
G12136	Marathon	86,996,306	71,864,324
G12209	BP and Marathon	117,623,360	241,838,693

 

 

 

The shapefiles loaded into GEODB are MMS_BLOCKS, GULF_STATES, DISTR_BND(MMS district boundary), CON_SHEL_BND (Continental Shelf Boundary), BOREHOLE, ACTIVE_LEASE layer. They were loaded under SDE schema.