Updating Feature Class Attributes from Table

ModernElectric · ‎04-08-2013

Greetings-

     I have tried multiple things and trying to find a better/easier/faster way of doing this:

I have a File Geodatabase Feature Class (Service_Locations) that has around 9500 records in it. This is the location and data for our Electric meters. The data behind it (Customer info, meter attributes, ETC) are stored/maintained/updated in our CIS system (Data held in an Oracle Database). Everynight - I have a python script that automatically exports a View from the Oracle Database (Customer data, Meter Data) and brings it directly into the File Geodatabase as a Geodatabase Table.

What I have done in the past is to:
    Add Join (Feature Class Table TO Service_Locations FC)
    Calculate Field tool to say - Bring this field from the table (Meter Number) into this field of the FC (TWACs Number).
I have done this inside and edit session and a data model to Python script (Outside of an edit session)
Each way is very time consuming and does not work fast.

QUESTION: Are there different tools that I can use to make this process much faster so I can build a Python Script to run automatically at night? The Join and Calculate Field tools are not working for me anymore..

Thank you

MichaelVolz · ‎04-08-2013

Chris:

How long does it take to run the python script that is outside an edit session? 9500 records is not alot of records, so this process should not take long (I would think 1-2 minutes at the most).

ModernElectric · ‎04-08-2013

Chris:

How long does it take to run the python script that is outside an edit session? 9500 records is not alot of records, so this process should not take long (I would think 1-2 minutes at the most).

When I built the Python script - I had a total of 18 different fields to update from (2) different tables. So I did the Join for (1) table - did all those fields, removed the join and did the same thing for the other table. This was outside of an edit session. To do this - it
took almost 8 hours for the python script to run. When I do it inside an edit session in ArcMap without a model/script it takes about 1 to 2 minutes per field to update. I want to be able to do this automatically at night with a script so I am not taking time to do it the long way.

MichaelVolz · ‎04-08-2013

Can you provide the python script that you are running? Please put it in the appropriate code block syntax so it is indented correctly.

ModernElectric · ‎04-08-2013

Can you provide the python script that you are running? Please put it in the appropriate code block syntax so it is indented correctly.

How exactly do I add the Python Script to the text window here so you can see it the way you are requesting??

MichaelVolz · ‎04-08-2013

When you are posting your reply go to the Code wrap menu and select # (It is in the 2nd row to the right above the posting window). Put CODE (with brackets) at the beginning of your code and /CODE (with brackets) at the end of your code as shown in my below sample that does not do anything but provide a visual sample.

# Import system modules
import sys, string, os             # arcgisscripting
import arcpy
import arcpy.mapping
from arcpy import env
import smtplib #E-mail library

# Create the Geoprocessor object
# gp = arcgisscripting.create(9.3)
# gp.overwriteoutput = 1
arcpy.env.overwriteOutput = True

# Load required toolboxes...
# gp.AddToolbox("C:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")
# gp.AddToolbox("C:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Conversion Tools.tbx")

# Read in values from config file that are used to set Local variables
configfile = open('config_py.txt', 'r')

vars = configfile.read().rstrip().split(",")

connSDE = vars[0]
conn_EOC_Data = vars[1]
conngdb_Schema = vars[2]

ModernElectric · ‎04-08-2013

mvolz47;287670 wrote:
When you are posting your reply go to the Code wrap menu and select # (It is in the 2nd row to the right above the posting window). Put CODE (with brackets) at the beginning of your code and /CODE (with brackets) at the end of your code as shown in my below sample that does not do anything but provide a visual sample.

#
# Set the necessary product code
import logging

# Import arcpy module
import arcpy

# if run in ArcGIS Desktop, show messages, also print to log
def log(method, msg):
    print msg
    method(msg)

logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s %(levelname)-s %(message)s',
        datefmt='%a, %d %b %Y %H:%M:%S',
        filename='ServiceLocationsAttributes_Updates.log',
        filemode='w'
        )

log(logging.info, "Updating Service Locations Feature Class Begins")

