PostGIS
Synopsis
+ geometry `*`+ST_QuantizeCoordinates*+ (` geometry `+g , int
prec_x , int prec_y , int prec_z , int prec_m );
Description
ST_QuantizeCoordinates determines the number of bits (N)
required to represent a coordinate value with a specified number of
digits after the decimal point, and then sets all but the N most
significant bits to zero. The resulting coordinate value will still
round to the original value, but will have improved compressiblity. This
can result in a significant disk usage reduction provided that the
geometry column is using a
compressible
storage type. The function allows specification of a different number
of digits after the decimal point in each dimension; unspecified
dimensions are assumed to have the precision of the x dimension.
Negative digits are interpreted to refer digits to the left of the
decimal point, (i.e., prec_x=-2 will preserve coordinate values to
the nearest 100.
The coordinates produced by ST_QuantizeCoordinates are independent
of the geometry that contains those coordinates and the relative
position of those coordinates within the geometry. As a result, existing
topological relationships between geometries are unaffected by use of
this function. The function may produce invalid geometry when it is
called with a number of digits lower than the intrinsic precision of the
geometry.
Availability: 2.5.0
Technical Background
PostGIS stores all coordinate values as double-precision floating point integers, which can reliably represent 15 significant digits. However, PostGIS may be used to manage data that intrinsically has fewer than 15 significant digits. An example is TIGER data, which is provided as geographic coordinates with six digits of precision after the decimal point (thus requiring only nine significant digits of longitude and eight significant digits of latitude.)
When 15 significant digits are available, there are many possible
representations of a number with 9 significant digits. A double
precision floating point number uses 52 explicit bits to represent the
significand (mantissa) of the coordinate. Only 30 bits are needed to
represent a mantissa with 9 significant digits, leaving 22 insignificant
bits; we can set their value to anything we like and still end up with a
number that rounds to our input value. For example, the value 100.123456
can be represented by the floating point numbers closest to
100.123456000000, 100.123456000001, and 100.123456432199. All are
equally valid, in that ST_AsText(geom, 6) will return the same
result with any of these inputs. As we can set these bits to any value,
ST_QuantizeCoordinates sets the 22 insignificant bits to zero. For a
long coordinate sequence this creates a pattern of blocks of consecutive
zeros that is compressed by PostgreSQL more effeciently.
|
Examples
SELECT ST_AsText(ST_QuantizeCoordinates('POINT (100.123456 0)'::geometry, 4));
st_astext
-------------------------
POINT(100.123455047607 0)WITH test AS (SELECT 'POINT (123.456789123456 123.456789123456)'::geometry AS geom)
SELECT
digits,
encode(ST_QuantizeCoordinates(geom, digits), 'hex'),
ST_AsText(ST_QuantizeCoordinates(geom, digits))
FROM test, generate_series(15, -15, -1) AS digits;
digits | encode | st_astext
--------+--------------------------------------------+------------------------------------------
15 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 123.456789123456)
14 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 123.456789123456)
13 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 123.456789123456)
12 | 01010000005c9a72083cdd5e405c9a72083cdd5e40 | POINT(123.456789123456 123.456789123456)
11 | 0101000000409a72083cdd5e40409a72083cdd5e40 | POINT(123.456789123456 123.456789123456)
10 | 0101000000009a72083cdd5e40009a72083cdd5e40 | POINT(123.456789123455 123.456789123455)
9 | 0101000000009072083cdd5e40009072083cdd5e40 | POINT(123.456789123418 123.456789123418)
8 | 0101000000008072083cdd5e40008072083cdd5e40 | POINT(123.45678912336 123.45678912336)
7 | 0101000000000070083cdd5e40000070083cdd5e40 | POINT(123.456789121032 123.456789121032)
6 | 0101000000000040083cdd5e40000040083cdd5e40 | POINT(123.456789076328 123.456789076328)
5 | 0101000000000000083cdd5e40000000083cdd5e40 | POINT(123.456789016724 123.456789016724)
4 | 0101000000000000003cdd5e40000000003cdd5e40 | POINT(123.456787109375 123.456787109375)
3 | 0101000000000000003cdd5e40000000003cdd5e40 | POINT(123.456787109375 123.456787109375)
2 | 01010000000000000038dd5e400000000038dd5e40 | POINT(123.45654296875 123.45654296875)
1 | 01010000000000000000dd5e400000000000dd5e40 | POINT(123.453125 123.453125)
0 | 01010000000000000000dc5e400000000000dc5e40 | POINT(123.4375 123.4375)
-1 | 01010000000000000000c05e400000000000c05e40 | POINT(123 123)
-2 | 01010000000000000000005e400000000000005e40 | POINT(120 120)
-3 | 010100000000000000000058400000000000005840 | POINT(96 96)
-4 | 010100000000000000000058400000000000005840 | POINT(96 96)
-5 | 010100000000000000000058400000000000005840 | POINT(96 96)
-6 | 010100000000000000000058400000000000005840 | POINT(96 96)
-7 | 010100000000000000000058400000000000005840 | POINT(96 96)
-8 | 010100000000000000000058400000000000005840 | POINT(96 96)
-9 | 010100000000000000000058400000000000005840 | POINT(96 96)
-10 | 010100000000000000000058400000000000005840 | POINT(96 96)
-11 | 010100000000000000000058400000000000005840 | POINT(96 96)
-12 | 010100000000000000000058400000000000005840 | POINT(96 96)
-13 | 010100000000000000000058400000000000005840 | POINT(96 96)
-14 | 010100000000000000000058400000000000005840 | POINT(96 96)
-15 | 010100000000000000000058400000000000005840 | POINT(96 96)