8

I need a way to programatically merge grid cells (polygon 15x15m) that are directly on top of each other and add together some of their attributes.

I am working in ArcGIS 10.1 with the data stored as a polygon feature class.

The data is the result of pedestrian survey across a pre-defined grid. On occasion the folks conducting the pedestrian survey get off course and record something in a column or row adjacent to the one they are working in (or the GPS position wanders). This results in two sets of data being recorded for each cell when the data from multiple GPS units are appended into a single feature class. I need a way to go through each cell, check to see if there are any duplicate features, if there are then merge their attributes (some integer, some text), and then delete the one feature that didn't get the merged data.

The 'Delete Identical' and 'Find Identical' tools don't do what I'm looking for - and doing it manually isn't an option as the feature class often has upwards of 10,000 features in it.

I know how to iterate through the feature class using python and check to determine if there are any duplicate features in that location. What I don't know how to do is deal with merging the features and updating the attributes. If anyone can offer some direction on how to accomplish this I would greatly appreciate it.

EDIT - More Description of Before/After State: In the picture below there are two features selected - identical 15x15 meter square polygons, I need to add together all the integer fields (the ones with TOT_ as a prefix) and append the GPS_UNIT, INITIALS, and REC_DATE string fields.

It's difficult to show since the features directly overlap.

enter image description here

EDIT 2: I have found that I can easily identify the duplicated features by calculating their XY centers, concatenating them into a string field as [X] & ',' & [Y] then opening the database in access and using a Find Duplicates query to identify which of the cells have been duplicated. I have not however, been able to figure out a good way to merge the attributes for each of the cells into a single row. Help with how to do that, either in python or in a database environment, would be very welcome!

1
  • 1
    Can you post a small sample area with "before and after correction" states?
    – blah238
    Jan 9, 2013 at 6:03

3 Answers 3

2

Since you know how to iterate and find the features that you want to merge, I would build a function to do the merge which could probably be arcpy.Dissovle_management(). Do the dissolve and you can probably use the aggregating parameters to combine the attributes then delete those features from the original and update the data back with a insert cursor.

How do you identify which features need to get merged?

1
  • So far the best way to ID features to merge is to iterate through and do a select by location using the selected feature as the source and the layer as the target. Then, I use arcpy.getCount to see how many features are selected, if it's more than one, then I need to merge the attributes of all the selected features into one and delete the others.
    – Kevin
    Jan 9, 2013 at 15:09
2

I ended up solving this problem after I migrated to Postgres and had some more powerful tools at hand. My solution was to simply delete extra features with identical GEOM fields - leaving one of course, and then recalculate the values from the other data was collected during fieldwork. This gave me a dataset with no spatially coincident features and accurate totals in the attribute tables. The full PHP code that I used is below; I'm certain the same thing could be accomplished in Python but PHP was the easiest route for me at the time.

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html lang="en">
<head>
    <title>TRU Recalculation</title>
</head>

