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.

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: