/// <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)
{
DataTable tab = new DataTable();
FeatureType fType = GetGeometryType(featureSetInfo.GeometryType);
FeatureSet fs = new FeatureSet(fType);
fs.IndexMode = false; //setting the initial index mode..
using (SQLiteCommand cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
//DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader();
SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();
SQLiteDataReader rdr = cmd.ExecuteReader();
string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
int numColumns = fs.DataTable.Columns.Count;
while (rdr.Read())
{
byte[] wkb = rdr[featureSetInfo.GeometryColumnName] as byte[];
IGeometry geom = wkbr.Read(wkb);
IFeature newFeature = fs.AddFeature(geom);
//populate the attributes
foreach (string colName in columnNames)
{
newFeature.DataRow[colName] = rdr[colName];
}
}
cmd.Connection.Close();
fs.Name = featureSetInfo.TableName;
//HACK required for selection to work properly
fs.IndexMode = true;
//assign projection
ProjectionInfo 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();
if (schemaTable == null)
{
return(null);
}
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 {
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.GeometryType;
}
cmd.Connection.Close();
return(result);
}
}
/// <summary>
/// Reads the complete feature set from the database
/// </summary>
/// <param name="featureSetInfo">information about the table</param>
/// <param name="sql">the sql query</param>
/// <returns>the resulting feature set</returns>
public IFeatureSet ReadFeatureSet(GeometryColumnInfo featureSetInfo, string sql)
{
var fType = GetGeometryType(featureSetInfo.GeometryType);
SpatiaLiteFeatureSet fs = new SpatiaLiteFeatureSet(fType)
{
IndexMode = true, // setting the initial index mode..
Name = featureSetInfo.TableName,
Filename = SqLiteHelper.GetSqLiteFileName(ConnectionString),
LayerName = featureSetInfo.TableName
};
using (var cmd = CreateCommand(ConnectionString, sql))
{
cmd.Connection.Open();
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();
// assign projection
if (featureSetInfo.Srid > 0)
{
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 (SQLiteCommand cmd = CreateCommand(connString, sqlQuery))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();
SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
DataTable schemaTable = rdr.GetSchemaTable();
foreach (DataRow r in schemaTable.Rows)
{
string colName = Convert.ToString(r["ColumnName"]);
string 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();
byte[] blob = rdr[result.GeometryColumnName] as byte[];
IGeometry geom = wkbr.Read(blob);
result.GeometryType = geom.GeometryType;
}
cmd.Connection.Close();
return result;
}
}
/// <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)
{
DataTable tab = new DataTable();
FeatureType fType = GetGeometryType(featureSetInfo.GeometryType);
FeatureSet fs = new FeatureSet(fType);
fs.IndexMode = false; //setting the initial index mode..
using (SQLiteCommand cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
RunInitialCommands(cmd.Connection);
//DotSpatial.Topology.Utilities.WkbReader wkbr = new DotSpatial.Topology.Utilities.WkbReader();
SpatiaLiteWkbReader wkbr = new SpatiaLiteWkbReader();
SQLiteDataReader rdr = cmd.ExecuteReader();
string[] columnNames = PopulateTableSchema(fs, featureSetInfo.GeometryColumnName, rdr);
int numColumns = fs.DataTable.Columns.Count;
while (rdr.Read())
{
byte[] wkb = rdr[featureSetInfo.GeometryColumnName] as byte[];
IGeometry geom = wkbr.Read(wkb);
IFeature newFeature = fs.AddFeature(geom);
//populate the attributes
foreach (string colName in columnNames)
{
newFeature.DataRow[colName] = rdr[colName];
}
}
cmd.Connection.Close();
fs.Name = featureSetInfo.TableName;
//HACK required for selection to work properly
fs.IndexMode = true;
//assign projection
ProjectionInfo proj = ProjectionInfo.FromEpsgCode(featureSetInfo.SRID);
fs.Projection = proj;
return fs;
}
}
