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?

davidtgq
  • 431
  • 1
  • 4
  • 13
  • 3
    A four byte integer [can store values up to about 2 billion](https://en.wikipedia.org/wiki/2147483647_(number)) (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 02:09
  • To clarify, the reason I said I'm favouring square meters is because 2 bytes would be sufficient – davidtgq Nov 21 '16 at 02:17
  • 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 02: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 06:40
  • I'd losslessly store what the user entered and the value converted to a standard unit separately. – CodesInChaos Nov 21 '16 at 09:18
  • 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 09:22
  • @CodesInChaos write that as an answer – Caleth Nov 21 '16 at 09:24
  • 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
  • 2
    Just make sure that everybody agrees. See also http://mars.jpl.nasa.gov/msp98/news/mco990930.html. I would also recommend that you use the same unit for all area columns in your database (and a consistent unit for all lengths etc). – Kris Vandermotten Nov 21 '16 at 15:10
  • 1
    @RemcoGerlich Storage is cheap, but memory and bigger RDS instances aren't. If the column is frequently accessed, wouldn't it need double the memory? – davidtgq Nov 21 '16 at 17:25
  • 1
    If you also measure properties in addition to buildings, note that some data may still be in [survey foot](https://en.wikipedia.org/wiki/Foot_(unit)#US_survey_foot), which is slightly different, so you may need to take that into account as well (depending on your use cases) – Hulk Nov 22 '16 at 09:59

6 Answers6

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.

Jules
  • 17,614
  • 2
  • 33
  • 63
  • 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
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.

Tulains Córdova
  • 39,201
  • 12
  • 97
  • 154
  • 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 (https://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
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.

Arseni Mourzenko
  • 134,780
  • 31
  • 343
  • 513
  • 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? – davidtgq Nov 21 '16 at 02:14
  • If you need to sort data directly in the database, then yes, it would make sense to have an additional column with a value computed automatically by the database from the other two column values. – Arseni Mourzenko Nov 21 '16 at 02:40
  • 1
    Then it's back to square one, should that additional column use square feet or square meters? – davidtgq Nov 21 '16 at 02: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 06: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
  • I've had the pain of metric conversions. I would prefer to use this approach. So +1. – Q Q Nov 21 '16 at 12:17
  • 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
  • Any database these days have support for views. Store the data as 2 columns, 1 for unit of measurement, the other as the value. Create a view with 2 computed columns: if your unit of measure is "meters", compute the "feet" and vice versa. Define the precision you want in the conversion (up to the 8th decimal square, for example), and go with it. Use the view to present data. Use the table to store it. – Machado Nov 21 '16 at 21:00
  • @user949300: consider reading the second comment which answers the question of the OP about sorting and filtering. I've also edited the answer by including what the comment was already saying. – Arseni Mourzenko Nov 22 '16 at 00:24
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.

Bernhard Hiller
  • 1,953
  • 1
  • 12
  • 17
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.

JimmyJames
  • 24,682
  • 2
  • 50
  • 92
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

paparazzo
  • 1,937
  • 1
  • 14
  • 23