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:

sqlite3 databasename.db

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.

CREATE TABLE "android_metadata"("locale" TEXT DEFAULT 'en_US');
CREATE TABLE "addresses"(_id integer PRIMARY KEY,
straatnaam text, huisnummer integer, latitude double, longitude double);

Now we’re gonna use the import the data.

.separator ","
.import enschede_addresses.csv addresses

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:

DELETE FROM addresses WHERE _id NOT IN(SELECT MIN(_id) FROM addresses GROUP BY straatnaam, huisnummer);
vacuum;

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.

Smoothing out sensor values from SensorEvents, specifically for Orientation, in Android

For my EnschedeLocator app, I want the user to get a compass needle that points towards the destination address using the device’s orientation sensor and the calculated bearing between the user’s location and his destination. For this, I implemented and tweaked Google’s Compass example.

However, the compass seemed very jittery and unreliable, and frustration ensued. The simple solution came via this StackOverflow thread. Simply store a number of recent values and average them. This simple fix made the needle rotate smoothly and stay on target.

/**Tweaked method for setting an average value. Pass the compassAverage to canvas.rotate()*/
public void setmValues(float[] mValues) {
        this.mValues = mValues;
        compassValues[0]= compassValues[1];
        compassValues[1]= compassValues[2];
        compassValues[2]= compassValues[3];
        compassValues[3]= compassValues[4];
        compassValues[4]=mValues[0];
        compassAverage = (compassValues[0] +compassValues[1] +compassValues[2] +compassValues[3] +compassValues[4])/5;

}

Using OSMDroid for Offline mapping in Android, step-by-step [Tutorial]

For my EnschedeLocator app, I wanted the users to be able to store an offline map on the device and see it while navigating to their destinations. In our BeerFinder project at NTNU last year, we were able to do this using Nutiteq’s android library, but poor documentation lead me to looking for other solutions. I discovered OSMDroid which is a library that aims to be an opensource alternative to Google MapView in Android.

Below, I will outline how I did it and how I solved some small obstacles i encountered. Note that there may be other and better ways of doing this, but this is what worked for me.

Tools needed

  • Eclipse with Android SDK (assuming you know the basics here).
  • Mobile Atlas Creator, for preparing map tiles.
  • OSMDroid library, available here. I used osmdroid-android-3.0.7.jar.
  • SLF4J Android library, available here. I used slf4j-android-1.5.8.jar. Without this library I got a NoClassDefFoundError when trying to run the app. Adding this library somehow fixed it [source].

Preparing the map

To download the map tiles I used Mobile Atlas Creator.

My steps were as follows, most settings are done in the options pane to the left of the map:

  1. Set atlas format to Osmdroid ZIP [Atlas – Convert Atlas Format].
  2. Set your map area coordinates, either by clicking and dragging on the map, or by setting min and max coordinates in the coordinate selection pane to the left.
  3. Set map source to OpenStreetMap Mapnik. Note: I originally set my source to something else, and the map wouldn’t appear on my phone. I’m sure there’s a simple fix for this, but haven’t looked into it yet.
  4. Select the zoom levels you want to include. This will impact the storage space required. As you select more levels, the number of tiles needed are displayed. Each pane takes about 20kilobytes. I included all levels because my area of interest is so small that I’m not to concerned about that yet, I might make small versions available when I publish the app. For my area, 178 tiles + 20kB = 3.48MB.
  5. In the Atlas Content pane, set the name of your map first, then click “add selection” (the name really isn’t important, though).
  6. Then select “Create atlas” and your map is stored in the atlas folder under Mobile Atlas Creator.
  7. Move the resulting zip-file to /mnt/sdcard/osmdroid/ on your device. (For a slight improvement in performance, you can unzip the file and move the resulting Mapnik-folder to /mnt/sdcard/osmdroid/tiles/ instead. The difference in size isn’t that big, but the zip-file might be a good way to organize different maps if you have several areas, but I digress).

That’s it for preparing the map! Again, these are steps and settings that worked for me, others might work better, but I haven’t researched them yet.

Importing the libraries to Android project

When I attach external libraries to my projects I put the .jar-files in the project folder so that I know where they are.

  • Move the osmdroid-android-3.0.7.jar and slf4j-android-1.5.8.jar (and osmdroid-android-3.0.7-javadoc.jar for documentation) into your project folder.
  • In eclipse, right click the project and click refresh, the files should show up.
  • Right-click the files and select [Build Path -> Add to Build Path].
  • Then, go into [Build Path -> Configure Build Path] and under the Libraries tab select the osmdroid.jar and edit javadoc location to the osmdroid-android-3.0.7-javadoc.jar file.

The code

This example code shows the map parts of the Activity class and should be enough to view the map. Note that this Activity doesn’t need to specify an .xml layout as we’re doing it in the code.

package com.yourpackage.name;

import org.osmdroid.util.GeoPoint;
import org.osmdroid.views.MapView;

import android.app.Activity;
import android.os.Bundle;

public class OSMDroidMapActivity extends Activity {
    public void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);

        MapView mapView = new MapView(this, 256); //constructor
        mapView.setClickable(true);
        mapView.setBuiltInZoomControls(true);
        setContentView(mapView); //displaying the MapView
        mapView.getController().setZoom(15); //set initial zoom-level, depends on your need
        mapView.getController().setCenter(new GeoPoint(52.221, 6.893)); //This point is in Enschede, Netherlands. You should select a point in your map or get it from user's location.
        mapView.setUseDataConnection(false); //keeps the mapView from loading online tiles using network connection.
    }
}

This displays the MapView only. If you would like to add some other Views, you can remove the setContentView(mapView); and add the following to your class and remember to do the right imports.

TextView myTextView = new TextView(this);
        myTextView.setTextAppearance(this, android.R.style.TextAppearance_Large_Inverse);
        myTextView.setText("Enschede, Netherlands");
        Button myUselessButton = new Button(this);
        myUselessButton.setText("Click");

        final RelativeLayout relativeLayout = new RelativeLayout(this);
        final RelativeLayout.LayoutParams mapViewLayoutParams = new RelativeLayout.LayoutParams(
                        RelativeLayout.LayoutParams.FILL_PARENT,RelativeLayout.LayoutParams.FILL_PARENT);
        final RelativeLayout.LayoutParams textViewLayoutParams = new RelativeLayout.LayoutParams(
                        RelativeLayout.LayoutParams.FILL_PARENT, RelativeLayout.LayoutParams.WRAP_CONTENT);
        final RelativeLayout.LayoutParams buttonLayoutParams = new RelativeLayout.LayoutParams(
                        RelativeLayout.LayoutParams.FILL_PARENT, RelativeLayout.LayoutParams.WRAP_CONTENT);
        buttonLayoutParams.addRule(RelativeLayout.ALIGN_PARENT_BOTTOM);

        relativeLayout.addView(mapView, mapViewLayoutParams);
        relativeLayout.addView(myTextView, textViewLayoutParams);
        relativeLayout.addView(myUselessButton,buttonLayoutParams);
        setContentView(relativeLayout);

Basically, we did three things here:

  • Create the TextView and Button.
  • Create the RelativeLayout and the rules for the different Views.
  • Add the Views to the Layout and finally set the Activity to show the layout.

Now we should get something like this:

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.