3D - data in a database
-
So I believe this is more of a question of theory than a programming question, so here it goes: How would you store three-dimensional data on a relational database? I've thought of these two options:
- store a 2-D table a BLOB and each row of the table being the third dimension
- use two tables, one with the second and third dimensions, where each column contains the ID of a row in another table, with the first dimension. The problem here is that the second (columns in first table) and first dimensions (columns in second table) would be fixed.
Does anyone here have better ideas? -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
So I believe this is more of a question of theory than a programming question, so here it goes: How would you store three-dimensional data on a relational database? I've thought of these two options:
- store a 2-D table a BLOB and each row of the table being the third dimension
- use two tables, one with the second and third dimensions, where each column contains the ID of a row in another table, with the first dimension. The problem here is that the second (columns in first table) and first dimensions (columns in second table) would be fixed.
Does anyone here have better ideas? -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
What is the data exactly? If it's points, then (x, y, z) tuples should work just fine? Then you can build more complex 3d structures based on the 3d tuples. Storing tables in BLOBs violates 1st normal form.. :rolleyes: -- Weiter, weiter, ins verderben. Wir müssen leben bis wir sterben.
-
So I believe this is more of a question of theory than a programming question, so here it goes: How would you store three-dimensional data on a relational database? I've thought of these two options:
- store a 2-D table a BLOB and each row of the table being the third dimension
- use two tables, one with the second and third dimensions, where each column contains the ID of a row in another table, with the first dimension. The problem here is that the second (columns in first table) and first dimensions (columns in second table) would be fixed.
Does anyone here have better ideas? -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
Quad trees are something that is often used in GIS (Geographical Information System) software to "index" data on a 2D map. The theory could be extended to "Octrees" (I don't know if that really exists, or if I just made up the word) for 3D space - although I have to admit to never having tried it. A quad tree splits your domain into four quadrants. An if necessary a quadrand can be split further and further until you get to your final leaf node. A quick calculation suggests you could "index" the entire world to about one metre accuracy in 26 levels. I would recon that in 43 layers you could cover everything from the surface of the Earth to the boundary of space (again to 1 metre accuracy), assuming that the Octree is a valid proposition.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
What is the data exactly? If it's points, then (x, y, z) tuples should work just fine? Then you can build more complex 3d structures based on the 3d tuples. Storing tables in BLOBs violates 1st normal form.. :rolleyes: -- Weiter, weiter, ins verderben. Wir müssen leben bis wir sterben.
Jörgen Sigvardsson wrote: What is the data exactly? It's a price table for windows. Width x Height makes the price for a window, and the third dimension is the region... diferent prices for each city. So, in this case, the width and height are fixed (say from 0.5 meter to 3 meters in 0.1 increments), but the regions at first are only two, but might later grow. For this kind of that, my idea of two tables may work very good. Only with two queries I can get the exact price for any given region, and window size. And this can be easily written in a quick stored proc. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
Quad trees are something that is often used in GIS (Geographical Information System) software to "index" data on a 2D map. The theory could be extended to "Octrees" (I don't know if that really exists, or if I just made up the word) for 3D space - although I have to admit to never having tried it. A quad tree splits your domain into four quadrants. An if necessary a quadrand can be split further and further until you get to your final leaf node. A quick calculation suggests you could "index" the entire world to about one metre accuracy in 26 levels. I would recon that in 43 layers you could cover everything from the surface of the Earth to the boundary of space (again to 1 metre accuracy), assuming that the Octree is a valid proposition.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
That sounds very interesting but I think it's way too much for what I'm trying to achieve. But I'll look into it anyway. Do you have any good webpage where I can read about it? -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
So I believe this is more of a question of theory than a programming question, so here it goes: How would you store three-dimensional data on a relational database? I've thought of these two options:
- store a 2-D table a BLOB and each row of the table being the third dimension
- use two tables, one with the second and third dimensions, where each column contains the ID of a row in another table, with the first dimension. The problem here is that the second (columns in first table) and first dimensions (columns in second table) would be fixed.
Does anyone here have better ideas? -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
Reading further details[^], I believe that the solution is actually simple. Notice how your problem statement almost sets the solution for your problem. Simply create a table with the columns Width, Height, ID_Region, Price. Then, another table with ID_Region. If you want to normalize it further, store Window and Height in another table and store (Size) only an ID_Size in your price table. Did you see? The database is actually very simple. But the UI will take your sleep away... :-D Yes, even I am blogging now!
-
Quad trees are something that is often used in GIS (Geographical Information System) software to "index" data on a 2D map. The theory could be extended to "Octrees" (I don't know if that really exists, or if I just made up the word) for 3D space - although I have to admit to never having tried it. A quad tree splits your domain into four quadrants. An if necessary a quadrand can be split further and further until you get to your final leaf node. A quick calculation suggests you could "index" the entire world to about one metre accuracy in 26 levels. I would recon that in 43 layers you could cover everything from the surface of the Earth to the boundary of space (again to 1 metre accuracy), assuming that the Octree is a valid proposition.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
Octrees do exist. I used to use them in ray-tracing to divide up a scene and calculate which objects are in which node of the Octree. This was used to reduce the number of ray-object intersections to test. Mike
-
Reading further details[^], I believe that the solution is actually simple. Notice how your problem statement almost sets the solution for your problem. Simply create a table with the columns Width, Height, ID_Region, Price. Then, another table with ID_Region. If you want to normalize it further, store Window and Height in another table and store (Size) only an ID_Size in your price table. Did you see? The database is actually very simple. But the UI will take your sleep away... :-D Yes, even I am blogging now!
Daniel Turini wrote: Simply create a table with the columns Width, Height, ID_Region, Price. Then, another table with ID_Region. Well, I think I was just trying to make things overly complicated. Thanks! Then I can get the price in just one query! Daniel Turini wrote: But the UI will take your sleep away... This is for a web app, but I think I'll write a little WinForms app to intially capture this. I don't think it will be that hard, with a grid of
TextBoxcontrols and someforloops. Also, as of the initial specification, the number of regions are fixed too (only two), but might change in the future. And the prices won't change. I think that will come on a later version. But still, I don't think that'll be too hard. Thanks!! -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
Jörgen Sigvardsson wrote: What is the data exactly? It's a price table for windows. Width x Height makes the price for a window, and the third dimension is the region... diferent prices for each city. So, in this case, the width and height are fixed (say from 0.5 meter to 3 meters in 0.1 increments), but the regions at first are only two, but might later grow. For this kind of that, my idea of two tables may work very good. Only with two queries I can get the exact price for any given region, and window size. And this can be easily written in a quick stored proc. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
I wouldn't consider this to be "3D" data, certainly not as far as GIS systems go. It seems more like just multiple attributes, even though there are 3 of interest (width, height, region), but also price. I wouldn't complicate your understanding by thinking of this as "3D". Dave "You can say that again." -- Dept. of Redundancy Dept.
-
Quad trees are something that is often used in GIS (Geographical Information System) software to "index" data on a 2D map. The theory could be extended to "Octrees" (I don't know if that really exists, or if I just made up the word) for 3D space - although I have to admit to never having tried it. A quad tree splits your domain into four quadrants. An if necessary a quadrand can be split further and further until you get to your final leaf node. A quick calculation suggests you could "index" the entire world to about one metre accuracy in 26 levels. I would recon that in 43 layers you could cover everything from the surface of the Earth to the boundary of space (again to 1 metre accuracy), assuming that the Octree is a valid proposition.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
yes, octrees really exist. They are quite handy as a matter of fact. Quadtrees represent 2D surface data, and although it can hold a 3rd dimension of height, it is strictly a 2D skin over a 3D object. Octrees organize data in quads with possible additions of height objects. ATI did a wonderful demonstration of a simple octree with full source. And per the example, it had some of the impossible objects for quad-trees, arches and caves. and you really do not want to get me talking in my field of work... really... I babble.. honest. :laugh: _________________________ Asu no koto o ieba, tenjo de nezumi ga warau. Talk about things of tomorrow and the mice in the ceiling laugh. (Japanese Proverb)