# Local variables:
SERVICE_LOCATION = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb\\Electric\\SERVICE_LOCATION"
CIS_Account_Data = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb\\CIS_Account_Data"
CIS_Service_Meters = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb\\CIS_Service_Meters"
SERVICE_LOCATION_FL = "SERVICE_LOCATION_FL"
SERVICE_LOCATION_FL__2_ = "SERVICE_LOCATION_FL"
SERVICE_LOCATION_FL__24_ = "SERVICE_LOCATION_FL"

# Process: Make Feature Layer of Service Location FC
log(logging.info, "Create Feature Layer for Service Location Feature Class")
arcpy.MakeFeatureLayer_management(SERVICE_LOCATION, SERVICE_LOCATION_FL, "", "", "SUBTYPE SUBTYPE VISIBLE NONE;PHASE PHASE VISIBLE NONE;OH_UG OH_UG VISIBLE NONE;FLOW FLOW VISIBLE NONE;PROTECTION_DEVICE PROTECTION_DEVICE VISIBLE NONE;CIS_SERVICE_ID CIS_SERVICE_ID VISIBLE NONE;TRANSFORMER_BANK_ID TRANSFORMER_BANK_ID VISIBLE NONE;POLE_NUMBER POLE_NUMBER VISIBLE NONE;LINE_AND_POLE LINE_AND_POLE VISIBLE NONE;LOCATION_TAG LOCATION_TAG VISIBLE NONE;CIRCUIT_ID CIRCUIT_ID VISIBLE NONE;CIRCUIT_SOURCE CIRCUIT_SOURCE VISIBLE NONE;ASSEMBLY_CODE ASSEMBLY_CODE VISIBLE NONE;DESIGN_STATE DESIGN_STATE VISIBLE NONE;COMMENTS COMMENTS VISIBLE NONE;LABEL_TEXT LABEL_TEXT VISIBLE NONE;LOT_NUMBER LOT_NUMBER VISIBLE NONE;EASEMENT EASEMENT VISIBLE NONE;ATS_DIRTY ATS_DIRTY VISIBLE NONE;DTS_CREATED DTS_CREATED VISIBLE NONE;DTS_CHANGED DTS_CHANGED VISIBLE NONE;CHANGED_BY CHANGED_BY VISIBLE NONE;ATS_GUID ATS_GUID VISIBLE NONE;LOCATION_OID LOCATION_OID VISIBLE NONE;SYMBOL_ROTATION SYMBOL_ROTATION VISIBLE NONE;ANCILLARYROLE ANCILLARYROLE VISIBLE NONE;ENABLED ENABLED VISIBLE NONE;OLD_LOCATION OLD_LOCATION VISIBLE NONE;TRANSFORMER_MAPNO TRANSFORMER_MAPNO VISIBLE NONE;TRUCK_NUMBER TRUCK_NUMBER VISIBLE NONE;SHAPE SHAPE VISIBLE NONE;OBJECTID OBJECTID VISIBLE NONE;METER_TYPE_ID METER_TYPE_ID VISIBLE NONE;METER_PACK_OID METER_PACK_OID VISIBLE NONE;METER_SOCKET METER_SOCKET VISIBLE NONE;SERVICE_ADDRESS SERVICE_ADDRESS VISIBLE NONE;LOC_TYPE LOC_TYPE VISIBLE NONE;SERVICE_TYPE SERVICE_TYPE VISIBLE NONE;MEMBER_NUMBER MEMBER_NUMBER VISIBLE NONE;CUSTOMER_NAME CUSTOMER_NAME VISIBLE NONE;MAIL_ADDRESS MAIL_ADDRESS VISIBLE NONE;MAIL_CITY MAIL_CITY VISIBLE NONE;MAIL_STATE MAIL_STATE VISIBLE NONE;MAIL_ZIP MAIL_ZIP VISIBLE NONE;ACCOUNT_NUMBER ACCOUNT_NUMBER VISIBLE NONE;TWACS_NUMBER TWACS_NUMBER VISIBLE NONE;METER_TYPE METER_TYPE VISIBLE NONE;METER_BOOK METER_BOOK VISIBLE NONE;BILLING_CYCLE BILLING_CYCLE VISIBLE NONE;PHONE PHONE VISIBLE NONE;PHONE_TYPE PHONE_TYPE VISIBLE NONE;ACCOUNT_STATUS ACCOUNT_STATUS VISIBLE NONE;RATE_CODE RATE_CODE VISIBLE NONE;MULTIPLIER MULTIPLIER VISIBLE NONE;READ_DATE READ_DATE VISIBLE NONE;BILLED_DATE BILLED_DATE VISIBLE NONE;DISCONNECT_DATE DISCONNECT_DATE VISIBLE NONE;CIS_EXPORT_DATE CIS_EXPORT_DATE VISIBLE NONE;DIAL_STYLE DIAL_STYLE VISIBLE NONE;KSUBH KSUBH VISIBLE NONE;FORM FORM VISIBLE NONE;METER_AMPS METER_AMPS VISIBLE NONE;METER_WIRES METER_WIRES VISIBLE NONE;METER_VOLTS METER_VOLTS VISIBLE NONE;METER_CLASS METER_CLASS VISIBLE NONE;METER_TYPE_DESC METER_TYPE_DESC VISIBLE NONE;MANUFACTURER MANUFACTURER VISIBLE NONE;SERVICE_ADD_NOTE SERVICE_ADD_NOTE VISIBLE NONE;AMPS AMPS VISIBLE NONE;SOCKET_CONDITION SOCKET_CONDITION VISIBLE NONE;POINT_X POINT_X VISIBLE NONE;POINT_Y POINT_Y VISIBLE NONE;MEWCo_MAP_GRID MEWCo_MAP_GRID VISIBLE NONE")

