|
BLOG
|
Since you say you want to keep the 3 latest records from the related tables that suggests that you have a date field associated with those records. If so, the date field needs to be loaded with the other data to add logic that would publish only the latest 3 records from each table. Anyway, limiting the number of records is possible, but there are a variety of approaches for implementing that limit and the most efficient method will be determined by the nature of the data that defines what records are the "latest 3 records". So please explain what data you have available that you would use to choose the latest 3 records from each table.
... View more
09-10-2018
07:16 AM
|
0
|
0
|
18068
|
|
BLOG
|
I have not worked with Portal or tried labels with code like this in web app builder, so if your issues are specific to those platforms, I can't really help. If it is assumed that the code will be compatible with the platform and your questions are related to making the code work for this configuration of table relationships then I should be able help. The basic structure of the code always reads the related tables first into one or more dictionaries using the key value that relates each record set to the parent table. Then the labels are built using the primary feature class features to provide the group header in the label and the dictionaries to provide the detail listings of each label. Correct me if I am wrong, but it appears from the table set you have outlined that the OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation feature class is the parent feature class controlling the labels and the other three tables all have a foreign keys related to the station field of that parent feature. It is not clear how the three related tables relate to each other, but I would assume they are in a Many to Many relationship to each other using just the foreign related key fields you have mentioned. As a result each related table may need its own section in the output label to avoid creating multiple copies of each related record in one table if you were to combine them with the records of another related table. If you follow this approach then you need to build three dictionaries which will be handled independently relative to the other dictionaries when you build the label. Using a very basic label layout that just displays each record in a raw form, the core code for extracting the data should be something like: # Initialize three global dictionaries for each related feature class/table
relateDict1 = {}
relateDict2 = {}
relateDict3 = {}
def FindLabel ( [station] ):
# declare the dictionary global so it can be built once and used for all labels
global relateDict1
global relateDict2
global relateDict3
# only populate the first dictionary if it has no keys
if len(relateDict1) == 0:
# Provide the path to the relate feature class/table
relateFC1 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentPrimaryStatSurvey"
# create a field list with the relate field first,
# followed by sort field(s), then label field(s)
relateFieldsList1 = ["parentpid", "pressure", "wetbulbtemp", "humidity"]
# process a da search cursor to transfer the data to the dictionary
with arcpy.da.SearchCursor(relateFC1, relateFieldsList1) 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 relateDict1:
# [searchRow[1:]] is a list containing
# a list of the field values after the key.
relateDict1[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
relateDict1[relateKey].append(relateRow[1:])
# delete the cursor, and row to make sure all locks release
del relateRows, relateRow
# only populate the second dictionary if it has no keys
if len(relateDict2) == 0:
relateFC2 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentSecondStatSurvey"
relateFieldsList2 = ["parentsid", "workdrybulb", "workwetbulb"]
with arcpy.da.SearchCursor(relateFC2, relateFieldsList2) as relateRows:
for relateRow in relateRows:
relateKey = relateRow[0]
if not relateKey in relateDict2:
relateDict2[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
relateDict2[relateKey].append(relateRow[1:])
del relateRows, relateRow
# only populate the third dictionary if it has no keys
if len(relateDict3) == 0:
relateFC3 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentCriticalStatSurvey"
relateFieldsList3 = ["parentcid", "quantity", levelnh3", "levelco"]
with arcpy.da.SearchCursor(relateFC3, relateFieldsList3) as relateRows:
for relateRow in relateRows:
relateKey = relateRow[0]
if not relateKey in relateDict3:
relateDict3[relateKey] = [relateRow[1:]]
else:
relateDict3[relateKey].append(relateRow[1:])
del relateRows, relateRow
# store the current label feature's relate key field value
# so that it is only read once, improving speed
labelKey = [station]
# 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 relateDict1:
# sort the list of the list of fields
sortedList = sorted(relateDict1[labelKey])
# process the sorted list
for fieldValues in sortedList:
# append related data to the label expression
# my label shows a list of related
# stations with sorted pressures, wetbuldtemps and humidities
expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
# clean up the list variables after completing the for loop
del sortedList, fieldValues
if labelKey in relateDict2:
sortedList = sorted(relateDict2[labelKey])
expression += '\n<FNT name="Arial" size="10"><BOL>Station Count = {}</BOL></FNT>'.format(len(sortedList))
for fieldValues in sortedList:
expression += '\n{0} - {1}'.format(fieldValues[0], fieldValues[1])
del sortedList, fieldValues
if labelKey in relateDict3:
sortedList = sorted(relateDict3[labelKey])
for fieldValues in sortedList:
expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])
del sortedList, fieldValues
# return the label expression to display
return expression I have not tried the efficiency step outlined by Fatih Dur, so I went with the code structure I have personally used before. Using Fatih's recommendation could result in shorter and cleaner code, so once you have a code base that you know is working you could try implementing that efficiency step and keep it if the revised code works the same.
... View more
09-06-2018
08:37 AM
|
1
|
0
|
18068
|
|
POST
|
Using the GetParameterAsText method means that all parameter values are converted to text before being passed to your code. Therefore you have to convert them back to their original type. You need to modify you code to: min_scale = int(arcpy.GetParameterAsText(5))
max_scale = int(arcpy.GetParameterAsText(6)) However, you may want to add validation to ensure they are valid values (not Null or out of range) before the parameters are converted and passed to the rest of your code.
... View more
09-05-2018
09:31 AM
|
1
|
1
|
3486
|
|
POST
|
I do not believe there is any built-in functionality to do labeling using tables in a 1:M relationship. My solution is posted in my Creating Labels with Related Table Data blog. Using this technique the labels will update every time the display refreshes.
... View more
09-05-2018
08:50 AM
|
2
|
1
|
1492
|
|
POST
|
You should try Cast(tot_value as float) > 500000 and cast(tot_value as int) > 500000. It is almost certainly the field type part of the argument that is throwing the error, since for a long time only float worked with fgdbs even though that type was not even in the documentation. I just stumbled on it randomly.
... View more
08-19-2018
11:01 AM
|
0
|
0
|
3238
|
|
IDEA
|
Thankfully Paste works the way it does, since I have never wanted to have it do the behavior you want and always want an exact copy. What you want would be like copying text and having Paste change the verb tense when it is pasted into Word. Someone may want that, but it would require much more than copy/paste is designed to do. However, Excel and Word do offer a few choices like copy values only or copy formatting only and do paste based on cursor position and selection, so the concept of paste options is not out of the question. For this your best option if this is a pressing need would be to create your own tool or have someone design a custom tool for you using python. The likely tool design would require a toolbar with a pair of dropdowns to choose the source and target layers rather than relying on the clipboard and a popup. Using the map extent for coming up with a position would be too vague to get a good result in my opinion and is not as consistent with standard tool behavior. Instead the tool could be triggered by clicking on the map. Whatever point you had selected in the source could be transferred to the position you clicked on the map. If you selected a group of points the click could act as the centroid for the group. There are code snippets throughout the forums that can be adapted to implement this tool. I have done similar tools in the past, but I can't offer my services to design one any time soon. I find designing my own tools is far better than waiting on Esri for something like this. Even if Esri eventually does come up with a tool, designing my own usually gives me the functionality I need about 2 to 10 years before Esti ever adds it. Esri tools are usually more generic and adapted to be used with any random data, but I still end up using mine since they are targeted to my exact data and workflow needs and typically still eliminate the need for multiple steps after the Esri tool is released.
... View more
08-19-2018
08:54 AM
|
1
|
0
|
942
|
|
POST
|
Nowhere do you say what database you are using. The answer is very dependent on the database. Asking this question without ever revealing the database you are using makes this an impossible question to troubleshoot. If this is a file geodatabase then CAST(TOT_VALUE AS INTEGER) > 500000 is valid and works. I just ran Select By Attribute on a text field using a Cast on a Substring in a file geodatabase with the expression CAST(SUBSTRING(LINE_LINK FROM 2 FOR 12) AS INTEGER) > 7000000 I ran it and got back the expected result. Therefore all I know is that you are not using a file geodatabase. This may also work for a shapefile, but I did not test that. The same expression does not work for SQL Server. For that I have to use: CAST(SUBSTRING(LINE_LINK,2,7) AS INT) > 7000000 The answer will be different for ORACLE and may be different for a shapefile. You have to search online for the syntax that specifically applies to each database to use methods like CAST or SUBSTRING, since there is no standard SQL implementation for these methods that works across databases. I have never used an AGOL Feature Layer, but you need to know which database that layer uses and find the syntax for it online to come up with a valid Cast expression.
... View more
08-19-2018
07:41 AM
|
2
|
2
|
6641
|
|
POST
|
Your data is in a Many to Many relationship and you need to create a Many to Many relationship class to do this properly. You would not store the relationship in either feature class, instead the Many to Many relationship class creates an intermediate table where each related project and pole are listed out like a normal One to One relationship (one record holds exactly one project related to one pole, so there are 4 records with a ProjectID of "A,B,C,D" with each pole in only one of the 4 records). The intermediate table is held inside of the relationship class object in ArcCatalog, and you can drag that object into the map to see the intermediate table. When you select any pole (say pole "A") and activate the relate you will get all of the related projects (Projects "A,B,C,D", "A,C" and "A,D"). For the data you have shown there would be 13 intermediate table records to relate the 6 projects to 4 out of the 7 poles (A,B,C and D). Populating of the intermediate table is done through the Attribute pane. A script could be written to create a standalone table with all of the records required for the intermediate table and then you could use that table to create and populate the relationship class with the Table to Relationship Class tool. I believe you would then be free to use a truly unique ID for each project (say like an ObjectID, GlobalID, Attribute Assistant Generate ID or anything you like such as PROJ-2018-01, PROJ-2018-02, etc) as your Project ID that has nothing to do with the poles and still populate a list field on each project of the related poles (or a list on the poles of the related projects) derived from the relationship class table using a script.
... View more
07-30-2018
03:12 PM
|
2
|
1
|
1420
|
|
POST
|
I found out the answer to my question. The table is embedded in the relationship class object in ArcCatalog and when you drag that object into the map it becomes a table with the relationship class name. The table is not a true standalone table and its restricted in what you can do with it. When it is in your map you can select and export records directly from the table view and when you activate a relate on the origin and destination of the relationship class the intermediate table will open and the related records of the intermediate table will be selected, but you cannot create any join or relate directly to the intermediate table or activate the relationship class relate directly through the intermediate table.
... View more
07-20-2018
02:10 PM
|
2
|
0
|
2098
|
|
POST
|
I created a M:N Relationship Class using the Create Relationship Class Tool. I can see the Relationship object in ArcCatalog, but I do not see any Intermediate Relationship Class Table, so I cannot add the table to ArcMap as instructed in the second step of the Populating an Attributed Relationship Class Table (Intermediate Table) instructions. I attached a screenshot of what I see. The relationship class is named ADPS_RCFC_FACILITIES_AREA and is a M:N relationship between the ADPS feature class and the RCFC_FACILITIES_AREA feature class. The RCFC_FACILITIES_LINE feature class is not part of the Relationship Class. There is no intermediate table as far as I can see. Where is it? I can create relationships between features by following steps 4 thru 7 in the instructions, but I cannot do anything to populate the additional fields I added when I created the relationship since I do not see any intermediate table. What am I supposed to be seeing that would let me do that? I am using ArcMap 10.5.1, Windows 7.
... View more
07-13-2018
02:08 PM
|
0
|
1
|
2256
|
|
POST
|
In Attribute Assistant use the X_Coordinate and Y_Coordinate methods. The coordinates are based on the feature class coordinate system and can be the Centroid or for lines the start or end of the line. It is listed under the All Methods help page for Attribute Assistant. Just Google it. Like all Attribute Assistant methods, the X and Y coordinate methods can trigger at feature creation and/or in response to geometry (or attribute) changes.
... View more
05-31-2018
10:33 AM
|
0
|
1
|
8791
|
|
POST
|
The Field Calculator is the wrong tool for this task and cannot be used to solve the problem. As others have mentioned a SearchCursor should be used to read all of the records except the summary record to sum the values and then an UpdateCursor can write the result to the summary record if it exists or an InsertCursor can create a summary record if it doesn't exist.
... View more
05-08-2018
08:26 AM
|
0
|
0
|
5893
|
|
POST
|
I have come up with an algorithm that can meet all of my criteria. In 3 minutes and 13 seconds the code can find solutions for 27,542 records in FC_A and 64,985 records in FC_B that match up on a total of 2,773 unique case values, so the performance is excellent. The code currently only solves the problem without writing an output, but now that the problem is solved I can extend the code to output the results in a variety of different ways, depending on my needs. A portion of the code could be used to replace the Spatial Join tool when features need to be matched based on a shared case value to avoid unwanted joins due to feature overlap. Additionally, the Spatial Join component of the code is similar to using the One To One option with a case field and the first option on another field in the summary options, but it is enhanced so that it will favor exact attribute matches on the second field over any spatial relationship and otherwise favor overlap of the FC_B centroid with the FC_A polygon over merely being the first FC_A feature to touch the FC_B feature. Beyond the spatial join, the code has additional logic for matching all unique values in the second field between FC_A and FC_B based on the closest feature when no exact attribute match or feature overlap exists, as I required. So solving problems like this can be done very efficiently with arcpy.
... View more
05-07-2018
02:14 PM
|
0
|
0
|
1331
|
|
POST
|
The particular set of data I am currently working with could disregard the spatial component, but the value assignment makes much more sense as shown than using a random assignment. However, I have other problems I would love to tackle that I have never attempted, because they are only worth tackling through this kind of spatial algorithm. The data in my example is current and historic parcels and the output I am trying to create mimics parcel history. I do not have access to parcel history data from my Assessor and if I did it would only complicate the problem by adding another many to many relationship I would have to manage. If the spatial problem was removed I would still be open to suggestions on how to approach the non-spatial portion of the problem, especially if they involved modules beyond native python and arcpy. I have avoided learning numpy up to now, but could be persuaded to learn it if it has application to solving problems like this.
... View more
05-03-2018
03:30 PM
|
0
|
0
|
1331
|
|
POST
|
I am trying to come up with an algorithm that can handle the problem of assigning the values of an attribute of a set of polygon features in one feature class to the set of polygon features in another feature class based on proximity. I can conceive of several ways to approach this problem using Python and/or Geoprocessing tools, but I need to optimize the final algorithm for performance. I am hoping for python coding or geoprocessing suggestions that could accomplish each step in the best order to achieve the greatest overall efficiency and speed. The suggestions do not have to tackle the whole problem and can be focused on any part, but I am laying out the complete problem so each step can be seen in light of the overall goal of the algorithm. Assume I have two polygon feature classes named FC_A and FC_B which have two attribute fields called Case_Field and Value_Field in both feature classes. The following conditions must be met for the algorithm to reach a solution: 1. All conditions listed below should only consider and compare the set of features that have a common Case_Field value in both FC_A and FC_B and if the set of features is not limited to a single Case_Field value then the number of overlapping features in both feature classes is massive. 2. A solution is only reached when every unique value in the Value_Field of FC_A has been be assigned to at least one feature in FC_B and all of the features in FC_B have been assigned a value in the Value_Field from the set of unique values in FC_A. 3. The total number of unique values in Value_Field in FC_A must be equal to or less than the number of features in FC_B to be analyzed by this algorithm, otherwise the set of features associated with that Case_Field value will be ignored. 4. If a feature in FC_A does not overlap any of the features in FC_B the value in Value_Field of FC_A must be assigned to the closest feature in FC_B, provided that the FC_B feature has not previously been assigned a value from FC_A to satisfy this condition. 5. Any feature in FC_B that is overlapped partially or completely by only one feature in FC_A will be assigned the value from the FC_A feature that overlaps it, provided that the FC_B feature has not already been assigned a value to satisfy condition 4 above. 6. Any feature in FC_B that has portions overlapped by two or more features in FC_A will be assigned the value from the FC_A feature that overlaps its centroid or that is closest to its centroid if the centroid is not overlapped, provided that the FC_B feature has not already been assigned a value to satisfy condition 4 above. An illustration of the problem is shown below for one of the sets of features in FC_A and FC_B that all share a common Case_Field value. The polygons with a grey fill and a colored outline are in FC_A and are labeled with the value stored in the Value_Field. The polygons that are colored light purple are in FC_B and have Null in the Value_Field. (Note: some of the values in the Value_Field of FC_A are actually associated with many polygons, but they should all be treated as a single dissolved polygon for the purposes of this algorithm.) The solution should look something like the output below. The FC_B fill and the FC_A outline have been assigned the same color for each value in the Value_Field Notice that values from the polygons in FC_A that do not overlap any of the polygons in FC_B have still been assigned to one of the FC_B polygons. I did this manually, so I may not have chosen the closest polygon to satisfy condition 4, but I would expect the algorithm to chose the closest polygon. If it turns out that after fully optimizing the steps that evaluate spatial proximity those steps would take 5 or more minutes to reach the solution of the sample problem above, I would probably end up settling for a solution that only satisfies conditions 1 through 3 using the fastest python algorithm that can randomly distribute the values from FC_A into FC_B. However, I prefer a solution that takes the spatial relationship into account.
... View more
05-03-2018
12:37 PM
|
0
|
3
|
1461
|
| Title | Kudos | Posted |
|---|---|---|
| 1 | 03-24-2026 11:37 PM | |
| 1 | 03-24-2026 08:01 PM | |
| 6 | 02-23-2026 08:34 AM | |
| 1 | 03-31-2025 03:25 PM | |
| 1 | 03-28-2025 06:54 PM |
| Online Status |
Offline
|
| Date Last Visited |
Thursday
|