[postgis-devel] Bit setting to compress PostGIS geometries

[Björn Harrtell]

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?

2018-03-14 9:05 GMT+01:00 Björn Harrtell <bjorn.harrtell at gmail.com>:

> 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.
>
> Note that second parameter is an array to be able to set the precision per
> ordinal. Here's the implementation for your pleasure:
>
> --example use
> select ST_AsText(ST_QuantizeGeometry(st_geomfromtext('POINT(123.12345
> 123.12345)'), ARRAY[5,5]));
> select ST_AsText(ST_QuantizeGeometry(st_geomfromtext('POINTZ(123.12345
> 123.12345 9.9999)'), ARRAY[3,3,1]));
> select ST_AsText(ST_QuantizeGeometry(st_geomfromtext('LINESTRING(123.12345
> 223.12345, 334.12345 434.12345, 999.999 999.999)'), ARRAY[1,1]));
> select ST_AsText(ST_QuantizeGeometry(st_geomfromtext('POLYGON((1 1, 2.555
> 2.555, 3 3, 1 1))'), ARRAY[1,3]));
>
> --drop type _quantize_geometry_context cascade;
> create type _quantize_geometry_context as (
> buffer bytea,
> endianness bool,
> off integer,
> masks bit(64)[],
> num_ordinals integer
> );
>
> --drop function _quantize_geometry_coord(c _quantize_geometry_context);
> create or replace function _quantize_geometry_coord(c
> _quantize_geometry_context) returns _quantize_geometry_context as
> $$
> declare
> masked_byte int;
> begin
> for o in 1 .. c.num_ordinals loop
> for i in 0 .. 7 loop
> masked_byte = get_byte(c.buffer, c.off) & substring(c.masks[o] from (i *
> 8) + 1 for 8)::int;
> c.buffer = set_byte(c.buffer, c.off, masked_byte);
> c.off = c.off + 1;
> end loop;
> end loop;
> return c;
> end;
> $$
> language plpgsql;
>
> --drop function _quantize_geometry_coords(c _quantize_geometry_context);
> create or replace function _quantize_geometry_coords(c
> _quantize_geometry_context) returns _quantize_geometry_context as
> $$
> declare
> num_coords int;
> begin
> num_coords = get_byte(c.buffer, c.off + 3);
> c.off = c.off + 4;
> for i in 0 .. num_coords - 1 loop
> c = _quantize_geometry_coord(c);
> end loop;
> return c;
> end;
> $$
> language plpgsql;
>
> --drop function ST_QuantizeGeometry(geom geometry, digits_precision
> integer[]);
> create or replace function ST_QuantizeGeometry(geom geometry,
> digits_precision integer[]) returns geometry as
> $$
> declare
> c _quantize_geometry_context;
> geomtype integer;
> bits_to_keep integer;
> ordinalstype integer;
> num_rings integer;
> num_objects integer;
> begin
> c.buffer = st_asbinary(geom, 'XDR');
> -- TODO: parse geometry type from wkb
> geomtype = st_dimension(geom)+1;
> -- TODO: parse number of ordinals from wkb
> c.num_ordinals = st_ndims(geom);
> for i in 1 .. c.num_ordinals loop
> bits_to_keep = ceil(digits_precision[i] / log(10, 2));
> c.masks[i] = B'111111111111111111111111111111
> 1111111111111111111111111111111111' << (52 - bits_to_keep);
> end loop;
> c.off = 5;
> if geomtype = 1 then
> c = _quantize_geometry_coord(c);
> elsif geomtype = 2 then
> c = _quantize_geometry_coords(c);
> elsif geomtype = 3 then
> num_rings = get_byte(c.buffer, c.off + 3);
> c.off = c.off + 4;
> for i in 0..num_rings-1 loop
> c = _quantize_geometry_coords(c);
> end loop;
>     else
>         raise 'Unsupported geometry type';
> end if;
> return st_geomfromwkb(c.buffer);
> end;
> $$
> language plpgsql;
>
> /Björn
>
> 2018-03-08 20:52 GMT+01:00 Regina Obe <lr at pcorp.us>:
>
>> I think the extra word is superfluous and hurts my fingers to type.
>>
>> Bur Dan likes it and he's the author so oh well.
>>
>> -----Original Message-----
>> From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
>> Behalf Of Paul Ramsey
>> Sent: Thursday, March 08, 2018 9:25 AM
>> To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
>> Subject: Re: [postgis-devel] Bit setting to compress PostGIS geometries
>>
>> ST_QuantizeCoordinates()?  or is the extra word superfluous?
>>
>> On Thu, Mar 8, 2018 at 6:21 AM, Daniel Baston <dbaston at gmail.com> wrote:
>> > My understanding is that it should work on big-endian architectures,
>> > but I haven't tested it. Maybe Sandro will set up a new bot using QEMU
>> > :)
>> >
>> > ST_Quantize, then? Or postgis_quantize_geometry?
>> >
>> > Dan
>> > _______________________________________________
>> > postgis-devel mailing list
>> > postgis-devel at lists.osgeo.org
>> > https://lists.osgeo.org/mailman/listinfo/postgis-devel
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>>
>> _______________________________________________
>> postgis-devel mailing list
>> postgis-devel at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20180315/03e019da/attachment.html>