1,000 missing survey data points to be fixed by interpolation

K

kashifjillani

New Member
Joined
Jul 26, 2011
Messages
47
Hello experts,

Tried all methods without success

I have large data of around 10,000 drainage pipe segments and 10,000 junctions in ArcGIS layer

The problem is: due to some reason certain junctions could not be accessed for survey, thus 1,000+ entries are missing for pipes (up stream and d/s invert elevation) and junctions' surface elevation

Logically, interpolation should work here. (based on slope, u/s tag invert level or two junction u/s tag invert level or likewise)

Tried to do it manually in excel, but quite tiring and with less confidence (unprofessional too)

Any clue how to fix it?



e.g.
<table border="0" cellpadding="0" cellspacing="0" width="531"><tbody><tr style="height:12.75pt" height="17"><td style="height:12.75pt;width:48pt" height="17" width="64"> <table border="0" cellpadding="0" cellspacing="0" width="960"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3291; width:68pt" span="2" width="90"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2560; width:53pt" span="2" width="70"> <col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" height="17" width="64">ASSETID</td> <td class="xl24" style="width:53pt" width="70">LENGTH</td> <td class="xl24" style="width:68pt" width="90">US_INVERT</td> <td class="xl24" style="width:68pt" width="90">DS_INVERT</td> <td class="xl24" style="width:58pt" width="77">PIPE_DIA</td> <td class="xl24" style="width:53pt" width="70">US_TAG</td> <td class="xl24" style="width:53pt" width="70">DS_TAG</td> <td style="width:48pt" width="64">TYPEID</td> <td style="width:48pt" width="64">PIPEMATERI</td> <td style="width:74pt" width="99">US_PIT_NAM</td> <td style="width:48pt" width="64">TYPEDESCRI</td> <td style="width:104pt" width="138">ASSETDESCR</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196934</td> <td align="right">11.13</td> <td align="right">37.46</td> <td align="right">37.34</td> <td align="right">300</td> <td align="right">7798</td> <td align="right">7797</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">17</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7798 - 7797</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196935</td> <td align="right">5.29</td> <td align="right">37.34</td> <td align="right">37.32</td> <td align="right">300</td> <td align="right">7797</td> <td align="right">7722</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7797 - 7722</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196936</td> <td align="right">12.95</td> <td align="right">40.25</td> <td align="right">40.2</td> <td align="right">300</td> <td align="right">7642</td> <td align="right">7641</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7642 - 7641</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196937</td> <td align="right">25.93</td> <td align="right">39.61</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7821</td> <td align="right">7846</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>D</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7821 - 7846</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196938</td> <td align="right">10.45</td> <td align="right">40.61</td> <td align="right">40.46</td> <td align="right">375</td> <td align="right">7783</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">22</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7783 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196939</td> <td align="right">76.51</td> <td align="right">0</td> <td align="right">40.42</td> <td align="right">375</td> <td align="right">7772</td> <td align="right">7771</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">2</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7772 - 7771</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196940</td> <td align="right">8.26</td> <td align="right">39.88</td> <td align="right">39.84</td> <td align="right">300</td> <td align="right">7735</td> <td align="right">7734</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7735 - 7734</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196941</td> <td align="right">10.11</td> <td align="right">49.48</td> <td align="right">49.42</td> <td align="right">300</td> <td align="right">8023</td> <td align="right">8022</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td>SE</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 8023 - 8022</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196942</td> <td align="right">7.41</td> <td align="right">41.56</td> <td align="right">41.47</td> <td align="right">300</td> <td align="right">7710</td> <td align="right">7709</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">16</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7710 - 7709</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196943</td> <td align="right">9.54</td> <td align="right">0</td> <td align="right">40.62</td> <td align="right">300</td> <td align="right">7659</td> <td align="right">7658</td> <td align="right">1505</td> <td>RCP Class (2)</td> <td align="right">40</td> <td>RCP Class (2) 300 mm diameter</td> <td>Pipe - 7659 - 7658</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196944</td> <td align="right">28.87</td> <td align="right">46.64</td> <td align="right">0</td> <td align="right">450</td> <td align="right">70108</td> <td align="right">8004</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">28</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 70108 - 8004</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196945</td> <td align="right">37.83</td> <td align="right">40.96</td> <td align="right">40.66</td> <td align="right">375</td> <td align="right">7912</td> <td align="right">7911</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">11</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7912 - 7911</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196946</td> <td align="right">21.53</td> <td align="right">40.28</td> <td align="right">0</td> <td align="right">375</td> <td align="right">7645</td> <td align="right">7644</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">23</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7645 - 7644</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196947</td> <td align="right">48.5</td> <td align="right">38.99</td> <td align="right">38.3</td> <td align="right">450</td> <td align="right">7907</td> <td align="right">7906</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">6</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7907 - 7906</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196948</td> <td align="right">80.65</td> <td align="right">0</td> <td align="right">39.88</td> <td align="right">375</td> <td align="right">7823</td> <td align="right">7822</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td>F</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7823 - 7822</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196949</td> <td align="right">37.62</td> <td align="right">39.19</td> <td align="right">0</td> <td align="right">450</td> <td align="right">7633</td> <td align="right">7632</td> <td align="right">1507</td> <td>RCP Class (2)</td> <td align="right">8</td> <td>RCP Class (2) 450 mm diameter</td> <td>Pipe - 7633 - 7632</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" height="17" align="right">196950</td> <td align="right">18.29</td> <td align="right">0</td> <td align="right">40.28</td> <td align="right">375</td> <td align="right">7881</td> <td align="right">7880</td> <td align="right">1506</td> <td>RCP Class (2)</td> <td align="right">5</td> <td>RCP Class (2) 375 mm diameter</td> <td>Pipe - 7881 - 7880</td> </tr> </tbody></table></td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:68pt" width="90">
</td><td class="xl22" style="width:58pt" width="77">
</td><td class="xl22" style="width:53pt" width="70">
</td><td class="xl22" style="width:53pt" width="70">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height:12.75pt" height="17"><td style="height:12.75pt" height="17" align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr></tbody></table>