# Process: Add Join (CIS Account Data Table)
log(logging.info, "Add CIS_Account_Data Table to Feature Layer")
arcpy.AddJoin_management(SERVICE_LOCATION_FL, "POLE_NUMBER", CIS_Account_Data, "MAPNO", "KEEP_ALL")

# Process: Calculate Field (Member Number)
log(logging.info, "Update Member Number Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", "[CIS_Account_Data.MEMBERNO]", "VB", "")

# Process: Calculate Field (Customer Name)
log(logging.info, "Update Customer Name Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.CUSTOMER_NAME", "[CIS_Account_Data.CUSTNAME]", "VB", "")

# Process: Calculate Field (Mailing Address)
log(logging.info, "Update Mail Address Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MAIL_ADDRESS", "[CIS_Account_Data.MAIL_ADDRESS]", "VB", "")

# Process: Calculate Field (Mail City)
log(logging.info, "Update Mail City Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MAIL_CITY", "[CIS_Account_Data.MAIL_CITY]", "VB", "")

# Process: Calculate Field (Mail State)
log(logging.info, "Update Mail State Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MAIL_STATE", "[CIS_Account_Data.MAIL_STATE]", "VB", "")

# Process: Calculate Field (Mail Zip)
log(logging.info, "Update Mail Zip Field")
arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MAIL_ZIP", "[CIS_Account_Data.ZIP]", "VB", "")

Here is a clip of the code

MichaelVolz · ‎04-08-2013

Chris:

Can you try running the script on just 1 field and see how long it takes. Then try running the script on 1 other field and see how long that takes. Then run the script on those two fields and see if it runs as long as running the 2 fields in individual scripts?

I'm just curious if the CalculateField_management process runs much longer on the 2nd field when run in series.

ModernElectric · ‎04-09-2013

Chris:

Can you try running the script on just 1 field and see how long it takes. Then try running the script on 1 other field and see how long that takes. Then run the script on those two fields and see if it runs as long as running the 2 fields in individual scripts?

I'm just curious if the CalculateField_management process runs much longer on the 2nd field when run in series.

I'm curious on one thing - is doing the "Make Feature Layer" and "Add Join" have anything to do with the long time it takes to do the
"Calculate Field" tool outside of an edit session??

I know there has to be an easier/faster way of taking data from a Geodatabase table and put it into a Geodatabase Feature Class....

I did the (1) field like you suggested and I did (2) fields in the same script and it did not seem to make a difference.....

MichaelVolz · ‎04-09-2013

I'm curious on one thing - is doing the "Make Feature Layer" and "Add Join" have anything to do with the long time it takes to do the
"Calculate Field" tool outside of an edit session??

Chris - Can you just run the "Make Feature Layer" and "Add Join" part of your python script and see how long it takes? This could determine that this is the cause of the long processing time? I would even just run the "Make Feature Layer" separately from the "Add Join" to determine which process is causing the long processing time.