<div dir="ltr">Ah yes - getting the same results as in your blog post:<div><br></div><div><div>create table circles as</div><div>select st_buffer(st_makepoint(-180 + 360*random(), -90 + 180*random()), random(), 128) as geom</div><div>from generate_series(1, 1.06e4);</div><div>select pg_size_pretty(pg_total_relation_size('"circles"'));</div><div>-- 100 MB</div><div><br></div><div>create table circles_9dig as select st_quantizegeometry(geom, array[9,9]) from circles;</div><div>select pg_size_pretty(pg_total_relation_size('"circles_9dig"'));</div><div>-- 77 MB</div><div><br></div><div>What made me confused was that when I tried it on some real world data it had been snapped to grid and apparently that also removes noise:</div><div><br></div><div>create table circles_snap_test as select st_snaptogrid(geom, 0.0001) from circles;</div><div>select pg_size_pretty(pg_total_relation_size('"circles_snap_test"'));</div><div>-- 82 MB</div><div><br></div><div>create table circles_snap_test as select st_snaptogrid(geom, 0.001) from circles;</div><div>select pg_size_pretty(pg_total_relation_size('"circles_snap_test"'));</div><div>-- 41 MB</div><div><br></div><div>PS. My updated SQL implementation with fixed handling of polygon rings can be found at <a href="https://gist.github.com/bjornharrtell/30d804c5df44feb9003bb33a8cd1e508">https://gist.github.com/bjornharrtell/30d804c5df44feb9003bb33a8cd1e508</a>.</div><div><br></div><div>/Björn</div><div class="gmail_extra"><br><div class="gmail_quote">2018-03-17 0:18 GMT+01:00 Daniel Baston <span dir="ltr"><<a href="mailto:dbaston@gmail.com" target="_blank">dbaston@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Are you now getting reasonable compression? The reason for negative precision is to represent values that are significant to the nearest 100, for example. (This is in a "digits of precision" world, not a "significant digits" world.)</div><div class="gmail-HOEnZb"><div class="gmail-h5"><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Mar 16, 2018 at 5:48 PM, Björn Harrtell <span dir="ltr"><<a href="mailto:bjorn.harrtell@gmail.com" target="_blank">bjorn.harrtell@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Had to slightly modify to make it work and I'm not sure why you want to go into negative digit precision.<div><br></div><div>SQL:</div><div><br></div><div><span><div>WITH test AS (SELECT 'POINT (123.456789123456 123.456789123456)'::geometry AS geom)</div><div>SELECT</div><div> digits,</div></span><div> encode(ST_QuantizeGeometry(geo<wbr>m, ARRAY[digits, digits, digits]), 'hex'),</div><div> ST_AsText(ST_QuantizeGeometry(<wbr>geom, ARRAY[digits, digits, digits]))</div><span><div>FROM test, generate_series(15, -15, -1) AS digits;</div><div><br></div></span><div>Result:</div><div><br></div><div><div>15<span style="white-space:pre-wrap"> </span>01010000005c9a72083cdd5e405c9a<wbr>72083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789123456 123.456789123456)</div><div>14<span style="white-space:pre-wrap"> </span>0101000000409a72083cdd5e40409a<wbr>72083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789123456 123.456789123456)</div><div>13<span style="white-space:pre-wrap"> </span>0101000000009a72083cdd5e40009a<wbr>72083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789123455 123.456789123455)</div><div>12<span style="white-space:pre-wrap"> </span>0101000000009072083cdd5e400090<wbr>72083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789123418 123.456789123418)</div><div>11<span style="white-space:pre-wrap"> </span>0101000000008072083cdd5e400080<wbr>72083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.45678912336 123.45678912336)</div><div>10<span style="white-space:pre-wrap"> </span>0101000000000070083cdd5e400000<wbr>70083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789121032 123.456789121032)</div><div>9<span style="white-space:pre-wrap"> </span>0101000000000040083cdd5e400000<wbr>40083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789076328 123.456789076328)</div><div>8<span style="white-space:pre-wrap"> </span>0101000000000000083cdd5e400000<wbr>00083cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456789016724 123.456789016724)</div><div>7<span style="white-space:pre-wrap"> </span>0101000000000000003cdd5e400000<wbr>00003cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456787109375 123.456787109375)</div><div>6<span style="white-space:pre-wrap"> </span>0101000000000000003cdd5e400000<wbr>00003cdd5e40<span style="white-space:pre-wrap"> </span>POINT(123.456787109375 123.456787109375)</div><div>5<span style="white-space:pre-wrap"> </span>01010000000000000038dd5e400000<wbr>000038dd5e40<span style="white-space:pre-wrap"> </span>POINT(123.45654296875 123.45654296875)</div><div>4<span style="white-space:pre-wrap"> </span>01010000000000000000dd5e400000<wbr>000000dd5e40<span style="white-space:pre-wrap"> </span>POINT(123.453125 123.453125)</div><div>3<span style="white-space:pre-wrap"> </span>01010000000000000000dc5e400000<wbr>000000dc5e40<span style="white-space:pre-wrap"> </span>POINT(123.4375 123.4375)</div><div>2<span style="white-space:pre-wrap"> </span>01010000000000000000c05e400000<wbr>000000c05e40<span style="white-space:pre-wrap"> </span>POINT(123 123)</div><div>1<span style="white-space:pre-wrap"> </span>01010000000000000000005e400000<wbr>000000005e40<span style="white-space:pre-wrap"> </span>POINT(120 120)</div><div>0<span style="white-space:pre-wrap"> </span>010100000000000000000050400000<wbr>000000005040<span style="white-space:pre-wrap"> </span>POINT(64 64)</div><div>-1<span style="white-space:pre-wrap"> </span>010100000000000000000000400000<wbr>000000000040<span style="white-space:pre-wrap"> </span>POINT(2 2)</div><div>-2<span style="white-space:pre-wrap"> </span>010100000000000000000000400000<wbr>000000000040<span style="white-space:pre-wrap"> </span>POINT(2 2)</div><div>-3<span style="white-space:pre-wrap"> </span>010100000000000000000000400000<wbr>000000000040<span style="white-space:pre-wrap"> </span>POINT(2 2)</div><div>-4<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-5<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-6<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-7<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-8<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-9<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-10<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-11<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-12<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-13<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-14<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div><div>-15<span style="white-space:pre-wrap"> </span>010100000000000000000000000000<wbr>000000000000<span style="white-space:pre-wrap"> </span>POINT(0 0)</div></div><span class="gmail-m_-242406063174472559HOEnZb"><font color="#888888"><div><br></div><div class="gmail_extra">/Björn</div></font></span><div><div class="gmail-m_-242406063174472559h5"><div class="gmail_extra"><br><div class="gmail_quote">2018-03-15 20:37 GMT+01:00 Daniel Baston <span dir="ltr"><<a href="mailto:dbaston@gmail.com" target="_blank">dbaston@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Björn,<div><br></div><div><div>I haven't fully gone through the implementation (seems impressive, though!). If you run a query like the one in the docs (copied below) do you see a similar amount of trimming? </div></div><div><br></div><div><div>WITH test AS (SELECT 'POINT (123.456789123456 123.456789123456)'::geometry AS geom)</div><div>SELECT</div><div> digits,</div><div> encode(ST_QuantizeCoordinates(<wbr>geom, digits), 'hex'),</div><div> ST_AsText(ST_QuantizeCoordinat<wbr>es(geom, digits))</div><div>FROM test, generate_series(15, -15, -1) AS digits;</div></div><div><br></div><div>Dan</div></div><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-HOEnZb"><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-h5"><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Mar 15, 2018 at 3:22 PM, Björn Harrtell <span dir="ltr"><<a href="mailto:bjorn.harrtell@gmail.com" target="_blank">bjorn.harrtell@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:12.8px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline">Apart from a bug with polygons that have more than one ring I think my implementation does what it should but I'm seeing *very* limited space reductions when going from 15 digits to 6. Any ideas why?</span><div><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223h5"><br><div class="gmail_extra"><br><div class="gmail_quote">2018-03-14 9:05 GMT+01:00 Björn Harrtell <span dir="ltr"><<a href="mailto:bjorn.harrtell@gmail.com" target="_blank">bjorn.harrtell@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div>Ok so I said why not implement using SQL (i.e PL/pgSQL) and I could not let it go even though it's clearly not a sane way of doing it.</div><div><br></div><div>Note that second parameter is an array to be able to set the precision per ordinal. Here's the implementation for your pleasure:</div><div><br></div><div>--example use<br></div><div><div>select ST_AsText(ST_QuantizeGeometry(<wbr>st_geomfromtext('POINT(123.123<wbr>45 123.12345)'), ARRAY[5,5]));</div><div>select ST_AsText(ST_QuantizeGeometry(<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:small;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:400;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px;background-color:rgb(255,255,255);text-decoration-style:initial;text-decoration-color:initial;float:none;display:inline"><wbr>st_geomfromtext</span>('POINTZ(123.12<wbr>345 123.12345 9.9999)'), ARRAY[3,3,1]));</div><div>select ST_AsText(ST_QuantizeGeometry(<wbr>st_geomfromtext('LINESTRING(12<wbr>3.12345 223.12345, 334.12345 434.12345, 999.999 999.999)'), ARRAY[1,1]));</div><div>select ST_AsText(ST_QuantizeGeometry(<wbr>st_geomfromtext('POLYGON((1 1, 2.555 2.555, 3 3, 1 1))'), ARRAY[1,3]));</div><div><br></div><div>--drop type _quantize_geometry_context cascade;</div><div>create type _quantize_geometry_context as (</div><div><span style="white-space:pre-wrap"> </span>buffer bytea,</div><div><span style="white-space:pre-wrap"> </span>endianness bool,</div><div><span style="white-space:pre-wrap"> </span>off integer,</div><div><span style="white-space:pre-wrap"> </span>masks bit(64)[],</div><div><span style="white-space:pre-wrap"> </span>num_ordinals integer</div><div>);</div><div><br></div><div>--drop function _quantize_geometry_coord(c _quantize_geometry_context);</div><div>create or replace function _quantize_geometry_coord(c _quantize_geometry_context) returns _quantize_geometry_context as</div><div>$$</div><div>declare</div><div><span style="white-space:pre-wrap"> </span>masked_byte int;</div><div>begin</div><div><span style="white-space:pre-wrap"> </span>for o in 1 .. c.num_ordinals loop</div><div><span style="white-space:pre-wrap"> </span>for i in 0 .. 7 loop</div><div><span style="white-space:pre-wrap"> </span>masked_byte = get_byte(c.buffer, c.off) & substring(c.masks[o] from (i * 8) + 1 for 8)::int;</div><div><span style="white-space:pre-wrap"> </span>c.buffer = set_byte(c.buffer, c.off, masked_byte);</div><div><span style="white-space:pre-wrap"> </span>c.off = c.off + 1;</div><div><span style="white-space:pre-wrap"> </span>end loop;</div><div><span style="white-space:pre-wrap"> </span>end loop;</div><div><span style="white-space:pre-wrap"> </span>return c;</div><div>end;</div><div>$$</div><div>language plpgsql;</div><div><br></div><div>--drop function _quantize_geometry_coords(c _quantize_geometry_context);</div><div>create or replace function _quantize_geometry_coords(c _quantize_geometry_context) returns _quantize_geometry_context as</div><div>$$</div><div>declare</div><div><span style="white-space:pre-wrap"> </span>num_coords int;</div><div>begin</div><div><span style="white-space:pre-wrap"> </span>num_coords = get_byte(c.buffer, c.off + 3);</div><div><span style="white-space:pre-wrap"> </span>c.off = c.off + 4;</div><div><span style="white-space:pre-wrap"> </span>for i in 0 .. num_coords - 1 loop</div><div><span style="white-space:pre-wrap"> </span>c = _quantize_geometry_coord(c);</div><div><span style="white-space:pre-wrap"> </span>end loop;</div><div><span style="white-space:pre-wrap"> </span>return c;</div><div>end;</div><div>$$</div><div>language plpgsql;</div><div><br></div><div>--drop function ST_QuantizeGeometry(geom geometry, digits_precision integer[]);</div><div>create or replace function ST_QuantizeGeometry(geom geometry, digits_precision integer[]) returns geometry as</div><div>$$</div><div>declare</div><div><span style="white-space:pre-wrap"> </span>c _quantize_geometry_context;</div><div><span style="white-space:pre-wrap"> </span>geomtype integer;</div><div><span style="white-space:pre-wrap"> </span>bits_to_keep integer;</div><div><span style="white-space:pre-wrap"> </span>ordinalstype integer;</div><div><span style="white-space:pre-wrap"> </span>num_rings integer;</div><div><span style="white-space:pre-wrap"> </span>num_objects integer;</div><div>begin</div><div><span style="white-space:pre-wrap"> </span>c.buffer = st_asbinary(geom, 'XDR');</div><div><span style="white-space:pre-wrap"> </span>-- TODO: parse geometry type from wkb</div><div><span style="white-space:pre-wrap"> </span>geomtype = st_dimension(geom)+1;</div><div><span style="white-space:pre-wrap"> </span>-- TODO: parse number of ordinals from wkb</div><div><span style="white-space:pre-wrap"> </span>c.num_ordinals = st_ndims(geom);</div><div><span style="white-space:pre-wrap"> </span>for i in 1 .. c.num_ordinals loop</div><div><span style="white-space:pre-wrap"> </span>bits_to_keep = ceil(digits_precision[i] / log(10, 2));</div><div><span style="white-space:pre-wrap"> </span>c.masks[i] = B'1111111111111111111111111111<wbr>111111111111111111111111111111<wbr>111111' << (52 - bits_to_keep);</div><div><span style="white-space:pre-wrap"> </span>end loop;</div><div><span style="white-space:pre-wrap"> </span>c.off = 5;</div><div><span style="white-space:pre-wrap"> </span>if geomtype = 1 then</div><div><span style="white-space:pre-wrap"> </span>c = _quantize_geometry_coord(c);</div><div><span style="white-space:pre-wrap"> </span>elsif geomtype = 2 then</div><div><span style="white-space:pre-wrap"> </span>c = _quantize_geometry_coords(c);</div><div><span style="white-space:pre-wrap"> </span>elsif geomtype = 3 then</div><div><span style="white-space:pre-wrap"> </span>num_rings = get_byte(c.buffer, c.off + 3);</div><div><span style="white-space:pre-wrap"> </span>c.off = c.off + 4;</div><div><span style="white-space:pre-wrap"> </span>for i in 0..num_rings-1 loop</div><div><span style="white-space:pre-wrap"> </span>c = _quantize_geometry_coords(c);</div><div><span style="white-space:pre-wrap"> </span>end loop;</div><div> else</div><div> raise 'Unsupported geometry type';</div><div><span style="white-space:pre-wrap"> </span>end if;</div><div><span style="white-space:pre-wrap"> </span>return st_geomfromwkb(c.buffer);</div><div>end;</div><div>$$</div><div>language plpgsql;</div><div><br></div><div>/Björn</div><div><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223m_1156247657918025599h5"><div><div class="gmail_extra"><br><div class="gmail_quote">2018-03-08 20:52 GMT+01:00 Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I think the extra word is superfluous and hurts my fingers to type.<br>
<br>
Bur Dan likes it and he's the author so oh well.<br>
<span class="gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223m_1156247657918025599m_7689105508533561244gmail-im gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223m_1156247657918025599m_7689105508533561244gmail-HOEnZb"><br>
-----Original Message-----<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@<wbr>lists.osgeo.org</a>] On Behalf Of Paul Ramsey<br>
Sent: Thursday, March 08, 2018 9:25 AM<br>
To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><wbr>><br>
</span><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223m_1156247657918025599m_7689105508533561244gmail-HOEnZb"><div class="gmail-m_-242406063174472559m_1056983290526570597gmail-m_7725040637561329223m_1156247657918025599m_7689105508533561244gmail-h5">Subject: Re: [postgis-devel] Bit setting to compress PostGIS geometries<br>
<br>
ST_QuantizeCoordinates()? or is the extra word superfluous?<br>
<br>
On Thu, Mar 8, 2018 at 6:21 AM, Daniel Baston <<a href="mailto:dbaston@gmail.com" target="_blank">dbaston@gmail.com</a>> wrote:<br>
> My understanding is that it should work on big-endian architectures,<br>
> but I haven't tested it. Maybe Sandro will set up a new bot using QEMU<br>
> :)<br>
><br>
> ST_Quantize, then? Or postgis_quantize_geometry?<br>
><br>
> Dan<br>
> ______________________________<wbr>_________________<br>
> postgis-devel mailing list<br>
> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/postgis-devel</a><br>
______________________________<wbr>_________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/postgis-devel</a><br>
<br>
______________________________<wbr>_________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/postgis-devel</a></div></div></blockquote></div><br></div></div></div></div></div></div>
</blockquote></div><br></div></div></div></div>
<br>______________________________<wbr>_________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/postgis-devel</a><br></blockquote></div><br></div>
</div></div></blockquote></div><br></div></div></div></div></div>
</blockquote></div><br></div>
</div></div></blockquote></div><br></div></div></div>