I have a QueryLayer that I need to export as a shapefile. This was previously implemented as an ArcSDE feature class which we converted to a QueryLayer (running against SQLServer) in order to have "live" access to data coming in from a variety of systems. We have an export tool built in model builder that worked with the ArcSDE feature class using the ExtractData toolbox, however this doesn't seem to work against the QueryLayer.

I would appreciate any insight or ideas!

Below is the code for the data extract:

import arcgisscripting, os, sys, traceback, zipfile
gp = arcgisscripting.create(9.3)

def getPRJFile(inputCoordSysString, prjDir):
    inputCoordSysString += ".prj"
    # walk through the dirs to find the prj file
    if os.path.exists(prjDir):
        for x in os.walk(prjDir):
            if inputCoordSysString in x[2]:
                return True, os.path.join(x[0], inputCoordSysString)
        return False, ""

    # if we got to here then it didn't find the prj file
    return False, ""

def zipUpFolder(folder, outZipFile):
    # zip the data
        zip = zipfile.ZipFile(outZipFile, 'w', zipfile.ZIP_DEFLATED)
        zipws(str(folder), zip, "CONTENTS_ONLY")
    except RuntimeError:
        # Delete zip file if exists
        if os.path.exists(outZipFile):
        zip = zipfile.ZipFile(outZipFile, 'w', zipfile.ZIP_STORED)
        zipws(str(folder), zip, "CONTENTS_ONLY")
        gp.AddWarning("  Unable to compress zip file contents.")

def zipws(path, zip, keep):
    path = os.path.normpath(path)
    # os.walk visits every subdirectory, returning a 3-tuple
    #  of directory name, subdirectories in it, and filenames
    #  in it.
    for (dirpath, dirnames, filenames) in os.walk(path):
        # Iterate over every filename
        for file in filenames:
            # Ignore .lock files
            if not file.endswith('.lock'):
                #gp.AddMessage("Adding %s..." % os.path.join(path, dirpath, file))
                    if keep:
                        zip.write(os.path.join(dirpath, file),
                        os.path.join(os.path.basename(path), os.path.join(dirpath, file)[len(path)+len(os.sep):]))
                        zip.write(os.path.join(dirpath, file),
                        os.path.join(dirpath[len(path):], file)) 

                except Exception, e:
                    gp.AddWarning("    Error adding %s: %s" % (file, e))

    return None

def createFolderInScratch(folderName):
    # create the folders necessary for the job
    folderPath = gp.CreateUniqueName(folderName, gp.scratchworkspace)
    gp.CreateFolder_management(gp.scratchworkspace, os.path.basename(folderPath))
    return folderPath

def getTempLocationPath(folderPath, format):
    # make sure there is a location to write to for gdb and mdb
    if format == "mdb":
        MDBPath = os.path.join(folderPath, "data.mdb")
        if not gp.exists(MDBPath):
            gp.CreatePersonalGDB_management(folderPath, "data")
        return MDBPath
    elif format == "gdb":
        GDBPath = os.path.join(folderPath, "data.gdb")
        if not gp.exists(GDBPath):
            gp.CreateFileGDB_management(folderPath, "data")
        return GDBPath
        return folderPath

def makeOutputPath(raster, inLayerName, convert, formatList, zipFolderPath, scratchFolderPath):
    outFormat = formatList[1].lower()

    # if we are going to convert to an esri format on the clip, put the output in the zipfolder
    # else put it in the scratch folder in a gdb
    if convert:
        outwkspc = getTempLocationPath(zipFolderPath, outFormat)
        outwkspc = getTempLocationPath(scratchFolderPath, "gdb")

    if inLayerName.find("\\"):
        inLayerName = inLayerName.split("\\")[-1]

    # make sure there are no spaces in the out raster name and make sure its less than 13 chars
    if outFormat == "grid":
        if len(inLayerName) > 12:
            inLayerName = inLayerName[:12]
        if inLayerName.find(" ") > -1:
            inLayerName = inLayerName.replace(" ", "_")

    # make the output path
    tmpName = os.path.basename(gp.createuniquename(inLayerName, outwkspc))
    tmpName = gp.validatetablename(tmpName, outwkspc)

    # do some extension housekeeping.
    # Raster formats and shp always need to put the extension at the end
    if raster or outFormat == "shp":
        if outFormat != "gdb" and outFormat != "mdb" and outFormat != "grid":
            tmpName = tmpName + formatList[2].lower()

    outputpath = os.path.join(outwkspc, tmpName)

    return tmpName, outputpath

def clipFeatures(lyr, where, featureFormat, zipFolderPath, scratchFolderPath, convertFeaturesDuringClip):
    global haveDataInterop
