Python Tip: Using ArcSDESQLExecute to access ArcSDE data faster

In a previous post titled “Using Python to access ArcSDE data faster with cx_Oracle” I described a Python module that allowed faster access to ArcSDE data stored in an Oracle database.  Another method exists that will allow you to access ArcSDE data with pure SQL queries using the ArcScripting geoprocessing libraries.  We can accomplish this by creating the ArcSDESQLExecute object.

To help demonstrate the capabilities of this function, we will create an example that queries the ArcSDE database and then uses some Python functionality to generate a CSV file from the results.  Start by importimg the necessary libraries and creating a geoprocessor object:

import arcgisscripting, os

gp = arcgisscripting.create(9.3)

Next create the ArcSDESQLExecute object.  The second parameter is the path to the SDE connection file:

sdeConn = gp.CreateObject("ARCSDESQLEXECUTE", "Database Connections\\GIS.sde")

Now let’s create an SQL statement and execute it.  Note:  This function will execute any valid SQL statement that you have permission to perform, including Updates and Deletes.  This is an excellent way of performing fast attribution changes.  However, use caution when modifying data:

sql = "SELECT OBJECTID, ROADNAME FROM ROADSEGMENT"

sdeReturn = sdeConn.Execute(sql)

The next section of code will generate a CSV file so that the output can easily be opened in Excel or Access.  You can modify it to any type of TXT file you wish.

file = open("output.csv", 'w')

for row in sdeReturn:

    line = ""

    for i in row:

        line += str(i) + ","

    file.write(line + "\n")

Finally, close the file and delete the ArcSDESQLExecute object:

file.close()

del sdeReturn

You will see that this function performs SQL queries much faster than the other geoprocessing tools because it uses the RDBMS to execute them.  ArcSDESQLExecute can even rollback transactions.  For more information see the ESRI documentation.

Using GDAL with ArcGIS Explorer

ArcGIS Explorer has extremely limited functionality right out of the box. If you are looking to do anything other than simply viewing data, you may find it very difficult with the ArcGIS Explorer api and standard .NET libraries. Fortunately, there are many open source GIS libraries that can be used to fill the gaps where the api is lacking. One such library is GDAL / OGR. These libraries can be found in the FWTools software package and are used in various commercial applications.

Much of the ArcGIS Explorer functionality is provided by these exact libraries. Therein lies our problem. The fact that the libraries are used in the application is not a problem in itself. The problem typically surfaces when you download the latest version of the library and attempt to use with Explorer. You may run into all sorts of odd problems, with the most common being a "dll not found" error. This error can be very misleading. The first reaction might be to see if the dll actually exists. If you check, you will find that it DOES exist, and it is in a location where the application should be able to find it. So why does Explorer give a dll not found error? Welcome to dll hell. For many a COM developer, this is familiar territory, but not so for .NET developers. In .NET, dll hell has been all but eradicated.

For those unfamiliar with dll hell, the short explination goes something like this: windows has a specific order in which it looks for dll's. It typically starts with the directory containing the application, after which it will search through each of the directories in the path variable. During this sequence, it will load the very first dll it finds with a matching name. "So what happens when the dll it finds is not the one my application is expecting?" you ask. This, my friends, is dll hell. I cannot tell you specifically what will happen in your case, but chances are it will not be good.

So why are we experiencing it here? Well, GDAL is not a native .NET library. It is written in C/C++ with .NET wrappers built on top. To some degree, the "dll not found" error is accurate, because it did not find the version of the dll it was searching for. So how do we fix that? Well, the simple answer is to simply use the same version of GDAL that is used by the main application. What is good about this is that you will not even need to include the dlls with your deployment, since they already exist within the application. You will have to include any of the .NET wrappers you use, however.

One roadblock that you may run into is finding the right version of GDAL. In the case of ArcGIS Explorer 10, GDAL 1.6.1 is used. Tracking down the right files can prove troublesome, since the version you need may not exist in binary form, which means you may have to download the source and compile it yourself. Since GDAL is typically built using nMake files, this can be quite a chore for the inexperienced. Fortunately, I have already done this for you, and I'm providing the .NET dlls for your convenience here.

If the version of GDAL that is included with the version of the application you have is different than the one posted here, you can find the instructions on how to build from source here.