Can send the table if required

Thanks in advance,

regards,

Kashif
:confused:
 
Sort by date Sort by votes
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Tweedle,

As you can see the volume of data we are dealing with, it is really not possible to check for consistency

However, we can fix some data in first run (50% of US_INVERT and DS/INVERT) is fixed.

Then, copy and paste values without formulae

Again, insert the formulae for missing values and see how it behaves.

To reply your previous post: US_TAG is not elevation value, it is just name of junction (manhole); the same junction tag can be upstream to one(s) and downstream to other(s).

The US_INVERT is the elevation which is available for some junctions and not available for some others.

Hope it answered the question, attaching the link to illustration (a picture worth a thousand words)


http://www.mediafire.com/?xli2xcb2a2fqz0v



Thanks Tweedle for following


P.S. If you can understand the problem from the data, illustration and posts. You are welcome to try an approach of your choice.


Regards,

Kashif
 
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hello Diddi,

Answering your questions one by one.

Since we are considering vector values as scaler, it is obvious the results would be not accurate – I would compare later the results (calculated) to actual ones to see if the results are under the tolerance.

The distances are available with every asset under the Length column.

We would prefer to interpolate just one point between two known points, as mentioned in the example. For the first run, after which number of unknown points would decrease, and we run the command another time, ….. until we fix all unknown points.

I think just interpolation would work here. But, if you understand the problem, and think extrapolation (I am not familiar to it) can also be done then no problem.

Certainly, these drainage pipes are parts of series (there are several networks)

Yes, the end user is aware of missing data, and the method would also be discussed along with accuracy analysis.

Please find the illustration on this link


<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
http://www.mediafire.com/?xli2xcb2a2fqz0v


Thanks Diddi for following

P.S. If you can understand the problem from the data, illustration and posts. You are welcome to try an approach of your choice.
 
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hello Shawnhet,

The smiley you use, keeps me in comfort,

Thanks for the expression, but I couldn’t run it, in the cell in question.

If you could please, copy this formula in your spread sheet (in the cell where Asset ID = 196939 and US_INVERT = 0) does it give the result 42.18 as described in the method earlier.

Please keep in mind, the formula is supposed to find the upstream asset by its own (based on US_TAG or DS_TAG of asset in question, to make link of two known tag elevations around one unknown tag elevation)

If we can calculate US_INVERT column, then DS_INVERT would not be a problem (or vice versa)

Attached an illustration to further clear the concept
http://www.mediafire.com/?xli2xcb2a2fqz0v


Thanks Shawnhet for following

