<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><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:0 0 0 .8ex;border-left:1px #ccc solid;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.<wbr>12345 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.<wbr>12345 123.12345 9.9999)'), ARRAY[3,3,1]));</div><div>select ST_AsText(ST_QuantizeGeometry(<wbr>st_geomfromtext('LINESTRING(<wbr>123.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'<wbr>111111111111111111111111111111<wbr>111111111111111111111111111111<wbr>1111' << (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="h5"><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="m_7689105508533561244gmail-im m_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="m_7689105508533561244gmail-HOEnZb"><div class="m_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>