TEXT vs BLOB on Android SQLite on coordinates values

Question

I have a sqlite BD with a table that contains coordinates of a large polyline (about 2000 characters). The structure of BD is:

CREATE TABLE "province" (
    `_id`   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    `_line` TEXT,
);

The format of values of _line is:

lat1,lon1;lat2,lon2;lat3,lon3;lat4,lon4 ...

A little example:

28.164033,-15.709076;28.151925,-15.699463;28.134972,-15.703583;28.121650,-15.707703;28.107115,-15.704956;28.079250,-15.713196;28.067133,-15.735168

Right now, field _line is TEXT type on SQLite Android database. In my class DAO I parse this string to an ArrayList, thats is a list of points.

My question is, it will be recomended change of _line datatype from TEXT to BLOB? Will be improve the performance?

Answer 1

In the database itself, TEXT and BLOB values are stored in exactly the same way. The only difference is the type reported to the application, or the behaviour of the built-in string functions.

However, the Android framework will automatically convert TEXT values between the database encoding (UTF-8) and the Java string encoding (UTF-16). This does not happen for BLOB values.

If you store your values as blobs, you can read them out of the database in exactly the same format in which you stored them there, but you risk that that format is UTF-16, which would waste lots of space.