topic Re: Updating Feature Class Attributes from Table in Data Management Questions

Updating Feature Class Attributes from Table

ModernElectric — Mon, 08 Apr 2013 15:03:11 GMT

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

Re: Updating Feature Class Attributes from Table

MichaelVolz — Mon, 08 Apr 2013 15:10:22 GMT

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).

Re: Updating Feature Class Attributes from Table

ModernElectric — Mon, 08 Apr 2013 15:25:14 GMT

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.

Re: Updating Feature Class Attributes from Table

MichaelVolz — Mon, 08 Apr 2013 15:35:13 GMT

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

Re: Updating Feature Class Attributes from Table

ModernElectric — Mon, 08 Apr 2013 15:45:56 GMT

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??

Re: Updating Feature Class Attributes from Table

MichaelVolz — Fri, 10 Dec 2021 23:24:51 GMT

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]

Re: Updating Feature Class Attributes from Table

ModernElectric — Fri, 10 Dec 2021 23:24:54 GMT

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

Re: Updating Feature Class Attributes from Table

MichaelVolz — Mon, 08 Apr 2013 17:38:38 GMT

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.

Re: Updating Feature Class Attributes from Table

ModernElectric — Tue, 09 Apr 2013 15:52:26 GMT

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.....

Re: Updating Feature Class Attributes from Table

MichaelVolz — Tue, 09 Apr 2013 16:24:01 GMT

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.

Re: Updating Feature Class Attributes from Table

ModernElectric — Tue, 09 Apr 2013 17:00:55 GMT

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.

I went through and just did the "Make Feature Layer" and "Add Join" tool on its own and it was done within 10 seconds. For some
reason - the calculate field tool takes a long time??? Not sure how to speed it up or if there is another tool that works the same way??

Is it a problem that I am trying to "Override" (E) values/data inside the field? Would it make any difference if the field was blank??

Re: Updating Feature Class Attributes from Table

MichaelVolz — Tue, 09 Apr 2013 17:25:42 GMT

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", "[CIS_Account_Data.MEMBERNO]", "VB", "")

Chris:

Maybe because you are using the VB syntax it is taking a long time for the CalculateField_management process. Try converting this to PYTHON with the appropriate syntax for field names [field name] as per the documentation.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000004m000000

Re: Updating Feature Class Attributes from Table

ModernElectric — Tue, 09 Apr 2013 20:56:51 GMT

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", "[CIS_Account_Data.MEMBERNO]", "VB", "")

Chris:

Maybe because you are using the VB syntax it is taking a long time for the CalculateField_management process. Try converting this to PYTHON with the appropriate syntax for field names [field name] as per the documentation.

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000004m000000

How exactly would I write that? I am still trying to figure out Python

Re: Updating Feature Class Attributes from Table

MichaelVolz — Wed, 10 Apr 2013 11:38:55 GMT

Chris:

Here is a code snippet where I use python syntax instead of VB syntax:

arcpy.CalculateField_management(rdcl_Layer, "MUNI_NUM_RIGHT_UNPAD", '!MUNI_NUM_RIGHT!.lstrip("0")', "PYTHON", "")

For your code: arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", "[CIS_Account_Data.MEMBERNO]", "VB", "")

change it to

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", '!CIS_Account_Data.MEMBERNO!', "PYTHON", "")

I would even set the env variable as the destination file geodatabase so you do not need to reference it in your call to CalculateField_management

Near the top of your script below import arcpy

from arcpy import env
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb\\CIS_Account_Data"

then your code could be

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON", "")

I'm not really sure this is the issue as I use VB notation in a CalculateField_management process where I have 200,000 records and it only takes 5 minutes as opposed to hours.

Re: Updating Feature Class Attributes from Table

ModernElectric — Wed, 10 Apr 2013 14:00:00 GMT

Chris:

Here is a code snippet where I use python syntax instead of VB syntax:

arcpy.CalculateField_management(rdcl_Layer, "MUNI_NUM_RIGHT_UNPAD", '!MUNI_NUM_RIGHT!.lstrip("0")', "PYTHON", "")

For your code: arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", "[CIS_Account_Data.MEMBERNO]", "VB", "")

change it to

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", '!CIS_Account_Data.MEMBERNO!', "PYTHON", "")

I would even set the env variable as the destination file geodatabase so you do not need to reference it in your call to CalculateField_management

Near the top of your script below import arcpy

