/// <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 List <GeometryColumnInfo> GetGeometryColumns(string connString)
{
var lst = new List <GeometryColumnInfo>();
var sql =
"SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns";
using (var cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
var r = cmd.ExecuteReader();
while (r.Read())
{
var gci = new GeometryColumnInfo();
gci.TableName = Convert.ToString(r["f_table_name"]);
gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]);
gci.GeometryType = GetGeometryType(Convert.ToString(r["type"]));
gci.CoordDimension = ConvertToCoordDimension(Convert.ToString(r["coord_dimension"]));
gci.SRID = Convert.ToInt32(r["srid"]);
gci.SpatialIndexEnabled = false;
lst.Add(gci);
}
cmd.Connection.Close();
}
return(lst);
}
/// <summary>
/// Creates a geometry-enabled (feature set) table.
/// </summary>
/// <param name="metadata">metadata describing the standard attributes</param>
/// <param name="geomMetadata">metadata describing the geometry column</param>
/// <returns></returns>
public bool CreateFeatureSetTable(SQLiteTableMetadata metadata, GeometryColumnInfo geomMetadata)
{
Contract.Requires(metadata != null && geomMetadata != null);
using (var conn = OpenDatabase())
{
CreateFeatureSetTable(conn, metadata, geomMetadata);
conn.Close();
}
return(true);
}
/// <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>
/// <param name="sql">the sql query</param>
/// <returns>the resulting feature set</returns>
public IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo, string sql)
{
FeatureSet fs = new SpatiaLiteFeatureSet(featureSetInfo.GeometryType);
fs.IndexMode = false; //setting the initial index mode..
using (var cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
//DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader();
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];
}
}
cmd.Connection.Close();
fs.Name = featureSetInfo.TableName;
//TODO: look into this more
//HACK required for selection to work properly
fs.IndexMode = true;
//assign projection
var proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
fs.Projection = proj;
return(fs);
}
}
//finds out the geometry column information..
private GeometryColumnInfo FindGeometryColumnInfo(string connString, string sqlQuery)
{
GeometryColumnInfo result = null;
using (var cmd = CreateCommand(connString, sqlQuery))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
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 GeometryColumnInfo();
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 = GetGeometryType(Convert.ToString(rdr["type"]));
}
cmd.Connection.Close();
return(result);
}
}
protected void CreateFeatureSetTable(SQLiteConnection conn, SQLiteTableMetadata metadata, GeometryColumnInfo geomMetadata)
{
string sql = metadata.GetCreateSqlCode();
var transaction = conn.BeginTransaction();
var cmd = new SQLiteCommand(sql, conn);
int nRows = cmd.ExecuteNonQuery();
sql = String.Format("SELECT AddGeometryColumn('{0}', '{1}', {2}, '{3}', '{4}');",
metadata.TableName, geomMetadata.GeometryColumnName, geomMetadata.SRID,
geomMetadata.GetGeometryType(), geomMetadata.CoordDimension.ToString().ToUpper());
cmd = new SQLiteCommand(sql, conn);
nRows = cmd.ExecuteNonQuery();
transaction.Commit();
}
/// <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 SaveFeatureSet(IFeatureSet fs)
{
using (SQLiteConnection conn = OpenDatabase())
{
var meta = new SQLiteTableMetadata(fs.Name);
SQLiteCommand cmd = new SQLiteCommand("DROP TABLE IF EXISTS " + meta.TableName, conn);
cmd.ExecuteNonQuery();
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);
}
//TODO: check if EpsgCode is correct == SRID
var geomMeta = new GeometryColumnInfo(meta.TableName, fs.FeatureType, fs.Projection.EpsgCode);
colNames += "[" + geomMeta.GeometryColumnName + "]";
vals += "ST_GeomFromWKB(?, " + geomMeta.SRID + ")";
parms.Add(new SQLiteParameter(DbType.Object));
try
{
CreateFeatureSetTable(conn, meta, geomMeta);
}
catch (Exception ex)
{
conn.Close();
return(false);
}
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++)
{
UpdateCommandParameters(fs.Features[i], parms, colNamesInFS, fs.Projection.EpsgCode);
insCmd.ExecuteNonQuery();
}
}
catch (KeyNotFoundException kex)
{
//TODO: log this
trans.Rollback();
conn.Close();
return(false);
}
catch (Exception ex)
{
//TODO: log this
trans.Rollback();
conn.Close();
return(false);
}
trans.Commit();
conn.Close();
}
return(true);
}
/// <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 IFeatureSet ReadFeatureSet(string connString, GeometryColumnInfo featureSetInfo)
{
var sql = String.Format("SELECT * FROM {0}", featureSetInfo.TableName);
return(ReadFeatureSet(connString, featureSetInfo, sql));
}
