Thursday 18 September 2008

Storing GPS points in a SQLite DB

Now that I managed to get data from a mock provider, the next task was to store the data in a database, so that I have a rudimentary GPS data logger.

There are two approaches for this: one is to simply write the stuff into a DB (or a file), the other is to implement a ContentProvider. The second approach is a bit more sophisticated, but it is where I will be heading anyway, plus there seem to be more working examples on the net.

Google provides the Notepad application as an example for a ContentProvider and I started with that code, chopping and changing as I went along. There is also a step by step guide in the online documentation.

I left most of the methods of the ContentProvider empty at the moment, only implementing the insert method, so I have a write-only, read-never DB, which is not terribly useful. Getting the data out will be the next step.
This is how the ContentProvider code looks right now:
package com.ucont;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

public class GPSDataContentProvider extends ContentProvider {

private static final String TAG = "GPSDataContentProvider";

private static final String DATABASE_NAME = "gpsdata.db";
private static final int DATABASE_VERSION = 2;
private static final String POINT_TABLE_NAME = "gpspoints";

public static final String AUTHORITY = "com.ucont";
public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/gpspoint");

/**
* This class helps open, create, and upgrade the database file.
*/
private static class DatabaseHelper extends SQLiteOpenHelper {

DatabaseHelper(Context context, String name) {
super(context, name, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
try {
Log.i(TAG, "Creating table " + POINT_TABLE_NAME);
db.execSQL("CREATE TABLE " + POINT_TABLE_NAME + " ("
+ GPSData.GPSPoint._ID + " INTEGER PRIMARY KEY,"
+ GPSData.GPSPoint.LATITUDE + " REAL,"
+ GPSData.GPSPoint.LONGITUDE + " REAL,"
+ GPSData.GPSPoint.TIME + " INTEGER"
+ ");");
} catch (SQLiteException e) {
Log.e(TAG, e.toString());
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + POINT_TABLE_NAME);
onCreate(db);
}
}

private DatabaseHelper mOpenHelper;

public boolean onCreate() {
mOpenHelper = new DatabaseHelper(getContext(),DATABASE_NAME);
return true;
}


@Override
public int delete(Uri arg0, String arg1, String[] arg2) {
// TODO Auto-generated method stub
return 0;
}


@Override
public String getType(Uri uri) {
Log.i(TAG, "getting type for " + uri.toString());
// TODO Auto-generated method stub
return null;
}


@Override
public Uri insert(Uri uri, ContentValues values) {
Log.e(TAG, "inserting value " + values.toString());

SQLiteDatabase db = mOpenHelper.getWritableDatabase();
long rowId = db.insert(POINT_TABLE_NAME, "", values);
if (rowId > 0) {
Uri noteUri = ContentUris.withAppendedId(GPSDataContentProvider.CONTENT_URI, rowId);
getContext().getContentResolver().notifyChange(noteUri, null);
return noteUri;
}

throw new SQLException("Failed to insert row into " + uri);
}


@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
// TODO Auto-generated method stub
return null;
}


@Override
public int update(Uri uri, ContentValues values, String selection,
String[] selectionArgs) {
// TODO Auto-generated method stub
return 0;
}
}



I was a bit stumped for a while that the onCreate call for the DB, which creates the table was called whenever I started the app. This is a topic also covered in the newsgroups, but I think that debate is a bit a red herring. I have not 100% pinned it down, but I have the feeling that the onCreate is called whenever my app has been recompiled and is in this sense a new app. Now that I am stable with this bit of code, the onCreate call is not fired every time. Nevertheless I have the create call in a try catch block, to trap the recreation of the table.

The other thing I am not 100% sure about is the second parameter to the insert method, a string. I have for now just left it empty, expecting an exception, but I do not get one. There might be something lurking there.

The other class I have is for the table definition, here in a class called GPSData:

package com.ucont;

import android.net.Uri;
import android.provider.BaseColumns;

public final class GPSData {
public static final String AUTHORITY = "com.ucont";

// This class cannot be instantiated
private GPSData() {}

/**
* GPS data table
*/
public static final class GPSPoint implements BaseColumns {
// This class cannot be instantiated
private GPSPoint() {}

/**
* The content:// style URL for this table
*/
public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/gpspoint");

/**
* The MIME type of {@link #CONTENT_URI} providing a track (list of points).
*/
public static final String CONTENT_TYPE = "mime/text";

/**
* The MIME type of a {@link #CONTENT_URI} sub-directory of a single point.
*/
public static final String CONTENT_ITEM_TYPE = "";

/**
* The default sort order for this table
*/
public static final String DEFAULT_SORT_ORDER = "modified DESC";

public static final String LONGITUDE = "longitude";
public static final String LATITUDE = "latitude";
public static final String TIME = "time";
}
}