from arcpy import env
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb\\CIS_Account_Data"

then your code could be

arcpy.CalculateField_management(SERVICE_LOCATION_FL__2_, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON", "")

I'm not really sure this is the issue as I use VB notation in a CalculateField_management process where I have 200,000 records and it only takes 5 minutes as opposed to hours.

Michael-

That helps out alot...I am not getting any error messages so I wrote the code correctly with your help. But that still does not solve my problem. It seems to be freezing or not finishing when I run the calculate field tool. The Geodatabase and all of the data is on my Local drive instead of being ran over the network. Also - I am running the Python script with Python IDLE 2.6.5. Do you think that might be a reason why it is taking so long to run?? You have been a great help in working with me on the code. I am not sure if
its the Geodatabase or the version of Python I am using or something else. I am only updating 9000 records and working with (1) field right now. Anything else you can possibly think of that I am doing wrong???

Re: Updating Feature Class Attributes from Table

MichaelVolz — Wed, 10 Apr 2013 14:08:28 GMT

Two things:

I would post this thread to the Python subforum as you might can help from more advanced python users.

Can you provide the remainder of your code as I'm wondering if the bottleneck might be with saving the feature class to disk as the arcpy.MakeFeatureLayer_management should only save the feature class to memory so you still need to save the feature class permanently to your file gdb?

Re: Updating Feature Class Attributes from Table

ModernElectric — Fri, 10 Dec 2021 23:24:57 GMT

Here is the current updated Python script that I have. I tried to run the script through Python IDLE and just running it by double-clicking on the file which brings up DOS... and it still seems to lag and lag.

I will post this on the Python form and see if someone else can think of something.

## Set the necessary product code
import logging

# Import arcpy module
import arcpy
from arcpy import env


# 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='ServiceLocation_CISAccountData_Updates.log',
        filemode='w'
        )

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

# Set environment settings
env.workspace = "C:\\MEWCo GIS System\\Electric System\\MEWCo_Electric_Model-LOCAL.gdb"

# Set Local variables:
inFeatures = "SERVICE_LOCATION"                         # Service Location Feature Class
layerName = "SERVICE_LOCATION_LAYER"                    # Service Location Feature Layer
fieldName1 = "SERVICE_ADDRESS"
expression1 = "CIS_Account_Data.SERVICE_ADDRESS"
joinField = "POLE_NUMBER"
joinField2 = "MAPNO"
joinTable = "CIS_Account_Data"

# Create a Feature Layer from Service_Location Feature Class
log(logging.info, "Create Feature Layer for Service Location Feature Class")
arcpy.MakeFeatureLayer_management(inFeatures, layerName)

# Join the feature layer to a table
log(logging.info, "Join Table to Feature Layer Service Location")
arcpy.AddJoin_management(layerName, joinField, joinTable, joinField2)
    
# Process: Calculate Field (Member Number)
log(logging.info, "Update Member Number Field")
arcpy.CalculateField_management(layerName, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON")

log(logging.info, "Complete")

Re: Updating Feature Class Attributes from Table

MichaelVolz — Wed, 10 Apr 2013 14:54:28 GMT

Chris:

When is the feature class being saved to disk, as I do not see this in your code sample?

arcpy.CalculateField_management(layerName, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON")

This appears to be incorrect as layerName should be the in_table
expression should be !CIS_Account_Data.MEMBERNO! as you need to explicitly specify that this is coming from a join field.

Re: Updating Feature Class Attributes from Table

ModernElectric — Wed, 10 Apr 2013 15:09:31 GMT

Chris:

When is the feature class being saved to disk, as I do not see this in your code sample?

arcpy.CalculateField_management(layerName, "SERVICE_LOCATION.MEMBER_NUMBER", '!MEMBERNO!', "PYTHON")

This appears to be incorrect as layerName should be the in_table
expression should be !CIS_Account_Data.MEMBERNO! as you need to explicitly specify that this is coming from a join field.

No Dice.... I am working with just this (1) field and after waiting 20 minutes - it does not complete. Is is possible this temp feature layer is causing issues??

Re: Updating Feature Class Attributes from Table

MichaelVolz — Wed, 10 Apr 2013 15:49:49 GMT

Chris:

Can you try to create a new layer on disk and work with that instead of the FeatureLayer? I'm not sure this is possible, as you might have to work with the in-memory feature layer and then save to disk once all the processing has been completed.