Latest Contributions by JustinWolff

The DBAs of our customer agency ended up using the delete/insert/update tables to create custom views for their analysis.  This worked out well and avoided the need to enable historical versioning or geodatabase archiving.  You are correct though to recommend leaving the underlying DBMS stuff alone, and thankfully so did this customer. ... View more

Biraja, Thanks, I had found that but had not realized this dataset contains two similar fields, 'OBJECTID_1' and 'OBJECTID'.  In this case OBJECTID_1 is the object identifier and the OBJECTID field is a text field leftover from geoprocessing.  But we're guessing this is the issue.  I have to wait until this evening when users are gone to release locks so I can delete and recreate the view.  (Deleting and recreating the view in SSMS alone doesn't fix it, because it still needs to be re-registered with the gdb in Arc.) Thanks for the help. Justin ... View more

Thanks Dan.  No, I haven't read those but will get to them this afternoon.  I'm marking this as Correct since this is likely the kind of info we're looking for.  We just haven't messed with Pro with regards to any of our automated scripting yet and are hesitant because we don't want to break anything.   ... View more

Thanks Jake.  What we actually found was far simpler.....we didn't have a Default User Type Role set.  Sigh... -Justin ... View more

Thanks Randy.  We have this as far as we need it for now, onto other things.  I added some comments to help us remember how it works next time we need it. # Note: usaddress module will need installed from https://github.com/datamade/usaddress # This script is meant to be used with a toolbox within ArcGIS # This script requires two (2) geodatabases, one for the input addresses, and one for the output errors.  # This script requires the input address table to have a field named 'addr', containing the single-line addresses # This script requires the input address table to have fields named: oid, addr, addrNum, addrNumPre, addrNumSuf, # buildNm, corOf, intSep, lndMrkNm, notAddr, occTyp, occId, plcNm, recip, stNm, strNm, strNmPreDir, strNmPreMod, # strNmPreTyp, strNmPstDir, strNmPstMod, strNmPstTyp, subAddrId, subAddrTyp, uspsBxGrpId, uspsBxGrpTyp, uspsBxId, uspsBxTyp, zip # The fields listed above were created just for this script and like 'addr' can be changed to anything necessary.  (i.e. change to match Nebraska statewide parcel address schema) import arcpy from arcpy import env import os import usaddress from collections import OrderedDict arcpy.env.overwriteOutput = True # Get input table table = arcpy.GetParameterAsText(0) # Get the field containing the single-line addresses infield = arcpy.GetParameterAsText(1) # Get the output table for errors (Note: output and input tables must be in different geodatabases) outErrors = arcpy.GetParameterAsText(2) # List of fields used for parsing/tagging input addresses (input table) in try section below fields = ["OID@", "addr", "addrNum", "addrNumPre", "addrNumSuf", "buildNm", "corOf", "intSep", "lndMrkNm", "notAddr", "occTyp", "occId", "plcNm", "recip", "stNm", "strNm", "strNmPreDir", "strNmPreMod", "strNmPreTyp", "strNmPstDir", "strNmPstMod", "strNmPstTyp", "subAddrId", "subAddrTyp", "uspsBxGrpId", "uspsBxGrpTyp", "uspsBxId", "uspsBxTyp", "zip"] # List of fields used for error reporting (output table) in except section below fields1 = ["addr", "oid"] with arcpy.da.UpdateCursor(table, fields) as cursor:      for oid, addr, addrNum, addrNumPre, addrNumSuf, buildNm, corOf, intSep, lndMrkNm, notAddr, occTyp, occId, plcNm, recip, stNm, strNm, strNmPreDir, strNmPreMod, strNmPreTyp, strNmPstDir, strNmPstMod, strNmPstTyp, subAddrId, subAddrTyp, uspsBxGrpId, uspsBxGrpTyp, uspsBxId, uspsBxTyp, zip in cursor:           try: #The strings listed in the 'get' parenthesis below correspond to the usaddress output (i.e., "AddressNumber", etc.)                method = usaddress.tag(addr)[0]                addrNum = method.get("AddressNumber", "") #address number                addrNumPre = method.get("AddressNumberPrefix", "") #a modifier before an address number, e.g. ‘Mile’, ‘#’                addrNumSuf = method.get("AddressNumberSuffix", "") #a modifier after an address number, e.g ‘B’, ‘1/2’                buildNm = method.get("BuildingName", "") #the name of a building, e.g. ‘Atlanta Financial Center’                corOf = method.get("CornerOf", "") #words indicating that an address is a corner, e.g. ‘Junction’, ‘corner of’                intSep = method.get("IntersectionSeparator", "") #a conjunction connecting parts of an intersection, e.g. ‘and’, ‘&’                lndMrkNm = method.get("LandmarkName", "") #the name of a landmark, e.g. ‘Wrigley Field’, ‘Union Station’                notAddr = method.get("NotAddress", "") #a non-address component that doesn’t refer to a recipient                occTyp = method.get("OccupancyType", "") #a type of occupancy within a building, e.g. ‘Suite’, ‘Apt’, ‘Floor’                occId = method.get("OccupancyIdentifier", "") #the identifier of an occupancy, often a number or letter                plcNm = method.get("PlaceName", "") #city                recip = method.get("Recipient", "") #a non-address recipient, e.g. the name of a person/organization                stNm = method.get("StateName", "") #state                strNm = method.get("StreetName", "") #street name, excluding type & direction                strNmPreDir = method.get("StreetNamePreDirectional", "") #a direction before a street name, e.g. ‘North’, ‘S’                strNmPreMod = method.get("StreetNamePreModifier", "") #a modifier before a street name that is not a direction, e.g. ‘Old’                strNmPreTyp = method.get("StreetNamePreType", "") #a street type that comes before a street name, e.g. ‘Route’, ‘Ave’                strNmPstDir = method.get("StreetNamePostDirectional", "") #a direction after a street name, e.g. ‘North’, ‘S’                strNmPstMod = method.get("StreetNamePostModifier", "") #a modifier after a street name, e.g. ‘Ext’                strNmPstTyp = method.get("StreetNamePostType", "") #a street type that comes after a street name, e.g. ‘Avenue’, ‘Rd’                subAddrId = method.get("SubaddressIdentifier", "") #the name/identifier of a subaddress component                subAddrTyp = method.get("SubaddressType", "") #a level of detail in an address that is not an occupancy within a building, e.g. ‘Building’, ‘Tower’                uspsBxGrpId = method.get("USPSBoxGroupID", "") #the identifier of a USPS box group, usually a number                uspsBxGrpTyp = method.get("USPSBoxGroupType", "") #a name for a group of USPS boxes, e.g. ‘RR’                uspsBxId = method.get("USPSBoxID", "") #the identifier of a USPS box, usually a number                uspsBxTyp = method.get("USPSBoxType", "") #a USPS box, e.g. ‘P.O. Box’                zip = method.get("ZipCode", "") #zip code                cursor.updateRow([oid, addr, addrNum, addrNumPre, addrNumSuf, buildNm, corOf, intSep, lndMrkNm, notAddr, occTyp, occId, plcNm, recip, stNm, strNm, strNmPreDir, strNmPreMod, strNmPreTyp, strNmPstDir, strNmPstMod, strNmPstTyp, subAddrId, subAddrTyp, uspsBxGrpId, uspsBxGrpTyp, uspsBxId, uspsBxTyp, zip])           except Exception:                cursor1 = arcpy.da.InsertCursor(outErrors, fields1)                cursor1.insertRow([addr, oid])                arcpy.AddMessage("Bad Input Address = : {}".format(addr))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Thanks again for the help. Justin ... View more

