Speed up conversion from table to shp

[__]

Hi,

I've been working on a Python script to convert a table (from MS Access .mdb) to a shapefile. The code below works well for my smaller tables (about 2,000 records with 50 fields; runs in 51 seconds), but takes far too long for larger ones (about 75,000 records with 50 fields; still running after 1 hour 45 minutes...).

The idea is to programmatically replicate the ArcMap actions of "Add XY Data" and "Export Data to Shapefile", so I'm using gp.MakeXYEventLayer_management and gp.CopyFeatures_management. It takes much more time via Python than the actions would take in ArcMap, which leads me to wonder if I'm missing something.

Any suggestions on how to make this work faster?

 
    # Import system modules
    print "Importing system modules..."
    import sys, string, os, arcgisscripting
    # Create the Geoprocessor object
    gp = arcgisscripting.create()
    # Load required toolboxes...
    print "Loading toolboxes..."
    gp.AddToolbox("C:/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")
    # Setting OverWriteOutput to True allows geoprocessing tools to overwrite
    #    the output if it already exists.
    gp.OverWriteOutput = 1
    XY_Table = ""
    #-------------Groundwater Part ----------------------
    GWLayer = "'Groundwater$'_Layer3"
    v_Groundwater = \\\\Computer\\AutoSitesShp\\sitefile_01.mdb\\GW 
    GW = "\\\\Computer\\AutoSitesShp\\AutoGW"
   # Process: Make XY Event Layer (GW)...
    print "Making XY Event Layer (GW)..."
    gp.MakeXYEventLayer_management(v_Groundwater, "dec_long_va", "dec_lat_va", GWLayer, "GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];IsHighPrecision")
    # Process: Copy Features...
    print "Copying features (GW)..."
    gp.CopyFeatures_management(GWLayer, GW, "", "0", "0", "0")
    GWLayer =0
    v_Groundwater = 0
    GW = 0
    # Clear local variables
    v_Surface_Water___Layer = 0
    SW = 0
    XY_Table = 0
    v_Surface_Water__ = 0
    # Set OverWriteOutput back to false
    gp.OverWriteOutput = 0
    print "Done."

[ChrisSnyder]

Maybe a cursor would be faster - that is cursor through the .mdb file and build a shapefile that way? Seems like your .mdb file is on a network... Could it be that the network is slow? I would try to 1st copy the .mdb file locally, and then make the XYEventLayer.

[GünterDörffel]

Hi,

first thing: strong support for Chris - get the mdb from the network! ...

and then read in the mdb sequentially and write it via a cursor into the target FC ... you can use the code on this page as stub code for the geometry handling

[__]

Thanks for the replies.

About the network: Yes, the files are being processed over a network. Yes, this probably adds some time. However, I don't think this is my main issue; I can perform the same task that takes 2+ hours via Python in a few minutes through ArcMap (with the same network setup). It's the gp.CopyFeatures_management line that takes most of the time; the rest runs in less than 1 minute.

About cursors: This sounds promising, but it's completely new to me, I'm a programming rookie, and I couldn't really see how to apply the example scripts to my files. I'm trying to create a shapefile of points from an Access table that contains site information. The sites have about 50 fields describing various parameters, all of which need to be accessable upon "Identify" in ArcMap. Each site has its own unique name and lat/long coordinate.

Could someone please provide me with more specific examples? Thanks.

[__]

This just doesn't make any sense: using zero scripting in ArcMap (just clicking Add XY Data, then right clicking events layer and exporting the data) this process takes about 2 minutes. I can accomplish the same thing in ModelBuilder in about 10 minutes using "Make XY event layer" and "Copy features (management)". My Python script (which uses the same to geoprocessing commands as ModelBuilder) takes 2 hours. All of this with the same data and same save locations.

For both the ModelBuilder and Python scripts, the step that takes the longest is Copy features. (Add XY Data takes less than 30 seconds.)

What's the discrepancy here?

(I'm still open to alternatives to the Copy features method also; I haven't been able to make sense of cursors yet.)

[ChrisSnyder]

Hmm... Not sure what the heck is wrong. copy features is the correct tool, but not sure why it is taking so long. I have heard about this sort of thing on the old forums (Copy Features was taking forever).

Some things to try:

1) Once you make the XYEventLayer, try making a new feature layer from GWLayer using the gp.MakeFeatureLayer tool, then use the feature layer as input into the CopyFeatures tool.

2) Make a TableView out of your .mdb table (using MakeTableView tool), then run the XYEventLayer tool using the table view as input.

[ShaunConway]

Not sure if this helps, but it might be worth a try. I ran into a similar speed issue when exporting our Parcel feature class to shapefile and found the Quick Export (Data Interoperability Tools) to be much more efficient!

Model Using Copy Features or Feature Class to Shapefile or Feature Class to Feature Class : 20+ mins

Model Using Quick Export : 2mins

Good luck and hope this helps!

[__]

Thanks. I think this will work. First trial did in 5 minutes what my old code did in 2 hours.

[__]

Just worked out the final kinks (hopefully). It's a little odd that the Quick Export tool requires a separate overwrite setting.This drove me nuts at first becuase it was appending my database each time even though I had the geoprocessor object set to overwrite.

In case anyone else wants to do something similar, this code takes my XY data from a table and creates/overwrites a table within a personal geodatabase. It would have to be modified considerably for anyone else who wants to use it, but at least this is a starting point.

 
    # Import system modules
    print "Importing system modules..."
    import sys, string, os, arcgisscripting
    # Create the Geoprocessor object
    gp = arcgisscripting.create()
    # Load required toolboxes...
    print "Loading toolboxes..."
    gp.AddToolbox("C:/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")
    # Setting OverWriteOutput to True allows geoprocessing tools to overwrite
    #    the output if it already exists.
    gp.OverWriteOutput = 1
    XY_Table = ""
    outputDB = '"\\\\ComputerB\\AutoSitesShp\\SitesShp.mdb"'
    outputLayer = "SurfaceWater"
    inputTable = "\\\\ComputerB\\AutoSitesShp\\sitefile_01.mdb\\SW"
 
   # Process: Make XY Event Layer (SW) ...
    print "Making XY Event Layer (SW) ..."
    gp.MakeXYEventLayer_management(inputTable, "dec_long_va", "dec_lat_va", outputLayer, "GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];IsHighPrecision")
 
    # Process: Quick Export (SW)...
    print "Processing Quick Export (SW) ..."
    Output_Dataset = "GEODATABASE_MDB," + outputDB + ",\"RUNTIME_MACROS,\"\"OVERWRITE_GEODB,yes,TRANSACTION_TYPE,TRANSACTIONS,SIMPLIFY_GEOM,no,X_ORIGIN,0,Y_ORIGIN,0,XY_SCALE,0,HAS_Z_VALUES,auto_detect,Z_ORIGIN,0,Z_SCALE,0,GRID_1,0\"\",META_MACROS,\"\"DestOVERWRITE_GEODB,yes,DestTRANSACTION_TYPE,TRANSACTIONS,DestSIMPLIFY_GEOM,no,DestX_ORIGIN,0,DestY_ORIGIN,0,DestXY_SCALE,0,DestHAS_Z_VALUES,auto_detect,DestZ_ORIGIN,0,DestZ_SCALE,0,DestGRID_1,0\"\",METAFILE,GEODATABASE_MDB,COORDSYS,,__FME_DATASET_IS_SOURCE__,false\""
    gp.QuickExport_interop(outputLayer, Output_Dataset)

Thanks again to everyone who posted suggestions!