topic Re: Best approach at continually updating data in Developers Questions

Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 15:33:25 GMT

Hi Everyone,

I am hoping to generate some ideas on how I should best handle a weekly update of some GIS data, particularly the attributes.

This seems simple enough but I wanted to see what other’s thought. I am working on a project where we have some parcels and each one has a status (with like 9 different options). Each week, we update this status with a new status, but only when it needs updating. The parcels have unique identifiers.

We get this info in the form of an Excel sheet (with the unique parcel number and the new status, etc.). So far, I have been manually updating each parcel’s status with the new status since this is the quick and dirty way, but I want to find a more automated solution.

Based on this vague information, what are your thoughts on the most efficient way of handling this?

Thanks!

(to shorten this post, I’ll put some thoughts in a reply below)

Re: Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 15:34:15 GMT

I had thought of making a Python toolbox through ArcMap or ArcGIS Pro that would list each parcel, show the current status, and then give a drop-down list for updating the status. But, it seems that the toolboxes do not necessarily behave this way. This might call for a different kind of program.

I had thought of having some kind of interface that lists each parcel and allows the user to update the status with a drop-down, and use the UpdateCursor (arcpy) to update each row, etc. But, wasn’t sure if this would work the best way. My thoughts were leaning towards utilizing the tkinter module to make a form…

The data is in a file geodatabase but that doesn’t really make or break anything. The data will also be displayed in ArcGIS Online. I am not opposed to using any kind of web-based programming to handle these updates either.

Anyways, I would love ideas and suggestions

Re: Best approach at continually updating data

RebeccaStrauch__GISP — Mon, 18 Apr 2016 15:43:36 GMT

Adrian, if it is already in and Excel spreadsheet with a common field (parcel ID), why not just related the two files and calculate the field. If there isn't a reason to view this manually before the update, the should work faster.

might not hurt to create a backup of the attribute table before the update, in case there are any blips in the process. Depending on where the data is stored, this might just be a version or an archive.

Geodatabase archiving—ArcGIS Help | ArcGIS for Desktop

Re: Best approach at continually updating data

DarrenWiens2 — Mon, 18 Apr 2016 15:56:21 GMT

This sounds like something accomplished with a join (on parcel number) then field calculate status.

Re: Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 16:22:17 GMT

Rebecca, thanks for the reply. That is a great idea and would work well if the data was always clean and nice. I guess I could 'force' the people providing the data to make it match my domains. Definitely good idea about the archiving.

I had the thought of allowing the project manager access to some kind of updating tool so that he could be the one putting in the updates (with a simple drop down list) when he got the updates (but these types of people really like to delegate, haha). The other thing is, it's not always in a spreadsheet. Sometimes it's an email list.

But, I like the idea of making this a spreadsheet type of action to where I can force the updating people to give the correct information, and then relate, then calculate the field.

Re: Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 16:25:00 GMT

Darren, this just seems too simple. I want to make an app that will be flashy and do everything I want without any issues!!

Though, I like your approach. I might have to start making people give me spreadsheets in a standard format instead of the [junk] that I always get, haha.

Re: Best approach at continually updating data

DarrenWiens2 — Mon, 18 Apr 2016 16:37:29 GMT

For your own sanity, I'd keep it simple as possible - it is so painful trying to anticipate every little thing the user can do to break your system. If possible, you should only give them the option to enter data correctly. I agree you should request a standardized format. You may also consider using a database that can take advantage of data validation upon entry (like an FGDB domain, although which DB you choose will depend on your setup).

Re: Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 17:53:46 GMT

I like this idea. Sanity is good. Users are good at breaking systems, unfortunately. I will have to implement something like this. I appreciate y'all's help!

Re: Best approach at continually updating data

WesMiller — Sat, 11 Dec 2021 10:20:10 GMT

Adrian,

You could absolutely over complicate if you wanted.

Get your domain
Validate your excel against your domain
Fix errors
Join the tables
Query for changes originField <> updatedField
Calculate originField to match updatedField

#import module
import arcpy


#get the feature class we want the domains for
fc = arcpy.GetParameterAsText(0)


desc = arcpy.Describe(fc)
desc1 = arcpy.Describe(desc.path)


#print desc1.datasetType
if desc1.dataType == 'FeatureDataset':
    fcworkspace = desc1.path
else:
    fcworkspace = desc.path




#List the domains
domains = arcpy.da.ListDomains(fcworkspace)


#List the fields 
fields = arcpy.ListFields(fc)


domainList = []


#Loop through the fields, determine if they have a domain and add
#those with domains to the dictionary and assign it a default value
for field in fields:
    if field.domain:
        domainList.append(field.domain)


#Create a dictionary to hold the data
domaindict = {}


#Loop through domains and grab the coded values for feature class
for domain in domains:
    if domain.domainType =='CodedValue':
        if domain.name in domainList:
            domaindict[domain.name]=domain.codedValues