Josh - A quick question for you as you have experience with the usaddress library.  I have things working like I want using usaddress.tag, but I'd also like to see what the output is using the usaddress.parse.  However, when I switch 'tag' to 'parse' in the code below (line 28), all of the records are returned as exceptions, placed in my output error table.  I tried removing commas from the 'addr' input address field values and that didn't make a difference.  Can the 'tag' and 'parse' methods not simply be interchanged like this?  Thanks Justin # Created by Justin Wolff 11/26/2018 # Note: usaddress module will need installed from https://github.com/datamade/usaddress import arcpy from arcpy import env import os import usaddress from collections import OrderedDict arcpy.env.overwriteOutput = True # Get input table table = arcpy.GetParameterAsText(0) # Get the field containing the single-line addresses infield = arcpy.GetParameterAsText(1) # Get the output table for errors outErrors = arcpy.GetParameterAsText(2) # List of fields fields = ["OID@", "addr", "addrNum", "addrNumPre", "addrNumSuf", "buildNm", "corOf", "intSep", "lndMrkNm", "notAddr", "occTyp", "occId", "plcNm", "recip", "stNm", "strNm", "strNmPreDir", "strNmPreMod", "strNmPreTyp", "strNmPstDir", "strNmPstMod", "strNmPstTyp", "subAddrId", "subAddrTyp", "uspsBxGrpId", "uspsBxGrpTyp", "uspsBxId", "uspsBxTyp", "zip"] fields1 = ["addr", "oid"] with arcpy.da.UpdateCursor(table, fields) as cursor:      for oid, addr, addrNum, addrNumPre, addrNumSuf, buildNm, corOf, intSep, lndMrkNm, notAddr, occTyp, occId, plcNm, recip, stNm, strNm, strNmPreDir, strNmPreMod, strNmPreTyp, strNmPstDir, strNmPstMod, strNmPstTyp, subAddrId, subAddrTyp, uspsBxGrpId, uspsBxGrpTyp, uspsBxId, uspsBxTyp, zip in cursor:           try:                method = usaddress.parse(addr)[0]                addrNum = method.get("AddressNumber", "")                addrNumPre = method.get("AddressNumberPrefix", "")                addrNumSuf = method.get("AddressNumberSuffix", "")                buildNm = method.get("BuildingName", "")                corOf = method.get("CornerOf", "")                intSep = method.get("IntersectionSeparator", "")                lndMrkNm = method.get("LandmarkName", "")                notAddr = method.get("NotAddress", "")                occTyp = method.get("OccupancyType", "")                occId = method.get("OccupancyIdentifier", "")                plcNm = method.get("PlaceName", "")                recip = method.get("Recipient", "")                stNm = method.get("StateName", "")                strNm = method.get("StreetName", "")                strNmPreDir = method.get("StreetNamePreDirectional", "")                strNmPreMod = method.get("StreetNamePreModifier", "")                strNmPreTyp = method.get("StreetNamePreType", "")                strNmPstDir = method.get("StreetNamePostDirectional", "")                strNmPstMod = method.get("StreetNamePostModifier", "")                strNmPstTyp = method.get("StreetNamePostType", "")                subAddrId = method.get("SubaddressIdentifier", "")                subAddrTyp = method.get("SubaddressType", "")                uspsBxGrpId = method.get("USPSBoxGroupID", "")                uspsBxGrpTyp = method.get("USPSBoxGroupType", "")                uspsBxId = method.get("USPSBoxID", "")                uspsBxTyp = method.get("USPSBoxType", "")                zip = method.get("ZipCode", "")                cursor.updateRow([oid, addr, addrNum, addrNumPre, addrNumSuf, buildNm, corOf, intSep, lndMrkNm, notAddr, occTyp, occId, plcNm, recip, stNm, strNm, strNmPreDir, strNmPreMod, strNmPreTyp, strNmPstDir, strNmPstMod, strNmPstTyp, subAddrId, subAddrTyp, uspsBxGrpId, uspsBxGrpTyp, uspsBxId, uspsBxTyp, zip])           except Exception:                cursor1 = arcpy.da.InsertCursor(outErrors, fields1)                cursor1.insertRow([addr, oid])                arcpy.AddMessage("Bad Input Address = : {}".format(addr))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ ... View more

