/// <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)
{
List <GeometryColumnInfo> lst = new List <GeometryColumnInfo>();
string sql =
"SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns";
using (SQLiteCommand cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
GeometryColumnInfo gci = new GeometryColumnInfo();
gci.TableName = Convert.ToString(r["f_table_name"]);
gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]);
gci.GeometryType = Convert.ToString(r["type"]);
gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]);
gci.SRID = Convert.ToInt32(r["srid"]);
gci.SpatialIndexEnabled = false;
lst.Add(gci);
}
cmd.Connection.Close();
}
return(lst);
}
/// <summary>
/// Reads the complete feature set from the database
/// </summary>
/// <param name="connString">sqlite db connection string</param>
/// <param name="sqlQuery">the SQL Query string</param>
/// <returns>the resulting feature set</returns>
public IFeatureSet ReadFeatureSet(string connString, string sqlQuery)
{
//RunInitialCommands(connString);
//Find the geometry type and geometry column
GeometryColumnInfo geomInfo = FindGeometryColumnInfo(connString, sqlQuery);
return(ReadFeatureSet(connString, geomInfo, sqlQuery));
}
/// <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
{
TableName = Convert.ToString(r["f_table_name"]),
GeometryColumnName = Convert.ToString(r["f_geometry_column"]),
GeometryType = Convert.ToString(r["type"])
};
try
{
gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]);
}
catch (Exception)
{
var coords = Convert.ToString(r["coord_dimension"]);
switch (coords)
{
case "XY":
gci.CoordDimension = 2;
break;
case "XYZ":
case "XYM":
gci.CoordDimension = 3;
break;
case "XYZM":
gci.CoordDimension = 4;
break;
}
}
gci.Srid = Convert.ToInt32(r["srid"]);
gci.SpatialIndexEnabled = false;
lst.Add(gci);
}
cmd.Connection.Close();
}
return(lst);
}
// when clicking "OK"
private void BtnOkClick(object sender, EventArgs e)
{
foreach (DataGridViewRow r in dgGeometryColumns.SelectedRows)
{
GeometryColumnInfo item = r.DataBoundItem as GeometryColumnInfo;
if (item != null)
{
IFeatureSet fs = _slh.ReadFeatureSet(item);
_mainMap.Layers.Add(fs);
}
}
Close();
}
/// <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);
}
}
// when clicking "OK"
private void BtnOkClick(object sender, EventArgs e)
{
SpatiaLiteHelper slh = new SpatiaLiteHelper();
foreach (DataGridViewRow r in dgGeometryColumns.Rows)
{
if (r.Selected)
{
GeometryColumnInfo item = r.DataBoundItem as GeometryColumnInfo;
if (item != null)
{
IFeatureSet fs = slh.ReadFeatureSet(_connString, item);
_mainMap.Layers.Add(fs);
}
}
}
}
/// <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);
}
}
/// <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)
{
List<GeometryColumnInfo> lst = new List<GeometryColumnInfo>();
string sql =
"SELECT f_table_name, f_geometry_column, type, coord_dimension, srid, spatial_index_enabled FROM geometry_columns";
using (SQLiteCommand cmd = CreateCommand(connString, sql))
{
cmd.Connection.Open();
SQLiteDataReader r = cmd.ExecuteReader();
while (r.Read())
{
GeometryColumnInfo gci = new GeometryColumnInfo();
gci.TableName = Convert.ToString(r["f_table_name"]);
gci.GeometryColumnName = Convert.ToString(r["f_geometry_column"]);
gci.GeometryType = Convert.ToString(r["type"]);
gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]);
gci.SRID = Convert.ToInt32(r["srid"]);
gci.SpatialIndexEnabled = false;
lst.Add(gci);
}
cmd.Connection.Close();
}
return lst;
}
/// <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 = $"SELECT * FROM {featureSetInfo.TableName}";
return(ReadFeatureSet(connString, featureSetInfo, sql));
}
//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="featureSetInfo">information about the table.</param>
/// <returns>the resulting feature set.</returns>
public IFeatureSet ReadFeatureSet(GeometryColumnInfo featureSetInfo)
{
var sql = $"SELECT * FROM '{featureSetInfo.TableName}'";
return(ReadFeatureSet(featureSetInfo, sql));
}
/// <summary>
/// Finds a list of all valid geometry columns in the database.
/// </summary>
/// <returns>the list of geometry columns.</returns>
public List <GeometryColumnInfo> GetGeometryColumns()
{
if (_version4Plus)
{
var lst = new List <GeometryColumnInfo>();
var sql = "SELECT f_table_name, f_geometry_column, geometry_type, srid FROM geometry_columns";
using (var cmd = CreateCommand(ConnectionString, sql))
{
cmd.Connection.Open();
var r = cmd.ExecuteReader();
while (r.Read())
{
var gci = new GeometryColumnInfo
{
TableName = Convert.ToString(r["f_table_name"]),
GeometryColumnName = Convert.ToString(r["f_geometry_column"]),
Srid = Convert.ToInt32(r["srid"]),
SpatialIndexEnabled = false
};
if (int.TryParse(r["geometry_type"].ToString(), out int geometryType))
{
AssignGeometryTypeStringAndCoordDimension(geometryType, gci);
}
lst.Add(gci);
}
cmd.Connection.Close();
}
return(lst);
}
else
{
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(ConnectionString, sql))
{
cmd.Connection.Open();
var r = cmd.ExecuteReader();
while (r.Read())
{
var gci = new GeometryColumnInfo
{
TableName = Convert.ToString(r["f_table_name"]),
GeometryColumnName = Convert.ToString(r["f_geometry_column"]),
GeometryType = Convert.ToString(r["type"]),
Srid = Convert.ToInt32(r["srid"]),
SpatialIndexEnabled = false
};
try
{
gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]);
}
catch (Exception)
{
var coords = Convert.ToString(r["coord_dimension"]);
switch (coords)
{
case "XY":
gci.CoordDimension = 2;
break;
case "XYZ":
case "XYM":
gci.CoordDimension = 3;
break;
case "XYZM":
gci.CoordDimension = 4;
break;
}
}
lst.Add(gci);
}
cmd.Connection.Close();
}
return(lst);
}
}
/// <summary>
/// Gets the GeometryType string and the CoordDimension from the given geometryTypeInt and assigns them to the given GeometryColumnInfo.
/// </summary>
/// <param name="geometryTypeInt">geometryTypeInt that indicates the GeometryType and CoordDimension that was used.</param>
/// <param name="gci">The GeometryColumnInfo the values get assigned to.</param>
private static void AssignGeometryTypeStringAndCoordDimension(int geometryTypeInt, GeometryColumnInfo gci)
{
var dimensionBase = geometryTypeInt / 1000;
geometryTypeInt = geometryTypeInt - (dimensionBase * 1000); // get only the last number
switch (geometryTypeInt)
{
case 1:
gci.GeometryType = "point";
break;
case 2:
gci.GeometryType = "linestring";
break;
case 3:
gci.GeometryType = "polygon";
break;
case 4:
gci.GeometryType = "multipoint";
break;
case 5:
gci.GeometryType = "multilinestring";
break;
case 6:
gci.GeometryType = "multipolygon";
break;
case 7:
gci.GeometryType = "geometrycollection";
break;
default:
gci.GeometryType = "geometry";
break;
}
if (dimensionBase >= 3)
{
gci.CoordDimension = 4;
}
else if (dimensionBase >= 1)
{
gci.CoordDimension = 3;
}
else
{
gci.CoordDimension = 2;
}
}
/// <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)
{
string sql = String.Format("SELECT * FROM {0}", featureSetInfo.TableName);
return ReadFeatureSet(connString, featureSetInfo, sql);
}
/// <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;
}
}
/// <summary>
/// Gets the GeometryType string and the CoordDimension from the given geometryTypeInt and assigns them to the given GeometryColumnInfo.
/// </summary>
/// <param name="geometryTypeInt">geometryTypeInt that indicates the GeometryType and CoordDimension that was used.</param>
/// <param name="gci">The GeometryColumnInfo the values get assigned to.</param>
private static void AssignGeometryTypeStringAndCoordDimension(int geometryTypeInt, GeometryColumnInfo gci)
{
var dimensionBase = geometryTypeInt / 1000;
geometryTypeInt -= dimensionBase * 1000; // get only the last number
gci.GeometryType = geometryTypeInt switch
{
1 => "point",
2 => "linestring",
3 => "polygon",
4 => "multipoint",
5 => "multilinestring",
6 => "multipolygon",
7 => "geometrycollection",
_ => "geometry",
};
if (dimensionBase >= 3)
{
gci.CoordDimension = 4;
}
else if (dimensionBase >= 1)
{
gci.CoordDimension = 3;
}
else
{
gci.CoordDimension = 2;
}
}
/// <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));
}
/// <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 = Convert.ToString(r["type"]);
try
{
gci.CoordDimension = Convert.ToInt32(r["coord_dimension"]);
}
catch (Exception)
{
var coords = Convert.ToString(r["coord_dimension"]);
switch (coords)
{
case "XY":
gci.CoordDimension = 2;
break;
case "XYZ":
case "XYM":
gci.CoordDimension = 3;
break;
case "XYZ;":
gci.CoordDimension = 4;
break;
}
}
gci.SRID = Convert.ToInt32(r["srid"]);
gci.SpatialIndexEnabled = false;
lst.Add(gci);
}
cmd.Connection.Close();
}
return lst;
}