textline = ""
for key,value in domaindict.iteritems():
    textline = textline + key + '\n'
    for k,v in value.iteritems():
        textline = textline + '\t' + str(k) + '\t:\t' +str(v) + '\n'
arcpy.AddMessage(textline)

Re: Best approach at continually updating data

AdrianWelsh — Mon, 18 Apr 2016 19:40:20 GMT

Wes, I love over complications! This code is great! Thanks for sharing. I'll have to tinker around with it some and come up with some added complications!

Re: Best approach at continually updating data

TedKowal — Tue, 19 Apr 2016 16:16:21 GMT

Adrian Just to add a little more to Rebecca's comments... I have a similar situation with updating maintenance needs on bridges. Our situation, we have many different consultants inspecting bridges all with different software systems and such ... but all had an excel spreadsheet in common. So I learned and applied a little vba programming to excel to "Standardize" and validate the excel form to match our domains and attributes... this being a little down and dirty has work out very well for us.

Re: Best approach at continually updating data

TedKowal — Tue, 19 Apr 2016 16:22:09 GMT

--- LOL ---

Excel VLookup is soooooooo much more simple. Guess I am a minimalist.

Re: Best approach at continually updating data

DarrenWiens2 — Tue, 19 Apr 2016 16:45:53 GMT

Well, why bother using all those ArcGIS/arcpy tools? You can just manipulate the Excel through Python.

Re: Best approach at continually updating data

AdrianWelsh — Sat, 11 Dec 2021 10:20:12 GMT

Alright Wes Miller, I finally was able to get back to this. I attempted to over-complicate but said "screw it" on the domains.

After some quality checking, this seemed to work really well, but I wanted to share my code to see how I can improve things. This is what I have:

#import modules
import arcpy
from time import strftime

# start the timer...
print 'Start Script: ' + strftime('%Y-%m-%d %H:%M:%S')

# identify variables for feature class and table
workspace = 'C:/Users/AWelsh/Documents/2016/' + \
            '15019-MWN-SR68_Bangerter to 12600 South (11203)/' +\
            '11203-RedwoodRd-Bangerter-to-126S/' +\
            '11203-RedwoodRd-Bangerter-to-126S.gdb'

fcParcels = 'Parcel_SpatialJoin_3_JoinTable_FIX'
tblParcels = 'ParcelTable_20160519'

# set the workspace environment to our workspace
arcpy.env.workspace = workspace

# identify fields used for updating
fieldsParcels = ['PARCEL_NUMBER', 'PARTIAL_SUBMITTAL', 'SUBMITTALSCHEDULE',
                'COUNTY_TAX_ID_NO', 'RW_Sheet', 'SQUARE_FEET_OF_TAKE',
                'SQUARE_FEET_PERP_EASE', 'SQUARE_FEET_TEMP_EASE',
                'RISK_MITIGATION', 'Status']

# Here is what we're going to do:
# One-by-one, check each row in the Feature Class against
# each row in the Table, and update if there are changes...

# first, it helps to "zero" out the arrays and counter
fcrow = ['','','','','','','','','','']
tblrow = ['','','','','','','','','','']
counter = 0

# putting this all in a try-catch statement to catch any errors
try:

    with arcpy.da.SearchCursor(tblParcels, fieldsParcels) as tblCursor:
        for tblrow in tblCursor:
            with arcpy.da.UpdateCursor(fcParcels, fieldsParcels) as fcCursor:
                fcrow = ['','','','','','','','','','']
                # ...need to zero out the row again...
                for fcrow in fcCursor:
                    if (str(tblrow[0]) == str(fcrow[0])
                        and str(tblrow[3]) == str(fcrow[3])):
                        fcrow[1] = tblrow[1]
                        fcrow[2] = tblrow[2]
                        fcrow[4] = tblrow[4]
                        fcrow[5] = tblrow[5]
                        fcrow[6] = tblrow[6]
                        fcrow[7] = tblrow[7]
                        fcrow[8] = tblrow[8]
                        fcrow[9] = tblrow[9]
                        print('Parcel ' + str(fcrow[0]) + ' was updated.')
                        fcCursor.updateRow(fcrow)
                        counter = counter + 1
                        continue

# except statement to catch the errors
except Exception:
    e =sys.exc_info()[1]
    print(e.args[0])
    arcpy.AddError(e.args[0])
except arcpy.ExcecuteError:
    print(arcpy.GetMessages(2))

# how many rows were updated?
print 'Updated ' + str(counter) + ' rows.'
# end the timer...
print 'Finshed Script: ' + strftime('%Y-%m-%d %H:%M:%S')

Re: Best approach at continually updating data

WesMiller — Thu, 16 Jun 2016 12:53:37 GMT

If it does what you need it to do, go with it. You may have gotten a lot more speed using a table join and calculate field tools.