tom‎ > ‎

androidufo

Ufo Sightings Android App README











This file discusses how to create the Ufo Sightings Android 
application.  This application's apk file is 52 Megabytes 
and therefore is too big to upload to Google Play.  Most of 
this README discusses the imperfect creation of the SQLite 
database from the data text file.  That data file was made 
by someone else, Ganglion, and is available on the 
Infochimps.com web site.  The Java source code used to 
create the app is not discussed much here.  

#############################################################
The script ufoTableMakerSqlite4.txt contains sqlite3 
commands that will run and will create the 
table, read the data in the data file, create 
a few indexes. 

Make it like this, all on one line:

cat  ufo_awesome.tsv |sed 's/^0000/00000000/'|
sed 's/\(....\)\(..\)\(..\)/\1\t\2\t\3/'|  
sed 's/^/INSERT INTO ufosightings (sighted_at_year, 
sighted_at_month, sighted_at_day, reported_at, location, 
shape, duration, description) VALUES (/'|
sed "s/$/');/"|sed "s/\t/','/g"|sed "s/','/,/"|sed "s/'//"|
sed "s/'//"|sed "s/'//"|sed "s/'//"|sed "s/'//" > ufo_awesome.sql

...or instead, like this to split the city and state out of location:

 cat ufo_awesome.tsv |sed 's/^0000/00000000/'|
sed 's/\(....\)\(..\)\(..\)/\1\t\2\t\3/'|  
sed 's/^/INSERT INTO ufosightings (sighted_at_year, sighted_at_month, 
sighted_at_day, reported_at, location, shape, duration, description) 
VALUES (/'|sed "s/$/');/"|sed "s/\t/','/g"|sed "s/','/,/"|sed "s/'//"|
sed "s/'//"|sed "s/'//"|sed "s/'//"|sed "s/'//"|
sed "s/,' \([^,]*\), \([^']*\)','/,' \1','\2','/" > ufo_awesome.sql

...there's alot of strangeness in there, like, for example, the 
"""SPACEcityname,statename""" always has a leading space and always 
has a comma.  Foreign countries have a location value that ENDS with a 
comma like: """(India),""", except for Canada which looks like 
"""(Canada), ON""" with trailing province code.  Also notice that some 
sighted_at values have """0000""" which sed extends to 8 characters so 
that the subsequent sed can split out the year month day for every record.  

The database can be created by running the code in the file 
named ufoTableMakerSqlite3.txt, shown here, with the just 
create database populator file we just created, named 
ufo_awesome.sql, in the current directory:
#############################################################
CREATE TABLE ufosightings (sighted_at_year integer default null, 
sighted_at_month integer default null, sighted_at_day integer default null,  
reported_at integer default null,  location_city text default null, 
location_state text default null, shape text default null,  
duration text default null,  description text default null); 

.read ufo_awesome.sql

CREATE INDEX idxsighted_at_year ON ufosightings(sighted_at_year);
CREATE INDEX idxsighted_at_month ON ufosightings(sighted_at_month);
CREATE INDEX idxsighted_at_day ON ufosightings(sighted_at_day);
CREATE INDEX idxlocation_city ON ufosightings(location_city);
CREATE INDEX idxlocation_state ON ufosightings(location_state);
CREATE INDEX idxdescription ON ufosightings(description);
#############################################################

The resulting sqlite3 database is suitable for 
use with an Android application.  

To turn the text files into an sqlite3 database, 
run this command with everything in the current 
directory:

sqlite3 ufos < ufoTableMakerSqlite3.txt

Lots of errors will stream by indicating a problem 
with that particular line in the text file.  Fix 
them or try again.  The number of records you have 
can be found like this:

sqlite3 ufos
> select count(*) from ufosightings;

...and should match the number of lines in the 
database populator (SQL INSERTS, one per line) 
which you can find like this:

cat ufo_awesome.sql |wc

The resulting sqlite3 database, named ufos, is 
suitable for use in an Android application by 
placing the file in the "assets" directory of the 
Android application. 

Test out the database like this:

sqlite3 ufos
.help
.schema
select count(*) from ufosighting;
.quit

This png file looks good in android in res/drawable-mdpi
ba6e5cf41991fa7cc6e8c1ad391aea9a  ../alien.png

Unfortunately, the application file zoomed to 52 megabytes 
and cannot be uploaded to Google Play.  Efforts to shrink 
the size have been unsuccessful.  Bah!

ċ
Ufosightings.tar.gz
(29k)
Thomas Maher,
Oct 8, 2013, 9:28 AM
Comments