In this article I am going look at the effect ordinate precision has on the storage space occupied by tables containing sdo_geometry data. In a related article I will also look at the effect storage precision may have on performance (ie searching and returning data in SQL).
Sdo_Geometry Coordinate Precision
There is a little known side effect of using too many digits of precision when describing and storing spatial data using Oracle’s SDO_GEOMETRY object type. That side effect is that the storage requirements can be significantly greater than expected.
Unlike other spatial storage formats (normally some sort of compressed binary) Oracle storage is open. Very open. Oracle stores the ordinates describing the spatial data (SDO_GEOMETRY) in an array of NUMBER called SDO_ORDINATE_ARRAY.
Note: This is an array of NUMBER and not an array of binary double. Oracle stores a NUMBER in a variable storage format so small numbers take less space than larger NUMBERs. A BINARY_DOUBLE’s storage size is fixed regardless as to the precision of the data being held.
The ramification of this is that your storage costs are in relation to the number of digits used.
The Google Mercator Map coordinates that are created by projection from the original longitude/latitude data are unnecessarily large as can be shown from the following example:
Being that the input data was only specified to 0.001 of a degree, an output – in meters – specified to 8 decimal places seems somewhat excessive.
But if I rounded this data to 1 decimal place what would be the potential saving in storage?
Oracle’s documentation shows how to calculate the size of a NUMBER as stored in the database. As such, an analysis was carried out on a table of Australian land parcels to see what the effect of rounding the ordinates to 1cm and 1mm would have on data size.
Let’s apply a bit of SQL to compute NUMBER size for the above Google Mercator geometry before and after a potential rounding of its ordinates:
So, trimming the X and Y ordinates to 1cm (2 digits of precision) should produce a 31.6 percent reduction in storage, while trimming to 1mm should produce a 23.7 percent reduction in storage.
That’s a single object, what about geometries stored in tables?
Trimming or Rounding
To execute the trimming of an actual sdo_geometry object requires a relatively simply PL/SQL function. I created just such a function a long time ago which is available in my GEOM PL/SQL package available for free on this website. A standalone version of this function is presented in an associated article on this website called Rounding Coordinates or Ordinates in SDO_GEOMETRY
Note that the original parcel data is also over specified:
So, the benefits of a decrease in disk storage and traffic should be obvious to all. But can we see any statistical benefits in this change? (These calculations should be ratified before making any decisions with respect to your data.) I will include some numbers in the next few days.
I hope this is useful to someone.
If you found this article informative, try the followup article on the effect rounding has on precision.