Latest Contributions by RichardFairhurst

Your approach still mystifies me and I still don't understand your business rules.  Your rules may make sense to you and may be correct for your business needs, but on the surface they at least partially conflict with my experience in synchronizing data and matching tables.  The picture in your head of how everything should work is not transferring into mine yet. I highly recommend that you reconsider the rule that says: Table 2 - Features missing [EasyID] need to be assigned the next number for that [SiteID] in either Table 1 or 2. If current EasyID's for a site are '1', '3', '4A', '6-7','S', and '55', new features would be '2', '4', '5', '6', '7', '8'....etc. The EasyID is anything but easy to understand or program as you have described it.  Filling in these blanks this way seems arbitrary to me especially given that the natural sort of the strings is actually '1', '3', '4A', '55', '6-7', 'S'.  Why fill in blanks at all?  Over time that means any deleted records will have their SiteID + EasyID combination reused for an entirely unrelated record, and therefore that key is only unique within the snapshot in time before the script reuses it.  In other words, you will never be able to use the SiteID + EasyID key if you ever have to compare two different data snapshots that were taken before and after the script ran.  This rule may make sense to you, but in my experience this is a bad database practice.  Unique keys (single or multi-field) are only valuable in my experience if they are unique to one record over all time or support actual data relationships and become a problem if they are ever reused for completely unrelated records.  I personally don't want to help implement this rule, since its seems excessively complicated to me, and I believe from experience that a day will come when you will want to use that key to recover from a data corruption event and the code that implements this rule will make that recovery nearly impossible.  You also will greatly increase the likelihood of creating data corruption if you accidentally link together two snapshots that reassigned the same keys to different records. I have several other questions about this EasyID field.  How many characters are allowed in this field?  Why does it contain letters and what is the significance of those letters?  Why are there dashes to combine two numbers?  Since this field is a string field, how do your users handle the fact that it will never sort numerically in any table, since you don't include leading spaces or strings to right-justify them?  What type of business are you working for where this business process was developed to track any of this data in either table? So what little I do understand (or think I understand) I will try to present some code that should fit your needs. This code is more or less what I would start with.  Key fields always should come first in the field list and value fields always should follow.  I would incorporate the OID field into the code processes and dictionaries as a fail safe unique key for linking back to the original table where ever the user defined keys turn out to be duplicated and not unique. The code below handles both a 1:1 and 1:M relationship possibility, so even if the key value is not unique you will be able to trap that and fix it. import arcpy import sys #Tables  T1 = r"C:\Python\Scratch.gdb\Table1"  T2 = r"C:\Python\Scratch.gdb\Table2"  fields = ["SiteID", "EasyID", "FeatureID", "OID@"]  # Intialize T1 as a dictionary T1Dict = {} # Initialize a list to hold any concatenated key duplicates found T1KeyDups = [] # Open a search cursor and iterate rows with arcpy.da.SearchCursor(T1, fields) as searchRows:    for searchRow in searchRows:     # Build a composite key value from 2 fields     keyValue = '{};{}'.format(searchRow[0], searchRow[1])     if not keyValue in T1Dict:       # Key not in dictionary.  Add Key pointing to a list of a list of field values       T1Dict[keyValue] = [ list(searchRow[2:]) ]      else:        # Key in dictionary is not unique.       T1KeyDups.append(keyValue)       # Append a list of field values to the list the Key points to       T1Dict[keyValue].append( list(searchRow[2:]) ) del searchRows, searchRow # Sample of how to access the keys, record count, and record values of the dictionary for keyValue in T1Dict.keys():   for i in range(0, len(T1Dict[keyValue])):     print "The SiteID;EasyID key is {} with {} record(s).  Record {} has FeatureID {} and ObjectID {}.".format(keyValue, len(T1Dict[keyValue]), i+1, T1Dict[keyValue][0], T1Dict[keyValue][1]) if len(T1KeyDups) > 0:   # Duplicate keys exist in T1   # Give a warning and either exit the script or else do a fix of T1 before proceeding   print("Duplicate keys found!  They are:")   for keyValue in T1KeyDups:      for i in range(0, len(T1Dict[keyValue])):        print "The SiteID;EasyID key is {} with {} record(s).  Record {} has FeatureID {} and ObjectID {}.".format(keyValue, len(T1Dict[keyValue]), i+1, T1Dict[keyValue][0], T1Dict[keyValue][1])   # Either exit or fix T1 here   sys.exit(-1) ... View more

You still have to start a loop, but you can break out of it and kill the cursor at any time: import arcpy fc = 'c:/data/base.gdb/features' # Open a cursor on some fields in a table with arcpy.da.SearchCursor(fc, ['OID@', 'SHAPE@AREA']) as cursor:      for row in cursor:           # Do  something with the first row of data here           break del cursor           ... View more

