AddGeometryColumn Error (InitSpatialMetadata related)

[MSBilgin]

Hi,

I am trying to create spatialite db but no luck. Here is my code to

getWritableDatabase().rawQuery("select InitSpatialMetadata(1)", null);
String sqlCizim = "create table CIZIM(OBJECTID INTEGER PRIMARY KEY AUTOINCREMENT, KATMAN TEXT, VERI TEXT, TARIH TEXT)";
getWritableDatabase().rawQuery(sqlCizim, null);
String sqlCizimGeometri = "select AddGeometryColumn('CIZIM', 'GEOM', 3857,'GEOMETRY', 'XY')";
getWritableDatabase().rawQuery(sqlCizimGeometri, null);

[MSBilgin]

I noticed an interesting thing;

Firstly I generated a spatialite db by using QGIS and copied it to the device. Then successfully created a new table and added geometry column from Android app. It works without any problem with pre initialized spatialite db's. I think the problem is about InitSpatialMetadata() sql function. Do you have any advice ?

[phprad]

Hi, Creating a new spatialite database requires that you load some extension which do not come with SQLITE by default.

I would suggest you create an empty database using the Spatialite_gui and copy it to your application. With that you can create new tables and add Geometry Columns.

[sevar83]

Hello, I think I do not get the problem. Could you please provide more info? I've made a test case with your code and do not see anything suspicious for now.

[MSBilgin]

Hello, I think I do not get the problem. Could you please provide more info? I've made a test case with your code and do not see anything suspicious for now.

Here is sample code https://gist.github.com/MSBilgin/4174e151216d93225175459c465b3a47

[sevar83]

Ok, the problem is within InitSpatialMetaData(1) - particularly when the parameter "transaction" is set to 1 or TRUE. It makes the insertions of all spatial coordinate systems to be nested transactions within one single transaction. There is some issue with nesting transactions cause one test covering nested transactions fails for some reason. I have to insvestigate it deeper. But as a workaround I suggest to simply use InitSpatialMetaData(0). It would be slower but that's not more than a few secs and happens only once I guess.

[sevar83]

I think the Android-friendly way to use Spatialite's functions that has the "transaction" parameter like:

• InitSpatialMetaData()
• InitSpatialMetaDataFull()
• UpgradeGeometryTriggers()
• ... and about 50 more

is to supply FALSE (0) to the transaction parameter and wrap them with beginTransaction()/endTransaction() like this:

try {
  database.beginTransaction();
  database.rawQuery("select InitSpatialMetaData(0)", null);
  database.setTransactionSuccessful();
finally {
  database.endTransaction();
}

[xiaohepan]

I got the same problem,i solve this problem by run cursor. val cursor = database.rawQuery("select InitSpatialMetaData(0)", null); cursor.moveToFirst() cursor.close