Convert Result Object to GDB Table

JohnDye · ‎06-05-2013

I have a table being returned from the Network Analyst OD Cost Matrix...

I want to convert the result object to a GDB Table and I tried using this..

ODCM_Table = arcpy.na.Solve(outNALayer) #Solves the Origin-Destination Cost Matrix and produces the ODCM_Table
arcpy.TableToTable_conversion(ODCM_Table, workspace + "\Default.gdb", "ODCM") # Thought this should convert result to GDB Table

But it throws an error:
Runtime error Traceback (most recent call last): File "<string>", line 1, in <module> File "c:\program files\arcgis\desktop10.1\arcpy\arcpy\conversion.py", line 1904, in TableToTable raise e ExecuteError: Failed to execute. Parameters are not valid. ERROR 000732: Input Rows: Dataset GPL0 does not exist or is not supported Failed to execute (TableToTable).

I thought maybe the cause of the error was the formatting of the result object being held on the 'ODCM_Table' variable, so I printed it to see what it returns...

print ODCM_Table
GPL0

No problem I can see there.

So how can I convert this result object to an actual table?

JohnDye · ‎06-11-2013

I think I've angered the GIS gods...

arcpy.da.FeatureClassToNumPyArray(ODCM_LYR, "*")

Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
RuntimeError: cannot open 'C:\ArcGIS\SYSTEM\COF\Data\BG2Branches.lyr'

JamesCrandall · ‎06-11-2013

I think I've angered the GIS gods...
arcpy.da.FeatureClassToNumPyArray(ODCM_LYR, "*")
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
RuntimeError: cannot open 'C:\ArcGIS\SYSTEM\COF\Data\BG2Branches.lyr'

Is there a BG2Branches Feature Class (not a .lyr)?

JohnDye · ‎06-11-2013

Is there a BG2Branches Feature Class (not a .lyr)?

Nope. That's the point. This is supposed to produce that Feature Layer which I can then convert to an FC.

RhettZufelt · ‎06-11-2013

I don't see where you are assigning ODCM_LYR to anything, is that just missing from the posted code.

also, if you are trying to convert from the numpyarray to FC, wouldn't that be NumPyArrayToFeatureClass instead of FeatureClassToNumPyArray ?

R_

JohnDye · ‎06-11-2013

I don't see where you are assigning ODCM_LYR to anything, is that just missing from the posted code.

Hey Rhett,
Thanks for following up. I appreciate the efforts. Sorry, I should have included the reference. In that particular test, 'ODCM_LYR' was referenced to the result object using:

ODCM_LYR = arcpy.na.Solve(outNALayer)
ODCM_LYR = ODCM_LYR.getOutput(0)

also, if you are trying to convert from the numpyarray to FC, wouldn't that be NumPyArrayToFeatureClass instead of FeatureClassToNumPyArray ?

In this test, I wasn't trying to convert from a numpy array to FC, I was trying to get the Lines NA Class into a Numpy Array and then follow that up with a NumpPyArraytoTable to get the table. I never got it into the array though =(

Currently, I'm working around all of this by running the tool out of Arcmap and extracting the table directly using:

arcpy.TableToTable_conversion("BG2Branches\Lines", workspace, str(cbsaFD) + "_ODCM")

and it works just fine...but I dont think I should be forced to run this out of ArcMap only. It should be executable through Catalog or as a Standalone Script as well.

RhettZufelt · ‎06-11-2013

