Community

Offline database backed tile layer

3114
7
02-22-2012 04:57 AM
JozefKaslikowski
by
New Contributor
‎02-22-2012 04:57 AM
The offline abilities of the android runtime are limited to using compact caches. Even those are pretty much unusable when tiling large areas. My maps for example end up as 800+MB with thousands of files. Half the tiles are blank and completely unneeded. So I now import them into a sqlite database and throwaway all of the blank tiles, cutting my download size in half and making my file count one. As a result, the transfers of the map caches are much faster for offline use.

I can post the tile importer script also if anyone else is interested.


package com.main.utilinspect;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.util.List;

import org.codehaus.jackson.JsonFactory;
import org.codehaus.jackson.JsonParser;

import android.content.Context;
import android.util.Log;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.esri.core.internal.c.d;
import com.esri.core.internal.c.h;
import com.esri.core.internal.c.l;

import com.esri.android.map.TiledServiceLayer;

public class OfflineDbTiledLayer extends TiledServiceLayer {
 
 File workingDirectory;
 String mapDefinition;
 String databaseName;
 private SQLiteDatabase database;
 File blankImage;
 
 byte[] blankImageBytes;
 
 private final Object lock = new Object();
 
 
 private static final String TAG = "OfflineTiledLayer"; 
 

 public OfflineDbTiledLayer(Context paramContext, File workingDirectory, String mapDefinition, String databaseName)  {
  super("required");
  this.workingDirectory = workingDirectory;
  this.mapDefinition = mapDefinition;
  this.databaseName = databaseName;
  
  String databasePath = workingDirectory.getAbsolutePath() + File.separator + databaseName;
  
  this.database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS); 
  
  this.blankImage = new File(workingDirectory.getAbsolutePath() + File.separator + "blank.png");
  
  RandomAccessFile raFile = null;
  
  try {
   raFile = new RandomAccessFile(this.blankImage, "r");
   
   blankImageBytes = new byte[(int) raFile.length()];
   raFile.readFully(blankImageBytes);
   
  } catch (FileNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }  
  finally {
   if(raFile != null) {
    try {
     raFile.close();
    } catch (IOException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   }
  } 
  
  h h1 = null;
  
  try
  {
  
  JsonParser paramJsonParser = new JsonFactory()
  .createJsonParser(new File(workingDirectory.getAbsolutePath() + File.separator + mapDefinition));
  paramJsonParser.nextToken(); 
  
  h1 = h.a(paramJsonParser, "test");
  }
  catch(Exception ex){
   
  }  
  
  
  setFullExtent(h1.f());  
  setDefaultSpatialReference(h1.c());
  setInitialExtent(h1.e());
  
  l l1;
        List list;
        int i;
        double ad[] = new double[i = (list = (l1 = h1.d()).h).size()];
        double ad1[] = new double;
        for(int j = 0; j < list.size(); j++)
        {
            ad = ((d)list.get(j)).b();
            ad1 = ((d)list.get(j)).a();
        }
  
  setTileInfo(new com.esri.android.map.TiledServiceLayer.TileInfo(l1.f, ad, ad1, i, l1.c, l1.b, l1.a));
  
  super.initLayer();
        return;  
 } 
 
 private void openDatabase(){
  if(!database.isOpen()){
   String databasePath = workingDirectory.getAbsolutePath() + File.separator + databaseName;
   this.database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS); 
  }
 }
 
 private void closeDatabase(){
  if(database.isOpen()){
   this.database.close();
  }
 }

 @Override
 protected byte[] getTile(int level, int column, int row) throws Exception {
  byte[] tileImage;  
  
  Log.i(TAG, "getTile");
  
  Log.i(TAG, "getTile - retrieving tile"); 
  
  
  synchronized(lock) {
   
   Log.i(TAG, "getTile - entered synchronized block");
  
   openDatabase();  
   
   // First check to see if the tile exists in the database
   Cursor tileCursor = database.rawQuery("SELECT image FROM tiles WHERE level = " + Integer.toString(level) + " AND row = " + Integer.toString(row) + " AND column = " + Integer.toString(column), null);
   
   if(tileCursor != null && tileCursor.getCount() > 0) {
    tileCursor.moveToFirst();
    tileImage = tileCursor.getBlob(0);
    Log.i(TAG, "getTile - tile found, returning image");      
   }
   else {
    // The tile does not exist in the database, read the blank placeholder tile and serve it
    tileImage = blankImageBytes;
    Log.i(TAG, "getTile - tile not found returning blank");
   } 
   
   tileCursor.close();  
   this.database.close();
  }
  
  Log.i(TAG, "getTile - exited synchronized block");
  
  return tileImage; 
 }

 

}
0 Kudos
7 Replies
hermancheah
by
New Contributor
‎03-01-2012 04:12 PM
The offline abilities of the android runtime are limited to using compact caches. Even those are pretty much unusable when tiling large areas. My maps for example end up as 800+MB with thousands of files. Half the tiles are blank and completely unneeded. So I now import them into a sqlite database and throwaway all of the blank tiles, cutting my download size in half and making my file count one. As a result, the transfers of the map caches are much faster for offline use.

I can post the tile importer script also if anyone else is interested.


package com.main.utilinspect;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.util.List;

import org.codehaus.jackson.JsonFactory;
import org.codehaus.jackson.JsonParser;

import android.content.Context;
import android.util.Log;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.esri.core.internal.c.d;
import com.esri.core.internal.c.h;
import com.esri.core.internal.c.l;

import com.esri.android.map.TiledServiceLayer;

public class OfflineDbTiledLayer extends TiledServiceLayer {
    
    File workingDirectory;
    String mapDefinition;
    String databaseName;
    private SQLiteDatabase database;
    File blankImage;
    
    byte[] blankImageBytes;
    
    private final Object lock = new Object();
    
    
    private static final String TAG = "OfflineTiledLayer";    
    

    public OfflineDbTiledLayer(Context paramContext, File workingDirectory, String mapDefinition, String databaseName)  {
        super("required");
        this.workingDirectory = workingDirectory;
        this.mapDefinition = mapDefinition;
        this.databaseName = databaseName;
        
        String databasePath = workingDirectory.getAbsolutePath() + File.separator + databaseName;
        
        this.database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);    
        
        this.blankImage = new File(workingDirectory.getAbsolutePath() + File.separator + "blank.png");
        
        RandomAccessFile raFile = null;
        
