Sorry, I could not reply soon or call due to my work constraints.
As suggested by many of you, I tried checking the depth of water by tying a stone to rope. Got to see wet stone after some depth. (around 400 ft)
To confirm the same and to check the water source, today I got the camera scan done. It showed the stored water at 370 ft. So totally 700 ft water was stored or approx 2500 ltrs.
When we scanned further, we could see water source at 800 ft, though there were small sources in between.
Now, we need to see the motor n pipe, and the cost for the same.
Lessons learnt:
1. Your geologist's prediction may go wrong in some cases, and you may not get water as he suggested. So its totally your luck.
2. If you do not get water, do not panic. Follow the methods mentioned above after some days. It may yield water.
3. Most important, dig a borewell only if its really required. If there is Cauvery connection already or if there are chances of getting the same in near future, please avoid digging borewell.
If you go beyond 800 ft, you may end up spending around 3 to 3.5 lakhs for borewell. (In my case, it will take nearly 2 years for getting cauvery water)
4. Summer is the best season to dig borewell, as we get to know the water source precisely.
5. Last but not the least, please pay proper attention to underground water recharge. That is the only way we can help our earth.