Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Cancel
- Home
- :
- About RichardFairhurst
RichardFairhurs
MVP Alum
since
07-04-2014
4 weeks ago
3570
Posts created
554
Kudos given
316
Solutions
1379
Kudos received
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
My Ideas
(20 Idea Submissions)Latest Contributions by RichardFairhurst
|
POST
|
I would also not ever create a table named TABLE. That is too likely to be a key word somewhere in ArcGIS and you need to avoid naming objects like Tables and fields with key word names. They can bite you in random ways, depending on how the object name is parsed and passed around in the internal code of the tools you are using. Some parts of the code may recognize the value passed as a string for an object name, while other parts may think they are being passed a key word used by SQL in table creation operations.
... View more
02-11-2015
09:51 AM
|
0
|
8
|
5919
|
|
POST
|
So just to be clear, the orp field and DECWell contain this 6 character string. In other words, the fields identified by input d (orpNames) and input f (DECwell) hold the same set of values. (again this code is not commented with a lot of meaningless variable names and is complex to follow). At this point I would add print outputs to verify what the actual ID and s values are within the loop that inserts records immediately prior to inserting each record. Or else put that loop in a try except block and print those values in the except block if you are getting some records inserted and a specific records is failing. Do you know if the failure is always occurring with the first insert record operation, or is it only for a specific record?
... View more
02-11-2015
09:30 AM
|
0
|
10
|
5919
|
|
POST
|
I agree I was mistaken about the long value vs. list value in your last loop. Your DECwell field length should be explicitly set when you create that field to be at least as long as the length of the longest owner name in your list. You just accepted the default text length, which I believe is 50. The moment you try to insert a record with an owner name with 51 characters or more you will get an error. You should add logic to get the longest string length of wellnum[key] in your first loop before creating the field and making sure the new field is long enough (add a little more unless you know the max. field length and it will not grow). Also, the code you posted never defines the wellnum dictionary, so I cannot be sure that dictionary does not have an issue. If wellnum is an undefined variable then the insertrow line is the first to use it and will throw all sorts of weird errors. If wellnum was defined in code you did not post, are you sure wellnum even has the same key list as the match dictionary? If it might not you need logic to deal with that. i.e., insert a row only when - if key in wellnum: I would say this post is not about simple insert cursor issues, it is about complex dictionary interactions and value evaluation issues.
... View more
02-11-2015
08:24 AM
|
0
|
12
|
5919
|
|
POST
|
It appears you are dealing with a one-to-many relationship of wells to owners and that your match dictionary contains a list of Long values, not a single long value. You must iterate not only over the objects in the match dictionary, but the listed items in the values of that dictionary to insert just the long value, not a list of long values. Additionally, you are not getting the one-to-many relationship preserved in the match dictionary, because you are resetting the list each time you find the same owner name. You should have the logic be: for o in well_owners:
for p in orps_owners:
if orps_owners
== well_owners :
if not o in match:
match = []
match .append(p)
arcpy.CreateTable_management(h, "TABLE") arcpy.AddField_management("TABLE", "DECWell_", "TEXT") arcpy.AddField_management("TABLE", "match", "LONG") for key in match: rows = arcpy.da.InsertCursor("TABLE", ["DECWell_", "match"])
for s in match[key]:
for item in wellnum[key]:
rows.insertRow((wellnum[key], item))
del rows You could add a check to see if s == [] and still create the wellnum[key] entry with a null item value, assuming you want all matched and unmatched well owners in the final output.
... View more
02-11-2015
07:43 AM
|
1
|
14
|
5919
|
|
BLOG
|
Performance Issue of Past Solutions The subject of creating labels that include data from related feature classes/tables in a One-To-Many or Many-To-Many relationship has come up many times over the years, and while there have been a few solutions proposed, all have suffered from poor performance. However, I have discovered a way to overcome the performance issues after further experimentation with the techniques I described in my Turbo Charging Data Manipulation with Python Cursors and Dictionaries. Previous solutions were slow, because they kept processing queries for each label being generated by building an SQL expression from the relate value of each feature being labeled to return the set of related records one label at a time from the related feature class/table. This is an extremely inefficient and slow way to process look-ups against a relate. Solving Performance Issues by Using Global Dictionaries for Related Data Dictionaries are the perfect solution for handling relate look-ups. The reason is that the insert/delete/look-up time of items in the dictionary is amortized constant time - O(1) - which means no matter how big the dictionary gets, the time it takes to find something remains relatively constant. This is highly desirable for high-speed look-ups. Therefore it is much more efficient to read an entire related feature class/table into a dictionary and process the related value of each label against a dictionary key value than it is to repeatedly process SQL queries against the related feature class/table for each label relationship. However, a dictionary would also be no good if the entire related feature class/table had to be reloaded into the dictionary as each label was being processed. Fortunately there is no need to do that, and the entire related feature class/table can be loaded into the dictionary once when the first label is created. To do that the dictionary is created as a global variable of the label function that is only loaded by the first label. All other labels just check to see if the dictionary has already been loaded. If it has, the related feature class/table will not be queried again. As long as the labels are being generated, all subsequent labels will just use the already loaded global dictionary to get the related feature class/table data. Each time the map is refreshed, the global dictionary is rebuilt for just the first label again and then that dictionary is used until all of the labels are refreshed. Therefore, edits to the related feature class/table will be reflected each time the labels are refreshed. Editing a related feature class will cause the map to refresh as edits are posted. However, editing a related standalone table will not cause the map to automatically refresh the labels as each edit is posted. You will have to manually refresh the map to see the standalone table updates. However, I consider that a good thing, since waiting for map refreshes after editing one record at a time is very time consuming and there is no need to refresh the labels at all if the related standalone table is only edited in fields that do not participate in the label expression. Example 1: The Fundamental Code to Create Labels Using this Technique The labels shown in this picture were created by the code below. The related intersection event table used to create these labels contains over 130,000 records, all of which are read into the dictionary when the first label is processed. These labels took less than 9 seconds to draw. To use the code below I went to the Label tab of the Routes and checked the Label check box. Then I pressed the Expression button for the labels, I changed the Label Parser from VBScript to Python. After checking the Advanced expression option, I placed the code below in the expression editor. This expression creates a label for my layer of linear referenced Routes shown above and includes data from a related table of intersection linear reference events. It lists the Route ID (ROUTE_NAME) in the header of the label in 12 point Bold font. Under that I show a summary count value of the number of intersection cross street names contained in the related table in 10 point Bold font. Then I list the related cross street names and their event measure value for each intersection in the regular 8 point font. The list of cross streets is sorted as a list based on the measures so that they appear in driving order going in the direction of the route's measure orientation. # Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
# declare the dictionary global so it can be built once and used for all labels
global relateDict
# only populate the dictionary if it has no keys
if len(relateDict) == 0:
# Provide the path to the relate feature class/table
relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
# create a field list with the relate field first (ROUTE_NAME),
# followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
# process a da search cursor to transfer the data to the dictionary
with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
for relateRow in relateRows:
# store the key value in a variable so the relate value
# is only read from the row once, improving speed
relateKey = relateRow[0]
# if the relate key of the current row isn't found
# create the key and make it's value a list of a list of field values
if not relateKey in relateDict:
# [searchRow[1:]] is a list containing
# a list of the field values after the key.
relateDict[relateKey] = [relateRow[1:]]
else:
# if the relate key is already in the dictionary
# append the next list of field values to the
# existing list associated with the key
relateDict[relateKey].append(relateRow[1:])
# delete the cursor, and row to make sure all locks release
del relateRows, relateRow
# store the current label feature's relate key field value
# so that it is only read once, improving speed
labelKey = [ROUTE_NAME]
# start building a label expression.
# My label has a bold key value header in a larger font
expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)
# determine if the label key is in the dictionary
if labelKey in relateDict:
# sort the list of the list of fields
sortedList = sorted(relateDict[labelKey])
# add a record count to the label header in bold regular font
expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = {}</BOL></FNT>'.format(len(sortedList))
# process the sorted list
for fieldValues in sortedList:
# append related data to the label expression
# my label shows a list of related
# cross streets and measures sorted in driving order
expression += '\n{} - {:.4f}'.format(fieldValues[1], fieldValues[0])
# clean up the list variables after completing the for loop
del sortedList, fieldValues
else:
expression += '\n<FNT name="Arial" size="10"><BOL>Cross Street Count = 0</BOL></FNT>'
# return the label expression to display
return expression
Example 2: Adapting the Code to Produce Table Style Labels The labels shown represent only one of the possible ways I could have summarized and/or listed the related feature class/table data. A semi-tabular presentation is possible if I use a fixed-spaced font like Courier New. A method for making a tabular style label was given in this post by Jennifer Horsman; however, her code used VBScript and used an inefficient search cursor algorithm. Below I have adapted her code to use Python and the much more efficient dictionary algorithm shown above. # Initialize a global dictionary for a related feature class/table
relateDict = {}
def FindLabel ( [ROUTE_NAME] ):
# declare the dictionary global so it can be built once and used for all labels
global relateDict
# only populate the dictionary if it has no keys
if len(relateDict) == 0:
# Provide the path to the relate feature class/table
relateFC = r"C:\Users\OWNER\Documents\ArcGIS\Centerline_Edit.gdb\CL_INTERSECTIONS_PAIRS"
# create a field list with the relate field first (ROUTE_NAME),
# followed by sort field(s) (MEASURE), then label field(s) (CROSS_STREET)
relateFieldsList = ["ROUTE_NAME", "MEASURE", "CROSS_STREET"]
# process a da search cursor to transfer the data to the dictionary
with arcpy.da.SearchCursor(relateFC, relateFieldsList) as relateRows:
for relateRow in relateRows:
# store the key value in a variable so the relate value
# is only read from the row once, improving speed
relateKey = relateRow[0]
# if the relate key of the current row isn't found
# create the key and make it's value a list of a list of field values
if not relateKey in relateDict:
# [searchRow[1:]] is a list containing
# a list of the field values after the key.
relateDict[relateKey] = [relateRow[1:]]
else:
# if the relate key is already in the dictionary
# append the next list of field values to the
# existing list associated with the key
relateDict[relateKey].append(relateRow[1:])
# delete the cursor, and row to make sure all locks release
del relateRows, relateRow
# store the current label feature's relate key field value
# so that it is only read once, improving speed
labelKey = [ROUTE_NAME]
# variables to adjust table cell sizes
iMaxLbl1Sz = 0
iMaxLbl2Sz = 0
iSpace = 5
# determine if the label key is in the dictionary
if labelKey in relateDict:
# sort the list of the list of fields
sortedList = sorted(relateDict[labelKey])
# process the sorted list to determine cell spacing
for fieldValues in sortedList:
strLabel1 = fieldValues[1]
strLabel2 = '{:.4f}'.format(fieldValues[0])
if (len(strLabel1) > iMaxLbl1Sz):
iMaxLbl1Sz = len(strLabel1)
if (len(strLabel2) > iMaxLbl2Sz):
iMaxLbl2Sz = len(strLabel2)
# clean up the fieldValues variable once the for loop is done
del fieldValues
# My label has a key value header followed by a record count
expression = labelKey
expression += '\n<UND>Cross Street Count = {}</UND>'.format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 1 - len('Cross Street Count = {}'.format(len(sortedList))))
# process the sorted list
for fieldValues in sortedList:
strLabel1 = fieldValues[1]
strLabel2 = '{:.4f}'.format(fieldValues[0])
k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
# append related data to the label expression
# my label shows a list of related
# cross streets and measures sorted in driving order
expression += '\n' + strLabel1 + "." * k1
expression += "|"
expression += "." * k2 + strLabel2 + "|"
# clean up all list variables after completing the for loops
del sortedList, fieldValues
else:
# My label has a key value header followed by a record count
expression = labelKey
expression += '\n<UND>Cross Street Count = 0</UND>'
# return the label expression to display
return expression
The code above results in this output: Example 3: Another Table Style Label Alternative Here is an alternative table style layout. The code below includes all of the code shown in the previous example through line 36, but replaces the code that began in line 37 in the code for example 2 as follows:. # variables to adjust table cell sizes
iMaxLbl1Sz = 0
iMaxLbl2Sz = 0
iSpace = 5
# determine if the label key is in the dictionary
if labelKey in relateDict:
# sort the list of the list of fields
sortedList = sorted(relateDict[labelKey])
# process the sorted list to determine cell spacing
for fieldValues in sortedList:
strLabel1 = fieldValues[1]
strLabel2 = '{:.4f}'.format(fieldValues[0])
if (len(strLabel1) > iMaxLbl1Sz):
iMaxLbl1Sz = len(strLabel1)
if (len(strLabel2) > iMaxLbl2Sz):
iMaxLbl2Sz = len(strLabel2)
# clean up the fieldValues variable once the for loop is done
del fieldValues
# My label has a key value header followed by a record count
expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey + "<CLR red='255' green='255' blue='255'>" + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len("_"+labelKey)) + "</CLR>"
expression += "\n_<UND>Cross Street Count = {}</UND>".format(len(sortedList)) + '_' * (iMaxLbl1Sz + iMaxLbl2Sz + iSpace + 2 - len('_Cross Street Count = {}'.format(len(sortedList))))
# process the sorted list
for fieldValues in sortedList:
strLabel1 = fieldValues[1]
strLabel2 = '{:.4f}'.format(fieldValues[0])
k1 = (iMaxLbl1Sz - len(strLabel1)) + 2
k2 = iSpace + (iMaxLbl2Sz - len(strLabel2)) - 3
# append related data to the label expression
# my label shows a list of related
# cross streets and measures sorted in driving order
expression += '\n_<UND>' + strLabel1 + "." * k1
expression += "|"
expression += "." * k2 + strLabel2 + "</UND>_"
# clean up all list variables after completing the for loops
del sortedList, fieldValues
else:
# My label has a key value header followed by a record count
expression = "<CLR red='255' green='255' blue='255'>_</CLR>" + labelKey
expression += '\n_<UND>Cross Street Count = 0</UND>'
# return the label expression to display
return expression
With some adjustments to the label style and using the Maplex Label Engine, the example 3 code variation can produce an output that looks like the example below: Considering Other Possibilities Supported by this Technique More complex relationship primary and foreign keys can also be handled by modifying the code above. A dictionary works for almost anything where exact values shared between the parent feature class and related feature class/table can be looked-up, even when a join or relate is not possible in ArcMap. For example, the dictionary key could be used to do look-ups based on a sub-string from a field or on many fields in the parent feature class and/or related feature class/table to create a multi-field key look-up without having to parse or concatenate the values of those fields into a new field in the original feature classes/tables. Look-ups based on portions of dates or numeric calculations could also be done without creating fields to hold those values, as long as an exact match between the two sources can be made. A limited set of spatial look-ups can also be handled by a dictionary, such as finding exact matches or duplicates of shapes or finding extracted coordinates that are shared by the two geometries (for example, a dictionary look-up of points can be done against the from or to end point coordinates of a line where the points overlap the line ends). However, dictionaries cannot be used to speed up inexact value matching or near proximity spatial relationships. Additionally, I could have used multiple global dictionaries to build labels from more than one relate look-up. This is useful in situations where the parent feature class has several fields relating to more than one related feature class/table, or where multi-level feature class/table relationships exist (i.e., the parent feature class relates to a child feature class/table, and the child feature class/table relates to another child feature class/table through one or more of its fields). Situations Where the Technique Shown Might Not Work and Possible Solutions The primary reason that this technique may not work will occur when the related feature classes/tables are too large to fit in memory after being loaded into a dictionary. In these cases, applying some sort of query filter on the related feature class/table based on the entire set of parent features in the current map extent would be required before loading it into the dictionary to keep the dictionary from becoming too large. While it may be possible to do this with arcpy mapping code, I have not tried that to see if it works, but that is something that I may look into later. Memory management of this code is also important to avoid memory leaks, since this code can bypass the normal memory management processes of the label engine. For example, memory issues may occur after several label refreshes if variables used to process the lists of listed fields in for loops are not deleted after the loops complete and the list variables are no longer needed. How I Configured the Labels Shown in the Examples In case you like the label style layout shown in my screen shot above, here are the settings I used: I used the Standard Label Engine. The initial label symbol was set to be the Bullet Leader symbol from the predefined symbol list. I modified the font from 10 point to 8 point. For the labels in screen shot 1 I used Arial font and for the table style labels in screen shot 2 I used Courier New (or some other fixed space font). The Placement Properties are set to Horizontal Placement. I pressed the Symbol button, then I pressed the Edit Symbol button, then I chose the Advanced Text tab and pressed the Text Background Properties Button. On the Text Background Properties dialog I changed the leader style to the angled line type associated with the fifth radio button. I pressed the Symbol button under the Leader check box and changed the line and arrow symbol shown as a dot to red. After returning to the Text Background Properties dialog, I checked the Border option for the background and then pressed the Symbol button under the Border check box to set the border fill and border line style to No Fill. After pressing OK on all child dialogs, I set the horizontal alignment on the General tab on the first Symbol dialog to Left.
... View more
02-06-2015
08:15 PM
|
14
|
48
|
30006
|
|
POST
|
I doubt you can do anything without programming it yourself. I don't work with web service programming, only desktop. Since desktop has no built-in solution to accomplish what you want (unless you are just describing the Identify result that comes up when you use the Show All Visible Layers option with the Identify tool), I doubt you can overcome the additional limitations of performance and reduced out-of-the-box functionality to get a web service solution. If you become a programmer and figure out a solution post it.
... View more
02-02-2015
03:13 PM
|
2
|
0
|
2149
|
|
POST
|
I don't use relationship classes. I just use the relate option in the Joins and Relates option of the layers and tables. That kind of relate does not require the related layers or table to be in the same workspace with each other. It cannot be set up in ArcCatalog, but it is easy to set up in Desktop in any map. To me it does what you need through the tree view. What you are wanting is a live two way join or query table, but that either is not practical or not possible. A query table basically only works if the FCs are all in the same workspace. But I don't use them even then, because they perform horribly and are no better than a static geoprocessed FC once you set them up. A standard Join only works for 1:1 and M:1 relationships and only can be done one way, because once two FCs are joined you cannot do the Join in the opposite direction. Relating objects to derive new workflows is fundamental to everything I work on, but I have never seen anyone do anything remotely like what you seem to be describing. You would have to show me a mock up picture of what you think you should be able to see for me to even understand you (especially if there are any 1:M or M:M related features involved). Another solution I see is to run a geoprocessing operation that does Spatial Join 4 times with each serving as the primary join parent. Each Spatial Join would retain the shape of the parent FC. They could be stored in any of the existing workspaces, or in a new workspace set up for them. For the attributes you could either use the 1:M option of the Spatial Join and duplicate objects whenever more than one feature in the other FCs needs to have its attributes combined, or use the 1:1 option and combine attributes from multiple features into text fields using the Join list option. The four new FCs would be static, but could be refreshed for end users when you publish new edits. Editors would not be able to see these related objects without rerunning the geoprocessing operation and managing the workspace locks. The final solution I see, since an attribute relationship is already in place, is running a python script using dictionaries and cursors to do the attribute transfers into the parent FCs periodically after doing edits. Each FC would continue to have their original attributes, which would be edited directly, but in addition you would add all of the necessary attribute fields to hold all attributes maintained in the other FCs, which the editors would not touch. In many cases those fields would be text fields that could hold lists. Then I could write a python script that could fill in the related fields, including doing any needed summary operations or list creations for 1:M and M:M relationships. The FCs could be stored in any or all of the workspaces, and the only restriction is that each FC that receives updates would have to be closed by all editors while the process runs. This script would probably only take under 5 minutes to run for 4 FCs with 30,000 features each.
... View more
01-31-2015
02:22 PM
|
0
|
2
|
2149
|
|
POST
|
I do not know if you mean that you want the Identify tool to make the information visible or if you want some other method of identifying a feature to give you this information. If you use the Identify tool and the features have a common field value in each (primary and foreign keys), then you could create 6 relates (Community to the other 3 FCs, built-up areas to UMP and Admin, and UMP to Admin). When you identify any of the features you can drill down to the others by expanding the relate tree. With those 6 relates you should be able to go up or down to any related feature. But it will cut off circular relates by only going up to relates that are parents of the current FC or each other or down to the FCs that are children of the current FC or each other. Some possible relate chains are shown below for my data. As you can see, the relate tree supports all relationship types (1:1, 1:M, M:1, M:M): Using Spatial Join you can create a new feature class that combines the attributes of all the feature that touch each other while retaining the shapes of a chosen primary parent feature class.
... View more
01-31-2015
10:46 AM
|
0
|
4
|
2149
|
|
POST
|
I have edited the link in my post to be current to the url that appears in my browser when I pull up that idea on the Ideas site. Try it again. Or else just go to Esri Arcgis Ideas | Ideas Submission Portal and search for Expand Summary operations to include Min or Max Dates and fgdb Subqueries
... View more
01-30-2015
01:18 PM
|
0
|
0
|
5296
|