Using GDAL with ArcGIS Explorer, you can potentially add on a mountain of functionality that does not exist natively within the application. For those who need some additional functionality but cannot afford to pony up the dough for a full blown ArcGIS license, this may be the solution you are looking for.

Using ArcObjects for coordinate transformations in Python

Some of you may find yourselves in situations where you may need to develop in Python but are limited by the existing geoprocessing methods available.  Luckily, there is a method of accessing the ArcObject COM libraries through Python.  The comtypes package allows you use all of the methods available to .NET developers.  The presentation Using ArcObjects in Python gives an excellent overview of how to accomplish this.

I’m going to walk you through a specific example using comtypes.  In this example, we will perform a coordinate conversion on a point from a Geographic Coordinate System to a Projected Coordinate System.  To get started, download and install comtypes.  Once this is complete it’s time to start coding.  We’ll start off by defining four functions.  These will make more sense later.  First get the COM library path:

def GetLibPath():

   import _winreg

   keyESRI = 
      _winreg.OpenKey(_winreg.HKEY_LOCAL_MACHINE,

                        "SOFTWARE\\ESRI\\ArcGIS")

   return _winreg.QueryValueEx(keyESRI,  
          "InstallDir")[0] + "com\\"

Next we need to create a procedure to get the ArcObjects module:

def GetModule(sModuleName):

   import comtypes

   import comtypes.client as comc

   sLibPath = GetLibPath()

   comc.GetModule(sLibPath + sModuleName)

Create a function that creates a new object:

def NewObj(MyClass, MyInterface):

   import comtypes

   try:

        ptr = comtypes.client.CreateObject(MyClass,
               interface=MyInterface)

        return ptr

   except:

        return None

Finally, create a function for casting objects:

def CType(obj, interface):

   try:

        newobj = obj.QueryInterface(interface)

        return newobj

   except:

        return None

Now we need to define the main function to perform the coordinate transformation:

def TransformCoords(x, y, gcs, pcs, trans, direction):

The x and y are the longitude and latitude of the point in decimal degrees.  In order to perform transformation you will need to look up some ESRI codes.  The list of geographic coordinate system codes are here: Page 1, Page 2, Page 3.  The projected coordinate systems are here: Page 1, Page 2, Page 3, Page 4.  And the transformations are here: Page 1, Page 2, Page 3.   Lastly, you’ll need the direction codes here.

For those familiar with writing ArcObjects in C# I will include the C# equivalent with each piece of code.  Now we need to get the Geometry module and import it:

   GetModule("esriGeometry.olb") 

   import comtypes.gen.esriGeometry as esriGeometry

C#:

using ESRI.ArcGIS.Geometry;

Next we create a new point object and assign the x and y values:

   point = NewObj(esriGeometry.Point,
          esriGeometry.IPoint)

   point.X = x

   point.Y = y

C#:

Point point = new Point();

point.X = x;

point.Y = y;

It’s time to start using the codes we looked up.  Let’s define the spatial reference for the geographic coordinate system:

   sRefEnv = 

       NewObj(esriGeometry.SpatialReferenceEnvironment,

          esriGeometry.ISpatialReferenceFactory)

   incomingCoordSystem =

   CType(sRefEnv.CreateGeographicCoordinateSystem(gcs),

      esriGeometry.ISpatialReference)
   point.SpatialReference = incomingCoordSystem

C#:
SpatialReferenceEnvironment sRefEnv = new  
   SpatialReferenceEnvironment();

ISpatialReference incomingCoordSystem = 

   sRefEnv.CreateGeographicCoordinateSystem(gcs);

point.SpatialReference = incomingCoordSystem;

Define the projected coordinate system:

   outgoingCoordSystem = 

   CType(sRefEnv.CreateProjectedCoordinateSystem(pcs), 

      esriGeometry.ISpatialReference)

C#:

ISpatialReference outgoingCoordSystem = 
sRefEnv.CreateProjectedCoordinateSystem(pcs);

Define the geographic transformation:

   geoTransformation = 

      CType(sRefEnv.CreateGeoTransformation(trans), 

         esriGeometry.IGeoTransformation)

C#:

IGeoTransformation geoTransformation = 
(IGeoTransformation)sRefEnv.CreateGeoTransformation(trans);

