topic Re: Python: Comparing attributes in a SQL Server table with a ArcSDE feature class in Geoprocessing Questions

Python: Comparing attributes in a SQL Server table with a ArcSDE feature class

KevinCressy — Thu, 27 May 2010 07:51:44 GMT

Morning all,

I have been asked to write a python script to update an ArcSDE feature class using information recorded in a separate SQL Server table.

I have a table of data that regularly gets updated by staff in my organisation. This table has a unique identifier that corresponds with a unique identifier in a point feature class in ArcSDE. I need to:

- look up the first unique ID in the table
   - find the corresponding value in the ArcSDE feature class.
   - check to see if the attributes values in the table are different to the attribute values for same feature in the ArcSDE feature class
   - if there are any changes update the ArcSDE feature with the attribute values from the table.
- go to next record in the table and start the process again.

Is there an easy way cycle through each feature in the ArcSDE feature class and make this comparison?

Any help would be appreciated.

Many thanks,

Kevin

Re: Python: Comparing attributes in a SQL Server table with a ArcSDE feature class

BernardNtiamoah — Tue, 15 Jun 2010 14:24:03 GMT

hi KCressy,
First of all, you need to loop thru the feature class and compare each row[FIELD] value to the table.
If they are different, then grab the tables row[FIELD value and use it to update the feature class.
Here is an example:
table = "c:/mydb.mdb/myTable"
fc = "c:/mydb.mdb/myFC"
#common field
cField = "FIELD1"
#other fields
F2 = "FIELD2"
F3 = "FIELD3"
F4 = "FIELD4"
gp.arcgisscripting.create()
rows = gp.updatecursor(fc)
row = rows.next()
while row:
    #grab the common field value
    fcValue = row.getvalue(cField)

    #grab the rest of the fields value
     fcF2 = row.getvalue(F2)
     fcF3 = row.getvalue(F3)
     fcF4 = row.getvalue(F4)

     #Now loop thru the table and compare the values
      tableRows = gp.searchcursor(table)
      tableRow = tableRows.Next()
      while tableRow:
              #grab the common field value
                 tableValue = tableRow.getvalue(cField)
                #grab the rest of the table fields values(just like what we did for the fc above)
                 tableF1 = tableRow.getvalue(FIELD)
                 tableF2 = tableRow.getvalue(FIELD2)
                  ......
                 #Now compare table to fc using the common field
                  if str(tableValue) == str(fcValue):
                           #compare the rest of the fields the same way
                            e.g if str(fcF2) <> str(tableF1):

                             #if they are not the same then update the fc with the table value
                                 row.setvalue(F2, tableF1) #(or row.FIELDNAME = tableF1 also works)
                               #continue to compare the rest of the fields values and update accordingly, if necessary
                               #after changing all the values
                                rows.updateRow(row)
                     row2 = rows2.Next()
      row = rows.Next()

I hope this example helps. Otherwise let me know what the problem is.

Bernard
GIS Programmer/Analyst

Re: Python: Comparing attributes in a SQL Server table with a ArcSDE feature class

JasonWarzinik — Wed, 21 Jul 2010 13:12:34 GMT

Bernard,

In your example your table and fc are in a personal GeoDB would this same code work for versioned data ArcSDE?

Re: Python: Comparing attributes in a SQL Server table with a ArcSDE feature class

BernardNtiamoah — Sat, 18 Sep 2010 05:49:07 GMT

Of course, they would work. Just store a copy of the database connection in any drive,say G:drive or whatever. Example G:\Folder\DB.sde. Set this as your workspace and there you go!. Let me know if you have any other question.

Re: Python: Comparing attributes in a SQL Server table with a ArcSDE feature class

VINCENTHERRERA — Thu, 21 Oct 2010 20:26:08 GMT

Of course, they would work. Just store a copy of the database connection in any drive,say G:drive or whatever. Example G:\Folder\DB.sde. Set this as your workspace and there you go!. Let me know if you have any other question.

I have a similar issue with compare however what I need to compare is globalID's in (2) tables due to replication this code seems to address what I would like to do, however I dont need it to update any records.What I would like to do after it compares is receive a message that tells me it matched or not even better it would be nice if I could get it to created a list of matched and unmatched globalID's based on the same features.
Do you think I can use this and modify it to read the (2) feature classes?

Vin