        try {
            raFile = new RandomAccessFile(this.blankImage, "r");
            
            blankImageBytes = new byte[(int) raFile.length()];
            raFile.readFully(blankImageBytes);
            
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
        finally {
            if(raFile != null) {
                try {
                    raFile.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }    
        
        h h1 = null;
        
        try
        {
        
        JsonParser paramJsonParser = new JsonFactory()
        .createJsonParser(new File(workingDirectory.getAbsolutePath() + File.separator + mapDefinition));
        paramJsonParser.nextToken();    
        
        h1 = h.a(paramJsonParser, "test");
        }
        catch(Exception ex){
            
        }        
        
        
        setFullExtent(h1.f());        
        setDefaultSpatialReference(h1.c());
        setInitialExtent(h1.e());
        
        l l1;
        List list;
        int i;
        double ad[] = new double[i = (list = (l1 = h1.d()).h).size()];
        double ad1[] = new double;
        for(int j = 0; j < list.size(); j++)
        {
            ad = ((d)list.get(j)).b();
            ad1 = ((d)list.get(j)).a();
        }
        
        setTileInfo(new com.esri.android.map.TiledServiceLayer.TileInfo(l1.f, ad, ad1, i, l1.c, l1.b, l1.a));
        
        super.initLayer();
        return;        
    }    
    
    private void openDatabase(){
        if(!database.isOpen()){
            String databasePath = workingDirectory.getAbsolutePath() + File.separator + databaseName;
            this.database = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);    
        }
    }
    
    private void closeDatabase(){
        if(database.isOpen()){
            this.database.close();
        }
    }

    @Override
    protected byte[] getTile(int level, int column, int row) throws Exception {
        byte[] tileImage;        
        
        Log.i(TAG, "getTile");
        
        Log.i(TAG, "getTile - retrieving tile");    
        
        
        synchronized(lock) {
            
            Log.i(TAG, "getTile - entered synchronized block");
        
            openDatabase();        
            
            // First check to see if the tile exists in the database
            Cursor tileCursor = database.rawQuery("SELECT image FROM tiles WHERE level = " + Integer.toString(level) + " AND row = " + Integer.toString(row) + " AND column = " + Integer.toString(column), null);
            
            if(tileCursor != null && tileCursor.getCount() > 0) {
                tileCursor.moveToFirst();
                tileImage = tileCursor.getBlob(0);
                Log.i(TAG, "getTile - tile found, returning image");                        
            }
            else {
                // The tile does not exist in the database, read the blank placeholder tile and serve it
                tileImage = blankImageBytes;
                Log.i(TAG, "getTile - tile not found returning blank");
            }    
            
            tileCursor.close();        
            this.database.close();
        }
        
        Log.i(TAG, "getTile - exited synchronized block");
        
        return tileImage;    
    }

    

}



Hi Jeff
can i have a look at the importer script please.
0 Kudos
JozefKaslikowski
by
New Contributor
‎03-02-2012 09:10 AM
Hi Jeff
can i have a look at the importer script please.



This is the python script that does 

#This script updates all tiles in an ArcGIS Server 10 map cache

import datetime
import os
import sqlite3
import shutil

# Access the geoprocessing tools
import arcgisscripting

root = "\\\\server\drive$\\arcgisserver\\arcgiscache\\inspections\\Layers\\_alllayers"

web_path = "\\\\server\\drive$\\inetpub\\wwwroot\\download\\"

print "Started Cache Regeneration - " + str(datetime.datetime.now())


gp = arcgisscripting.create()

# Set up all of the variables for the update tool
server_name = "server"
object_name = "inspections"
data_frame = "Layers"
layers = ""
constraining_extent = ""
scales = "1000;2000;4000;8000;16000;32000;64000;128000;256000"
update_mode = "Recreate All Tiles"
thread_count = "7"
antialiasing = "NONE"

# Run the Update Map Server Cache tool
try:
    #print 'Starting Cache Update'
    gp.UpdateMapServerCache(server_name, object_name, data_frame, layers, constraining_extent, scales, update_mode, thread_count, antialiasing)
    #print 'Finished Cache Update'

# Get the error messages if the tool fails
except:
    gp.AddMessage(gp.GetMessages(2))
    print gp.GetMessages(2)

print "Completed Cache Regeneration - " + str(datetime.datetime.now())

levels = os.listdir(root)

files_deleted = 0

for level in levels:
    rows = os.listdir(root + "\\" + level)

    for row in rows:
        images = os.listdir(root + "\\" + level + "\\" + row)

        for image in images:
            image_path = root + "\\" + level + "\\" + row + "\\" + image
            image_stats = os.stat(image_path)
            #print image + " - " + str(image_stats.st_size) + " bytes"

            if image_stats.st_size <= 1146: #638
                os.remove(image_path)
                files_deleted += 1

            if (files_deleted % 1000) == 0:
                print "deleted " + str(files_deleted)
                
print "deleted " + str(files_deleted) + " files, " + str((files_deleted * 1146.0) / 1048576) + " MB"

print "Completed Empty File Pruning - " + str(datetime.datetime.now())

connection = sqlite3.connect("tilestore.sqlite")

cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS \"tiles\"")
connection.commit()
cursor.execute("CREATE TABLE \"tiles\" (\"level\" INTEGER NOT NULL , \"row\" INTEGER NOT NULL , \"column\" INTEGER NOT NULL , \"image\" BLOB, PRIMARY KEY (\"level\", \"row\", \"column\"))")
connection.commit();