Re-project the coordinates and assign the new geometry to the point:

   geometry = CType(point, esriGeometry.IGeometry2)

   geometry.ProjectEx(outgoingCoordSystem, 
          direction,geoTransformation, False, 0, 0)

   point = CType(geometry, esriGeometry.IPoint)

       

C#:

IGeometry2 geometry = point as IGeometry2;

geometry.ProjectEx(outgoingCoordSystem, esriTransformDirection.esriTransformForward, geoTransformation, 
   false, 0, 0);

point = (Point)geometry;

Finally, assign the coordinates to an array and return them:

   points = [point.X, point.Y]

       

   return points

In the end you have an array of the transformed x and y of the original point.  Another method exists that can also be helpful called ctypes.  With ctypes, you can access C libraries much like comtypes.  An excellent example of transformation using ctypes can be found here.

Using Python to access ArcSDE data faster with cx_Oracle

Using Python and ArcObjects to access data can be painfully slow, especially with large amounts of data.  However, there is another method for accessing data from Oracle that will allow your scripts to run faster.  The Python extension module, cx_Oracle, allows you to access an Oracle database without using ArcObjects.  Using cx_Orcale you can quickly query and modify attributes for tables and feature classes in ArcSDE.

NOTE:  Do NOT attempt to modify (INSERT, UPDATE, DELETE, etc) a table or feature class that is registered as versioned.  The results are unpredictable at best and can result in data loss.  However, you can safely SELECT any table or feature class.

To start you’ll need to download the cx_Oracle module here.  Once you have it installed and are ready to write some code, begin by importing the cx_Oracle module:

import cx_Oracle

Next we need to connect to the Oracle database.  There are several ways to do this.  For a complete reference, see the cx_Oracle documentation.  In this example we first create a DSN:

dsn = cx_Oracle.makedsn("gisdb01.devfaction.com", 

      "1521", "gisdb")

In this example, gisdb01.devfaction.com is the host, 1521 is the port, and gisdb is the name of the database.  Once the DSN is created, we connect to the database using the DSN:

orcl = cx_Oracle.connect('', '', dsn)

Now that we are connected to the database, we need to create a cursor:

cursor = orcl.cursor()

The next step is to create a SQL statement to execute.  This statement can be any SQL syntax that Oracle supports.  We will start off with a simple SELECT statement:

sql = "SELECT id, name FROM DB.ROADS WHERE id < 10"

This statement will return two fields from the ROADS table where the ID is less than 10.  Now it’s time to execute the statement:

cursor.execute(sql)

Now that we have run a query, we need to use the data that was returned.  To this we can loop through each row in the cursor:

for row in cursor:

     print row[0] + ", " + row[1]

The results should look something like this:

1, Walnut

2, Oak

3, Spruce

When you are finished remember to delete the cursor and close your connection:

del cursor

orcl.close()

You can also delete and modify data using cx_Oracle.  It may be possible to INSERT new records but it is not recommended for ArcSDE objects.  Follow the same steps to create the connection and cursor then create a SQL statement and execute it.:

sql = "UPDATE DB.ROADS SET name = '1st' " +\

      "WHERE id = 3"

cursor.execute(sql)

You will need to commit your changes before closing the connection:

orcl.commit()

In addition to creating scripts that run faster, cx_Oracle can help users who are more familiar with writing SQL than using ArcObjects.  But I want to reiterate one more time that modifications should not be performed on feature classes or tables that are registered as versioned.

cx_Oracle consists of many more useful functions.  I encourage you to read through the documentation and see what else exists.

Development Faction Company Info

Established in 2009, Development Faction, LLC is the collective vision of several industry professionals highly skilled in the disciplines of Geospatial Information, Software Design & Development, Database Design / Management, Web Design & Implementation, and Project Management.

With a combined experience of over 20 years in the industry, Development Faction possesses the knowledge to evaluate, plan, and strategically implement custom tailored solutions to meet our customer’s needs.

Our unique business structure and well rounded skill set awards us the opportunity to handle projects of any size / complexity by eliminating the use of sub-consultants.

Our esteemed staff is well versed in using the industry standard commercial-off-the-shelf (COTS) software packages, developing custom software / applications, and open source software packages; therefore Development Faction can fulfill our customer’s needs in the most cost efficient manner.

Development Faction is an official Microsoft® BizSpark™ software development start-up firm.

For more information on Development Faction, visit our website:

http://www.devfaction.com

or contact us:

info@devfaction.com