Decompressing single blob in SQLite DB using C#

Question

Inside of my .mbtiles SQLite db, there's a column called tile_data holding the information relating to the specific tile that is being queried.

My application is taking a user long/lat as an input and then converting to a tile_row and tile_column equivalent values, then pulling the single blob with the data inside via the SQL query below.

The query (fully functional, just to show the code) is:

string query =
   String.Format("SELECT tile_data from tiles WHERE zoom_level = {0} AND tile_row = {1} AND tile_column = {2}", ZoomLevel, _merc.TileLat, _merc.TileLong);
   SQLiteCommand command = new SQLiteCommand(query, DbConn);
   SQLiteDataReader reader = command.ExecuteReader();

          while (reader.Read())
                {
                    // ...
                }

With the result being a single blob file, querying SELECT * will pull back the single tile with all data.

The blob inside of tile_data is a compressed (gzip) vector file that I need to access and work with, but I can't figure out how to decompress it within my application. Most gzip links that I've read through have been regarding file compression and outputting it to a .txt (or similar), whereas I'm looking to use this data in memory without needing an external file.

How can I decompress the blob and access he data in memory, rather than an external file?

You should be able to pass the data to a MemoryStream, then pass that to a GZipStream with CompressionMode.Decompress then pass that to a StreamReader. — juharr
– juharr, Commented Jul 1, 2016 at 12:10
You could use SqlDataReader.GetStream to allow you to pull in the data in chunks instead of loading the entire file into a MemoryStream. — juharr
– juharr, Commented Jul 1, 2016 at 12:22

juharr · Accepted Answer · 2016-07-01 12:27:04Z

2

Try something like this

SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    using(var file = reader.GetStream(0))
    using(var unzip = new GZipStream(file, CompressionMode.Decompress))
    using(var fileReader = new StreamReader(unzip))
    {
        while(!fileReader.EndOfStream)
        {
            var line = fileReader.ReadLine();
        }
    }
}

Depending on exactly what your file is you might need to substitute a different type of reader.

answered Jul 1, 2016 at 12:27

juharr

32.4k4 gold badges60 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Decompressing single blob in SQLite DB using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related