About the BLOB-TinyPoint internal encodingStarting 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 classic BLOB-GEOMETRY encoding always requires to explicitly define the BBOX of the encoded Geometry.
4 double precision values are required to store the BBOX (32 bytes).
- But in the very specific case of POINT Geometries this isn't strictly required just because the BBOX of a POINT is the POINT itself.
So the new BLOB-TinyPoint encoding lacks an explicit BBOX thus requiring less storage space, as shown in the following table:
Geometry Class BLOB-GEOMETRY
size in bytes
size in bytes
POINT 60 24 POINT Z 68 32 POINT M 68 32 POINT ZM 76 40
|The complete and fully detailed technical specification of the BLOB-TinyPoint encoding is published here|
Cross version compatibility issues
- libspatialite-5.0.0 (or any subsequent version) is strictly required so to correctly read and write the new BLOB-TinyPoint encoding.
- all previous versions will simply consider it as a generic unqualified BLOB, and will consequently fail to recognize a valid Geometry encoding.
- so you should always carefully consider where to adopt the new BLOB-TinyPoint encoding.
It could be absolutely painless in the context of some specific application fully based on SpatiaLite 5.x.
But it could cause many severe troubles in a more complex environment where many different softwares (possibly based on different versions of SpatiaLite) are expected to closely interact.
- the default behavior of libspatialite-5.0.0 is to continue using the classic BLOB-GEOMETRY encoding, so to avoid any cross version issue.
You are always expected to explicitly request SpatiaLite to use the new BLOB-TinyPoint encoding.
Enabling/Disabling TinyPointYou can selectively enable or disable TinyPoint by calling the following SQL functions:
SELECT EnableTinyPoint(); ------------------------- NULL SELECT IsTinyPointEnabled(); ---------------------------- 1 SELECT DisableTinyPoint(); ------------------------- NULL SELECT IsTinyPointEnabled(); ---------------------------- 0
- immediately after calling EnableTinyPoint() all POINT-Geometries will be stored into the new BLOB-TinyPoint format until the end of the current session, or until DisableTinyPoint() is called.
- immediately after calling DisableTinyPoint() all POINT-Geometries will be stored into the classic BLOB-GEOMETRY format until the end of the current session, or until EnableTinyPoint() is called.
Note: this always is the default setting when establishing a new DB connection.
- you can eventually call IsTinyPointEnabled() so to check if BLOB-TinyPoint is currently enabled or disabled.
- Important notice: SpatiaLite 5.0.0 is always able to correctly decode all BLOB-TinyPoint Geometries; enabling / disabling only applies when encoding.
in other words, you can freely choose which encoding is to be adopted for all POINT Geometries being created during the current session.
Alternative mechanism based on external variableIf 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 testI'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:
- id INTEGER PRIMARY KEY
- name TEXT
- geom POINT (SRID=4326)
|without Spatial Index||1.1 GB||637 MB|
|with Spatial Index||1.7 GB||1.3 GB|
- The new BLOB-TinyPoint encoding can effectively ensure an impressive saving of storage space (about 50% in the mean case).
Warning: this only applies to Geometry data of the Point type, but has no effect on other Geometry Classes, on attribute data and on Spatial Indices.
Real world DB-files usually contains a mix of Point, Linestring and Polygon Geometries associated with rich sets of attributes and supported by Spatial Indices.
This test was based on a rather borderline sample (a single huge table of Points including just one attribute), so it's strongly biased toward the optimistic side.
Real world DB-files will surely show less impressive storage savings, presumably in the range between 10% and 25% depending on the actual nature of the datasets.
- Using the BLOB-TinyPoint encoding can effectively be a valid solution for all applications desperately requiring to squeeze to minimal terms the size of their DB-files (as is the typical scenario for embedded or mobile devices).
- The generalized adoption of the BLOB-TinyPoint encoding should always be very carefully planned on more complex environments using many different software packages, possibly based on different versions of SpatiaLite.
Never forget or overlook that BLOB-TinyPoint will surely pose severe cross-version compatibility issues.