Josh, You and Randy both helped and gave me correct answers to what really ended up being separate questions.  I just tossed a coin and will mark you as having the correct answer. Thank you and Randy both for the help.  (I can't promise I won't be back asking more questions....) I really appreciate the assistance. Justin ... View more

Thanks Randy - that did the trick, and it makes sense.  Thank you for the explanation. Justin ... View more

Okay, I did recognize that i can't use the same cursor, so starting a new cursor ('cursor1') within the exception is now only populating the errors table with actual error records, but there are multiple entries for each error.   import arcpy from arcpy import env import os import usaddress from collections import OrderedDict arcpy.env.overwriteOutput = True # Get input table table = arcpy.GetParameterAsText(0) # Get the field containing the single-line addresses infield = arcpy.GetParameterAsText(1) # Get the output table for errors outErrors = arcpy.GetParameterAsText(2) # List of fields fields = ["addr", "addrNum", "stNm", "zip", "OID@"] with arcpy.da.UpdateCursor(table, fields) as cursor:      for addr, addrNum, stNm, zip, oid in cursor:           try:                parse = usaddress.tag(addr)[0]                addrNum = parse.get("AddressNumber", "")                stNm = parse.get("StreetName", "")                zip = parse.get("ZipCode", "")                cursor.updateRow([addr, addrNum, stNm, zip, oid])           except Exception:                cursor1 = arcpy.da.InsertCursor(outErrors, fields)                for row in (str(oid)):                     cursor1.insertRow([addr, addrNum, stNm, zip, oid])                                     arcpy.AddMessage("Error with record: {}".format(oid))                arcpy.AddMessage("Bad Input Address = : {}".format(addr))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Need to figure out why it's giving multiple returns now... Justin ... View more

I spent most of the day on this and didn't get too far, but I have something.  I wasn't able to write to a table in the same .gdb as the source address table, but I am writing to a table in a different .gdb.  This isn't a huge deal.  However, using the code below, as soon as the first exception is found, it stops parsing the addresses and populating fields in the source table ('addresses2'), and then populates all remaining records in the errors table ('errors').  So once it finds an exception it doesn't go back to the try. import arcpy from arcpy import env import os import usaddress from collections import OrderedDict arcpy.env.overwriteOutput = True # Get input table table = arcpy.GetParameterAsText(0) # Get the field containing the single-line addresses infield = arcpy.GetParameterAsText(1) # Get the output table for errors outErrors = arcpy.GetParameterAsText(2) # List of fields fields = ["addr", "addrNum", "stNm", "zip", "OID@"] with arcpy.da.UpdateCursor(table, fields) as cursor:      for addr, addrNum, stNm, zip, oid in cursor:           try:                parse = usaddress.tag(addr)[0]                addrNum = parse.get("AddressNumber", "")                stNm = parse.get("StreetName", "")                zip = parse.get("ZipCode", "")                cursor.updateRow([addr, addrNum, stNm, zip, oid])           except Exception:                cursor = arcpy.da.InsertCursor(outErrors, fields)                for row in (str(oid)):                     cursor.insertRow([addr, addrNum, stNm, zip, oid])                                     arcpy.AddMessage("Error with record: {}".format(oid))                arcpy.AddMessage("Bad Input Address = : {}".format(addr))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ Below you can see how record 310 in the addresses2 table is the first exception, which is written to the errors table, but all subsequent records are written as exceptions as well. I feel I'm close, I'm just not sure where to go from here. Thanks Justin ... View more