Hello, I am relatively new to using the model builder and I have a problem maybe someone could help me with. I have a table in a Geodatabase, and I have a shapefile. The geodatabase table for sake of argument has 1000 records, and the shapefile has 900 records. I would like a model that would pull out or extract those 100 records that exist in the geodatabase table but not in the shapefile. Is this easy to do, and does anyone have suggestions on how to go about this?
If they have a common unique ID attribute that matches in both databases, then a standard join and a selection where the FID of the shapefile IS NULL will get you the answer of which IDs do not exist in the shapefile that do exist in the geodatabase. However, for the set that did match on the common ID, this won't tell you if they have changed geometry or other attributes, so it is not a complete comparison.
If the only relationship is spatial it is nearly impossible if they are complex polygon shapes that have to have identical geometry. Line geometry is also difficult to process, but with an advanced license there are some tools that make it much easier. Points can use Spatial Join, provided there is no overlapping points in a the shapefile.
So what constitutes a match for your data?
Thanks for the replies. I do have a match in both tables, the Parcel ID. I know how to do this manually, but I am trying to automate this process so that is why I was thinking of doing this with model builder.
I think this task is more easily achieved using some custom code or relational database views, that is how have done this in the past. In custom code I can load all the data into arrays and compare between them. We find those that are in table1 but missing from table2 and those in table2 that are missing from table 1. So there are two outputs, parcel ids missing from table 1 and those missing from table 2. Before using custom code this was done in SDE/SQL by creating 2 table views (join) between the two tables/layers and creating the querying to look for null values on the joined table side.
If you must use Model Builder, I'm not sure what the best option is there... You may want to take a look at Make Query Table tool, but the inputs cannot be a shapefile. And the output is temporary unless the document is saved or copied to another layer. This could give you the parcel IDs that match if you build the expression like this 'Table1.PID = Table2.PID' - But in reality you want to find those missing from each other, right? It would give you a table of similar parcels that you could use to compare each of the other layers to, but I'm not sure it helps that much. There are many new tools in ArcGIS 10.x and I'm a bit surprised if there isn't a tool for this sort of analysis.
You don't have SDE by any chance? A relational database or custom app could get the results better than model builder, unless there is a tool for this that I am unaware of.
- I think Richard has you pointed in the best direction for using model builder with his previous post. Let us know how it turns out.
Array.Sort(CombineData) Do Until i >= UBound(CombineData) Var1 = Mid(CombineData(i), 1, (InStr(1, CombineData(i), "|") - 1)) ' gets parcel ID Var2 = Mid(CombineData(i + 1), 1, (InStr(1, CombineData(i + 1), "|") - 1)) ' gets next parcel ID If Var1 = Var2 Then i = i + 1 Else If Mid(CombineData(i), (InStr(1, CombineData(i), "|") + 1)) = "A" Then ' checks table designator ListBox2.Items.Add(Mid(CombineData(i), 1, (InStr(1, CombineData(i), "|") - 1))) ElseIf Mid(CombineData(i), (InStr(1, CombineData(i), "|") + 1)) = "Z" Then ' checks table designator ListBox1.Items.Add(Mid(CombineData(i), 1, (InStr(1, CombineData(i), "|") - 1))) End If End If i = i + 1 Loop