Thanks for all your replies. Please let me clarify:
I need to do certain range operations efficiently and I prefer SQL
functionality, plus SQLite gives a cross platform file storage format. The
table having 3.2 megabytes is just an example. I am storing much more
(orders of 10^5 of points) however the effect is similar.
When I store several tables side by side the effect seems to be worse (I
assume SQLite leaves the possibility for inserts and hence needs to have
some space to do them efficiently, please correctly if I am wrong). SQLite
forums suggest to store tables in separate files to avoid vacuum space in
between the tables, are there any other methods to save space?
On Wed, Jun 10, 2015 at 3:51 PM Hick Gunter <hick at scigames.at> wrote:
> Apart from the fact that latitude and longitude have defined ranges +-90
> and +-180 respectively, why are you choosing SQLite as the storage format
> for densely populated single table of constant <whatever> if all you want
> to do is read the values?
>
> If you are looking for a minimum space, uncompressed format you might as
> well memory-map the C structure.
>
> typedef struct point_t {
> double lat,lon;
> };
>
> typedef struct table_t {
> uint64_t cnt;
> point_t pnt[1];
> };
>
>
>
>
> -----Urspr?ngliche Nachricht-----
> Von: Igor Stassiy [mailto:istassiy at gmail.com]
> Gesendet: Mittwoch, 10. Juni 2015 14:45
> An: sqlite-users at mailinglists.sqlite.org
> Cc: Kirill Afanasev
> Betreff: [sqlite] static sqlite database
>
> Hello,
>
> I have a question about controlling the size of sqlite database
> containing records of the form id -> payload where payload has fixed size
> and id is an integer column.
>
> I am developing a simple storage for points in an sqlite database,
> the problem is that space consumption on disk is slightly unsatisfying.
> Here is what I mean:
>
> A program to generate some data for the database:
>
> #include <cstdlib>#include <cstdint>#include <iostream> using
> namespace std;
>
> int main() {
> int n;
> cin >> n;
>
> for(int i = 0; i < n; i++) {
> double lat = 180*(rand()*1.0/RAND_MAX);
> double lon = 180*(rand()*1.0/RAND_MAX);
> int64_t id = rand()*1LL*rand();
> cout << id << "," << lat << "," << lon << "\n";
> }
>
> return 0;}
>
> To import the data run:
>
> g++ filldb.cpp
> echo 100000 | ./a.out > db.text
> sqlite3 test.db
> sqlite> create table points(id UNSIGNED INT, lat REAL, lon REAL);
> sqlite> .separator , .mode csv .import db.text points
>
> The size of the database right after this shows 3.3M, however the
> "real"
> size of data is 100000*(8 bytes)*3 = 2.4M and after
>
> sqlite> create index points_index on points(id);
>
> the database size more than doubles to 5.1M
>
> Changing id UNSIGNED INT to INTEGER PRIMARY KEY does help, but is
> there anything else to be done, in particular when the database is static
> (we know there wont be any inserts)?
>
> Thank you
>
> Igor
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org<mailto:
> sqlite-users at mailinglists.sqlite.org>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
> Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the
> use of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