P.S. If you can understand the problem from the data, illustration and posts. You are welcome to try an approach of your choice.

Regards,
 
Upvote 0
i need to see some real data to be able to crunch this properly. the proplem is purely 2 dimansional? only displacement from a pit and elevation.

assumptions: the pits are colinear
 
Upvote 0
diddi said:
i need to see some real data to be able to crunch this properly. the proplem is purely 2 dimansional? only displacement from a pit and elevation.

assumptions: the pits are colinear
Click to expand...

Hello Diddi,

The excel file can be downloaded at

http://www.mediafire.com/?uniy9cic1gvxytp

The actual file is in shp format (arcgis) from where I have copied it in excel

Note: you would observe duplicates under us_tag and ds_tag

Yes, we are dealing with horizontal distances and elevation (2D) only.


Hope it would help,
 
Upvote 0
Hi Kashif;

I took a stab at a vba solution and calculated 215 USINVERT points.
Care to spot check it?

ThatAssetID is the asset used in calculations
ThatLength & ThatUSInvert are the metrics for that asset.
CALC is a formula I added as an audit mechanism
Comparable Assets are the other assets that 'may have matched'
DSTAG = USTAG
{you should be able to copy it from here and paste to Excel.}
Excel Workbook
BCDEFGIJKMO
7ASSETIDLENGTHUS_INVERTDS_INVERTUS_TAGDS_TAGThatAssetIDThatLengthThatUSInvertCALCComparable Assets
1319693976.5142.1840.427772777119704834.7742.9842.18196939, 197048, 197143, 197234
166719861248.3937.3936.496175617419862462.8938.5737.39198612, 198624, 200106, 260970
271119965833.1619.2519.221929192819989244.3619.2819.25199658, 199892, 200118, 200195
300419995134.8220.2620.184384438320002621.8320.3120.26199951, 200026, 200220, 200316
305019999711.8321.0420.9830931822620010823.0621.1521.04199997, 200108, 200267, 200524
31472000949.1122.4922.392960295919993010.9522.622.49199926, 199930, 200094, 200122
517820572190.8550.6650.02203772037620571448.8951.0150.66205570, 205714, 205721, 205767
63672159367.8123.0522.95447944782158127.7823.1523.05215769, 215812, 215860, 215936
734021734943.613.9113.5142843221728550.7514.3813.91217218, 217285, 217349, 217471
777321949040.9217.3216.478261826021948315.4117.6417.32219312, 219483, 219490, 263359
Sheet
 
Upvote 0
HMMM IT SEEMS TO BE CLOSE, TWEEDLE,

I WORKED ON ROW 1667 AND FOUND THAT:

ASSET 198624 IS AT UPSTREAM AND 198612 IS AT DOWNSTREAM, AND THE POINT WHERE THEY ARE JOINED WAS NOT ACCESSED.


BY MANUAL CALCULATIONS:
BASE OF TRIANGLE = 62.89+48.39 = 111.28
PERPENDICULAR = 2.08
ANGLE = 1.0708 (DEGREES)

SO THE POINT IN QUESTION IS 48.39m UPSTREAM OF +36.49 AND 62.89m DOWNSTREAM OF +38.57

BY FURTHER CALCS: IT COMES UP AS +37.66

UNKNOWN OF FORMULA USED BY YOU, I THINK, THATLENGTH SHOULD BE 48.39m and ANGLE SHOULD BE USED (ALREADY OBTAINED FROM THATUPSTREAM AND DOWNSTREAM)

THATASSETID SHOULD ALSO BE 198612 ITSELF, TO OBTAIN UPSTREAM ELEVATION

IN CALC COMPARABLE ASSETS

ASSETID 200106 SHOULD BE TAKEN AS ITS USTAG OR DSTAG ARE NOT RELEVANT TO TAGS IN QUESTION.

HOPE THE DUST IS SETTLING DOWN NOW, AND WE CAN SEE THE CLEAR PICTURE.

REGARDS,
 
Upvote 0
kashifjillani said:
BY MANUAL CALCULATIONS:
BASE OF TRIANGLE = 62.89+48.39 = 111.28
PERPENDICULAR = 2.08
ANGLE = 1.0708 (DEGREES)

SO THE POINT IN QUESTION IS 48.39m UPSTREAM OF +36.49 AND 62.89m DOWNSTREAM OF +38.57

