Help to design a database for topography

[Mihail]

Hi !

Some generalities about topography
The topographic points are represented in 3 coordinates:
X - to the North
Y - to the Est
Z - altitude

Zero angle is to the North and is increased in the clock wise.

The trigonometric circle has 400 grades (not only 360 like in math).

There are predefined points (the X, Y, Z coordinates are known).
Here is OK: this points can be stored in a table (table PredefPoints).

Based on this points and some measurements (angles & distances) is possible to calculate new points.

And here is where I am in trouble:
1)
Based on this calculated points and other measurements will be calculate other points and so on. I can't know how many intermediate points will be calculate until the last point (the purpose point) will be calculate.
2)
The mathematical method is not the same for each case.

While to implement math is an easy problem, to design the database is a big one because, as I see now, I must store the calculated points in a table in order to refer again in the next iteration.
In order to make things even harder to accomplish after this points are calculated once is very probable to iterate the calculations, making small changes in measurements values, in order to eliminate the measurements errors.
But I need to store at least the original values for measurements (if not the new values and the new values again and again) in order to restart iterations after a human error.

Also is needed to store even the mathematical method used to calculate each point.

More than I need a report to show how each point has been calculated: Base points, measurements, mathematical method =>, New Point.

Some ideas on how to design a database to reach this ?

Thank you !

[twinnyfo]

Mihail,

You mention in point 2) above, that the mathematical method for calculating is not always the same. One key question is how many different methods of calculation are there? Two? Three? Twenty-Four? If there are only a few methods for calculation, I would begin by establishing public functions in a module. The variables required for these calculations could easily be sent from the form on which the data points will reside to calculate new data.

With each set of points, within the table itself, you could have a separate field that identifies with method of calculaiton is required, then, depending on that value, the form would send the data to the public function(s) as required.

I am not sure about all the details of your calculations, but it appears to me that you begin with several points, calculate new ones and then calculate additional ones based on these new points? This sounds almost like a self-replicating database that would quickly become very large, depending on the number of iterations you send the data through.

This might be a start, but I am having problems envisioning exactly how your database will work. That is a weakness on my part right now....

Please let me know if there is additional information you can provide.

Thanks!
twinnyfo

[dsatino]

I'm not sure I'm following exactly what you want, but the table structure and relationships in the picture should steer you in the right direction. Further explanation is below:

PrimaryPoint
PointID is an autonumber field
The x,y,z coord fields are a unique composite key

Iteration
IterationID is an autonumber field
The Iteration and PointID fields are a unique composite key

CalcPoints
CalcPointID is an autonumber field



This setup assumes that iterations are at the PrimaryPoint level and that each iteration will produce any and all CalcPoints.

You will need to programmitically determine the iteration number for each PrimaryPoint.

[Mihail]

Thank you, both, for your time and good ideas.

@twinnyfo
There are only few methods of calculating.
So, seems to be Ok to design public functions for that.

Yes. I start with some points (say P0 points) then, based on this points I calculate new points (say P1 points) using different methods.

From here start my real troubles:
-First because for all P1 points is necessary to store the method I use to calculate this points. Ok. I have an idea how to do that.

-Second because at a later time (say tomorrow) I use the old points (Po) as well as the new points (P1) to calculate other new points (P2).
So, at this time, P0 = P0 + P1. This is what I don't know (anyway... I have not a good idea) how to implement.

@dsatino
Thank you too for the answer.
I use iterations in order to calculate, very exact, the new points not for generate this points. I know: my English is not very good.

To explain better:
I am somewhere on the map, in P(Xp=?, Yp=?); I use only 2 coordinates because I can explain more easy.
If I know three "old" points A(Xa,Ya) , B(Xb,Yb) , C(Xc,Yc) and the distances between P and this points PA, PB, and PC then I can calculate Xp and Yp.
But any measurement has errors. So I need to know (at least) one more point D(Xd,Yd) and the distance PD (of course, PD has errors too).

Here is the loop (the iteration)
After I calculate P, I verify the distance from my P to D. Of course this is not equal with PD I measure. So I need to adjust a little bit PA, PB, PC and PD (all of them) then I recalculate P(Xp=?, Yp=?)
And I do this again and again until the error is less than... a value.

After all that I can say that I know the point P.
From now to ever I can use P as well as A, B, C or D to calculate other points. Next time is no need to calculate again P.
But this approach seems to broke the rules of normalization ?!?!?
It is why I am in doubt:
I store or not this points in order to use they next time ?
If i don't store this points (because I can calculate any time) how I can refer one of them when I need ?

And is very possible that in the future someone ask me how I calculate P. So I need to know somehow that I use A,B,C and D as fixed (known) points and PA, PB, PC, PD as measured distances.

Thank you for yours patience with my poor English !

[twinnyfo]

Mihail,

I think dsatino is on the right track. Every time you calculate a point you save it to the CalcPoints table, but you always know how you got there with the iteration table. Either way, because we are talking about points being recalculated often, I can see the tables getting huge pretty quickly, because it looks like the calculated points may be refined again and again over time?

I would be interested to see the final product....

[Mihail]

Indeed. In theory. In real life I know (maybe) 200 points then I calculate 100 - 200 new points per a year. It is equal to zero for Access.
I think that is no problem for a database for all my country (or for a much bigger country like USA) and for all employes which determine new points... and have my productivity :).

Thank you again for your help !