Format of BLOB

[Stefan]

Hello there,

i may be a complete moron and new to sqlite.

I was looking at the BLOB-data of the sample database test-2.3.sqlite
and supposed the format of the BLOB in the database would be WKB.

Looking at the binary representation first row in table HighWays the
byte-values are 00 01 78 7f 00. The first byte (00) is the little/big-
endian flag, saying the following data is in big-endian. The next 4
bytes are the WKB-type, expecting here a linestring with four bytes 00
00 00 02. But what are the values 01 78 7f 00?

Can anybody help me?

Thanks!!

[markb]

http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.1.html#t3

[a.fu...@lqt.it]

Hi Stefan,

the internal Geometry representation [BLOB] used in
SpatiaLite isn't exactly WKB ... it's a slightly
modified WKB [mainly for performance reasons]

you can find a full reference here:
http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.1.html#t3.3

Please be aware: the documentation is a little bit
outdated [v.2.3.1].
the latest v.2.4.0 Release Candidate supports 3D geoms
and Compressed geoms: accordingly to this, the internal
BLOB format has been changed [still undocumented].

Anyway, for uncompressed 2D [xy] geoms the previous format
[i.e. the one explainded in the docs] is absolutely
the same, with no difference at all.

bye
Sandro

[Stefan]

Thank you for your very quick answer!

The format of the BLOB is a SpatiaLite internal format. It is
documented well, but so far only SpatiaLite can handle it. This is not
optimal for my uses, because the databases will only be readable if
users use the spatialite libraries. No other system will have access
to the data. I think this is not an open system that we can rely in
the future.

Stefan.

[a.fu...@lqt.it]

Hi Stefan,

I don't understand your complaints about 'lack of compatibility':
you can easily use a wide collection of SQL functions such as:
ST_AsText(), ST_AsBinary(), ST_GeomFromText(), ST_GeomFromWKB()
in order to transform internal BLOBs to/from corresponding
standard WKB/WKT representations.

Anyway, if you *absolutly* need to store WKB or WKT geometries
in SQLite, you can then use the OGR/FDO format, corresponding
to this OsGeo RFC:
http://trac.osgeo.org/fdo/wiki/FDORfc16

SpatiaLite can support this alternative format as well,
via the VirtualFDO module:
http://www.gaia-gis.it/spatialite/spatialite-sql-2.3.1.html#p16fdo

Anyway, be aware: following the FDO/OGR way you'll loose a
lot of speed, getting a quite sluggish DB :-(

bye
Sandro

[markb]

Doesn't get much more open then that. Open licensing, broad
interoperability, and good documentation. Walks and talks like the
proverbial OSDuck, duck typing and all. This rubber ducky passes the
OS float test for sure. -Mark

[Stefan]

Hi Sandro, hi marc,

my problem here is, that I depeloped quite a similar library than
SpatiaLite, and wanted my library to access data stored by SpatiaLite.
My libaries are some sort of middleware, giving developers access to
data stored in SDE, ESRI geodatabases, MySQL, DXF, Geo-Media and so
on. Like FDO, but much more faster and easier to handle.

I use the OGC conformat WKB format to store the geometry data in a
sqlite BLOB field, i am also using the sqlite R*Tree for indexing. So
i thought it would be easy to access SpatiaLite-data without having my
libraries linked to the SpatiaLite libraries. This should result in
one library to access sqlite-databases. With SpatiaLite storing its
own format i have to ask my users: "So, you want to open an *.sqlite
file. Wich type of data is stored in it: a) WKB b) SpatiaLite". This
is a scenario I wanted to avoid.

With SpatiaLite following the OGC "Simple Feature Specification for
SQL" using tables such as geometry_columns, it would be
straightforward for me to use WKB as the binary geometry
representation.

Browsing through the documentation of spatialites binary format, i
found two arguments used:
1) directly expose explicit MBRs
2) using a weak column-type enforcement, checking the type of a BLOB.

I Think
1) The MBRs must not be stored in the blob, they are already stored in
the idx_<table-name>_<geom-col>-Tables, directly accessable by SQL.
2) The format of SpatiaLites BLOBs is quite similar to WKB and
therefor it is easy to get confused by routines interpretind the data:
the first byte is always 00, this can be interpreted as the endianess
in WKB. The second SpatiaLite byte is the endianess (00 or 01). in WKB
this is the first byte of the geomety-type beeing 00 in big-endian
systems and 01 for point in little-endian systems. The main
distinction can only be done on bytes 0, maybe 1, 38, and the last one
being FE.

So this is the long form, why I was surprised SpatiaLite no using WKB.

Stefan.

[a.furieri]

Hi Stefan,

now I can understand better what are you intersted in:
- you aren't planning to use SpatiaLite at all
- you are simply interested into reading/writing SpatiaLite's
own BLOB geometries

All rigth, there isn't nothing intrinsically wrong in your approach.
Even more: this is quite trivial to implement.

You can succesfully apply "duck typing", previously cited by Mark:

"when I see a bird that walks like a duck and swims like a duck
and quacks like a duck, I call that bird a duck"

criterion #1
using the Metadata tables layout as a signature
---------------------------------------------------
as you can easily notice, each SpatiaLite DB has to contain
'geometry_columns' and 'spatial_ref_sys' meta-tables:
and such tables has a very specific column layout.
i.e. they are quite strongly different from the
corresponding ones defined in FDO/OGR
quite strongly enough diffent to enable us in immediately
recognizing if a generic *.sqlite DB does actually
corresponds to FDO/OGR or to SpatiaLite.

criterion #2
using BLOB internal signatures
------------------------------
you've already pinpointed this by yourself ...

if a generic BLOB data satisfies the following
conditions, then it's a SpatiaLite's own Geometry:
- byte[0] == 0x00
- byte[1] == 0x00 or 0x01
- byte[38] == 0x7C
- last byte == 0x0FE
- the 32-bits int in bytes[39-42] (when parsed
accordingly to declared endiannes) has to be
one of the followings:
1, 2, 4, 5, 6, 7,
1001, 1002, 1003, 1004, 1005, 1006, 1007,
2001, 2002, 2003, 2004, 2005, 2006, 2007,
3001, 3001, 3003, 3004, 3005, 3006, 3007,
1000001, 1000002, 1000003, 1000004, 1000005, 1000006, 1000007

checking this e.g. on the first ten rows of each Geom table
doesn't takes a long time at all ...
and the probability that such signatures can be satisfied
by anything different from a genuine SpatiaLite DB are more
or less zero
[obviously, this excluding a purposely forged DB]

nothing new on this: popular and widespread formats such
as PNG, GIF, TIFF and JPEG relies upon *MagicNumber*
internal signatures, and they works perfectly well

bye,
Sandro

[Stefan]

Hi Sandro,

thanks for your reply and your time!!

The Duck type thing is hilarious! I didn't got first what Marc meant
by this.

Stefan.