You say it is working just fine in ArcMap, if Table to Table is working in ArcMap, then it will run as standalone as well (I use this all the time in my standalone scripts. however, some datasets don't support it, and CopyRows can often be used to achieve same result on these). Have you ran it in ArcMap, then after it finishes, Go to the Geoprocessing window, Results, expand Current Session and right-click on the Table to Table entry. Then pick, "Copy as python snippet".

Then you should be able to paste that snippet directly in python as standalone or within ArcDesktop (might have to change paths/variable names).

Sorry I can't be more help, but have never had the need for numpy stuff, don't know what the .solve, .getOutput results would look like, or what they contain. Without understanding what the "data" itself looks like/formatted, my input will be limited.

R_

JohnDye · ‎06-12-2013

Hey Rhett,
I pulled the Python snippet through arcmap and this was it returned:

# Replace a layer/table view name with a path to a dataset (which can be a layer file) or create the layer/table view within the script # The following inputs are layers or table views: "BG2Branches\Lines" arcpy.TableToTable_conversion("BG2Branches/Lines","C:/Users/jdk588/Documents/ArcGIS/Default.gdb","ODCM_Test","#","""Name "Name" true true true 128 Text 0 0 ,First,#,/ODLines,Name,-1,-1;OriginID "OriginID" true true true 4 Long 0 0 ,First,#,/ODLines,OriginID,-1,-1;DestinationID "DestinationID" true true true 4 Long 0 0 ,First,#,/ODLines,DestinationID,-1,-1;DestinationRank "DestinationRank" true true true 4 Long 0 0 ,First,#,/ODLines,DestinationRank,-1,-1;Total_Length "Total_Length" true true true 8 Double 0 0 ,First,#,/ODLines,Total_Length,-1,-1""","#")

As you can see, it still references the 'BG2Branches\Lines' which is accessible in ArcMap but not ArcCatalog.
The issue is that the the BG2Branches is some sort of composite NA Layer, and is not written to disk anywhere. It's like a Group Layer of some sort, and there's no real method to get the sublayers without an instantiation in a dataframe.

So I have figured out a solution that works without having to run the tool from ArcMap. Basically, I just save the result to a layer file, then use the mapping module to leverage an ArcGIS Template MXD and load the layer into the Template MXD's Dataframe. One that's done, I can extract the table from the Template MXD's Dataframe without ever having to actually open the MXD. After the 'TableToTable_conversion' is complete and I have a copy of the table on disk, I remove the layer from the Template MXD and then delete the actual layer file. It takes about 1-2 minutes for all of that to occur, so not too shabby.

arcpy.na.Solve(outNALayer) arcpy.SaveToLayerFile_management(outNALayer, workspace + "\\" + outLayerFile, "RELATIVE")   mxd = arcpy.mapping.MapDocument(r" C:\Program Files (x86)\ArcGIS\Desktop10.1\MapTemplates\Traditional Layouts\LetterPortrait.mxd") df = arcpy.mapping.ListDataFrames(mxd)[0] addlyr = arcpy.mapping.Layer(r???C:\Temp\BG2Branches.lyr") arcpy.mapping.AddLayer(df, addlyr) for lyr in df: if lyr.name == "Lines":           arcpy.TableToTable_conversion(lyr, addlyr, "ODCM_Table")           arcpy.mapping.RemoveLayer(df, lyr)           arcpy.Delete_management(workspace + ???\\??? + str(addlyr) + ???.lyr???)      else:           arcpy.AddError (???ODCM Lines Not Found.???)

RhettZufelt · ‎06-12-2013

John,

Wasn't really thinking about the layer not being available out of ArcMap, as i don't pick the layer from the dropdown, I always click the folder and browse to it. The "layers" in the dropdown are often auto-created views. When browse to the datasource, if it is not a compatible type, it will not be visible to pick from. in this case, that is when I know I have to bring in the FC and make a view first.

Now that you have it working as far as saving to a layer file, have you tried the TableToTable on the layer file itself? .lyr files are acceptable input to the TableToTable tool, and I can dump the table to a new one straight from any of my layer files (though, none of them are from a na.solve)


outfile =  workspace + "\\" + outLayerFile
newtable = workspace + "\\" + "ODCM_Table"

arcpy.na.Solve(outNALayer)
arcpy.SaveToLayerFile_management(outNALayer, outfile , "RELATIVE")

arcpy.TableToTable_conversion(outfile ,newtable,"#","#","#")

Would think something like this would work without the load of .mapping.

R_

BradShore1 · ‎06-13-2013

Here is what I do.

class LicenseError(Exception):
    pass

import arcpy, os, traceback, sys, tkMessageBox
from arcpy import env
arcpy.env.overwriteOutput = True
                                             

#Checkout Network Analyst License
try:
    if arcpy.CheckExtension("Network") == "Available":
        arcpy.CheckOutExtension("Network")
    else:
        # Raise a custom exception
        #
        raise LicenseError


#Set Variables
    Members = r"C:\ArcGIS\HSD\HSD_May2013\HSD.gdb\Members"
    Shell_D = r"C:\ArcGIS\HSD\HSD_May2013\HSD.gdb\Shell_D"
    Distance = r"C:\ArcGIS\HSD\HSD_May2013\HSD.gdb\Distance"                                        
    ND = r"C:\ArcGIS\Geodabases\Basemap.gdb\na\na_ND"
    OD_Lines = r"in_memory\OD_Lines"
    Lines = r"OD_Matrix\Lines"
    OD_Matrix = "OD_Matrix"


#Processes for Distance Analysis
    arcpy.MakeODCostMatrixLayer_na(ND,OD_Matrix,"Distance","","1")
    arcpy.AddLocations_na(OD_Matrix, "Origins", Members, "", "", "", "", "", "Clear")
    env.workspace = r"C:\ArcGIS\HSD\HSD_May2013\Database"
    workspaces = arcpy.ListWorkspaces("Distance*", "FileGDB")
    for workspace in workspaces:
        expression1 = workspace[-6:-4]
        print "Distance: " + expression1 + " Miles"
        env.workspace = workspace        
        fclist = arcpy.ListFeatureClasses()
        for fc in fclist:
            expression2 = '"' + str(fc[4:8]) + '"'
            arcpy.CalculateField_management(Shell_D, "SpecialtyCode", expression2, "PYTHON_9.3")
            print "Calculating Distance for Specialty Code " + expression2
            arcpy.AddLocations_na(OD_Matrix, "Destinations", fc, "Name Code #", "", "", "", "", "Clear")
            arcpy.Solve_na(OD_Matrix, "SKIP","CONTINUE")
            arcpy.Select_analysis(Lines, OD_Lines, "\"Total_Distance\" <=" + expression1)
            count = arcpy.GetCount_management(OD_Lines)
            arcpy.CalculateField_management(Shell_D, "Distance", count, "PYTHON_9.3")
            arcpy.Append_management (Shell_D, Distance)

except:
    tb = sys.exc_info()[2]
    tbinfo = traceback.format_tb(tb)[0]
    pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(sys.exc_info()[1])
    tkMessageBox.showwarning("Error",pymsg)

finally:
# Check in the Network Analyst extension
    arcpy.CheckInExtension("Network")

If I remember correctly, I had to make the output of the OD Matrix an in memory table(using the arcpy.Select_analysis) and append it to an empty table. I think I kept getting an error if I tried writing the table straight to disk. The code above is in a standalone script.

JohnDye · ‎06-13-2013

Rhett,
Yes, I tried executing Table to Table directly against the result layer ('outNALayer') and it fails. Here's why:

The result layer object ('outNALayer') from the OD Cost Matrix is a Network Analyst Composite Layer which is made up of multiple sublayers, each of which references a particular Network Analyst object (Origins, Destinations, Point Barriers, Line Barriers, Polygon Barriers & Solved Route/Lines) used in the Network Analysis Solve function.
See this article: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//004700000034000000

[INDENT][/INDENT]"The network analysis layer has its own properties, even though it is a composite layer made up of subordinate feature layers that in turn have their own sets of properties. Some of the network analysis layer properties are crucial to defining the analysis you want to perform. Properties include the impedance to be used and the restrictions to be obeyed, as well as the U-turn policy, output shape type, accumulation attributes, and properties for finding network locations. Additionally, each kind of analysis has specific properties of its own."

The Solved Route ('Lines') is what I was after once the OD Cost Matrix completes because I wanted the distance from the origin to the destination.
See this article: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00470000004r000000#GUID-823C3FF5-FE11...

[INDENT][/INDENT]"The Lines network analysis class stores information about the resulting paths from the origins to the destinations. As with other feature layers, its symbology can be accessed and altered from its Layer Properties dialog box.
The Lines class is an output-only class; it is empty until the analysis is complete. Since OD cost matrix analyses tend to result in a multitude of lines, the line objects are not displayed in the Network Analyst window; rather, only a count of the lines is displayed there. You can inspect the individual lines in the attribute table of the Lines feature class.
When you choose to display lines in the OD cost matrix analysis layer, they appear as straight lines. They are generalized. However, their cost attributes always report the least-cost network path."

The issue is that there is no way to access the Composite Layer ('outNALayer')'s sublayers (Origins, Destinations, Point Barriers, Line Barriers, Polygon Barriers & Solved Route/Lines) outside of a dataframe.
This is why the only solution was to load the Composite Layer ('outNALayer') into an MXD, reference the dataframe, and extract from there. You cannot access the sublayers through any arcpy function that I've been able to find. You have to go through the dataframe to get to those objects/sublayers.