[postgis-devel] Bit setting to compress PostGIS geometries

[Björn Harrtell]

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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20180314/f2374828/attachment.html>