<html>
<head>
</head>
<body>
<div><br />
</div>
<div><br />
</div><br />
<br />
2014-08-07 Sandro Santilli wrote:<br />
<br />
On Thu, Aug 07, 2014 at 01:09:54PM +0200, Nicklas Avén wrote:<br />
>> Ah, Actually delta values will probabaly save you some space.<br />
>> <br />
>> <br />
>> The coordinates is saved as signed varInt. 1 byte only reaches from -64 to 63.<br />
>> 1 bit is lost in the varInt format signalizing if there is any more bytes<br />
>> And since it is signed another byte is lost on the positive side.<br />
>> <br />
>> <br />
>> So all values outside -64 - 63 will need 2 bytes in the ST_AsTWKB-version but in the agg version many of them will only use 1 byte if the delta value to the point before per axis is between -64 and 63<br />
><br />
>Got it, but that's still a max of x2 size difference,<br />
>while we're talking about a x3 size difference here.
<div><br />
</div>
<div>No, it actually makes perfectly sense :-)</div>
<div>I didn't notice until now that all your points was that concentrated.</div>
<div><br />
</div>
<div>This is what you will get </div>
<div><br />
</div>
<div>ST_AsTWKB per point</div>
<div>header 1 byte</div>
<div>type 1 byte</div>
<div>coordinates 2x2 bytes (if 2d)</div>
<div><br />
</div>
<div>ST_AsTWKBagg per point</div>
<div>coordinates 2x1 bytes (your bbox tells there will be no delta values exceeding 1 byte limit)</div>
<div><br />
</div>
<div><br />
</div>
<div>so, ST_AsTWKB will need 6 bytes per point and ST_AsTWKB will need 2 bytes per point + 3 bytes for header, type and npoints in the begining</div>
<div><br />
</div>
<div>If you use 5 decimals instead of 0 the difference will be much smaller.</div>
<div>But the twkbagg version can be tuned by ordering the inputted geometries so they are statistically more close to each other.</div>
<div><br />
</div>
<div>I have done some tests on real world data and I saved almost 30% by ordering on on of the axes like:</div>
<div><br />
</div>
<div>SELECT ST_asTwkbAgg(geom, 5) from </div>
<div>(select geom from the_table order by ST_X(geom)) a;</div>
<div><br />
</div>
<div>But that of course depends a lot of the data set. </div>
<div><br />
</div>
<div>/Nicklas</div>
<div><br />
</div>
<div><br />
</div>
<div><br />
</div>
<div><br />
><br />
>--strk;<br />
><br />
>> <br />
>> <br />
>> 2014-08-07 Sandro Santilli wrote:<br />
>> <br />
>> On Thu, Aug 07, 2014 at 10:44:40AM +0200, Nicklas Avén wrote:<br />
>> >> <br />
>> >> <br />
>> >> 2014-08-07 Sandro Santilli wrote:<br />
>> >> <br />
>> >> On Wed, Aug 06, 2014 at 07:04:34PM +0200, Nicklas Avén wrote:<br />
>> >> >> On on., 2014-08-06 at 18:40 +0200, Sandro Santilli wrote:<br />
>> >> >> > Nicklas, I was looking at ST_AsTWKB_agg but I think it's broken.<br />
>> >> >> > Why would otherwise the output of ST_AsTWKB_agg be smaller than<br />
>> >> >> > the output of ST_AsTWKB ?<br />
>> >> >> <br />
>> >> >> It is possible because the aggregate version uses delta values between<br />
>> >> >> the points which often gives smaller values to store. <br />
>> >> ><br />
>> >> >Is there no separator between records in the output of the aggregator ?<br />
>> >> >How's it different from collecting the geometries and passing the<br />
>> >> >collection to ST_AsTWKB ?<br />
>> >> <br />
>> >> The differnce is that by using ST_AsTWKBagg you won't loose the individual id:s.<br />
>> >> If you use ST_Collect it will just be a collection.<br />
>> ><br />
>> >I requested no IDs, the queries were:<br />
>> ><br />
>> > ST_AsTWKB (g,0)<br />
>> > ST_AsTWKBagg(g,0)<br />
>> ><br />
>> >The dataset I selected (10000 points) have this extent:<br />
>> ><br />
>> > BOX(-169.499567541294 -79.583924216684,-160.680307442788 -71.3369439435191)<br />
>> ><br />
>> >I specified 0 as number of digits. This means that any absolute value<br />
>> >also fits a single byte, right ?<br />
>> ><br />
>> >So, if IDs are absent in both and delta values fit within one byte in both...<br />
>> >what else could make 10k points take 60k bytes with the non-aggregate<br />
>> >and 20k bytes (1/3) with the aggregate call ?<br />
>> ><br />
>> >--strk;<br />
>> ><br />
>> ><br />
><br />
>-- <br />
><br />
> () ASCII ribbon campaign -- Keep it simple !<br />
> /\ http://strk.keybit.net/rants/ascii_mails.txt <br />
><br />
>
</div>
</body>
</html>