Databases
StackOverflow2013
Postsdbo
POGeoPoints from existing SQLite Database
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGeoPoints from existing SQLite Database
    text
    copied!<p>I am trying to populate a map overlay with markers using lat and long from an existing SQLite database. I have found examples where you input the lat and long as part of the method but none where it is populated from the database itself. I presume you use a Cursor to do this but I am not sure how to do it. I have used Cursors to populate ListViews but can not seem to modify the code for the map overlay.</p> <p>Can anyone point me in the right direction or direct me to a full tutorial?</p> <p>Here is my code so far, as I am new to this I need examples as can't construct code well yet.</p> <p>LocationMap2.java</p> <pre><code>import com.google.android.maps.MapView; import android.app.Activity; import android.content.Context; import android.database.Cursor; import android.os.Bundle; import android.util.Log; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.CursorAdapter; import android.widget.TextView; public class LocationMap2 extends Activity { // we use a string to hold the name of our extra, // it must include the full package name public final static String ID_EXTRA = "com.willows.UKBikeTrails._ID"; private AllScotlandHelper dbDataBaseHelper = null; private Cursor ourCursor = null; public void onCreate(Bundle savedInstanceState) { try { super.onCreate(savedInstanceState); setContentView(R.layout.map); // this is our MapView element, obtained by id from our XML Layout MapView mapView = (MapView) findViewById(R.id.mapview); // create our database Helper dbDataBaseHelper = new AllScotlandHelper(this); // we call the create right after initializing the helper, just in // case // they have never run the app before dbDataBaseHelper.createDatabase(); // // open the database!! Our helper now has a SQLiteDatabase database // object dbDataBaseHelper.openDataBase(); // get our cursor. A cursor is a pointer to a dataset, in this case // a set of results from a database query ourCursor = dbDataBaseHelper.getCursor(); // tell android to start managing the cursor // we do this just incase our activity is interrupted or ends, we // want the activity // to close and deactivate the cursor, as needed startManagingCursor(ourCursor); } catch (Exception e) { // this is the line of code that sends a real message to the Log Log.e("ERROR", "ERROR IN CODE: " + e.toString()); // this is the line that prints out the location // the code where the error occurred. e.printStackTrace(); } } class DataBaseAdapter extends CursorAdapter { DataBaseAdapter(Cursor c) { super(LocationMap2.this, c); } public void bindView(View row, Context ctxt, Cursor c) { DataBaseHolder holder = (DataBaseHolder) row.getTag(); holder.populateFrom(c, dbDataBaseHelper); } @Override public View newView(Context ctxt, Cursor c, ViewGroup parent) { LayoutInflater inflater = getLayoutInflater(); View row = inflater.inflate(R.layout.trail_list, parent, false); DataBaseHolder holder = new DataBaseHolder(row); row.setTag(holder); return (row); } } static class DataBaseHolder { private TextView name = null; DataBaseHolder(View row) { name = (TextView) row.findViewById(R.id.row); } void populateFrom(Cursor c, AllScotlandHelper r) { name.setText(r.getName(c)); } } } </code></pre> <p>LocationMapHelper:</p> <pre><code>import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.Locale; import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteException; import android.database.sqlite.SQLiteOpenHelper; import android.database.sqlite.SQLiteQueryBuilder; import android.util.Log; class LocationMapHelper extends SQLiteOpenHelper { //we declare a bunch of useful constants //they should be pretty obvious what they are! private static final String DATABASE_PATH = "/data/data/com.willows.UKBikeTrails/databases/"; private static final String DATABASE_NAME = "ukBikeTrails.db"; private static final int SCHEMA_VERSION = 1; public static final String TABLE_NAME = "trail_type"; public static final String COLUMN_ID = "_id"; public static final String COLUMN_TITLE = "TYPE"; // BE AWARE - if you make changes to your database using sqlitebrower, you will need to refresh the assets folder in eclipse public static final String SECOND_TABLE_NAME = "trail"; public static final String SECOND_COLUMN_ID = "_id"; public static final String SECOND_COLUMN_TITLE = "NAME"; public SQLiteDatabase dbSqlite; private final Context myContext; public LocationMapHelper (Context context) { super(context, DATABASE_NAME, null, SCHEMA_VERSION); this.myContext = context; // check if exists and copy database from resource //createDB(); } @Override public void onCreate(SQLiteDatabase db) { // check if exists and copy database from resource } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } public void createDatabase() { createDB(); } private void createDB() { boolean dbExist = DBExists(); if (!dbExist) { //By calling the method we create an empty database into the default system location //We need this so we can overwrite that database with our database this.getReadableDatabase(); //now we copy the database we included! copyDBFromResource(); } } private boolean DBExists() { SQLiteDatabase db = null; try { String databasePath = DATABASE_PATH + DATABASE_NAME; db = SQLiteDatabase.openDatabase(databasePath, null, SQLiteDatabase.OPEN_READWRITE); db.setLocale(Locale.getDefault()); db.setLockingEnabled(true); db.setVersion(1); } catch (SQLiteException e) { Log.e("SqlHelper", "database not found"); } if (db != null) { db.close(); } return db != null ? true : false; } private void copyDBFromResource() { InputStream inputStream = null; OutputStream outStream = null; String dbFilePath = DATABASE_PATH + DATABASE_NAME; try { inputStream = myContext.getAssets().open(DATABASE_NAME); outStream = new FileOutputStream(dbFilePath); byte[] buffer = new byte[1024]; int length; while ((length = inputStream.read(buffer)) &gt; 0) { outStream.write(buffer, 0, length); } outStream.flush(); outStream.close(); inputStream.close(); } catch (IOException e) { throw new Error ("Problem copying database from resource file."); } } public void openDataBase() throws SQLException { String myPath = DATABASE_PATH + DATABASE_NAME; dbSqlite = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE); } @Override public synchronized void close() { if (dbSqlite !=null) { dbSqlite.close(); } super.close(); } //the following two methods return the column you want and it's title (getName) public Cursor getCursor() { SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder(); queryBuilder.setTables(TABLE_NAME); String[] asColumnsToReturn = new String[] { COLUMN_ID, COLUMN_TITLE }; //make sure you get your search pass correctly! Cursor mCursor = queryBuilder.query(dbSqlite, asColumnsToReturn, null, null, null, null, "_id"); //the name after the nulls dictates how the results are returned. ie in order of "column name" return mCursor; } public String getName(Cursor c) { return(c.getString(1)); //where 1 refers to COLUMN_TITLE line 11 rows above (0 would be COLUMN_ID and so on) } public Cursor getLatLong(String id) { String[] args = null; String query = "SELECT _id, LATITUDE,LONGITUDE FROM trail"; return (getReadableDatabase().rawQuery(query, args)); } </code></pre> <p>MapItemizedOverlay:</p> <pre><code>import java.util.ArrayList; import android.app.AlertDialog; import android.content.Context; import android.graphics.drawable.Drawable; import com.google.android.maps.ItemizedOverlay; import com.google.android.maps.OverlayItem; public class MapItemizedOverlay extends ItemizedOverlay&lt;OverlayItem&gt; { private ArrayList&lt;OverlayItem&gt; mOverlays = new ArrayList&lt;OverlayItem&gt;(); private Context mContext; public MapItemizedOverlay(Drawable defaultMarker, Context context) { super(boundCenterBottom(defaultMarker)); mContext = context; } public void addOverlay(OverlayItem overlay) { mOverlays.add(overlay); populate(); } @Override protected OverlayItem createItem(int i) { return mOverlays.get(i); } @Override public int size() { return mOverlays.size(); } @Override protected boolean onTap(int index) { OverlayItem item = mOverlays.get(index); AlertDialog.Builder dialog = new AlertDialog.Builder(mContext); dialog.setTitle(item.getTitle()); dialog.setMessage(item.getSnippet()); dialog.show(); return true; } } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload