9

I am using ArcMap 10.2.2 with an Advanced license. I have used ArcMap for other basic analyses, but am fairly new to the program and have only basic coding skills, with no SQL experience outside of very basic queries using the SQL code builders in ArcMap.

I have polygons of species' geographic ranges and I have a table with a list of pathogens/diseases carried by those species. Certain species carry multiple pathogens. In an ideal world, I'd join the polygons of the ranges to the table of the species+pathogens and keep the polygon/spatial data, but it doesn't seem like there is a GIS tool to do this (I am going to later count overlapping pathogens, so it's important that each species-pathogen combination has its own feature, rather than one feature that has the information for multiple pathogens). These are what my data look like (#1 & #2, #3 is for later):

Attribute table for spatial features:

attribute table for spatial features

Table without any spatial data:

table without spatial data

(Please note that these are not real data, and the pathogen-species combinations may not be real. I actually have 115 polygons and 519 table rows. Species carry anywhere between 1-40 pathogens and thus species have anywhere between 1-40 rows in my table)

As an added bonus, because of future analyses I need to do, I would ideally like to be able to do this one-to-many join such that I could keep an extra column of data from the table (see below for an example of how this might look). However, this is less important because if I can figure out how to do the join, I can just redo the analysis.

table without spatial data, with extra field

There are a few solutions that I found to this problem, but they didn't work for me for reasons listed below:

Solution 1: David Aalbers's blog - I am too much of a novice to figure out how to edit the newer script and figure out how to import it into ArcMap. I tried the older script and it just generated a ton of empty polygons titled test, test_1, test_1_1, test_1_1_1, etc.

Solution 2: One-To-Many Join - I tried to use the Make Query Table tool (and I have no SQL knowledge, although I used the expression builder) and I got ERROR 000383: Issue with a table, cannot find the workspace & Failed to execute (MakeQueryTable). All my files are in the same folder, but they are not in a geodatabase.

  • 1
    Make Query Table will work with database only – FelixIP Jan 19 '16 at 23:34
  • 1
    Make Query Table requires all data in the same fgdb. – klewis Jan 20 '16 at 14:05
22

To multiply the features do this (available to those who have ArcGIS 10.1 or above).

  1. Place the features and table into the same file geodatabase (you must convert Shapefiles/Excel/DBF files into the geodatabase for this to work).
  2. Make sure your polygons have a unique ID field that will be preserved (you can create a Long field and calculate the ObjectID into it so that the ObjectID value will not be lost).
  3. Do a standard Join of the table (attributes, no spatial data) to the features (spatial data) on the matching field. Note that the attribute table of the features (spatial data) will have the same number of features that it did before, but don't worry about this, they all show up in the next step.
  4. Export the features to the same geodatabase as a new feature class (right click the layer from the table of contents and choose export). Note that the number of features in attribute table of the exported features now has the right number of features, the same as the original table (with attributes, no spatial data) or, in the case of a many to many join, a complete set of features for all combinations of features and related records.

The features will multiply in a new feature class where every feature will be present (even if it did not have a match in the table) and where there was a 1:M match before, the features will be multiplied to make a 1:1 match of each feature to all of its table entries. The Unique ID field you created in step 2 will allow you to relate the multiplied polygons to the original single polygon set. With the new feature class of multiplied features you can do selections on the table fields, summaries, Spatial Joins, etc.

If you want to generate a unique ID value that represents each unique species and disease combination you can use the 10.2 version of the tool described in this Blog post. Having this type of key lets you use it to do standard joins to summary tables and statistics based on a multi-field relationship as a substitute for creating an actual multi-field relationship using a Make Query Table set up.

Here is a good reference from esri: https://support.esri.com/en/technical-article/000001228

  • Works perfectly when exported to within the geodatabase! – MooseGirl Jan 20 '16 at 15:20
  • 1
    I have modified my step 4 to state that the export should be to a new feature class in the same geodatabase as the original data. Following that procedure should always work, although it is possible that some variations of where the source and output data are stored may be allowed as well. – Richard Fairhurst Jan 20 '16 at 15:31
  • It was clear before (although future desperate people will surely appreciate it being spelled out), but originally I hadn't properly connected to the geodatabase folder (or something) because it wouldn't let me export to the geodatabase. I restarted and it worked fine. – MooseGirl Jan 20 '16 at 15:59
  • 1
    I added that the joined features will not have the right number of attributes and the exported features will. This frustrated me a lot when I tried this, until I went through all the steps. – nittyjee Feb 13 '18 at 1:24
  • It reads like there's a missing 'not' in #3, "...will not have the same number of features..." – matt wilkie Dec 18 '18 at 19:09
-2

SQL query:

SELECT spp, disease, type
FROM table1, table2
WHERE table1.bimonial = table2.spp;

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

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.