Hello experts,
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)
ASSETID LENGTH
US_INVERT DS_INVERT US_TAG DS_TAG
*196939 76.51 0 40.42 7772 7771
*197048 34.77
42.98 0 7773 7772
196938 10.45 40.61 40.46 7783
7771
196939 76.51 0 40.42 7772 7771
196940
8.26 39.88 39.84 7735 7734
Drainage pipes data
Above,
I have put * to just two rows which are involved in calculations out of the whole data from 10,062 rows (Row 3 to Row 10,064)
1. To calculate US_INVER (US_TAG) elevation we need to find an asset where 7772 is under column heading
DS_TAG
2. That is ASSETID 197048
3. After finding it (through some function/formula), we have got on US_INV and one DS_INV
4. Now we want to know elevation of a point in between two known elevations: which are +42.98 and +40.42 and total
distance b/w these two points is length of Asset ID 196939(76.51m) and 197048(34.77m)=111.28m
5. The point is located 34.77m downstream of +42.98 and 76.51m upstream from of +40.42
6. We have just created a right angle triangle where Perpendicular is 42.98-40.42=2.56m and Base is
34.77+76.51=111.28
7. To calculate the angle of drainage pipe tan Ñ² = 2.56/111.28, Ñ²=1.317
8. After knowing the angle of pipe we can easily know the elevation of any point along the hypotenuse
9. So, US_INVERT (US_TAG) = 42.98 - tan (1.317) * 34.77 = +42.18
Alternatively
1. After step 5. we have another choice; by dividing value of perpendicular with base and multiply with distance
from upstream and subtracting from upstream elevation i.e. 42.98 - (2.56/111.28)*34.77 = +42.18 again
This is the
value we require at US_INVER (US_TAG) of Asset ID 196939
And
DS_INVERT (DS_TAG) of ASSETID 197048
Also, something similar would be require to fill in the missing data under column DS_INVERT
There are
around 1000 missing values, if we know the formula we Ctrl+H (find and replace “0”) to save time and effort
Also,
sometime one asset upstream we wont get any value (“0”again), in that case we can go two steps up (but, if it is not possible
we can leave it for manual calculation)
The main challenge here, in my understanding is finding a function which
could find US_TAG value of row (in question) in a DS_TAG column and start calculating by taking values from that row and row
in question.
Note: the DS_TAG value could be found in multiple rows, in that case priority should be given to a
row with least value in LENGTH column. (again if it is difficult, any of row could be taken)
It seems we are
pushing MSEXCEL to its limits from where C++ programming starts,
The complete file in excel format is uploaded at
http://www.mediafire.com/?uniy9cic1gvxytp
http://www.mediafire.com/?uniy9cic1gvxytp
Thanks
for your time reading and efforts to solve it
Regards,
Kashif