Hunter: That is a good question, and trying to explain that line of code will probably help me understand and use it to the best advantage in the future as well. I will try to break it apart from left to right. valueDict1 = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList)} 1.  Assignment to the final dictionary variable (this should be obvious, but it is definitely important) valueDict1 = 2.  The opening curly bracket (and the closing curly bracket at the very end) makes everything inside part of a dictionary.  Everything else inside the curly brackets ultimately is designed to extract the Key/Value pairs read from the table that will be used by later dictionary look-up processes. { ... } 3.  r stands for a Row read from a cursor opened on your table.  r[0] reads the first (zero based) element of the row, which is actually the value from the first field in the each row that is being read.  The colon following r[0] means that this value will be the dictionary key used by later dictionary look-ups. r[0]: 4: Following the colon is the dictionary Value associated with the dictionary key.  This is defined by everything falling inside the pair of parentheses, which is a tuple, or collection of values that can be treated and passed as a single value.  Dictionary key/value pairs are limited to using a single value after the colon, but that includes collections like a tuple or a list as long as they can be passed as one value. ( ... ) 5.  The r[1:] inside the parenthesis are part of a list comprehension that extracts all of the field values in the rest of the cursor fields being read as the separate items within the tuple or list collection.  The [1:] means start with the value of the second field in the field list and continue appending values from every field that follows until the last field in the field list is read. 6  The "for r in" is part of a list comprehension that iterates through each row in a cursor. for r in 7.  The last part of the list comprehension occurs before the end curly bracket of the dictionary.  This code opens a search cursor on the specified table (sourceFC) which will be used to read the rows from the table.  The cursor will be read one row at a time by the iterator in item 6 above and each row will contain the values of the fields specified in the provided field list (sourceFieldsList). arcpy.da.SearchCursor(sourceFC, sourceFieldsList) The structure above is good where every row will result in a dictionary key that is always unique.  When more than one row can contain the same dictionary key value, usually you need to use a structure to read a cursor into a dictionary similar to the one shown below (there are many possible variations on this pattern, so this is just one example): # Build a summary dictionary from a da SearchCursor with unique key values of a field storing a list of the sum of that value and the record count.  valueDict = {} with arcpy.da.SearchCursor(sourceFC, sourceFieldsList) as searchRows:     for searchRow in searchRows:         keyValue = searchRow[0]         if not keyValue in valueDict:              # assign a new keyValue entry to the dictionary storing a list of the first NumberField value and 1 for the first record counter value             valueDict[keyValue] = [searchRow[1], 1]         # Sum the last summary of NumberField value with the current record and increment the record count when keyvalue is already in the dictionary          else:             valueDict[keyValue][0] += searchRow[1]             valueDict[keyValue][1] += 1 ... View more

What distinguishes the skipped records from the records that were step 6 inserted into the T2 table?  I see no way to keep track of what is new in T1 since the last time the script was run to make that choice.  All of the T2 insertions and the skipped records have no SiteID, so that is not a difference to make that choice.  I don't agree that you have the steps in the correct order from what I can see.  Your logic appears backwards, since normally I would deal with Nulls and new verses old records as my first steps in any comparison script, not towards the end.  My scripts deal with new verses old by renaming existing data and deriving current data from another source, so that the comparison is easy to make.  Possibly a variation of that approach would apply here, so that a last run version of the data is created so you can make sure you know what is really new and what you have processed before. I would also add the ObjectID field for both tables to your field list and reorder the fields as: ["SideID", "EasyID", "FeatureID", "OID@"] The ObjectID would be used in subroutines to impose order on Null values and to validate your assumptions of unique keys. Anyway you never said if errors are occurring of if just unexpected values are being assigned.  Unexpected values indicate a logic failure, while errors indicate a syntax or data validation failure.  I am almost certain you will experience many logic errors developing and testing the script since there are so many dependencies at each stage that have to be considered, so develop only on test data and back up your data before trying it out on your live data. My blog avoided going into anything this complex, because it hopes to make the core of the principles and the approach I was demonstrating easy to follow.  You may want to look at this post to see an example of where I adapted the approach to deal with a much more complex many-to-many relationship between tables for further ideas about ways to vary the basic approach outlined in the Blog. ... View more

