Easiest Way To Export Longitude And Latitude Data Stored In A Sqlite Database To A File So It Can Be Imported To A Google Map Api Through A Website?

I have created an application that records a series of longitude and latitude values in a SQLite database and display them as a coloured track on a MapActivity. I now want to be ab

Solution 1:

Ok Just add this class in your Android Project (with your respective modifications)

publicclassDatabaseAssistant
{
    privatestaticfinalStringEXPORT_FILE_NAME="/sdcard/datanaexport.xml";

    private Context _ctx;
    private SQLiteDatabase _db;
    private Exporter _exporter;

    publicDatabaseAssistant( Context ctx, SQLiteDatabase db )
    {
        _ctx = ctx;
        _db = db;

        try
        {
            // create a file on the sdcard to export the// database contents toFilemyFile=newFile( EXPORT_FILE_NAME );
                        myFile.createNewFile();

                        FileOutputStreamfOut=newFileOutputStream(myFile);
                        BufferedOutputStreambos=newBufferedOutputStream( fOut );

            _exporter = newExporter( bos );
        }
        catch (FileNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    publicvoidexportData( )
    {
        log( "Exporting Data" );

        try
        {
            _exporter.startDbExport( _db.getPath() );

            // get the tables out of the given sqlite databaseStringsql="SELECT * FROM sqlite_master";

                    Cursorcur= _db.rawQuery( sql, newString[0] );
                    Log.d("db", "show tables, cur size " + cur.getCount() );
                    cur.moveToFirst();

                    String tableName;
                    while ( cur.getPosition() < cur.getCount() )
                    {
                        tableName = cur.getString( cur.getColumnIndex( "name" ) );
                        log( "table name " + tableName );

                        // don't process these two tables since they are used// for metadataif ( ! tableName.equals( "android_metadata" ) &&
                        ! tableName.equals( "sqlite_sequence" ) )
                        {
                            exportTable( tableName );
                        }

                        cur.moveToNext();
                    }
                _exporter.endDbExport();
            _exporter.close();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
    }

    privatevoidexportTable( String tableName )throws IOException
    {
        _exporter.startTable(tableName);

        // get everything from the tableStringsql="select * from " + tableName;
        Cursorcur= _db.rawQuery( sql, newString[0] );
        intnumcols= cur.getColumnCount();

        log( "Start exporting table " + tableName );

//      // logging//      for( int idx = 0; idx < numcols; idx++ )//      {//          log( "column " + cur.getColumnName(idx) );//      }

        cur.moveToFirst();

        // move through the table, creating rows// and adding each column with name and value// to the rowwhile( cur.getPosition() < cur.getCount() )
        {
            _exporter.startRow();
            String name;
            String val;
            for( intidx=0; idx < numcols; idx++ )
            {
                name = cur.getColumnName(idx);
                val = cur.getString( idx );
                log( "col '" + name + "' -- val '" + val + "'" );

                _exporter.addColumn( name, val );
            }

            _exporter.endRow();
            cur.moveToNext();
        }

        cur.close();

        _exporter.endTable();
    }

    privatevoidlog( String msg )
    {
        Log.d( "DatabaseAssistant", msg );
    }

    classExporter
    {
        privatestaticfinalStringCLOSING_WITH_TICK="'>";
        privatestaticfinalStringSTART_DB="<export-database name='";
        privatestaticfinalStringEND_DB="</export-database>";
        privatestaticfinalStringSTART_TABLE="<table name='";
        privatestaticfinalStringEND_TABLE="</table>";
        privatestaticfinalStringSTART_ROW="<row>";
        privatestaticfinalStringEND_ROW="</row>";
        privatestaticfinalStringSTART_COL="<col name='";
        privatestaticfinalStringEND_COL="</col>";

        private BufferedOutputStream _bos;

        publicExporter()throws FileNotFoundException
        {
            this( newBufferedOutputStream(
                    _ctx.openFileOutput( EXPORT_FILE_NAME,
                    Context.MODE_WORLD_READABLE ) ) );
        }

        publicExporter( BufferedOutputStream bos )
        {
            _bos = bos;
        }

        publicvoidclose()throws IOException
        {
            if ( _bos != null )
            {
                _bos.close();
            }
        }

        publicvoidstartDbExport( String dbName )throws IOException
        {
            Stringstg= START_DB + dbName + CLOSING_WITH_TICK;
            _bos.write( stg.getBytes() );
        }

        publicvoidendDbExport()throws IOException
        {
            _bos.write( END_DB.getBytes() );
        }

        publicvoidstartTable( String tableName )throws IOException
        {
            Stringstg= START_TABLE + tableName + CLOSING_WITH_TICK;
            _bos.write( stg.getBytes() );
        }

        publicvoidendTable()throws IOException
        {
            _bos.write( END_TABLE.getBytes() );
        }

        publicvoidstartRow()throws IOException
        {
            _bos.write( START_ROW.getBytes() );
        }

        publicvoidendRow()throws IOException
        {
            _bos.write( END_ROW.getBytes() );
        }

        publicvoidaddColumn( String name, String val )throws IOException
        {
            Stringstg= START_COL + name + CLOSING_WITH_TICK + val + END_COL;
            _bos.write( stg.getBytes() );
        }
    }

    classImporter
    {

    }

}

instatiate DatabaseAssistant Class

DatabaseAssistantDA=newDatabaseAssistant(myContext, mySQLiteDatabase);

Do you want export data??? so...

DA.exportData();

;) hope this help!

Jorgesys

Solution 2:

Hi LordSnoutimus i had a very similar situation so I used this code.

Export an Android SQLite db to an XML file on the SD Card very useful, just make few changes for your app :)

so lets code it!

Jorgesys