/// <summary>
/// Finds out information about the geometry column
/// </summary>
/// <param name="conn"></param>
/// <param name="sqlQuery"></param>
/// <returns></returns>
private FeatureSetTableInfo FindGeometryColumnInfo(SQLiteConnection conn, string sqlQuery)
{
FeatureSetTableInfo result = null;
using (var cmd = new SQLiteCommand(sqlQuery, conn))
{
var wkbr = new SpatiaLiteWkbReader();
var rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
var schemaTable = rdr.GetSchemaTable();
foreach (DataRow r in schemaTable.Rows)
{
var colName = Convert.ToString(r["ColumnName"]);
var colDataType = Convert.ToString(r["DataType"]);
//if BLOB, then assume geometry column
if (Type.GetType(colDataType) == typeof(byte[]))
{
result = new FeatureSetTableInfo();
result.GeometryColumnName = colName;
break;
}
}
if (result != null && rdr.HasRows)
{
rdr.Read();
var blob = rdr[result.GeometryColumnName] as byte[];
var geom = wkbr.Read(blob);
result.GeometryType = geom.FeatureType;
}
return(result);
}
}
/// <summary>
/// Finds a list of all valid geometry columns in the database
/// </summary>
/// <param name="connString">connection string</param>
/// <returns>the list of geometry columns</returns>
public IEnumerable <FeatureSetTableInfo> ListFeatureSets(SQLiteConnection conn)
{
string version = GetSpatiaLiteDbVersion(conn);
if (version.Length == 0)
{
this.ErrorMessage = "The database doesn't appear to be valid.";
log.Error(this.ErrorMessage);
return(new List <FeatureSetTableInfo>());
}
if (!version.StartsWith("4"))
{
this.ErrorMessage = "The database software requires version 4 of the SpatiaLite framework.";
log.Error(this.ErrorMessage);
return(new List <FeatureSetTableInfo>());
}
var sql = "SELECT f_table_name, f_geometry_column, geometry_type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns";
SQLiteCommand cmd = null;
try
{
var columns = new List <FeatureSetTableInfo>();
cmd = new SQLiteCommand(sql, conn);
var r = cmd.ExecuteReader();
while (r.Read())
{
var info = new FeatureSetTableInfo();
info.TableName = Convert.ToString(r["f_table_name"]);
info.GeometryColumnName = Convert.ToString(r["f_geometry_column"]);
info.GeometryType = GetGeometryType(Convert.ToString(r["geometry_type"]));
CoordinateDimension coordDim;
if (!Enum.TryParse(Convert.ToString(r["coord_dimension"]), out coordDim))
{
info.CoordDimension = CoordinateDimension.XY;
}
info.SRID = Convert.ToInt32(r["srid"]);
info.SpatialIndexEnabled = false;
columns.Add(info);
}
return(columns);
}
catch (Exception ex)
{
log.Error("Failed to retrieve feature set listing from database: {0}", ex.Message);
return(new List <FeatureSetTableInfo>());
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
/// <summary>
/// Reads the complete feature set from the database
/// </summary>
/// <param name="connString">sqlite db connection string</param>
/// <param name="featureSetInfo">information about the table</param>
/// <returns>the resulting feature set</returns>
public bool TryReadFeatureSet(SQLiteConnection conn, FeatureSetTableInfo featureSetInfo, out IFeatureSet featureSet)
{
featureSet = null;
if (featureSetInfo == null)
{
return(false);
}
var sql = string.Format("SELECT * FROM {0}", featureSetInfo.TableName);
return(TryReadFeatureSet(conn, featureSetInfo, sql, out featureSet));
}
/// <summary>
/// Reads the feature set from the database, satisfying the given SQL query
/// </summary>
/// <param name="connString">sqlite db connection string</param>
/// <param name="featureSetInfo">information about the table</param>
/// <param name="sqlFilter">the sql query; to select all: "SELECT * FROM TableName"</param>
/// <returns>the resulting feature set</returns>
public bool TryReadFeatureSet(SQLiteConnection conn, FeatureSetTableInfo featureSetInfo, string sqlFilter, out IFeatureSet featureSet)
{
featureSet = null;
if (featureSetInfo == null)
{
return(false);
}
var fs = new SpatiaLiteFeatureSet(featureSetInfo.GeometryType);
fs.IndexMode = false; //setting the initial index mode..
SQLiteCommand cmd = null;
try
{
cmd = new SQLiteCommand(sqlFilter, conn);
var wkbr = new SpatiaLiteWkbReader();
var rdr = cmd.ExecuteReader();
var columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
while (rdr.Read())
{
var wkb = rdr[featureSetInfo.GeometryColumnName] as byte[];
var geom = wkbr.Read(wkb);
var newFeature = fs.AddFeature(geom);
//populate the attributes
foreach (var colName in columnNames)
{
newFeature.DataRow[colName] = rdr[colName];
}
}
fs.Name = featureSetInfo.TableName;
//TODO: look into this...
//HACK required for selection to work properly
fs.IndexMode = true;
// assign projection
ProjectionInfo proj;
if (featureSetInfo.SRID >= 0)
{
try
{
proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
}
catch (Exception ex)
{
log.Warning("Unable to find projection info from EPSG code {0}, using default: {1}", featureSetInfo.SRID, ex.Message);
proj = new ProjectionInfo();
}
}
else
{
proj = new ProjectionInfo();
}
fs.Projection = proj;
featureSet = fs;
return(true);
}
catch (Exception ex)
{
log.Error("Unable to read feature set {0} using SQL \"{1}\": {2}", featureSetInfo.TableName, sqlFilter, ex.Message);
return(false);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
/// <summary>
/// Creates a feature set table using the given SQLite database connection. Catches exceptions and returns creation status.
/// </summary>
/// <param name="conn"></param>
/// <param name="metadata"></param>
/// <param name="geomMetadata"></param>
/// <returns></returns>
private bool TryCreateFeatureSetTable(SQLiteConnection conn, SQLiteTableMetadata metadata, FeatureSetTableInfo geomMetadata)
{
try
{
string sql = metadata.GetCreateSqlCode();
log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);
var transaction = conn.BeginTransaction();
var cmd = new SQLiteCommand(sql, conn);
int nRows = cmd.ExecuteNonQuery();
log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);
sql = String.Format("SELECT AddGeometryColumn('{0}', '{1}', {2}, '{3}', '{4}', 1);", // last parameter means that only NOT NULL geometries will be accepted
metadata.TableName, geomMetadata.GeometryColumnName, geomMetadata.SRID,
geomMetadata.GetGeometryType(), geomMetadata.CoordDimension.ToString().ToUpper());
log.Debug("TryCreateFeatureSetTable: sql=\"{0}\"", sql);
cmd = new SQLiteCommand(sql, conn);
nRows = cmd.ExecuteNonQuery();
log.Debug("TryCreateFeatureSetTable: nRows={0}", nRows);
transaction.Commit();
}
catch (Exception ex)
{
this.ErrorMessage = string.Format("Unable to create feature set table \"{0}\": {1}", metadata.TableName, ex.Message);
log.Error(this.ErrorMessage);
return(false);
}
return(true);
}
/// <summary>
/// Save a feature set to a SQLite database. The FeatureSet is saved to a table
/// with the same name as FeatureSet.Name. Existing tables will get dropped.
/// </summary>
/// <param name="connstring">database connection string</param>
/// <param name="fs">the feature set to save</param>
public bool TrySaveFeatureSet(SQLiteConnection conn, DotSpatial.Data.IFeatureSet fs)
{
var meta = new SQLiteTableMetadata(fs.Name);
if (meta.TableName == null || meta.TableName.Length == 0)
{
this.ErrorMessage = "Invalid table name provided";
log.Error(this.ErrorMessage);
return(false);
}
int srid = 0;
if (fs.Projection == null)
{
srid = -1; // unknown
}
else
{
srid = fs.Projection.AuthorityCode;
}
using (SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + meta.TableName, conn))
{
log.Debug("TrySaveFeatureSet: dropped table {0} if it exists", meta.TableName);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.ErrorMessage = string.Format("SQL execution issue 1: {0}", ex.Message);
log.Error(this.ErrorMessage);
return(false);
}
}
string colNames = "";
string vals = "";
List <SQLiteParameter> parms = new List <SQLiteParameter>();
List <string> colNamesInFS = new List <string>();
foreach (DataColumn c in fs.DataTable.Columns)
{
meta.Columns.Add(new Column(c.ColumnName, c.DataType));
colNames += "[" + c.ColumnName + "], ";
vals += "?, ";
parms.Add(new SQLiteParameter());
colNamesInFS.Add(c.ColumnName);
}
var geomMeta = new FeatureSetTableInfo(meta.TableName, fs.FeatureType, srid);
colNames += "[" + geomMeta.GeometryColumnName + "]";
vals += "ST_GeomFromWKB(?, " + geomMeta.SRID + ")";
parms.Add(new SQLiteParameter(DbType.Object));
if (!TryCreateFeatureSetTable(conn, meta, geomMeta))
{
return(false);
}
log.Debug("Created feature set table");
var trans = conn.BeginTransaction();
try
{
SQLiteCommand insCmd = new SQLiteCommand(conn);
insCmd.CommandText = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2});", meta.TableName, colNames, vals);
insCmd.Parameters.AddRange(parms.ToArray());
for (int i = 0; i < fs.Features.Count; i++)
{
try
{
UpdateCommandParameters(fs.Features[i], parms, colNamesInFS, srid);
//log.Debug("Starting insert of feature {0}: sql=\"{1}\" parameters=\"{2}\"", i, insCmd.CommandText, GetParametersAsString(insCmd.Parameters));
//log.Debug(string.Join("|", Array.ConvertAll(fs.Features[i].BasicGeometry.Coordinates.ToArray(), x => x.X.ToString() + "," + x.Y.ToString())));
insCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.ErrorMessage = string.Format("SQL execution issue 2: {0}", ex.Message);
log.Error(this.ErrorMessage);
return(false);
}
}
log.Debug("Inserted {0} features", fs.Features.Count);
}
catch (KeyNotFoundException kex)
{
log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, kex.Message);
trans.Rollback();
return(false);
}
catch (Exception ex)
{
log.Error("Unable to save feature set into table \"{0}\": {1}", fs.Name, ex.Message);
trans.Rollback();
return(false);
}
trans.Commit();
log.Debug("Saved feature set into the table");
return(true);
}
