4

I'm working in ArcMap 10 and can move around in R, though am not an expert.

I have a shp file and an xlsx file. The shp file is 20,221 rows and includes a field GID (grid ID) which is not unique among rows because multiple polygons named with a PID (park ID) can have the same GID. PID-GID pairs are unique.

The xlsx file 230,895 rows and also has a GID field which is not unique among rows because for each GID there are 60 years worth of data, each year represented by a row. Year-GID pairs are unique.

How can I combine the two files so that for each PID-GID combo in the shp file I get a row for each GID-year combo in the xlsx file?

I've been playing with joins and query tables and can't get it yet. Much thanks.

  • 1
    Can you post a screenshot of your two tables with the relevant fields? The Make Query Table tool is what immediately comes to mind, but if you're saying that isn't working it would be helpful to know why - what is it doing and what isn't it that you expect. – Chris W Nov 21 '14 at 23:27
  • Thanks, Chris. I will prepare screenshots, if needed. When I select either file as the first for the Make Query Input Tables, the tool and Arcmap freezes. Does this have to do with the instructions: "This tool accepts data from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase, or an OLE DB connection"?? Maybe I can't use shp files and xlsx files in this tool? – ecologist1234 Nov 21 '14 at 23:39
  • 1
    Reading over this a few times, it seems that a Make Query Table based on GID will get what you want - every GID in the shape will get all matching GIDs in the xlsx (so, duplicate records). Note the result is temporary and must be exported to a new feature class. Yes, in order to use that tool you need to bring your shapefile and xlsx file (as a table) into a geodatabase to get it to run. There are a number of tools and features (such as nested SQL statements) only available if the data is in a geodatabase. – Chris W Nov 21 '14 at 23:41
  • 1
    try using a .csv and not xlsx file. – Geoffrey West Nov 21 '14 at 23:54
  • 2
    If you have Desktop 10.1 or later, once you get your data into the same gdb, you can use a standard join and standard export to convert your data from a Many to Many relationship to a 1:1 relationship. The Join will not show this in the table view prior to the export, but the exported file will have all of the related records combined based on the Join field. See the section on One-to-many and many-to-many relationships on this help page: resources.arcgis.com/en/help/main/10.1/index.html#//…. – Richard Fairhurst Nov 22 '14 at 6:39
3

The Make Query Table tool, based on GID will get what you want - every GID in the shape will get all matching GIDs in the xlsx (so, duplicate records). This will potentially generate a very large number of records. For example, if a GID appears in one table four times and six in the other, you will get 24 records.

Note the result is temporary and must be exported to a new feature class. In order to use that tool you need to bring your shapefile and xlsx file (as a table) into a geodatabase to get it to run. There are a number of tools and features (such as nested SQL statements) only available if the data is in a geodatabase, and it can vary depending on that underlying database (OLE, personal, SDE, file, etc.). Null values are another thing only supported in databases (not shapefiles). Also note that Excel files don't always play nice with ArcGIS, and it may be better to convert it to a csv first. You may or may not need a schema.ini file and to make sure your column headings are valid (no spaces or restricted characters, don't start with numbers, etc.).

As Richard points out in his comment, apparently as of 10.1 while a 1:M or M:M Join will only display the first returned matching record, if that join is done on data in a database, when the result is exported or fed to geoprocessing tools it will have all of the record combinations.

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.