levels = os.listdir(root)

for level in levels:
    level_number = int(level[1:], 16)
    rows = os.listdir(root + "\\" + level)
    #print("level - " + level)

    for row in rows:
        row_number = int(row[1:], 16)
        #print row_number
        images = os.listdir(root + "\\" + level + "\\" + row)

        for image in images:
            column_number = int(image[1:-4], 16)
            #print "\t" + str(column_number) + " - " + image
            image_path = root + "\\" + level + "\\" + row + "\\" + image            
            image_file = open(image_path, "rb")
            binary = sqlite3.Binary(image_file.read())            
            cursor.execute("INSERT INTO tiles (level, row, [column], image) VALUES (?, ?, ?, ?)", (level_number, row_number, column_number, binary))
            

connection.commit()
cursor.close()
print "Completed Building Tile Cache - " + str(datetime.datetime.now())

print "Starting File Transfer - " + str(datetime.datetime.now())
shutil.copy("tilestore.sqlite", web_path + "tilestore.sqlite.zip")
print "Completed File Transfer - " + str(datetime.datetime.now())
0 Kudos
MarkusUntera
by
New Contributor
‎03-30-2012 01:32 AM
Hi,

I'm new to Android. I rewrote your class a little, because I need to use tile files directly.
Though I can't get it to work. When debugging program inserts to "public OfflineDbTiledLayer()" function. It doesn't insert to "protected byte[] getTile()". Should it?
Is it possible you could send some sample project. I would immensely appreciate.

Regards,

Markus
0 Kudos
JozefKaslikowski
by
New Contributor
‎04-05-2012 12:29 PM
Hi,

I'm new to Android. I rewrote your class a little, because I need to use tile files directly.
Though I can't get it to work. When debugging program inserts to "public OfflineDbTiledLayer()" function. It doesn't insert to "protected byte[] getTile()". Should it?
Is it possible you could send some sample project. I would immensely appreciate.

Regards,

Markus


I don't think you can see it break into that code since that is called by the ESRI internal classes.
0 Kudos
LukeCatania
by
New Contributor III
‎04-12-2012 05:49 AM
Is the script actually importing the Compact Cache Bundle Files into an SQLite DB or is it extracting the images (essentially the exploded format) in the bundle and putting those files in the DB.  That is, would I need to create the cache in exploded format in order to import the files with your script. Can you provide some timing info?  How long does it take to import for a particular size cache?  Is viewing the tiles as fast and smooth as it is with the ArcGISLocalTiledLayer class?
0 Kudos
JozefKaslikowski
by
New Contributor
‎04-20-2012 05:20 AM
Is the script actually importing the Compact Cache Bundle Files into an SQLite DB or is it extracting the images (essentially the exploded format) in the bundle and putting those files in the DB.  That is, would I need to create the cache in exploded format in order to import the files with your script. Can you provide some timing info?  How long does it take to import for a particular size cache?  Is viewing the tiles as fast and smooth as it is with the ArcGISLocalTiledLayer class?



It uses an exploded cache since there wouldn't be any benefit of creating the compact cache only to rip it back apart. It takes a few minutes, probably less than 20 to build the entire sqlite db. I have noticed no difference at all in speed versus a local tiled layer.
0 Kudos
LukeCatania
by
New Contributor III
‎05-09-2012 07:30 AM
It uses an exploded cache since there wouldn't be any benefit of creating the compact cache only to rip it back apart. It takes a few minutes, probably less than 20 to build the entire sqlite db. I have noticed no difference at all in speed versus a local tiled layer.


Have you updated the code to the latest release 1.1?  I get an error that cannot resolve com.esri.core.internal.c.l
0 Kudos