A little data handling, csv table data to SQLite database

Thought I’d post a little step-by-step on how I imported the .csv table data from the Open Data Enschede into an SQLite database and prepare it for my EnschedeLocator. This is mostly for my own sake, so that I have it as a reference, but it might also be of interest to others.

Address data set [.csv 8.3MB]

Store your data set in a working folder. I am on a Mac and have SQLite3 installed and will use the terminal. The following steps on Windows and Linux should be similar.

The first thing I do is to open the file in Excel and remove the columns I don’t need. I only need the street name, house number, latitude and longitude. Delete the other columns and save. You don’t need the first row with the column names, so just delete that too.

Then insert a new column in front of street name. Here we will keep each address’ primary key. Just use the formula =ROW() and double-click the bottom right corner of the box to apply to all cells in that column. Save the .csv file. It should now be about 3.8 MB.

Then open Terminal and move to your working folder. Create your SQLite database by executing:

[code]
sqlite3 databasename.db
[/code]

To make the database Android friendly, you have to create a new table called android_metadata and fill it with some columns. Then create a table for the addresses.

[code lang="sql"]
CREATE TABLE "android_metadata"("locale" TEXT DEFAULT 'en_US');
CREATE TABLE "addresses"(_id integer primary key,
straatnaam text, huisnummer integer, latitude double, longitude double);
[/code]

Now we’re gonna use the import the data.

[code lang="sql"]
.separator ","
.import enschede_addresses.csv addresses
[/code]

And that should be enough. Note that if you have a .tsv-file or something else, you need to change the .separator command accordingly.

The first time I tried, I only got the first row imported. This is due to the line-break format of the csv-file, SQLite requires the LF-format. This can be fixed in a text editor, such as TextWrangler.

If this happened to you too, run a DELETE FROM ADDRESSES; to erase the data you imported, change the line break format and import it again.

Here are the complete steps in terminal:

Due to removing some columns in the dataset, the database now contains some redundant entries, and since we’re storing the data locally on the device, we want to minimize the size.

Here is one way of removing all but one row containing the same street name and house number:

[code lang="sql"]
DELETE FROM addresses WHERE _id NOT IN(SELECT MIN(_id) FROM addresses GROUP BY straatnaam, huisnummer);
vacuum;
[/code]

Only deleting rows doesn’t reduce the file size of the db, to do so, run the vacuum command in addition. The size of the database now went from 3.8 to 3.1 MB.

That’s it! The database file is now ready to be read by an Android application.

[App] EnschedeLocator Beta, Offline map and simple navigation in Enschede for Android

Background

Two weeks ago, a friend and I visited the OpenData Enschede Hackaton. The municipality has a project making a platform for serving data to the public, an overview of the datasets can be found here. I sat down with the address dataset and converted it into an SQLite database, and started working. Now I feel like the app is ready for trials, so I am making the  beta available here. You need Android 2.3 or higher (due to restrictions in the database size in the earlier versions) to run it and the app itself requires 6.6 MB and it also stores the map on your sdcard, taking up 1.93 MB there.

Download EnschedeLocator Beta [.apk]

I’m not going to monitor how many people download it, but if you do and have some notes, tips, flattering or offensive remarks, please let me know in the comments, twitter, email or any way you see fit.

I have tested this app with my own Samsung Galaxy S I9000, if you use a different phone and experience strange behaviour, please let me know.

Description

The app features a map and address database stored locally on the user’s device, allowing for simple offline navigation in Enschede.

Screens

Usage

  1. Start typing in street name. Suggestions for streets pops up, select your destination street.
  2. Select the house number from the spinner wheel and touch the Go-button.
  3. An OpenStreetMap of Enschede displays with your location (the balloon) and that of your destination. A compass needle, getting data from your device’s orientation sensor and your location, is pointing in the straight-line direction of your destination.

Tips

  • Make sure your location on the map is correct before embarking on your adventure in the night.
  • If the compass needle seems wonky, move your device in a figure-eight like this. If it still seems wonky, don’t base your journey on it.

Source code

The source code will be released on Github.

The map

The map comes from OpenStreetMap and is prepared as described in an earlier post. To save space, only one zoom level (15) is selected, this is were the starting level in the app as well. To add more zoom levels, just follow the steps in the aforementioned post and put the resulting .zip file in the /sdcard/osmdroid/ folder of your phone.

Note

The app stores the map in /mnt/sdcard/osmdroid/Enschede.zip, if it doesn’t already exist. To completely uninstall app, you must first clear data and uninstall from your application settings, then remove the osmdroid folder from your sdcard.

Populating AutoCompleteTextView with SQLite data

I’m currently working on a hobby project with working title EnschedeLocator, which is an Android app where users can type in an address and using their unit’s GPS receiver, get an arrow that points them to the destination. A simple offline navigation tool. The addresses comes from Opendata Enschede, and are stored with coordinates in a database shipped with the app.

For my EnschedeLocator app I want the user to be able to start typing the name of the street he wants and then receive suggestions as he types. The idea is that he has to find a match in order to continue or else the street wont be in the database.

After spending too much time trying to populate an AutoCompleteTextView using a Cursor directly (might be a dead end), the simple solution came from GiantFlyingSaucer. Simply create a method in your SQLiteOpenHelper class that turns you query into a String-array and then use that to populate the ArrayAdapter as shown on Android Developers.