This was mostly copied from the notepad application and as I am not yet taking any data out all the content type definitions are not right. The only stuff used pretty much are the column names, latitude etc.

Now how is all this called?

My old onLocationChange in my main activity now stores the new location like this:

public void onLocationChanged(Location loc) {
Log.e(TAG, loc.toString());

ContentValues values = new ContentValues();

Double lon = loc.getLongitude();
Long time = loc.getTime();
values.put(GPSData.GPSPoint.LONGITUDE, loc.getLongitude());
values.put(GPSData.GPSPoint.LATITUDE, loc.getLatitude());
values.put(GPSData.GPSPoint.TIME, loc.getTime());
getContentResolver().insert(GPSDataContentProvider.CONTENT_URI, values);
}

The next steps will be to get the data out again and to store them in a GPX file on the SD card. Only once that is done can I be sure that the data is actually in the DB.

8 comments:

g2d said...

Hi! Thanks for your snippets! Great Job!

I make an intensive use of SQLite in my c++ apps and to check if a table exists I have this function :

bool
Database::TableExists( const string & name ) {
bool result = false;

try {
ostringstream sql;

sql << "SELECT COUNT(name) FROM sqlite_master ";
sql << "WHERE type='table' AND ";
sql << "name=\'" << name.c_str() << "\'";

result = ( m_db->execScalar(sql.str().c_str()) > 0 );
} catch ( CppSQLite3Exception& ex ) {
result = false;
cerr << ex.errorCode() << ":" << ex.errorMessage() << endl;
}
return result;
}


I think that you could implement this function in Java in your onCreate(...) method...

Kind regards,

luca

Marcus said...

Hi,

I have been using parts of your code for a project I am working on, however, I am receiving an exception and the program crashes.
I have checked the logcat and apparently it is finding an error:

"Failed to find provider info for package.name"

The following line seems to be making the error appear, the program can run normally without it but it does not enter any details into the database:

"getContentResolver().insert(GPSDataContentProvider.CONTENT_URI, values);"

Thanks,
Marcus

Marcus said...

Hi,

I have been using parts of your code for a project I am working on, however, I am receiving an exception and the program crashes.
I have checked the logcat and apparently it is finding an error:

"Failed to find provider info for package.name"

The following line seems to be making the error appear, the program can run normally without it but it does not enter any details into the database:

"getContentResolver().insert(GPSDataContentProvider.CONTENT_URI, values);"

Thanks,
Marcus

Ludwig said...

What you post here is not the actual error, it is a consequence of another one. You need to look at your stack when the exception occurs. Without posting more info I cannot help you.
[Googling your error message throws up this:
http://groups.google.com/group/android-beginners/browse_thread/thread/50b7aed7fab7381?pli=1

This post is relating to a similar error someone else had. Your bug is likely to be different, of course.]

Marcus said...

Ludwig,

I have provided an image of the entire logcat output if you wouldn't mind looking.

http://i50.tinypic.com/33e0h6w.jpg

Thanks

Marcus

Ludwig said...

I think your trace shows quite clearly that the problem lies at the point where you try to find your ContentProvider through the ContentResolver. There you get an IllegalArgumentException because
'content://james.android....' cannot be found. I guess you have not declared this correctly in the Manifest.

Check your Android Manifest and maybe just try first with a very simple ContentProvider. This error has nothing to do with my code examples. Only once you manage to get into the ContentProvider you have reached my code.

Marcus said...

From the tutorials I have read I didn't realise the content provider needs to be declared in the manifest. The only permission declarations I have are for the GPS and Internet processes.

for example:

Jahmelon said...

I have found it more usefull to store lat long as an object or comma delimited string within one record of an SQLite database rather than one record for each coordinate. Eventually you are going to want to perform a query for all the records in the Coordinate table in order to get all your coordinates. This is WAY slower than looping through a split comma delimted string. It might not make a difference for a couple hundred points, but if you have 1000+, its quicker