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;\"> </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>
