Zipcode database

This app displays an SQLite database of 41,755 records in a ListView. The database file is not created by the app. We create the database file by hand with sqlite3 and insert it into the app.

Source code in Zip.zip

In Android 4.*, the database file no longer has to be split into three separate files as in Android 2.*. But it still has to be copied into the /data/data/edu.nyu.scps.zip/databases directory. I made the helper smart enough to copy the file only once.

Create the database file

Create an sqlite database file named zipcode.sqlite from zip5.csv using the .import command we saw here. zip5.csv has six fields, but the zipcodes table in zipcode.sqlite must also have the _id field. Here’s how to create the additional field. We also create one more table, named android_metadata.

sqlite3 zipcode.sqlite

sqlite> CREATE TABLE android_metadata (
"locale" TEXT DEFAULT "en_US"
);

sqlite> .tables

sqlite> INSERT INTO android_metadata (locale) VALUES ("en_US");
sqlite> .dump android_metadata

sqlite> CREATE TABLE zipcodes (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
zipcode INTEGER,
city TEXT,
state TEXT,
latitude REAL,
longitude REAL,
county TEXT
);

sqlite> CREATE TABLE dummy (
zipcode INTEGER,
city TEXT,
state TEXT,
latitude REAL,
longitude REAL,
county TEXT
);

sqlite> .tables
sqlite> .dump zipcodes
sqlite> .dump dummy

sqlite> .mode csv
sqlite> .separator ','
sqlite> .show

sqlite> .import zip5.csv dummy

sqlite> SELECT count(*) FROM dummy;
41755

sqlite3> SELECT * FROM dummy WHERE zipcode == 10003;
10003,"New York",NY,40.731253,-73.989223,"New York"

sqlite> INSERT INTO zipcodes (zipcode, city, state, latitude, longitude, county)
SELECT * FROM dummy;

sqlite> SELECT count(*) FROM zipcodes;
41755

sqlite3> SELECT * FROM zipcodes WHERE zipcode == 10003;
3227,10003,"New York",NY,40.731253,-73.989223,"New York"

sqlite> DROP TABLE dummy;
sqlite> VACUUM;

sqlite> .quit

VACUUM reduced the zipcode.sqlite file from 4,257,792 bytes to 2,154,496 bytes.

Split the database file

We want to copy zipcode.sqlite into the assets folder of our project. But the maximum size of a file that can be read in this folder is 1,048,576 bytes == 2²⁰ bytes == 1 megabyte and zipcode.sqlite is 2,154,496 bytes. See this link. The LogCat pane of the Eclipse Debug perspective said

11-12 12:32:48.458: DEBUG/asset(615): Data exceeds UNCOMPRESS_DATA_MAX (2154496 vs 1048576)

We can split the file into smaller files using split on Mac and other Unixes. How would we do this on Windows?

bc
scale = 10
2154496 / 3
718165.3333333333
control-d

split -b 718166 zipcode.sqlite
ls -l xaa xab xac
-rw-r--r--  1 nyuuser  staff  718166 Dec 20 13:41 xaa
-rw-r--r--  1 nyuuser  staff  718166 Dec 20 13:41 xab
-rw-r--r--  1 nyuuser  staff  718164 Dec 20 13:41 xac

Rename the files to make it easier for Java to loop through their names:

mv xaa xa0
mv xab xa1
mv xac xa2
ls -l xa0 xa1 xa2

Create list_item.xml

Highlight the layout folder in the Package Explorer.
File → New → File

File name: list_item.xml
Finish

Create the assets folder

The root folder of your project should already have subfolders named src, gen, res, etc. Give the root folder an aditional subfolder named assets if it doesn’t already have one. In the Package Explorer of the Eclipse Java perspective, select the root folder of your project. Then
File → New → Folder Enter or select the parent folder: (the root folder of your project) Folder name: assets Finish
Then drag the three files xa0, xa1, xa2 into the assets folder in the Package Explorer.
Select how files should be imported into the project: • Copy files OK

adb shell
# pwd
# ls -l

# cd /data/data/edu.nyu.scps.list/databases
# pwd
# ls -l

Increase the partition size of the emulator

The Console pane of the Java perspective in Android 4.* said

[2011-12-20 13:28:57 - Zip] Installation error: INSTALL_FAILED_INSUFFICIENT_STORAGE
[2011-12-20 13:28:57 - Zip] Please check logcat output for more details.
[2011-12-20 13:28:57 - Zip] Launch canceled!

Quit (i.e., kill) the emulator. Go to the Java perspective of Eclipse, pull down the Run menu, and select Run Configurations…. In the left pane, under Android Application, select Zip. In the right pane, under the Target tab, add the following emulator command line option (measured in megabytes). Press the Apply and Run buttons.

-partition-size 1024

Things to try

Change the SELECT statement in the getCursor method in Helper.java to one of the following. WHERE will probably cause the position and id arguments passed to onItemClick in ZipcodeActivity.java to be different. See SQLiteQueryBuilder.

SELECT * FROM zipcodes;
SELECT * FROM zipcodes ORDER BY zipcode;
SELECT * FROM zipcodes ORDER BY zipcode DESC;
SELECT * FROM zipcodes ORDER BY city;

SELECT * FROM zipcodes WHERE longitude < -170;
SELECT * FROM zipcodes WHERE 39.75 < latitude AND latitude < 40.25;
SELECT * FROM zipcodes WHERE abs(latitude - 40) <= .25;
SELECT * FROM zipcodes WHERE city == 'Yonkers';
SELECT * FROM zipcodes WHERE city == county;

SELECT * FROM zipcodes WHERE city LIKE '%x%';
SELECT * FROM zipcodes WHERE city LIKE '% % % %';

Can the ListView display the _id field too?

What goes wrong if the cursor doesn’t SELECT the _id field?

The Activity’s onCreate method called setTextFilterEnabled. The text that we type on top of the ListView is displayed, but it doesn’t do anything. To enable text filtering, our adapter would have to implement the Filterable interface.

It takes several seconds to read in the database file. Do it in a separate thread.