6

In the context of houses, should area be stored as square feet or square meters?

The area of the largest houses in existence are greater than 32000 sqft, so a 4-byte integer may be required to store square feet. For this reason, I'm favouring square meters at the moment.

Are there any other considerations that are not immediately obvious?

| improve this question | | | | |
  • 3
    A four byte integer can store values up to about 2 billion (if it's a signed integer), which is plenty large for either measurement. Which country are you building houses in? Use the unit of measurement that is most commonly used in that country. If you're building houses in more than one country, store both the value and the units (or simply standardize on meters), and let the program decide which to convert to after the fact. – Robert Harvey Nov 21 '16 at 2:09
  • 5
    It's almost always better to standardize on a 4-byte integer for any whole number you want to capture. Computers have plenty of memory nowadays; standardizing on smaller integers just makes life more difficult. – Robert Harvey Nov 21 '16 at 2:20
  • 6
    Worrying about two bytes per entry is way premature. There are roughly 133 million homes in the U.S. Is 266 MB going to blow up your DB? No. – user949300 Nov 21 '16 at 6:40
  • 2
    One square meter is approximately eleven square feet. Can your application tolerate that rounding? If you want to use integers, then your area units have to be small enough to represent your required measurement accuracy. – John Forkosh Nov 21 '16 at 9:22
  • 2
    If everybody in the world had a house stored in your database, saving the two bytes only saves about 14GB; or less than $5 worth of SSD space. – RemcoGerlich Nov 21 '16 at 12:02
4

If your app will potentially be used outside the USA, Myanmar or Liberia, or will it interoperate with other systems, I'd suggest you use the more widely accepted measurement system to store the data and then convert it to whatever local mesurement system the user wants to display. Obviously the columns shouldn't be integers but real.

The metric system is widely accepted in science and enginneering for information exchange:

enter image description here Source: Wikipedia.

So my suggestion is that you store it in the metric system (square meters for area) and then convert appropiatelly to present it in whatever unit the user prefers (preference settings).

In the other hand, if your app will only be used in the USA, Myanmar or Liberia, store the areas in squared feet.

| improve this answer | | | | |
  • 1
    pedantic note: square feet will work in US and most likely work in Liberia but myanmar probably have no idea what it amounts to as they have their own system of measurement (en.wikipedia.org/wiki/Myanmar_units_of_measurement). That said, many places, even if officially on metric, still use the older system. Canada for example will more often display area for a house in square feet rather than square meters. – Newtopian Nov 21 '16 at 15:52
  • +1 on standardising to a single unit and convering as needed. Though to reduce rounding error you could store it as square cm 4 bytes should still be plenty, that is unless one plans to store data for buildings such as the Pentagon or the Boeing Everett Facility might test the limits. – Newtopian Nov 21 '16 at 15:56
4

Square metres stored as an integer may not have enough resolution to capture what you need to know. Converting to and from square feet or metres will lose information. Use floating point values to prevent this issue.

Use whichever unit your primary users are likely to want for storage. If you are only targeting a US/UK market (where square feet is the customary measurement), store that. Most of the rest of the world prefers metric, however, so for an international market prefer square metres.

| improve this answer | | | | |
  • 2
    Obligatory: 'When confronted with a problem, some programmers think "Oh, I know - I'll use floating point numbers!". Now they have 1.9999999997 problems.' Good point on the required resolution and information loss during conversions. – Mael Nov 21 '16 at 16:48
3

Neither.

Since you are asking this question, you are probably working on a system which uses both (or a system which will be used in different countries). This means that you should be able to store distances expressed in feet or meters.

In your database, do have not one, but two columns: one for the value, the other one for the unit. This would make it straightforward to store and retrieve the measurements, without doing the conversion under the hood (which is usually not what the user wants). Double conversion is especially problematic (for instance meters to feet to meters), since rounding errors will accumulate.

Note that you will probably need to support even more units than that. In the context of houses, I imagine that meters are not very precise: rooms will rather be measured in centimeters, if not millimeters (for electrical installations, it may matter). This makes this approach much more interesting than, say, having two columns, one for feet, another one for meters; with the approach I suggested, adding units won't lead to any schema change.

If you need to either sort the data (for instance larger houses appear first) or filter it (for example get only the houses larger than one hundred square meters), this is not enough: you would need a third column which contains a value converted to a common unit. It doesn't matter which unit exactly is used here, since those values will only be used for ordering and filtering.

| improve this answer | | | | |
  • 1
    I hadn't considered that solution. But, how would you sort or filter results by value? Wouldn't it be necessary to convert every value to a central unit just for comparison purposes anyway? – dvtan Nov 21 '16 at 2:14
  • 1
    Then it's back to square one, should that additional column use square feet or square meters? – dvtan Nov 21 '16 at 2:41
  • 8
    Mixing units in the same column is a terrible idea. What if you want to sort or select all the houses smaller than 1000s.f? Pick one unit, store the value as a 32 bit float and be willing to do the trivial math outside thd DB. – user949300 Nov 21 '16 at 6:34
  • 2
    Meta comment: while I don't think this is the best possible idea, there are some applications where this might be a reasonable approach, so I don't think downvoting it to -2 is really appropriate. The point about rounding errors is particularly important, so you certainly don't want to convert between one or the other when storing as integer as OP seems to be implying he would. – Jules Nov 21 '16 at 11:38
  • 1
    I am pretty sure for some use cases it is a good solution. But for many other use cases, however, this leads to heavily overengineered code, so suggesting this as the preferable solution in general, without a clear warning message about the drawbacks, is IMHO very bad advice. – Doc Brown Nov 21 '16 at 17:58
0

That's the wrong way to tackle the problem. The question to ask is: what unit will be used elsewhere? Is it one unit only, or are different units possible (don't forget tatami). If only one unit will be used, then use exactly that. If different units may be used, decide for one, and make sure that a value will always be entered together with the unit in the UI, and gets converted to the unit you choose.

| improve this answer | | | | |
0

The reason you give for using a different unit is completely the wrong thing to be concerned with. A 32 bit number can hold values much larger than that and even if that were the case, no one cares about optimizing on space in 2016.

The main decision point here will depend on what the values are used for. If you just need to store and display then do as @ArseniMourzenko suggests and keep a units column. If you need to do calculations on these values, it's going to depend on the specs for your calculations. If you were doing physics, use SI units, no contest. However if your app is used to calculate board-feet for flooring or how many studs are required to build the frame, you should problably stick with feet. Even in Canada where the metric system is used, building codes are still (at least in part) specified in feet.

| improve this answer | | | | |
0

1 square foot = 0.092903 square meter

1 square meter = 10.7639 square feet

The nearest simple integer conversion is 11
1/11 = 0.0909090909
The error is about 2%

If you use 521 / 5608 then you get integer conversion with very small conversion error

Say the minimum house is 1000 sq feet
You have to round up or down
.5 / 1000 = 0.05% rounding error

1000 sq feet is about 90 square meters
.5 / 90 = 0.56 % rounding error

If you convert 1000 square feet to meters and back using integer 11 then is comes back 1001
For a 0.1% rounding error.

Using square feet you have 10X better precision
For precision store in square feet and have a computed column for square meters
Always sort on the square feet

| improve this answer | | | | |
  • Not to argue but down vote can you share what is wrong? – paparazzo Nov 21 '16 at 17:57

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.