Latest Contributions by RichardFairhurst

I found another mistake in the calculation that determined House Numbers outside of the actual first and last address points.  I multiplied by the HOUSE_INTERVAL when I should have divided.  A corrected calculation is below for the From and To measures respectively: From House Numbers If [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MIN_FROM_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MIN_FROM_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MAX_TO_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MAX_TO_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MAX_FROM_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MAX_FROM_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MIN_TO_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MIN_TO_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 Else   Output = "" End If To House Numbers If [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MIN_FROM_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MIN_FROM_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MAX_TO_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MAX_TO_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MAX_FROM_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MIN_MIN_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MAX_FROM_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_ODD_RIGHT_Full.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]  + [ADDRESS_ODD_RIGHT_Full.MIN_TO_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_ODD_RIGHT_Full.MAX_MAX_MEAS]) / [ADDRESS_ODD_RIGHT_Full.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_ODD_RIGHT_Full.MIN_TO_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 Else   Output = "" End If ... View more

Just for the record, the Summary Statistics tool can do a Min and Max summary of a date field, but the process and result is far from ideal.  In the Summary Field drop down you have to type in the name of a date field, since the date fields do not appear in the drop down.  You have to ignore the warning that you have chosen a field type that has no valid statistics options.  Then you can choose the Min or Max summary options for the date field. However, when the tool runs the output Min or Max fields will convert the date to a double field.  The number is a correct representation of the appropriated date (at least for fgdb tables), so if you add a date field and use the field calculator to transfer the Min or Max date summary numbers into the added field, the correct dates will appear in the calculated date field. So that is the workaround for using Summary Statistics tool to do a Min or Max date field summary.  Far from ideal, but it can work. ... View more

Having done a few more of these I have found that some additional data preparations and address analysis that pays off and a way to get the Cul-de-sac ends to be correct after the other procedures I described. After getting the original address points located along the routes and then converted to line events, I added a field called ASC_DESC and made it a 17 character text field.  Then I selected and calculated the ASC_DESC to "Ascending" for all segments that meet the criteria: "MIN_MEAS" <> "MAX_MEAS" AND "FROM_HOUSE_NUMBER" < "TO_HOUSE_NUMBER" I selected and calculated the ASC_DESC to "Descending" for all segments that met the criteria: "MIN_MEAS" <> "MAX_MEAS" AND "FROM_HOUSE_NUMBER" > "TO_HOUSE_NUMBER" I selected and calculated the ASC_DESC to "Same House Number" for all segments that met the criteria: "MIN_MEAS" <> "MAX_MEAS" AND "FROM_HOUSE_NUMBER" = "TO_HOUSE_NUMBER" Finally, I selected and calculated the ASC_DESC to "Same Measure" for all segments that met the criteria: "MIN_MEAS" = "MAX_MEAS" For all of the segments that had the same measure (which are normally at line ends and often tied to cul-de-sacs) I performed a Summary Statistics with the following summary fields and case fields: Summary Fields: FROM_HOUSE_NUMBER Min FROM_HOUSE_NUMBER Max TO_HOUSE_NUMBER Min TO_HOUSE_NUMBER Max Case Field(s): RID MIN_MEAS In this result I added two double fields called MIN_HOUSE and MAX_HOUSE.  I selected and calculated the MIN_HOUSE value to be the lower of the Min_FROM_HOUSE_NUMBER and Min_TO_HOUSE_NUMBER values and the MAX_HOUSE was calculated to be the higher of the Max_FROM_HOUSE_NUMBER and Max_TO_HOUSE_NUMBER values. In another post I will describe how I used this to get the ideal range set on the segment that had a change of measure values and touched the end with multiple stacking house numbers.  That ultimately fixes most of the cul-de-sac problems (except when a houe is on the wrong side of the bulb, but that is for another discussion). Once the individual house address pair segments are categorized I unselected all records and did the Summary that generated the overall Min and Max measures and House Numbers for all address points and categorized their ASC_DESC characteristics.  I made both the individual segments and the overall address segments into Route Event layers.  Then I set up a relate between those two layers through the common RID values. Now I selected all of the overall address events that were "Ascending" and performed the relate to the individual house pairs.  I then selected from the current selection of individual house pair events all segments that had ASC_DESC values of "Descending".  Then I reversed the relate twice to see just the individual house pair events for segments with mixed Ascending and Descending events within an overall Ascending address set. Examining these events revealed out lying address with messed up house numbers (in one case 14735 had been mistyped as 71435, which really screws up a range).  I fixed obvious errors in the data and looked at clusters of addresses that switch direction only because of houses being closer or further away from the road.  If a address trend reversal would affect a centerline end point I paid particular attention to those house pairs and in some cases overrode the actual addresses (rarely, but small reversals were often due to flag lots).  This analysis resolved some of the previously Mixed range sets and also reveals jurisdictional number changes and ranges that should be divided and handled separately. Performing this analysis before doing the steps where the overlay of actual centerlines to the address points and assignment of centerline ranges resulted in fewer anomalies.  With the corrections of the overlapping measure house numbers (for a later post) the cul-de-sacs were able to be completed through the normal range assignment process. ... View more

Outer joins should work with the Make Query Table tool: [ATTACH=CONFIG]26856[/ATTACH] In this case, the input polygon feature has one feature and the input table has 3 features.  I set up the tool as follows: [ATTACH=CONFIG]26857[/ATTACH] Since I'm including the Shape field, the output will have three identical features: [ATTACH=CONFIG]26858[/ATTACH] Here's a blog on the topic. That is not the definition of an outer join.  I definitely agree Make Query Table can link up one-to-many and many-to-many relationships and create all record combinations where both tables have matching records (an inner join). Here is what wikipedia says an outer join is: "Outer join An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record�??even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both)." Make Query Table cannot do an outer join.  Any constraint in the criteria results in an inner join (i.e., both tables must have at least one record that matches the input criteria for an output record to be generated).  An unconstrained Make Query Table join (no criteria) will associate every record in both tables to every record in the other, making a 100 x 100 record join result in 10,000 output matches.  But even that is an inner join.  Even if the criteria is Table A <> Table B on a field, it will create an inner join of only mismatched field values from both tables. So using Make Query Table you cannot set up a criteria like: Table_A.Field_A = Table_B.Field_A Or Table_B.Field_A is Null The first half of the SQL makes sense with the Make Query Table tool, but not the second half.  Since the second half makes no sense to the tool it results in no records being outputted (or else it is ignored, I forget which). ... View more

I am trying to figure out a way to edit one polyline's geometry to match that of another quickly. A copy and paste cant be used because there is a trigger in SDE for unique IDs each time feature is created. So, the attributes must stay exactly as they were only modifying the vertices to match those of the "Correct" polyline / alignment. Those vertices never match in quantity or location. Has anyone found a method or tool to do this? Enable the Spatial Adjustment toolbar and use the Attribute Transfer Tool.  Set up the Transfer mapping to only alter geometry.  Works only in an edit session.  See the help here for 10.1.  Earlier versions had the tools under different toolbars, so I need to know your version to get you to the right info. Please provide that when making help requests as it saves time. ... View more

Unbelievable. Is there no tool available to do an outer join any longer? The Join tool will, on a single field, do an outer join (with limitations), but Make Query Table, which I assume you are using because a single field join won't work for what you need or because you need a one-to-many or many-to-many relationship table created, does not support an outer join.  If this is actually a one field join that is in a one-to-one or many-to-one relationship, then use Make Feature Layer or Make Table View and then the standard Join tool to create an outer join.  There never was an outer join tool for one-to-many and many-to-many before. At 10.1 I have seen instances where Feature Class to Feature Class will generate a one-to-many or many-to-many output from a standard join with that relationship type, but it is not visible until after the export is complete.  This is a new capability and still not sure when it operates and when it doesn't (standard Export does not seem to do this).  At 10.0 this new capability is not supported. So why were you trying to use the Make Query Table tool?  What is the relationship type and number of fields you need to join on? Python cursors and dictionaries can do this and are faster than any tools ESRI provides.  See this thread.  The code is new and unfamiliar for me too, but very fast once it is set up correctly. ... View more

I have tried creating an outer join using the "Make Table Query" geoprocessing tool, along with "Copy Features", in Modelbuilder to join a table to a feature class (within the same PGDB), but am getting mixed results each time. Additionally, records are being dropped somehow, for no apparent reason. I followed the suggestion from an old thread here, but using ArcGIS Desktop 10.0. Can anyone advise how to correctly use the "Make Table Query" tool? The Make Query Table tool only supports an inner join.  Outer joins are impossible with that tool.  Python and a pair of cursors joined through a dictionary is your best bet. ... View more

I have examined my outcome some more and found a few other situations I had not anticipated.  When I extracted the Ends from the Centerlines and calculated which point was the From end and which was the To end, I had not accounted for some segments being oriented in the opposite direction from my route.  While I would retain the identification of the original end point orientation for the final transfer back to the actual centerlines, for purposes of assigning addresses I would disregard the actual orientation of the segment and use the route orientation of the segments (low From measure to high To measure).  This keeps addresses that are left of the route on the left of every segment and simplifies assigning ranges significantly. So after assigning a From and To measure for each end based on the tool extraction order (From end first, To end second), I should have done the Locate Feature on Route tool, built the line segments and then joined those line segments back to the end points to select reversed centerline end points.  If the Min_Meas of the line event is equal to the To end point and the Max_Meas of line events is equal tp the From end point, the centerline is reversed.  I would also have added a Route_From_To field and recalculated the From and To point to be opposite of the original line for the reversed centelines, while confirming the From and To orientation of segments that went with the route.  Then I should have sorted the end points on the Route based orientation field before doing the Overlay Route Events operation.  Keeping everything consistent with the route's one orientation makes confirmation of correct address patterns and detection of anomalies much easier. At the final step when I would transfer my ranges back to the original centerlines, I would still have to separate out all of the centerline segments where the segment orientation was opposite of the route orientation.  These segments would reverse both Left and Right side addresses and From and To addresses.   As a result, at the final step, L_F_ADD of the route would fill in R_T_ADD of the centerline, L_T_ADD of the route would fill in R_F_ADD of the centerline, R_F_ADD of the route would fill in L_T_ADD of the centerline and R_T_ADD of the route would fill in L_F_ADD of the centerline. ... View more

Chris and Caleb: Thanks for exploring the dictionary syntax more fully and clarifying some of the options I have for working with them.  The dict pivot table emulation is interesting and would probably deal with my full many to.many relationship preservation question the best.  The summary example also could prove useful. Just one more example would be appreciated.  Summary Statistics cannot get the Min or Max date of a table, since date fields are unavailable for summary.  Sometimes I would want the Max date associated with the unique case of each name or other identifier.  Typically when I do get this information I then only want the related information of just the record with that max date for the unique ID.  How would I do something like that? So if I had something like (also treat the Last Name and First Name as separate fields): Smith, John,  Jan 10, 2013,  Applied Smith, John,  Feb 3, 2013,  Assigned Smith, John,  Mar 25, 2013,  Tested Smith, John,  Jun 11, 2013,  Hired Smith, Jack,  Jan 10, 2013,  Applied Smith, Jack,  Feb 7, 2013,  Assigned Smith, Jack,  Mar 25, 2013,  Tested Smith, Jack,  Jun 5, 2013,  Rejected Smith, Kim,  Jan 10, 2013,  Applied Smith, Kim,  Feb 12, 2013,  Rejected I would want: Smith, John,  Jun 11, 2013,  Hired Smith, Jack,  Jun 5, 2013,  Rejected Smith, Kim,  Feb 12, 2013,  Rejected Of course the real data would be many more unique cases with many more data records. A one shot way to do this kind of summary would have implications for many of my other similar workflows where I first have to summarize a value (say the Min From measure and Max To measure of a set of routes) and then get the associated data for just the record(s) that match that min or max value for that case (for example the house number associated with the min From measure and the max To measure of each route).  Using geoprocesssing this is a 3 step process at least. ... View more

You have to get creative - I routinely deal with one to many or many to manys by using either composite keys and/or composite look up values. A basic example: compKeyDict = {('smith, john', 1): "cat street", ('smith, john', 2): "dog street"} compValDict = {'smith, john': ["cat street", "dog street"]} So the first is a composite key involving two separate tuple keys with each tuple key associated to one value.  The second is a single key with a list composite values.  I understand how to access the second structure, having adapted the code you originally provided. However, how would I match the keys of the first example?  Would I always have provide a whole tuple for each key or could I get at it by using just the value 'smith, john'?  By extension, can the composite key approach involve a list of items, or is it limited to single values and tuples? ... View more

Here is a screen shot of the result for the Even Left Addresses.  Lines that are dark blue have normal address ranges.  Lines that are red have no address on at least one end of the segment.  The light blue selected lines have negative addresses. ... View more

Continuing forward with assigning to the full set of centerline segments.  I found that I had not created line segment events from the Centerline end points.  To do that I run the Summary Statistics tool on the Ends point Locate Features on Route run with the following settings: Summary Field: MEAS MIN MEAS MAX Case Field: RID ORIG_FID STNAME (My Street Name Field) L_F_ADD L_T_ADD R_F_ADD R_T_ADD I Joined these events on the ORIG_FID to the events I had created using the Overlay Route Events tool that merged the Centerline Segments to the Address Point data.  I transfer the L_F_ADD and L_T_ADD values over with the field calculator.  Then I break that join. Now I create a relate on the RID field from the selected Summary that were Ascending and Descending to the Centerline segment events table.  The selection should match: "ASC_DESC" IN ( 'Ascending', 'Descending') AND (("HOUSE_INTERVAL" > 2 AND "HOUSE_INTERVAL" < 10) OR ("HOUSE_INTERVAL" > -10 AND "HOUSE_INTERVAL" < -2)) Then I Select from the Current Selection those Centerline events where L_F_ADD is blank.  "L_F_ADD" <= ' ' Then I join that selection to the Summary Table on the RID fields.  Then I calculate the L_F_ADD field to be: Parser:  VB Script Show Codeblock:  Checked Prelogic Script Code: If [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MIN_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then   Raw = ([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MIN_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 Else   Output = "" End If L_F_ADD = Output Then I break the join and perform the relate on the RID field from the selected Summary that were Ascending and Descending to the Centerline segment events table.  The selection should match: "ASC_DESC" IN ( 'Ascending', 'Descending') AND (("HOUSE_INTERVAL" > 2 AND "HOUSE_INTERVAL" < 10) OR ("HOUSE_INTERVAL" > -10 AND "HOUSE_INTERVAL" < -2)) Then I Select from the Current Selection those Centerline events where L_T_ADD is blank.  "L_T_ADD" <= ' ' Then I join that selection to the Summary Table on the RID fields.  Then I calculate the L_T_ADD field to be: Parser:  VB Script Show Codeblock:  Checked Prelogic Script Code: If [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Ascending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   If Output > Raw Then Output = Output - 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] < [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 ElseIf [ADDRESS_EVEN_LEFT_Lines_Sum.ASC_DESC] = "Descending" AND [CL_ENDS_LINES.MAX_MEAS] > [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS] Then   Raw = ([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   Output = Round(([CL_ENDS_LINES.MAX_MEAS] - [ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS]) * [ADDRESS_EVEN_LEFT_Lines_Sum.HOUSE_INTERVAL]/2, 0) * 2  + [ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER]   If Output < Raw Then Output = Output + 2 Else   Output = "" End If L_T_ADD = Output I am now double checking the calculations that separated each range by 2 where s From and To measure meet on separate segments, since that is the most confusing thing to get correct and I may have made an error in one or more of the calculations when adjusting up or down by 2 house numbers.  It appears when sequential houses are equal or reversing order. I see a few instances, particularly when the order is descending house numbers relative to ascending measures.  That would be the most confusing case.  The error occurred in one of the calculations in a previous post when I was doing the ranges within the address point values. Both calculations turned out to be in error for the From and To measures in the case of a descending House number order.  The correct calculations are: For the From House Numbers between actual addresses the calculation should have been: Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then   If Int_House < Raw_House Then     Output = Int_House + 2   Else     Output = Int_House   End If Else   If Int_House > Raw_House Then     Output = Int_House - 2   Else     Output = Int_House   End If End If For the To House Numbers between actual addresses the calculation should have been: Raw_House = ( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) + [FROM_HOUSE_NUMBER] Int_House = Round(( [TO_HOUSE_NUMBER] - [FROM_HOUSE_NUMBER] ) * ( [meas] - [FROM_MEAS] ) / ( [TO_MEAS] - [FROM_MEAS] ) / 2, 0) * 2 + [FROM_HOUSE_NUMBER] If [FROM_HOUSE_NUMBER] < [TO_HOUSE_NUMBER] Then   If Int_House < Raw_House Then     Output = Int_House   Else     Output = Int_House - 2   End If Else   If Int_House > Raw_House Then     Output = Int_House   Else     Output = Int_House + 2   End If End If After rerunning these calculations, summarizing the line events from the centerlines actually between addressed and transferring the house numbers between actual ranges to the full set of centerline segments, all seems correct now. A few ranges went negative, which would have to also be checked. ... View more

To answer some of your questions: Yes. But you can use tuples as keys as well (aka a composite key) - which of course also have to be unique. You have to get creative - I routinely deal with one to many or many to manys by using either composite keys and/or composite look up values. A basic example: compKeyDict = {('smith, john', 1): "cat street", ('smith, john', 2): "dog street"} compValDict = {'smith, john': ["cat street", "dog street"]} I would put a wager in that anything (well pretty much anything) you can do in a RDBMS you can also do much faster and cheaper using dictionaries. All it takes is imagination and a lot of conditional expressions! If it helps, here's a practical example of making use of a composite value dictionary where the sorted order of the many values are important to the overal analysis: http://forums.arcgis.com/threads/89835-brainteaser-viewshed-wind-turbines-the-more-you-see-the-worse-it-gets...?p=320549&viewfull=1#post320549 I still don't follow Python syntax.  It makes no sense to me.  I am a VBA guy and resist Python until it proves its worth, but even then I don't read Python intelligently and the syntax and descriptors are totally unfamiliar.  Translate what you are doing into English, since I do not know what you are setting up.  Break it down.  For example, how do compKeyDict and compValDict relate to each other?  Is this the look up or the set up?  Are these two different ways of doing the same thing (looks like it to me).  It may as well be Martian.  What does the colon mean?  What are the brackets doing?  It is too cryptic. I can't get imaginative or creative if I have no idea what I am creating.  I also refuse to go to the Python.org site, since the help it provides is useless and even more confusing than the language. ... View more

Now I am moving on to the issue of addressing segments that fall outside of the actual address points.  To do that I need to extract the first and last address in a range.  This will only work if the addresses are continuously increasing or decreasing.  A range split due to jurisdictional changes or where the direction of addresses changes, like many cities do to divide roads that are north/south or east/west of a central road, have to be isolated and handled separately on each side of the address split. I used the Summary Statistics tool on the Even Left Address Range line segment events I created for all of those house numbers.  Here are the summary fields and case fields: Summary Fields: FROM_MEAS MIN TO_MEAS MAX MIN_HOUSE_NUMBER MIN MAX_HOUSE_NUMBER MAX Case Fields: RID The result of the summary is shown in a screen shot as a line event with thick pink lines indicating the actual coverage of a set of Even Left address points over a route. The Min and Max measures and house numbers will not necessary pair together, but will be used to detect ascending ranges, descending ranges and split ascending/descending ranges. To do that I first add a text field with 10 characters called ASC_DESC to the Summary table.  Then I joined the Summary to the set of Even Left Points that were sorted on the RID fields.  Then To find Ascending Routes I used first use the following selection: ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER Then I break the join and create a relate between the two tables on the RID field and perform the relate from the selected set of records in the ADDRESS_EVEN_LEFT_Sort table to the ADDRESS_EVEN_LEFT_Lines_Sum table and then back the other way around.  The I rejoin the tables as I had done previously and perform a Select from the Current Selection query using the following expression: ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER Then I break that join, recreate the relate between the two tables and perform it again from the selected set of records in the ADDRESS_EVEN_LEFT_Sort table to the ADDRESS_EVEN_LEFT_Lines_Sum table.  I calculate the ASC_DESC field of these routes as Ascending. Then I perform the same step only this time I use these two queries (first one as new selection and second as select from current selection): ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MIN_FROM_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MAX_MAX_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER and ADDRESS_EVEN_LEFT_Sort.MEAS = ADDRESS_EVEN_LEFT_Lines_Sum.MAX_TO_MEAS AND ADDRESS_EVEN_LEFT_Lines_Sum.MIN_MIN_HOUSE_NUMBER = ADDRESS_EVEN_LEFT_Sort.HOUSE_NUMBER After performing the relates back and forth I sort descending on the ASC_DESC field.  Any records that already contain an "Ascending" value are actually routes with zero length measures.  I will reselect the routes with an "Ascending" value and recalculate them as "No Length".  I perform the relate to the Summary table again, deselect the routes with a value of "No Length" in the ASC_DESC field and calculate the rest of the ASC_DESC values of the Summary table for these routes as "Descending". The rest of the routes that are still null are not Ascending, Descending, or No Length.  They are mixed addresses that reversed direction at some point within the address set.  So I select where the ASC_DESC field is null and calculate the ASC_DESC field as "Mixed". As a final check I did a selection in my summary table for all cases where ASC_DESC IN ('Ascending', 'Descending') AND MIN_MIN_HOUSE_NUMBER = MAX_MAX_HOUSE_NUMBER, but did not find any.  had I found some I would have recalculated the ASC_DESC value of these records as "One House". I resymbolized the summary line events to show the ASC_DESC categories on my routes.  The legend shows the pink lines are Ascending, the yellow lines are Descending.  The other two categories were rare and do not occur within the current extent, but they do occur elsewhere in my data.  The No Length lines will have Event errors and will not appear at all. Next I calculate the measure distance as a unit length separating the average house number in one house number intervals.  So I add a double field called HOUSE_INTERVAL.  I then select all of the Ascending and Descending Routes and calculate the value of this field as: If [ASC_DESC] = "Ascending" then   Output = ([MAX_TO_MEAS] - [MIN_FROM_MEAS]) / ( [MAX_MAX_HOUSE_NUMBER] - [MIN_MIN_HOUSE_NUMBER]) ElseIf [ASC_DESC] = "Descending" then   Output = ([MIN_FROM_MEAS] - [MAX_TO_MEAS]) / ( [MAX_MAX_HOUSE_NUMBER] - [MIN_MIN_HOUSE_NUMBER]) End If I then set the table view to only show selected records and sort the HOUSE_INTERVAL field Ascending to find any extremely small intervals (less than 2 feet to -2 feet apart) and will exclude them for examination later.  I also exclude the largest intervals (more than 10 feet or less than -10 feet apart).  Large separations could apply in commercial districts, but I would want to examine them, because they could also indicate that those addresses contain a hidden Mixed ascending/descending address range set.  They also could indicate an odd ball address is skewing the interval. I am going to stop this post here.  But next I will go about assigning address ranges to unassigned segments that do not fall between actual address points based on these intervals, where the route ascending/descending value makes sense. ... View more