[postgis-devel] Bit setting to compress PostGIS geometries

[Daniel Baston]

Hi Björn,

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?

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;

Dan

On Thu, Mar 15, 2018 at 3:22 PM, Björn Harrtell <bjorn.harrtell at gmail.com>
wrote:

> 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'1111111111111111111111111111111111111111111111111111111111111111'
>> << (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
>>>
>>
>>
>
> _______________________________________________
> 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/d8998f14/attachment-0001.html>