In what way are you getting stuck trying to join against [SiteID] and [EasyID] at the same time?  Are you getting errors?  The basic approach to a combined key will work as shown for the first 25 lines of code (I can't follow your overall logic beyond that). Null values in the key most likely cause most of problems, so I would restructure the code order to deal with the second part of your script first to fill in Null values in the EasyID field before worrying about the FeatureID field at all.  That involves processing a list of EasyIDs in the single key dictionary of just SideID key values first to verifying the unique value assumption for the non-Null EasyID values as well as filling in the Null EasyID values.  Don't build dictionaries for T2 at all until they can be used.  In any case, the cursor dictionary approach is your best option and can handle this whole set of processes, but each must happen in the correct order to avoid faulty assumptions about what set of fields contain unique values at each stage of the script. Clearly you are dealing with a high complex interrelationship between these two tables and a large set of rules that I have yet to understand. I have no context how these records and values came into existence of what uses they will serve in the future. More crucially, You have given me no information about the interrelationship this script has with user actions.  Every step you expect a user to do or not do creates a point of failure for your script and any of your rules and assumptions.  If the user has to manually set off the script, you must always start by verifying they did everything you expected them to do and didn't do anything you didn't expect them to do related to your script assumptions. Also, Xander is correct that mentioning my full name in a post puts a message in my inbox, which is the reason I saw this post when he did that. ... View more

I would actually not export to Excel.  I would export to a shapefile or feature class as polygons, which is what I assume the "US_CountyLines" feature class is.  If that layer is a polyline feature class then that may complicate things.  Presumably you have a state field in both the feature class and the table, so clean up should involve selecting the set or features where the two state fields contain the same value and reexporting that set, or selecting where the two state fields are different and deleting that set of features. It sounds like you will only be showing a single disease at a time on any given map and just have to figure out how to display the different proportions of numbers for each age group.  The pie chart should still be the best option for that display.  See this help file on how to set that up. ... View more

I fully agree with Xander that you should first analyze the end points of you lines that overlay each route to do a quick level analysis.  Point event tables can be sorted, duplicated, and then joined in such a way that two end points of a line can be transformed into a line event table in about 4 or 5 steps.  Then do more complex geoprocessing for lines that did not fall within a single route. I frankly do not trust the line overlay of this tool.  It will always fail to match the segments if your routes or your input lines have true curves in them (it fails without an error, but just does not include any portion of true curves in the line events generated).  Convert to shapefile to get rid of such curves if you intend to do line on line overlays.  For point on line overlays, true curves in the routes do not cause a problem. ... View more

You can use the attached dbf table as a join to your date field (the field must have dates only with no time other than 12 AM) to associate all of the typical date component groupings and break-downs, without calculating those values into the feature class or creating separate layers for each date grouping.  The table covers all dates between 1/1/1860 and 1/1/2102.  All grouping fields are numeric and are: YEAR_VALUE (only the values 1860-2102) MONTH_VALU (only the values 1-12) YEAR_MONTH (values 186001 to 210201, with the first 4 digits being 1860 to 2102 and last two digits being between 01 and 12) YEAR_DECIM = YEAR_DECIMAL_MONTH in gdb (a numeric representation of the year with a fractional value for the month of the year in 1/12th fractions) YEAR_DEC_1 = YEAR_DECIMAL_DAY in gdb (a numeric representation of the year with a fractional value for the day of the year in 1/365th or 1/366th fractions) DAY_VALUE (only the values 1-31) Because all of the grouping fields are numeric you can construct Quantities or Charts symbology with these values.  You can also do a multi-field sort of your table view by the MONTH_VALU field and then the YEAR_VALUE field or by the DAY_VALUE before or after the MONTH_VALU or YEAR_VALUE with this table joined to your date field.  Of course, if you join the table and then export/ copy features, etc. you can permanently add all of the grouping fields to a copy of your feature class without doing multiple field calculations. If you feel the table should include other grouping fields let me know.  For example, a string version of the Month and Day fields with leading zeros could be useful for Category symbology so that it sorts correctly, or a set of quarter or fiscal year groupings (for example, numeric fractional fiscal years like 20142015 with July 1-31 set as a fractional value of 0 and June 1-30 is set as 11/12th of the year for sorting purposes) ... View more

As the number of records being calculated grows, that trick quickly turns into a performance necessity. The same technique works for building a label expression function.  See this blog on that subject.  I wrote the blog the same day the light dawned on me that a global variable was essential to using a cursor inside of a loop in order to avoid doing time consuming query repetitions. You can run a cursor against your entire feature class or related FC/table and load value lists or summaries into dictionaries/lists for the entire record set, and then build labels using the in memory data not contained in the current record, without the performance hit of building SQL statements and running the cursor on every record. ... View more

That is not the value you should use, it is only a sample value in the correct format for you to imitate.  You need to change the directory and feature class name to be the one you are actually calculating. I would also change the code to make the list a global variable so that you do not run the cursor for each record (it only needs to be done once). list = [] def update(acres):    import arcpy    global list    if len(list) == 0:     with arcpy.da.SearchCursor(r"C:\yourPath\yourGDB.gdb\yourFC", ["Acres"]) as cursor:        for row in cursor:          list.append(row[0])     del cursor, row   S = sum(list)    return acres / S * 100 ... View more

I agree with Xander that the full code you posted populates dictionaries with the same flaw as I pointed out in the case of the match dictionary.  All values in all dictionaries need to be lists, and the list should only be initialized when the key is not in your dictionaries.  Then append values to the list each time the dictionary key reappears.  Use my suggestion for how to build the match dictionary as an example for the other dictionaries. ... View more