1

I'm creating a new Spatialite database using c# and .net through the following code:

using var connection = new SqliteConnection($"Data Source={Path.Combine(outputDirectory, "data.db")}");
connection.Open();

try
{
    connection.EnableExtensions();

    using var command = connection.CreateCommand();
    command.CommandText = "SELECT load_extension('mod_spatialite');";
    command.ExecuteScalar();

    //create the required tables
    using var tableCreateCommand = connection.CreateCommand();
    tableCreateCommand.CommandText =
                        "SELECT InitSpatialMetaData();" +
                        "CREATE TABLE Places (Id TEXT PRIMARY KEY UNIQUE NOT NULL, Name TEXT, Description TEXT, TypeId INTEGER, TotalReviews INTEGER, AverageRating INTEGER) WITHOUT ROWID;";
    tableCreateCommand.ExecuteScalar();

    //attach the geometry column
    using var geomAttachCommand = connection.CreateCommand();
    geomAttachCommand.CommandText = 
                        "SELECT AddGeometryColumn('Places', 'Location', 4326, 'POINT', 'XY');" +
                        "SELECT CreateSpatialIndex('Places', 'Location');";
    geomAttachCommand.ExecuteScalar();
}

That creates the database and table correctly, but the Location column is never created. If I open the newly created database using spatialite_gui and then execute SELECT AddGeometryColumn('Places', 'Location', 4326, 'POINT', 'XY'); again. It returns a 0 but once again, no column is created.

According to the docs the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE but they don't go into anymore details with regards to what the 0 actually means?

Is there anything that I'm missing that would cause the AddGeometryColumn function to fail?

1 Answer 1

1

It seems that SpatiaLite requires rowids. Remove WITHOUT ROWID and your commands will work. However, I am not sure if SpatiaLite works well if the primary key is of TEXT type. I would recommend to use INTEGER-AUTOINCREMENT PK as below, but if you definitely want to use TEXT PK then it would be good to test that all that you want to do really work correctly.

CREATE TABLE Places3(Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT, Description TEXT, TypeId INTEGER, TotalReviews INTEGER, AverageRating INTEGER);
1
  • Okay yeah that was it, I removed WITHOUT ROWID and the column gets created correctly, thanks!
    – Walter
    Commented Apr 23, 2023 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.