6 Replies Latest reply on Oct 2, 2013 12:43 PM by rfairhur24

    Table Join: Many to One - Create Overlapping Polygons

    ljcwalston
      I have a question on joining/relating non-spatial tabular data to a polygon GIS layer, which would essentially expand the GIS layer (new FIDs) by the number of matching tabular records to the spatial data. I'm working on this in ArcGIS Desktop 9.3.1. I have a polygon layer for township ranges. I have a separate table that tracks ecological data in each township range. The external table and the polygon dataset are associated by township range code. There are roughly 700 unique township range polygons in my area of interest. However, there are many multiple ecological records for each township range in the external tabular dataset that I am maintaining (~7,000). I would like to run some sort of table join on the GIS layer to add all matching ecological records to the GIS layer and expand the GIS layer by common attribute (township range code), thereby creating overlapping polygons where multiple ecological data occur. The simple and straightforward table joins and relates do not do this successfully and I have checked on previous forums that have attempted to ask this question with no helpful results. Is anyone aware of a work around, arcscript, or program that will perform this task?

      Thanks for any info,
      Lee
        • Re: Table Join: Many to One - Create Overlapping Polygons
          doclmillard
          I've done something similar, exporting a one-to-many relationship to a new feature class with one feature per joined attribute.

          I used the Make Query Table command.

          There should be some documentation in the Help file.
          • Re: Table Join: Many to One - Create Overlapping Polygons
            mkrupp
            Normaly this would be done with a relate not a join as join will attach to the first matching record it finds. Once you create the relate you open both tables, the attribute table as well as the related table. Selecting a 'one' record in the attribute table will select all of the matching 'many' records in the related one.

            Creating a new polygon for each match in a one to many sounds like bit of a data managment nightmare.
            • Re: Table Join: Many to One - Create Overlapping Polygons
              amberm
              Hi,

              I have the same problem and suggestions so far aren't fixes, so I'm re-posting. I am working on a 30-year animation and want to draw polygons colored by month repetitively.

              My shapefile has 12 overlapping polygons (one for each month of the year) with attributes numbered 1 - 12. My text file has one record for each year-month (30 x 12 = 360 records). In addition to the unique year-month value (1/1/1980, 2/1/1980...), the 360-record text file has a repetitive field numbered 1 - 12.

              The goal is to generate 360 polygons that can be time-enabled.

              Thanks

              Amber
              • Re: Table Join: Many to One - Create Overlapping Polygons
                rfairhur24
                Hi,

                I have the same problem and suggestions so far aren't fixes, so I'm re-posting. I am working on a 30-year animation and want to draw polygons colored by month repetitively.

                My shapefile has 12 overlapping polygons (one for each month of the year) with attributes numbered 1 - 12. My text file has one record for each year-month (30 x 12 = 360 records). In addition to the unique year-month value (1/1/1980, 2/1/1980...), the 360-record text file has a repetitive field numbered 1 - 12.

                The goal is to generate 360 polygons that can be time-enabled.

                Thanks

                Amber


                Make Query Table can solve the problem.  What did you do with that tool?  It is your only option prior to 10.1, unless you are a skilled programmer of Python or ArcObjects.

                The text file needs to be imported to file geodatabase and so does any shapefile for this to work.  The conversions process is not that hard.  Text files and shapefiles will limit you severely in what you can do.  If I were you I would incorporate a conversion process to abandon them for everything I do that is not a one time process.

                At 10.1 exporting from a standard join sometimes works now to translate one to many or many to many to one to one features (although it has some conditions where it does not work that I have yet to identify the cause).  However, you have to be able to create an actual join on a field with an identical set of shared values for that to work.

                Your description of your data is not sufficient to tell you anything else.  Screenshots are the best way to convey your data set up to others here on the forum.
                • Re: Table Join: Many to One - Create Overlapping Polygons
                  amberm
                  Thanks for the pointers - that was enough advice to get it working.

                  : )

                  Oddly, my Make Table Query result looked perfect but, when I exported the result to a shapefile, each year-month was replicated 12 times, resulting in 4000+ records. I tried adjusting everything I could think of and eventually just hand-deleted the extraneous records. Not an ideal solution, but I only need to do this once and the final result is exactly what I wanted.

                  I'm running v10.1.

                  -Amber
                  • Re: Table Join: Many to One - Create Overlapping Polygons
                    rfairhur24
                    Thanks for the pointers - that was enough advice to get it working.

                    : )

                    Oddly, my Make Table Query result looked perfect but, when I exported the result to a shapefile, each year-month was replicated 12 times, resulting in 4000+ records. I tried adjusting everything I could think of and eventually just hand-deleted the extraneous records. Not an ideal solution, but I only need to do this once and the final result is exactly what I wanted.

                    I'm running v10.1.

                    -Amber


                    Actually that is not odd behavior at 10.1.  It is the translation of one-to-many and many-to-many joins that 10.1 does now during export.  It does not show up when you create the Join.  It only appears after the export in the new output.  So effectively the feature replication happened twice, multiplying the effect.  Anyway, you got what you needed which is what counts.