Latest Contributions by RichardFairhurst

I looked at the hyperlink of the join with some data in Number 4 of your post that responded to my questions.  It did not answer some of the questions I need answered. 1.  I could not tell what fields were coming from what data source in the example.  If you used aliases (which you almost certainly did) convert the example to show the actual full field names so I can see how they would have to be used in a calculation.  Also you mentioned fields that I do not see in the example data:  ToFromTotalStats & FromToTotalStats from CarData_RoadLinks_Statistics and CarDataToFromTOTAL & CarDataFromToTOTAL in ITN_RoadLinks.  What field in your example data corresponds to the RoadLinkTOID field? 2.  You also said you only Keep Matching Records using the join.  How many features actually appear once the join is in place?  The answer to this question would partly help answer the next question. 3.  You also did not answer another critical question:  What is the relationship between the ITN_RoadLinks feature class and the CarData_RoadLinks_Statistics table? One-to-One - the RoadLinkTOID values are unique for each feature and each table record and only one matched table view record is possible for any given RoadLinkTOID value. Many-to-One - the features do not have unique RoadLinkTOID values, but every table record has a unique RoadLinkTOID value. One-To-Many - every feature has a unique RoadLinkTOID value, but every table record is not unique for the RoadLinkTOID values. Many-To-Many - the RoadLinkTOID values are not unique for the features or the table records. If the relationship is One-to-One, then the ITN_RoadLinks feature class only contains a small subset of data related to the CarData_RoadLinks_Statistics table.  At most 1,328 features are matched in the table.  The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features.  This relationship is unlikely. If the relationship is Many-to-One, then CarData_RoadLinks_Statistics could potentially relate to all or any subportion of the ITN_RoadLinks feature class and the matches would most likely greatly exceed 1,328 features.  Potentially every record in the ITN_RoadLinks feature class could be matched by the CarData_RoadLinks_Statistics table.  The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features.  The effect of the join is that in memory you are presented in essence with a table view that shows a one-to-one version of the feature and table records combined.  This relationship is the most likely and can contribute to memory instability. A One-to-Many relationship would mean that less than 1,328 features are involved in the join and potentially many of the records in the CarData_RoadLinks_Statistics table are not seen through the join.  This relationship is the least likely. A Many-to-Many relationship would involve a multiplication of features if fully joined that could be in the millions of features if the relationship was converted to a One-To-One relationship.  Many of the records in the CarData_RoadLinks_Statistics table associated with the features are not actually seen through the join.  This relationship is the second most likely, but the worst to deal with and the most likely to result in memory instability. -------- If the relationship type is Many-to-One then a python script that uses two cursors can be fairly easily written that would convert the CarData_RoadLinks_Statistics table to a dictionary to transfer the data.  This script should have a much lower memory footprint than a join if written properly.  A Many-to_many relationship would be almost impossible to work out without blowing out the memory. ... View more

