/// <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>
/// 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();
}
}
}