BY FURTHER CALCS: IT COMES UP AS +37.66
Click to expand...
I really don't think you need to use the mathematics of angles/tan/cosine whatever; the arithmetic of ratios will do instead; same values as above:
Excel Workbook
A
137.666
Sheet


If you have a height difference of 1 metre and the junction is halfway, you know to add half that metre; if it was three quarters the way along, you'd know to add 0.75 metres etc. unless I've missed something?


On another matter, you say in your Illustration.jpg that "The tag 7772 could be USTag to several assets <snip> < snip > We can assume any one result as correct". So if you have:
Excel Workbook
BCDEFG
2ASSETIDLENGTHUS_INVERTDS_INVERTUS_TAGDS_TAG
784021956216.300831319495
Sheet
and I wanted to know the DS_Invert of DS_Tag 19495,

if there was another row which contained this data (there is):
Excel Workbook
BCDEFG
2ASSETIDLENGTHUS_INVERTDS_INVERTUS_TAGDS_TAG
78502195724.7914.0213.97833419495
Sheet


Can I substitute and put 13.97 in the first row?
</snip>
 
Last edited:
Upvote 0
p45cal said:
I really don't think you need to use the mathematics of angles/tan/cosine whatever; the arithmetic of ratios will do instead; same values as above:
Excel Workbook
A
137.666
Sheet


If you have a height difference of 1 metre and the junction is halfway, you know to add half that metre; if it was three quarters the way along, you'd know to add 0.75 metres etc. unless I've missed something?


On another matter, you say in your Illustration.jpg that "The tag 7772 could be USTag to several assets <snip> < snip > We can assume any one result as correct". So if you have:
Excel Workbook
BCDEFG
2ASSETIDLENGTHUS_INVERTDS_INVERTUS_TAGDS_TAG
784021956216.300831319495
Sheet
and I wanted to know the DS_Invert of DS_Tag 19495,

if there was another row which contained this data (there is):
Excel Workbook
BCDEFG
2ASSETIDLENGTHUS_INVERTDS_INVERTUS_TAGDS_TAG
78502195724.7914.0213.97833419495
Sheet


Can I substitute and put 13.97 in the first row?
</snip>
Click to expand...
Hi p45cal,

Sorry couldnt find your name,

Thanks for your effort,

You are right, in my previous post it was stated that both ways are valid (either by trigonometry or arithmetic as you said)

Yes, we can put value of DS-TAG of AssetID 7850 ( 13.97 ) straightaway, because the TAGs are same.

However, it is not the typical case, if a tag (manhole) was not accessed, we wouldnt have any value. May be something special about this case (other pipe fully immersed that couldnt be surveyed). BUT, yet we can take this value anyway.

The main challenge is automation of these calculations, as you can see from the size of data, manual calculations for each are next to impossible!

ANOTHER, WAY I CAN SEE IS (just clicked):

I HAVE GOT GROUND SURFACE ELEVATION OF EACH TAG,

IF WE CAN DRAW GRAPHS OF CONNECTING TAGS (SURFACE ELEVATIONS AND PIPE INVERTS). THEN, FROM THE GRAPH WE CAN DRAG THE VALUES TO MATCH THE UPSTREAM AND DWONSTREAM TREND.

SO, WE NEED TO FIND THE ASSETS AND ARRANGE IN AN ORDER, US_TAG DS_TAG OF ONE ASSET THEN US_TAG DS_TAG OF NEXT AND SO ON UNTIL WE FIND NO DS_TAG VALUE AS MAIN/MW/OPEN DRAIN/OUTLET OR SOMETHING

THEN WE CAN FIX ALL THE 0 VALUES.

IT MIGHT NEED 20+ GRAPHS WITH TWO LINES IN EACH, (SURFACE ELEVATION LINE AND US_INVERT OR DS_INVERT)

TAGS AGAINST SURFACE ELEVATION VALUES ARE UPLOADED HERE

http://www.mediafire.com/?vus1lw0rktjonja

Note: The whole drainage network is not just one interconnected network but there are 20+ standalone networks.



I HOPE YOU GET THE IDEA.

THANKS AGAIN, AND I REALLY ACKNOWLEDGE YOUR EFFORT :-)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
951
smozgur
smozgur
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
408
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
B
  • Question Question
Webscrapping Loop to get table data
Replies
1
Views
723
John_w
J
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top