Thank you. That information is a much better starting point to work out your problem.  I am assuming that this information is relevant to the original calculation you proposed in your very first post.  My comments are based on that assumption.  However, I am concerned that you may have resolved that problem and are giving me information that relates to a different problem that you are now having.  So please let me know if your original calculation is still the problem you are trying to solve. My tests showed that your original calculation worked fine when it was applied to a feature class and table that matched the prefix and field names used in the calculation.  However, in your very first post you proposed a calculation that used a prefix to the fields called JOIN_CarData_RoadLinks.  That prefix makes no sense with the data you have described in your last post and definitely would result in an error.  So lets start there. Since both of these data sources mentioned in your last post are within File Geodatabases, after the join is created, the calculation field prefixes that would reference fields within the Feature Class would have the form: ITN_RoadLinks.field.  Calculation field prefixes that would references fields within the Table would have the form: CarData_RoadLinks_Statistics.field. For now I will assume that everywhere you wrote JOIN_CarData_RoadLinks you really meant to reference fields in the CarData_RoadLinks_Statistics table.  If that is true than the original calculation should have been written: Dim CarOrientation Dim RoadOrientation RoadOrientation = [CarData_RoadLinks_Statistics.Orientation] Dim RoadCompass RoadCompass = [CarData_RoadLinks_Statistics.CompassA] Dim RoadCompassMax RoadCompassMax = RoadCompass + 170     If RoadCompassMax > 360 Then     RoadCompassMax = RoadCompassMax - 360     ElseIf RoadCompassMax < 0 Then     RoadCompassMax = RoadCompassMax + 360     End If Dim RoadCompassMin RoadCompassMin = RoadCompass - 170     If RoadCompassMin > 360 Then     RoadCompassMin = RoadCompassMin - 360     ElseIf RoadCompassMax < 0 Then     RoadCompassMin = RoadCompassMin + 360     End If Dim PointBearing PointBearing = [CarData_RoadLinks_Statistics.Bearing] If PointBearing > RoadCompassMin And PointBearing < RoadCompassMax Then     CarOrientation = RoadOrientation End if If PointBearing < RoadCompassMin Or PointBearing > RoadCompassMax Then     If RoadOrientation = "-" Then         CarOrientation = "+"     End if     If RoadOrientation = "+" Then         CarOrientation = "-"     End if End if So which data source actually contains the fields named Orientation, CompassA and Bearing?  Are these fields all in the CarData_RoadLinks_Statistics table?  If not than any field in the calculation above that actually is found in the ITN_RoadLinks feature class should have the CarData_RoadLinks_Statistics prefix changed to the ITN_RoadLinks prefix. =========== I am concerned that you said in one of your posts that ArcMap is crashing when you do this manually.  Your data is local to your machine, which is good, so you don't need to consider network communications as a factor for the crash. I have a parcel database with approximately the same number of records you mentioned and ArcMap crashes frequently when I do joins or relates to it as well, usually during record selection or calculation through the join.  This is probably because joins and relates are built in memory, and if memory resources hit a wall, ArcMap crashes.  Some peoples seem to be able to work with even larger data sets, but the differences could be due to differences in the resources of the workstations processing the data. The crash could also be due to the fact your data sources reside in separate geodatabases and the resources needed to deal with that are managed differently than resources used when both are within a single geodatabase.  I would suggest manual tests in Desktop where the feature class and table are in the same geodatabase, just to see if that makes a difference. One way to make sure the problem is with resource management only is to do all of your tests on a dummy data base that uses a small fraction of your actual data first before scaling it up to your full dataset.  If errors trigger on the small scale data it is a user fault that needs to be resolved first, but if problems only arise when you attempt to go to a larger scale than it is likely a resource issue. When does ArcMap crash?  As soon as you attempt the join?  That is a definite resource issue. Does it crash only after beginning a field calculation?  Did you try a simple calculation on the data first or have you only attempted a complex calculation on the joined data?  You should try a field calculation after the join is created using a simple value like " " or 0 into a dummy field, just to see if ArcMap would let you do that.  If you could not do that on your full dataset, than resource management is the issue.  However, if you can get past these basic steps and crashes only occur when you do more complex calculations, then more small scale testing followed by large scale experimentation is required to determine what actually triggers the crash. When crashes due to resource use come into play, instability is difficult to predict, since you really don't know what is happening in the background and what the trigger point for the crash is.  It could indicate a memory leak on the part of ESRI in some cases.  You may actually have to provide ESRI with your actual data to determine if the problem you are having is reproducible or resolvable if you get to a point where you know it should work and every step works with a much smaller subset of data. ... View more

I would never incorporate Excel into a GIS process.  It is not a database and it is unreliable.  I never use it directly in ArcGIS and recommend that no one use it directly in ArcGIS.  I always convert it to a real database.  ArcGIS can be very particular about what works well and what does not, especially when you use joins, and almost every day I have to convert data from one format to another to get it to work reliably.  IMHO, Excel is only for those that cannot use a database and GIS professionals should never rely on it as a direct data source without conversion and data scrubbing. I would never call any Excel spreadsheet an "ordinary table" in any sense of those words. ... View more