<body>
    <!-- Progress bar holder -->
    <div id="progress" style="width:500px;border:1px solid #ccc;"></div>
    <!-- Progress information -->
    <div id="information" style="width"></div>

    <?php
        $tot_deb = 0;
        $mfr_tool = 0;
        $tot_ltool = 0;
        $tot_gs = 0;
        $tot_cerl = 0;
        $tot_cern = 0;
        $tot_fcr = 0;
        $tot_pfeat = 0;
        $tot_hist = 0;
        $tot_hfeat = 0;
        $tot_art = 0;

        $dbconn = pg_connect("host=localhost port=54321 dbname=sixksurvey user=postgres password=password");

        $TRU_set = pg_query($dbconn, "select gid, east, north, tot_deb, mfr_tool, tot_ltool, tot_gs, tot_cerl, tot_cern, tot_fcr, tot_pfeat, tot_hist, tot_hfeat, comment, tot_art, surf_sed, visibility, hdop, sats, gps_unit, initials, rec_date from trutest_full order by north asc");

        $total = pg_num_rows($TRU_set);
        $i = 1; //Just a counter for the progress bar

        if (pg_num_rows($TRU_set) > 0)
        {
            while($current_TRU = pg_fetch_row($TRU_set))
            {

                if ($current_TRU) 
                {
                    // Calculate the percent
                    $percent = intval($i/$total * 100)."%";

                    // Javascript for updating the progress bar and information
                    echo '<script language="javascript">
                    document.getElementById("progress").innerHTML="<div style=\"width:'.$percent.';background-color:#2CA25F;\">&nbsp;</div>";
                    document.getElementById("information").innerHTML="'.$i.' TRU Cells Recalculated.";
                    </script>';

                    // Select all the LITHICS within the current TRU and count them up according to their types, then assign the new count to the relevant total variable.
                    $ALL_Lithics = pg_query($dbconn,"SELECT type, art_count FROM lithic join trutest_full ON ST_CONTAINS(trutest_full.geom, lithic.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_LITHIC = pg_fetch_row($ALL_Lithics))
                    {
                        //If statement for tot_deb
                        if ($current_LITHIC[0] == 'Angular Debris' or $current_LITHIC[0] == 'Biface Thinning Flake' or $current_LITHIC[0] == 'Hammer stone')
                        {
                            $tot_deb += $current_LITHIC[1];
                        }

                        //If statement for mfr_tool
                        if ($current_LITHIC[0] == 'Test Nod/Core' or $current_LITHIC[0] == 'Reduced Core' or $current_LITHIC[0] == 'Core Red. Flake')
                        {
                            $mfr_tool += $current_LITHIC[1];
                        }
                        //If statement for tot_ltool
                        if ($current_LITHIC[0] == 'Scraper' or $current_LITHIC[0] == 'Uniface' or $current_LITHIC[0] == 'Retouched Tool' or
                            $current_LITHIC[0] == 'Proj. Point' or $current_LITHIC[0] == 'Biface' or $current_LITHIC == 'Other')
                        {
                            $tot_ltool += $current_LITHIC[1];
                        }
                    }


                    // Select all the CERAMICS within the current TRU and count them up according to their types, then assign the new count to the relevant total variable.
                    $ALL_Ceramics = pg_query($dbconn,"SELECT type, art_count FROM ceramic JOIN trutest_full ON ST_CONTAINS(trutest_full.geom, ceramic.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_CERAMIC = pg_fetch_row($ALL_Ceramics))
                    {
                        // Calculate new total for Local Ceramics
                        if ($current_CERAMIC[0] == 'EP Brown' or $current_CERAMIC[0] == 'EP brownware' or $current_CERAMIC[0] == 'EP Poly' or $current_CERAMIC[0] == 'EP Decorated' or $current_CERAMIC[0] == 'EP UB' or $current_CERAMIC[0] == 'Jornada Brown' or $current_CERAMIC[0] == 'EP Bichrome')
                        {
                            $tot_cerl += $current_CERAMIC[1];
                        }

                        // Calculate new total for Non-Local Ceramics
                        else
                        {
                            $tot_cern += $current_CERAMIC[1];
                        }
                    }
                    // Select all the FCR within the current TRU and count them up according to their types, then assign the new count to the relevant total variable.
                    $ALL_fcr = pg_query($dbconn,"SELECT art_count FROM fcr JOIN trutest_full ON ST_CONTAINS(trutest_full.geom, fcr.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_FCR = pg_fetch_row($ALL_fcr))
                    {
                        $tot_fcr += $current_FCR[0];
                    }               

                    // Select all the FEATURES within the current TRU and count them up 
                    $ALL_features = pg_query($dbconn,"SELECT type FROM fcr JOIN trutest_full ON ST_CONTAINS(trutest_full.geom, fcr.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_Feat = pg_fetch_row($ALL_features))
                    {
                        // Test the type of the feature to identify the historic features (I started here because there are fewer types, this is faster).  Rather than try to count the rows,
                        // I just add 1 to each total for each feature that is being tested
                        if ($current_Feat[0] == 'Historic Artifact Conc.' or $current_Feat[0] == 'Historic Water Feature' or $current_Feat[0] == 'Historic Structure')
                        {
                            $tot_hfeat += 1;    
                        }
                        else
                        {
                            $tot_pfeat += 1;
                        }
                    }
                    // Select all the GS within the current TRU and count them up 
                    $ALL_gs = pg_query($dbconn,"SELECT art_count FROM gs JOIN trutest_full ON ST_CONTAINS(trutest_full.geom, gs.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_GS = pg_fetch_row($ALL_gs))
                    {
                        $tot_gs += $current_GS[0];
                    }   

                    // Select all the HISTORIC within the current TRU and count them up according to their types, then assign the new count to the relevant total variable.
                    $ALL_historic = pg_query($dbconn,"SELECT art_count FROM historic JOIN trutest_full ON ST_CONTAINS(trutest_full.geom, historic.geom) WHERE trutest_full.gid = " . $current_TRU[0]);
                    while($current_HISTORIC = pg_fetch_row($ALL_historic))
                    {
                        $tot_hist += $current_HISTORIC[0];
                    }   

                    // Count all the artifacts and assign to TOT_ART
                    $tot_art = $tot_deb + $mfr_tool + $tot_ltool + $tot_cerl + $tot_cern + $tot_fcr + $tot_hist + $tot_gs;

                    // Something here to merge initials/date recorded/surface/visibiilty/etc into the comments for merged cells
                    // This code isn't the place to do this...  //Not dealing with duplicates here, just every cell in the set...


                    // Send the updated counts back to the database.
                    $result = pg_query($dbconn,"UPDATE trutest_full SET tot_deb = " . $tot_deb . ", mfr_tool = " . $mfr_tool . ", tot_ltool = " . $tot_ltool . ", tot_gs = " . $tot_gs . ", tot_cerl = " . $tot_cerl . ", tot_cern = " . $tot_cern . ", tot_fcr = " . $tot_fcr . ", tot_pfeat = " . $tot_pfeat . ", tot_hist = " . $tot_hist . ", tot_hfeat = " . $tot_hfeat . ", tot_art = " . $tot_art . " WHERE trutest_full.gid = " . $current_TRU[0]);

                    // This is for the buffer achieve the minimum size in order to flush data
                    echo str_repeat(' ',1024*64);

                    // Send output to browser immediately
                    flush();

                    if (!$result)
                    {
                        echo 'Update Query Failed in TRU.gid = ' . $current_TRU[0];
                    }

                    // Zero out all the hoppers for the next go-round
                    $tot_deb = 0;
                    $mfr_tool = 0;
                    $tot_ltool = 0;
                    $tot_gs = 0;
                    $tot_cerl = 0;
                    $tot_cern = 0;
                    $tot_fcr = 0;
                    $tot_pfeat = 0;
                    $tot_hist = 0;
                    $tot_hfeat = 0;
                    $tot_art = 0;

                    $i += 1;
                }
            }
        }
        echo 'TRU Recalculate Done';
    ?>

</body>
</html>
0

Since you are only wanting to merge attributes for cells (actually polygons) that are identical then I would use Union and keep all the attributes. That way you can iterate through all resultant polygons (cells) and have access to both sets of original attributes to write whatever new values you want. If the same attribute names are used by both original feature classes you may need to do some renaming of fields before the Union so that both remain available.

Actually, a quick re-read of your question tells me that you have only one input feature class. Union may still be useful because it can run against a single feature class as described here.

2
  • I hadn't thought to use Union instead of Append when combining the data from each GPS unit... Doing that would eliminate the issue of spatial overlap and then I would just have to deal with the easier. Any thoughts on issues that may arise when union-ing upwards of 20 feature classes at a time?
    – Kevin
    Jan 9, 2013 at 16:09
  • Only that you probably need to have Advanced (ArcInfo) licensing if you want to do it in one go. With Basic and Standard you would need to do Unions in pairs and aggregate the results.
    – PolyGeo
    Jan 9, 2013 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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