BLOB-TinyPoint
Not logged in

back

About the BLOB-TinyPoint internal encoding

Starting since version 5.0.0 SpatiaLite supports a new internal BLOB encoding named BLOB-TinyPoint.

All previous versions already supported the classic BLOB-GEOMETRY encoding; the new BLOB-TinyPoint is an alternative encoding specifically intended for storing Geometries of the POINT Type (POINT, POINT Z, POINT M and POINT ZM).
Both encodings are functionally equivalent, but BLOB-TinyPoint requires a significantly reduced storage amount, and this could be a rather critical factor on all platforms with very limited resources, as e.g. embedded or mobile devices.


The complete and fully detailed technical specification of the BLOB-TinyPoint encoding is published here

Cross version compatibility issues

Enabling/Disabling TinyPoint

You can selectively enable or disable TinyPoint by calling the following SQL functions:
SELECT EnableTinyPoint();
-------------------------
NULL

SELECT IsTinyPointEnabled();
----------------------------
1

SELECT DisableTinyPoint();
-------------------------
NULL

SELECT IsTinyPointEnabled();
----------------------------
0

Alternative mechanism based on external variable

If the external variable SPATIALITE_TINYPOINT=1 is actually set when establishing a new DB connection, then the BLOB-TinyPoint encoding will be immediately enabled for the current session.

Useful SQL tricks for BLOB-TinyPoint handling

SELECT rowid, IsGeometryBlob(geom), IsTinyPointBlob(geom)
FROM my_points;

SELECT Count(*)
FROM my_points
WHERE IsTinyPointBlob(geom) = 1;
You can call IsGeometryBlob() and/or IsTinyPointBlob() in order to quickly detect which one of the two alternative encodings is currently adopted for each Point-Geometry stored into a DB-file.
UPDATE my_points SET geom = GeometryPointEncode(geom)
WHERE IsGeometryPointBlob(geom) <> 1;

UPDATE my_points SET geom = TinyPointEncode(geom)
WHERE IsTinyPointBlob(geom) <> 1;
VACUUM;
You can call GeometryPointEncode() or TinyPointEncode() in order to convert Points from an encoding to the other.
Note: you should always call VACUUM after executing TinyPointEncode() in order to effectively reclaim any unused storage space.
UPDATE my_points SET geom = GeometryPointEncode(geom)
WHERE IsGeometryPointBlob(geom) = 1;

UPDATE my_points SET geom = TinyPointEncode(geom)
WHERE IsTinyPointBlob(geom) = 1;
VACUUM;
Note: calling TinyPointEncode() on behalf of data already encoded as BLOB-TinyPoint is an intrinsically stupid but absolutely harmless operation (will just waste several time with no apparent effect).
The same applies to GeometryPointEncode() on behalf of BLOB-GEOMETRY data.

Reality check: a practical test

I've created two different DB-files based on the same input dataset, the first one fully based on the classic BLOB-Geometry encoding, the second one based on the new BLOB-TinyPoint encoding.

The input dataset was the well known GeoNames, a worldwide collection of about 12 million POIs.
GeoNames includes several data columns, but in order to simplify as much as possible I just defined three columns in my test:
  1. id INTEGER PRIMARY KEY
  2. name TEXT
  3. geom POINT (SRID=4326)
The actual findings of this test are reported in the following table:
DB-file size
BLOB-GEOMETRY
encoded
BLOB-TinyPoint
encoded
without Spatial Index1.1 GB637 MB
with Spatial Index1.7 GB1.3 GB

Conclusions




back