I've had a look around on the web and I'm sure that the problem is to do with joining an ordinary table to a feature class, but I still have had no luck finding a solution. Has anyone come across this problem before and found a way around it? Thanks, Liam. Liam: I work in transportation and, although I don't use Network Analyst, I do use linear referencing of point and line events almost everyday.  So I am very familiar with working with data that has From and To direction dependent information.  I also transfer data between feature classes and tables almost everyday using Joins and the Field Calculator with no problems.  I have worked this way since at least ArcGIS 9.0 and currently am using ArcGIS 10.1 SP1. I looked at your semantics doc and at your example, but you have not clearly explained the key items I need to understand.  At some level you went from too little information to information overload using a shot gun approach of dumping everything.  I need to focus to several key things.  So lets take them one by one. 1.  I need to know the database type of the feature class and table you are working with.  This is the biggest factor affecting the join.  Do you have the feature class and table in a geodatabase.  If they are in a geodatabase are they in the same geodatabase or different geodatabases?  What type(s) of geodatabase are involved?  SDE, file geodatabase, or personal geodatabase?  Is one in a geodatabase and the other isn't, or are both outside of a geodatabase?  If the feature class is not a in a geodatabase is it in a shapefile, a coverage, or a CAD file?  If the table is not in a geodatabase is it in a dbf table, an info table, an Excel table, or a text file?  Are both in an editable workspace directory or does either have edit restrictions on their workspace directory?  There is no such thing as an "ordinary" table and each specific table type and feature class/table type mix may introduce different problems. 2.  What fields are you joining between the tables?  Name the fields in the feature class and the table and give their data type(s) (long, short, text, double, float, etc).  Does the join field ever have Null values in it in either the feature class or table?  If it is a text field, do the fields share exactly the same value in both tables?  Provide an example of a shared field value, especially if the fields are text fields.  Do you have to join the feature class to the table on two different feature fields, with one join based on the From direction data and the other join based on the To direction data?  More than one join could cause several problems related to Null values and records matching. 3.  What is the relationship type between the feature class and the table?  One-to-one, many-to-one, one-to-many, or many-to-many?  Joins only support one-to-one and many-to-one relationships, meaning that you can have only one table record match any given feature when you use the feature class as the original table and join the table to it.  If the relationship is one-to-many or many-to-many, a join will not support that relationship fully.  Meaning that ArcMap does not fully support one feature having two or more table records joined to it.  It will randomly choose one table record associated with a given feature and ignore all of the others table records when you create a join.  The complex relationships can cause several problems and may need several modifications to your workflow to achieve a successful data transfer. 4.  How many features are in the feature class.  How many records are in the table? So focus on giving clear answers to these questions.  They are critical to knowing how to work with your data correctly in everything you intend to do.  I have dealt with almost every one of these scenarios and have found the ones that work and workarounds for many that don't work directly. ... View more

I have a point shapefile of survey locations.  I also have a table of data corresponding to said survey locations.  The problem is that many of these survey points have been surveyed multiple times or with different equipment.  Each point has a unique primary key, and in the table, there are multiple entries with that same primary key, but it has another column that serves as a secondary key. Is there a way to join the point shapefile to the data table by primary key, but make it create duplicate coincident points for each unique secondary key? Depends on the version of ArcGIS that you have.  Prior to 10.1 there were only two ways to do this. Alternative 1. 1. Use the Make Query Table tool, which requires that the points and the table reside in the same geodatabase.  This output does not refresh from the sources without rerunning the Make Query Table tool and the tool output is not editable without being exported.  The exported features are disconnected from the sources and have to be recreated to reflect updates to the sources. Alternative 2. 2.a.  Add two fields to the points that contain the X/Y coordinates 2.b.  Use the Geometry Calculator to calculate the actual X/Y values to the fields in each point 2.c.  Using the table in Table View Mode (in Model Builder this requires the use of the Make Table View tool) as the source table create a join of the points so that the points are the join table.      Now you have two options: 2.d.1. With the Join in place use the Make X/Y Event Layer tool to use the X/Y coordinate fields of the points to convert the table to an in-memory point feature class.  All of the fields of the table and the points will be visible in this layer.  Updates to the source points or table directly will become immediately visible in this layer.  Edits done to the in-memory layer itself become immediately visible in the source table, but the source points are not editable through this layer. 2.d.2.a   Add the same two fields to the table to hold the X/Y coordinate values of the points 2.d.2.b.  Use the field calculator to transfer the X/Y coordinates of the points to the table records. 2.d.2.c.  Remove the join. 2.d.2.d.  Use the Make X/Y Event Layer tool to make the X/Y coordinates of the table into a in-memory point feature layer.  Only the attributes of the table will be visible in this layer.  Edits done to the source table are reflected in the in-memory points layer with a simple refressh, but the fields and features of the source points are not editable or visible through this layer. 2.e.  Optionally export the in-memory layer to a permanent feature class to make it a permanent point feature class.  This is disconnected from the original source tables and needs to be recreated to reflect updates to the sources. At 10.1 and later you have a 3rd option. Alternative 3 at 10.1. 3.  Like the Make Query Table option, both the points and the table must reside in the same geodatabase.  You can just create a join of the points layer without any special coordinate fields to the table using the points as the source and the table as the join. The one-to-many relationship is not visible or editable through the join.  But you can export the features to a new point feature class.  The export will duplicate records as necessary so that the one-to-many relationship will output a point feature class that has been converted to a one-to-one relationship.  This export output is not connected to the source features and has to be recreated to reflect updates to the sources. ... View more

