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?

why not a normal way? ... second table ? TABLE province_boundary, COLUMNS: pk _id, fk province_id, lat, lng ... — Selvin
– Selvin, Commented Oct 22, 2015 at 10:59
Leave it as TEXT. BLOB stands for Binary Large Object Data (mostly used for storing pictures). — Phantômaxx
– Phantômaxx, Commented Oct 22, 2015 at 11:37

CL. · Accepted Answer · 2015-10-22 17:59:56Z

16

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.

answered Oct 22, 2015 at 17:59

CL.

182k18 gold badges241 silver badges282 bronze badges

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

2 Comments

Ewoks Over a year ago

sooo, querying a table that has 200+ rows of which every single one has last column filled with 500kb-1.5MB of text, has no performance difference if that huge text is stored as text or blob?

CL. Over a year ago

If the encoding is the same, no.

Collectives™ on Stack Overflow

TEXT vs BLOB on Android SQLite on coordinates values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related