##        if not convertFeaturesDuringClip and not haveDataInterop:
##            raise "LicenseError"

        # get the path and a validated name for the output
        layerName, outputpath = makeOutputPath(False, lyr, convertFeaturesDuringClip, featureFormat, zipFolderPath, scratchFolderPath)

        # do the clip
        gp.select_analysis(lyr, outputpath, where)
        gp.AddMessage("  clipped %s..." % lyr)

        # if format needs data interop, convert with data interop
        if not convertFeaturesDuringClip:
            # get path to zip
            outputinzip = os.path.join(zipFolderPath, layerName + featureFormat[2])
            if featureFormat[2].lower() in [".dxf", ".dwg", ".dgn"]:
                gp.AddWarning("..using export to cad..")
                gp.ExportCAD_conversion(outputpath, featureFormat[1], outputinzip)
                if not haveDataInterop:
                    raise "LicenseError"
                diFormatString = "%s,%s" % (featureFormat[1], outputinzip)
                # run quick export
                gp.quickexport_interop(outputpath, diFormatString)

    except "LicenseError":
        gp.AddWarning("  failed to export to %s.  The requested formats require the Data Interoperability extension.  This extension is currently unavailable." % featureFormat[1])

        errorstring = gp.GetMessages(2)
        if errorstring.lower().find("failed to execute (quickexport)") > -1:
            gp.AddWarning("  failed to export layer %s with Quick Export.  Please verify that the format you have specified is valid." % lyr)
        elif errorstring.lower().find("failed to execute (clip)") > -1:
            gp.AddWarning("  failed to clip layer %s..." % lyr)
            gp.AddWarning("layer %s failed..." % lyr)

def clipAndConvert(lyrs, where, featureFormat, coordinateSystem):
        # for certain output formats we don't need to use Data Interop to do the conversion
        convertFeaturesDuringClip = False
        if featureFormat[1].lower() in ["gdb", "mdb", "shp"]:
            convertFeaturesDuringClip = True

        # get a scratch folder for temp data and a zip folder to hold
        # the final data we want to zip and send
        zipFolderPath = createFolderInScratch("zipfolder")
        scratchFolderPath = createFolderInScratch("scratchfolder")

        # loop through the list of layers recieved
        for lyr in lyrs:
            # temporary stop gap measure to counteract bug
            if lyr.find(" ") > -1:
                lyr = lyr.replace("'", "")
            describe = gp.describe(lyr)
            dataType = describe.DataType.lower()

            # make sure we are dealing with features or raster and not some other layer type (group, tin, etc)
            if dataType in ["featurelayer", "rasterlayer"]:
                # if the coordinate system is the same as the input
                # set the environment to the coord sys of the layer being clipped
                # may not be necessary, but is a failsafe.
                if coordinateSystem.lower() == "same as input":
                    sr = describe.spatialreference
                    if sr != None:
                        gp.outputcoordinatesystem = sr

                clipFeatures(lyr, where, featureFormat, zipFolderPath, scratchFolderPath, convertFeaturesDuringClip)
                gp.AddWarning("  Cannot clip layer: %s.  This tool does not clip layers of type: %s..." % (lyr, dataType))

        return zipFolderPath

        errstring = "Failure in clipAndConvert..\n"
        tb = sys.exc_info()[2]
        tbinfo = traceback.format_tb(tb)[0]
        pymsg = "ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n    " + \
                str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"
        errstring += pymsg
        raise "clipAndConvertError", errstring

if __name__ == '__main__':
        # Get the Parameters
        layers = gp.getparameterastext(0).split(";")
        where = gp.getparameterastext(1)
        inputFeatureFormat = gp.getparameterastext(2)
        coordinateSystem = gp.getparameterastext(3)
        outputZipFile = gp.getparameterastext(4)

        if gp.CheckExtension("DataInteroperability") == "Available":
            haveDataInterop = True
            haveDataInterop = False
        # Do a little internal validation.
        # Expecting "long name - short name - extension
        # If no format is specified, send features to GDB.
        if inputFeatureFormat == "":
            featureFormat = ["File Geodatabase", "GDB", ".gdb"]
            #featureFormat = inputFeatureFormat.split(" - ")
            featureFormat = map(lambda x: x.strip(), inputFeatureFormat.split("-"))
            if len(featureFormat) < 3:

        coordinateSystem = 'same as input'

        # Do this so the tool works even when the scratch isn't set or if it is set to gdb/mdb/sde
        if gp.scratchworkspace is None or os.path.exists(str(gp.scratchworkspace)) is False:
            gp.scratchworkspace = gp.getsystemenvironment("TEMP")
            swd = gp.describe(gp.scratchworkspace)
            wsid = swd.workspacefactoryprogid
            if wsid == 'esriDataSourcesGDB.FileGDBWorkspaceFactory.1' or\
               wsid == 'esriDataSourcesGDB.AccessWorkspaceFactory.1' or\
               wsid == 'esriDataSourcesGDB.SdeWorkspaceFactory.1':
                gp.scratchworkspace = gp.getsystemenvironment("TEMP")

        # clip and convert the layers and get the path to the folder we want to zip
        zipFolder = clipAndConvert(layers, where, featureFormat, coordinateSystem)

        # zip the folder
        zipUpFolder(zipFolder, outputZipFile)

        tb = sys.exc_info()[2]
        tbinfo = traceback.format_tb(tb)[0]
        pymsg = "ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n    " + \
                str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"

Embarrassingly, the column names in the SQL Server view had changed causing the export to break.

| improve this answer | |
  • 2
    We've all been there before... – RyanKDalton Jan 12 '12 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.