5

Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason.

Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

In OOP, we usually create objects to handle special primitive types like dates, money, weights, and dimensions. But unlike the other primitives, you can't simply add dimensions together and stuff them in a decimal column and hope to get them back out again.

You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

So I'm looking into the spatial data types which seem designed for map making, but it occurs to me that a data type designed to hold coordinates in 3d space would probably work just as well for defining 3d objects since it would still be a collection of x, y and z treated as a cohesive unit in one field with the ability to query coordinates separately.

Unfortunately, I'm having a hard time finding good information on using spatial types and I'm not sure if they can be used in this manner.

Can spatial types be used to store the dimensions of 3 dimensional objects?

4

Can spatial types be used to store the dimensions of 3-dimensional objects?

Yes, of course ST_MakePoint takes a Z cord (and even an M cord, hello time!)

Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason. [...] Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

Dollars are a unique function of cents; pounds a unique function of ounces (x,y,z) are nothing like that. They're three coordinates for discrete spatial dimensions.

You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

There is no point in reinventing the wheel. You could store them a lot of ways. They tend to be stored something ISO/IEC 13249-3:2011, detailed in PostGIS.

Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

The conventional approach to store (x,y,z) is with a spatial 3d type.

  • PostGIS documentation is a bit lacking. Where can one find what types are supported and what functions on them? (eg. can I store 42-d objects, etc?) – ypercubeᵀᴹ Apr 4 '17 at 17:31
  • No you can only 4d in PostGIS, and I would suggest keeping it 3d. However, you can store another dimensionality in the m coordinate. Docs are on postgis.net, as always with docs ymmv. I find them to be pretty good but certainly not all questions are answered, that's why dba.se exists. – Evan Carroll Apr 4 '17 at 17:38
2

Dollars and cents are just different units for the same amount. Whether a charge is ten dollars or one thousand cents, it's the same cost. In the same way the distance from your house to the beach is the same irrespective of being measured in miles, kilometres or stadia. Changing the dollar-number must necessarily change the cent-number.

Dimensions have the property of orthogonality. A measure in one dimension can change without affecting the measure in the other dimensions. One can move East or West without altering how far North you are. The latitude and longitude are independent variables.

So yes, if your problem domain has the corresponding number of independent variables, and they are of the same primative type that your GIS software supports (float?), then you can use that software's capabilities to process your problem.

Be careful of the geometry the software implements, however. Walking in a straight line on a Euclidian plane produces a very different result to walking on a straight line on the surface of a sphere!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.