So it worked once, but now it's giving me the Warning again.  I"m going to get it but thought I'd update you.  If I need to I'll post Join info and we'll go through it. td If the problem is with Nulls then the calculation should be revised as follows in the codeblock section: If IsNull([DDP_OID.Tim_Permission]) Then   Output = [Parcels20120128_1.RuleID] Else   If [DDP_OID.Tim_Permission] = "Yes" Then     Output = 2   Else     Output = [Parcels20120128_1.RuleID]   End If End If ... View more

Ok. In the field that I'm doing the "Select Layer by Attributes" there are a significant amount of rows that are <null>.  I'm going to eliminate them from the feature class and see.  I will be posting later today with more screenshots and such.  Do you know if I can post actual feature classes, .mxd files, etc. on here or are those attachments not allowed? thanks for your ongoing help. td Can you name the fields and field types that you used to create the join.  That is the starting point.  Then some sample values that should be identical in both tables.  A relate can test for record matches and difference as well (although don't try it with a huge table).  If the rows are Null in the source table verify that is true in the unjoined table, and not just in the join.  Joins are built in memory and sometimes do have strange behaviors if you run out of resources. ... View more

Richard, I got to step 4 and tried to run the model just to see if it would work up to that point and it didn't.  I did have to change the step 2 Select by Attribute to Location to select a point interactively.  I tried both methods of Interact (with and without the buffer) and neither worked. Maybe selecting against itself only works in Desktop and not ModelBuilder, so just go from Layer 1 to Layer 2 instead of Layer 1 to Layer 1.  Just keep track of where the selection I described is located and keep transferring selections back and forth until you have a selection in the correct layer at the end. Here is a model that does a similar thing for the beginning steps.  Notice that I used two separate instances of the same feature class to create the layers (The top two Make Layer tiers).  That may make all the difference. Anyway, I have a model that does something very similar and it keeps selecting a feature class against itself in two different layers to test that more than 25 property owners other than the original case owners exists within a buffer distance of the case parcels.  I just keep transferring selections back and forth between the two layers based on the same feature class. ... View more

1.  Create two layers based on the same Counties feature class.  I will call them Layer 1 and Layer 2 to keep it straight. 2.  Select your County of interest in Layer 1.  Use the Create New Selection option is Select By Attributes or get a user selection. 3.  On Layer 1 Select By Location on itself (Layer 1) and apply the Intersect option without any buffer or a very small positive buffer (say 1 foot) and select against itself.  This will be the selection of the County itself plus every County that adjoins the original County.  Use the Create New Selection option. 4.  Select by Location against Layer 2 using the Layer 1 feature selection as the location to base the search on and apply the Intersect option without any buffer or a very small positive buffer (say 1 foot).  Use the Create New Selection option.  This will be the selection of every County that is out as far as 2 Counties away from the original County including all Counties within that boundary. 5.  Select by Location against Layer 2 using the Layer 1 feature selection as the location to base the search on and apply a -1 foot (or very small negative) buffer to the selection.  Use the Remove From Selection option.  This will remove all Counties that included the County itself and the first ring of Counties surrounding it (Layer 1 Selection).  Layer 2 now contains every County that is 2 Counties away from the original and only those Counties. 6.  To transfer the selection of Layer 2 back to Layer 1 use Layer 2 as the feature selection you want to select against Layer 1 and apply a -1 (or very small negative) buffer to the Layer 2 feature selection.  Use the Create New Selection option. No new features had to be created.  This assumes your County layer is topologically correct and that no gaps or overlaps exist along any shared border between any two Counties. This can be done with real layers in a map or with inmemory layers, so Layer 1 could be a real map layer and Layer 2 could be an inmemory layer if you don't want it to actually appear in your map, and by using step 6 the final result will appear in Layer 1 in your map. These are the steps you would have to follow to do this manually in Desktop to achieve the effect you want (except that in Desktop there would not be any inmemory layer and you would have to have 2 real layers that were based on the same feature class in your map). ... View more

Hey, I'm making a model in Model Builder inside ArcGIS for Desktop 10 and running into the following problem: I have a feature class that I want to select a portion of based on the entry in a specific field and then modify a different field in the same feature class for just the rows that were selected using Calculate Field.  I've made a model that uses the "Select Layer By Attributes" to select the rows I wish to modify, and implemented the "Calculate Field" tool immediately after the "Select Layer By Attributes" tool to modify another field within the feature class. My problem is the "Calculate Field" tool modifies every row in the attribute table for the feature class instead of just the rows that were selected in the "Select Layer by Attribute" tool.  How can I make the "Calculate Field" tool work on just the selections from the preceding tool? I have been trying to learn VB If-Then statements and Select-Case statements to try to eliminate the "Select Layer by Attributes" portion of the model and simply do it all in the "Calculate Field" tool but my VB skills are basic at best and every time I try I get an error saying "No records within table".  I am using VB code in the "Code Block" window that is the following: If [DDP_OID.Tim_Permission] = "Yes" and in the "Expression" portion of the Calculate Field tool I have: Int ( 2 ) Any help would be absolutely wonderful. Thanks in advance, Tom [ATTACH=CONFIG]28207[/ATTACH] The calculation is structured incorrectly for VB.  It needs to be like this: Expression:  Output Code Block (Optional): If [DDP_OID.Tim_Permission] = "Yes" Then   Output = 2 Else   Output = [Parcels20120128_1.RuleID] End If This will only overwrite the Parcel20120128_1.RuleID with 2 when the value of DDP_OID.Tim_Permission is "Yes".  Otherwise it will just write the current value of Parcel20120128_1.RuleID into itself unchanged.  Then even if the records were not selected they will only be changed when the condition is met.  You must always provide an output value for every record the calculation might process, even if all you want to do is leave the value of the calculated field unchanged for some of those records. ... View more

Hello, I'm joining a large list of parcel numbers (from an Excel ss) to my parcel layer to extract the matching parcels. My issue is that I need to know which parcels from the list did not match up with a polygon. My join resulted in 11141 matches but there are 11268 parcel numbers in my ss . I need to find out which 127 parcels did not join and research those. Is there a way to do this? Thanks! Julie Excel is unreliable and I never use it, so if it causes problems doing what I recommend, convert it to a geodatabase table and save yourself a lot of frustration. Two ways to do this with a real table.  1.  Create a relate and select all records in the converted Excel table, then transfer the relate selection to the parcels.  Reverse the record selection within the parcel feature class.  That is your set of unmatched parcels. 2.  Create a join and select all records where the join table fields that are required are Null.  That is the set of parcels that are unmatched. Once you have the selection of unmatched records you can copy the records and paste them to a blank spreadsheet page.  Then rearrange the columns to match the main spreadsheet as best as you can and copy the new records to the end of the original spreadsheet page. If you convert the spreadsheet to a table, then export the selected unmatched parcel records to a feature class/table and use the load tool to append them to your table, or use the Merge or Append tool (no test option).  Depends on how well the field names and types match. ... View more

Richard, Many thanks for your help but I think we are talking about different things? I'm talking about this window: [ATTACH=CONFIG]28077[/ATTACH] If run the following code: Public Sub test()     Dim pMXDoc As IMxDocument     Set pMXDoc = ThisDocument     Dim n As Integer     n = pMXDoc.ContentsViewCount     Dim pContentsView  As IContentsView     For i = 0 To n - 1         Set pContentsView = pMXDoc.ContentsView(i)         Debug.Print pContentsView.Name     Next i End Sub It displays only the following names (so no catalog window): Display Source Visible Selection OK.  It thought you meant the TOC.  I'll look for the Catalog Window Interface. I would begin by trying the IGx interfaces.  They may perform the same actions in the Map Document Catalog tab as they do in the Catalog Application. ... View more