0

Primary usage of ArcGIS Desktop Basic: I have to find the intersection of two tables in the same file geodatabase based on the values in multiple fields. Almost all queries to find some help are multiple field queries in one joined table or spatial in nature. The first is just wrong. For the second, I can't assume a spatial accuracy in Table B (below), some elements have already shown to be out side of their geographic range. And that's ultimately the end goal of the project: to improve the spatial accuracy of Table B using Table A.

I have to find the records in A that match records in B, but not all records in B are in A.

Tables: A (0.5M records) B (0.4M records)

Fields to return: A.1, A.10, A.11 B.1

Fields to match. I have assured myself that the fields are of the same type (Text/String, Integer, etc) and that the pairings below are type and value matched. Some of the records in A are going to match some of the records in B.

A.2=B.2 A.3=B.3 A.4=B.4 A.5=B.5 A.6=B.6

Using the ESRI "Make Query Table" in 10.2 basic, I used this as the query:

`A.2=B.2 and A.3=B.3 and A.4=B.4 and A.5=B.5 and A.6=B.6`

Didn't specify any keys.

This returns with the message that there is an error with the expression and invalid SQL statement used.

Making the table on just field 6 takes less than a second but the table fails to open. Field 6 is the least granular.

I have expended my knowledge and feel I won't find the answer previously asked on the internet. This is probably just a database question and not a spatial issue, but I am using spatial data.

UPDATE: I worked on it today (Sunday) some more. Created a new text field (let's call it 20) and populated by concatenating all the fields together successfully so that 'A.20 = A.2 & A.3 & A.4 & A.5 & A.6' 'B.20 = B.2 & B.3 & B.4 & B.5 & B.6' This seemed to work perfectly using only one field to match, except it returned only 2/3 of the expected records. Field 5 has high granularity and removing it from consideration returned 160% of the expected records. Does the "Make Query Table" tool have a limit to the number of fields it can access?

  • Could you please edit your post to include 1) a sample of table A, 2) a sample of table B, and 3) the intended output? – Aaron Aug 9 '15 at 13:50
  • Aaron, I will see what I can do. But like I stated, there are half a million records. I know you are not asking for all of them, but I don't see what looking at a few of them will add. But I respect your rep points and will do my best to comply. – RickOrth Aug 10 '15 at 4:53
1

I have created a tool (Multiple Field Key to Single Field Key Tool - Relate Two Layers Based on More than One Field) that will match multiple fields where each field in one table/feature class has a corresponding match field in the other table/feature class. It creates a new Long field in each with a number that represents each unique value in the set of fields. It optionally can assign numbers to every value in one table and only matching values in the other table, or assign numbers to all values in both tables or only assign numbers to values that match in both tables (-1 is assigned to unmatched values for option 1 and 3).

There is a 10.2 version and a 10.3 version. The 10.3 version interfaces is better, but the 10.2 version interface works reasonably well. The field to field matching part of the interface is a little less intuitive in the 10.2 version, but it works.

Once you have a single field key value you can use standard join and relate operations to select records and transfer values between the two tables. You should also check out the methods for using cursors and dictionaries (Turbo Charging Data Manipulation with Python Cursors and Dictionaries) to transfer data even more quickly than a join or relate and to alter the shape field values from one table to the other.

  • Richard, if I'm reading this correctly, I think I did something similar by concatenating all the required fields into one string. Then I only had to match one field instead of 5. – RickOrth Aug 10 '15 at 4:58
  • That is one way to do it. A separator character needs to be included in the string to avoid numbers combining to potentially create duplicate values. If sorting is important you have to add leading zeros and international date formats for numeric and date fields to make them sort properly. My tool uses the native data formats in the tuple/list it creates for the dictionary and therefore sorts properly. My tool is much faster than the field calculator and will process your data much quicker if you have a very large number of records (1/2 million or more records). – Richard Fairhurst Aug 10 '15 at 13:18
  • Additionally, with string fields that are longer than 80 characters I have observed join corruption (failure, returning incorrect records) in some versions of ArcGIS and some databases. ArcGIS 10.2 may be stable, but avoid using comma as a separator in string join fields, since that may also corrupt the join. Anyway, a Long field is reliably stable in all versions of ArcGIS as a join field. – Richard Fairhurst Aug 10 '15 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.