SQL Server Spatial – Coordinate Storage Precision

SQL Server stores geography and geometry coordinates as binary data, adhering to the IEEE-754 standard for binary floating-point arithmetic. Based on this standard, each coordinate is stored as a double-precision floating-point number that is 64 bits (8 bytes) long.

However, although SQL Server stores coordinates as binary floating point values, Well-Known Text (WKT), the format from which you are most likely to create SQL Server spatial data, is a character string that represents coordinates as decimal values. Whenever you use a static method to create an instance of geography or geometry data from WKT (or any other static method that accepts decimal input), the supplied coordinates are implicitly converted to the closest possible value that can be represented in binary floating-point format…. { each coordinate value is effectively CAST AS binary(8) }

An 8-byte binary value is roughly equivalent to 15 digits of decimal precision, but the geography and geometry static methods allow you to create instances from coordinate values with greater precision than this; the Point() method, for example, will accept decimal coordinate values with up to 38 digits of precision. However, all created geometry or geography instances will ultimately be stored with the same 64-bit precision, and supplying coordinates with greater precision than this to a static method will not lead to any greater precision of the stored geography or geometry value.

To demonstrate this, consider the following code listing:

DECLARE @Precise geometry;

SET @Precise = geometry::Point(10.23456789012345, 0, 0);

DECLARE @SuperPrecise geometry;

SET @SuperPrecise = geometry::Point(10.234567890123456789012345678901234567, 0, 0);

SELECT @Precise.STEquals(@SuperPrecise);

The additional decimal places of precision supplied for the @SuperPrecise point cannot be represented in an 8-byte binary value. As a result, the stored coordinate values of both @Precise and @SuperPrecise are the same (0x3C8B514819782440).

The STEquals() method returns the value 1, which confirms that the coordinates of each created instance are the same, even though the supplied coordinates differed.

It is also worth remembering that coordinates supplied with less decimal precision will still occupy 8 bytes when converted to binary and saved to the database. Thus in the following example, even though @HighPrecision is stated with greater precision than @LowPrecision, the two sets of coordinates occupy exactly the same amount of space.

DECLARE @LowPrecision geometry;

SET @LowPrecision = geometry::STPointFromText('POINT(1 2)', 0);

DECLARE @HighPrecision geometry;

SET @HighPrecision = geometry::STPointFromText('POINT(1.2345678901234567890123456789 2.3456789012345678)', 0);

SELECT

DATALENGTH(@LowPrecision), --22

DATALENGTH(@HighPrecision); --22

The result demonstrates that you do not save storage space in the DB by supplying less precise coordinates; both points in this example require exactly 22 bytes of storage.