/// <summary>
/// Function to get a specific feature from the database.
/// </summary>
/// <param name="oid">The object id</param>
/// <returns>A feature data row</returns>
protected virtual FeatureDataRow GetFeatureInternal(uint oid)
{
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectClause(From)
+ string.Format(" WHERE {0}={1};", _dbUtility.DecorateEntity("_smOid_"), oid);
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
var fdr = CreateNewTable().NewRow();
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr.GetValue(1), Factory);
fdr[0] = dr.GetValue(0);
for (var i = 2; i < dr.FieldCount; i++)
{
fdr[i - 1] = dr.GetValue(i);
}
return(fdr);
}
}
}
}
return(null);
}
/// <summary>
/// Function to get a specific feature from the database.
/// </summary>
/// <param name="oid">The object id</param>
/// <returns>A feature data row</returns>
protected virtual FeatureDataRow GetFeatureInternal(uint oid)
{
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectClause(From)
+ string.Format(" WHERE {0}={1};", _dbUtility.DecorateEntity(ObjectIdColumn), oid);
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
var fdt = CreateNewTable();
FeatureDataRow row = null;
fdt.BeginLoadData();
var numColumns = fdt.Columns.Count;
var data = new object[numColumns + 1];
if (dr.GetValues(data) > 0)
{
var loadData = new object[numColumns];
Array.Copy(data, 0, loadData, 0, numColumns);
row = (FeatureDataRow)fdt.LoadDataRow(loadData, true);
row.Geometry = GeometryFromWKB.Parse((byte[])data[numColumns], Factory);
}
fdt.EndLoadData();
return(row);
}
}
}
}
return(null);
}
/// <summary>
/// Method to get the number of features in the datasource
/// </summary>
/// <returns>The number of features</returns>
protected virtual int GetFeatureCountInternal()
{
using (var conn = CreateOpenDbConnection())
{
using (var command = conn.CreateCommand())
{
var sql = new StringBuilder();
sql.AppendFormat("SELECT COUNT(*) FROM {0}", _dbUtility.DecorateTable(Schema, Table));
#pragma warning disable 612,618
if (!String.IsNullOrEmpty(DefinitionQuery))
{
sql.AppendFormat(" WHERE {0}", DefinitionQuery);
}
#pragma warning restore 612,618
else
{
sql.Append(FeatureColumns.GetWhereClause());
}
sql.Append(";");
command.CommandText = sql.ToString();
return((int)command.ExecuteScalar());
}
}
}
/// <summary>
/// Function to generate a spatial where clause for the intersection queries.
/// </summary>
/// <param name="bbox">The geometry</param>
/// <param name="command">The command object, that is supposed to execute the query.</param>
/// <returns>The spatial component of a SQL where clause</returns>
protected override string GetSpatialWhere(IGeometry bbox, DbCommand command)
{
var sqlCommand = (SqlCommand)command;
#pragma warning disable 612,618
var pwhere = new SqlParameter("@PWhere", !string.IsNullOrEmpty(DefinitionQuery)
? DefinitionQuery
: FeatureColumns.GetWhereClause(null));
#pragma warning restore 612,618
sqlCommand.Parameters.Add(pwhere);
return(string.Format("{3} IN (SELECT _tmp_.{3} FROM ST.RelateQueryWhere('{0}', '{1}', {2}, 'Intersects', @PWhere) AS _tmp_)",
Table, GeometryColumn, BuildGeometry(bbox, sqlCommand), DbUtility.DecorateColumn(ObjectIdColumn)));
}
/// <summary>
/// Function to create a new, empty <see cref="FeatureDataTable"/>
/// </summary>
/// <param name="force">Value indicating that a new feature data table should be created, no matter what.</param>
/// <returns>A feature data table</returns>
protected virtual FeatureDataTable CreateNewTable(bool force)
{
if (force || _baseTable == null)
{
var fdt = new FeatureDataTable {
TableName = Name
};
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectClause(From);
using (var da = CreateDataAdapter())
{
da.SelectCommand = cmd;
fdt = (FeatureDataTable)da.FillSchema(fdt, SchemaType.Source);
}
}
}
//Remove the geometry column, which is always the last!
fdt.Columns.RemoveAt(fdt.Columns.Count - 1);
if (_baseTable == null)
{
_baseTable = fdt;
return(_baseTable);
}
return(fdt);
}
return(_baseTable);
/*
* var res = new FeatureDataTable(dt);
* var resColumns = res.Columns;
* foreach (var column in dt.Columns)
* {
* resColumns.Add(new DataColumn(column.))
* }
* res.PrimaryKey = new [] {res.Columns[0]};
* return res;
*/
}
/// <summary>
/// Function to get a specific feature's geometry from the database.
/// </summary>
/// <param name="oid">The object id</param>
/// <returns>A geometry</returns>
protected virtual IGeometry GetGeometryByIDInternal(uint oid)
{
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, ObjectIdColumn, oid);
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
var geometry = GeometryFromWKB.Parse((byte[])dr.GetValue(0), Factory);
return(geometry);
}
}
}
}
return(null);
}
///// <summary>
///// Returns geometries within the specified bounding box
///// </summary>
///// <param name="bbox"></param>
///// <returns></returns>
//protected override Collection<IGeometry> GetGeometriesInViewInternal(Envelope bbox)
//{
// var features = new Collection<IGeometry>();
// using (var conn = CreateOpenDbConnection())
// {
// var strSQL = "SELECT ST.AsBinary(" + BuildGeometryExpression() + ") ";
// strSQL += "FROM ST.FilterQuery" + BuildSpatialQuerySuffix() + "(" + BuildEnvelope(bbox) + ")";
// if (!String.IsNullOrEmpty(DefinitionQuery))
// strSQL += " WHERE " + DefinitionQuery;
// if (!String.IsNullOrEmpty(OrderQuery))
// strSQL += " ORDER BY " + OrderQuery;
// using (var command = new SqlCommand(strSQL, conn))
// {
// conn.Open();
// using (var dr = command.ExecuteReader())
// {
// while (dr.Read())
// {
// if (dr[0] != DBNull.Value)
// {
// var geom = GeometryFromWKB.Parse((byte[]) dr[0], Factory);
// if (geom != null)
// features.Add(geom);
// }
// }
// }
// conn.Close();
// }
// }
// return features;
//}
///// <summary>
///// Returns the geometry corresponding to the Object ID
///// </summary>
///// <param name="oid">Object ID</param>
///// <returns>geometry</returns>
//protected override IGeometry GetGeometryByIDInternal(uint oid)
//{
// using (var conn = CreateOpenDbConnection())
// {
// var strSQL = "SELECT ST.AsBinary(" + BuildGeometryExpression() + ") AS Geom FROM " + Table +
// " WHERE " + ObjectIdColumn + "='" + oid.ToString() + "'";
// using (var command = new SqlCommand(strSQL, conn))
// {
// using (var dr = command.ExecuteReader())
// {
// while (dr.Read())
// {
// if (dr[0] != DBNull.Value)
// return GeometryFromWKB.Parse((byte[]) dr[0], Factory);
// }
// }
// }
// }
// return null;
//}
/// <summary>
/// Gets the object of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>A collection of object ids</returns>
protected override Collection <uint> GetObjectIDsInViewInternal(Envelope bbox)
{
var objectlist = new Collection <uint>();
using (var conn = CreateOpenDbConnection())
{
using (var command = (SqlCommand)conn.CreateCommand())
{
#pragma warning disable 612,618
var @where = !string.IsNullOrEmpty(DefinitionQuery)
? DefinitionQuery
: FeatureColumns.GetWhereClause(null);
#pragma warning restore 612,618
var strSQL = string.Format("SELECT _sm_.{4} FROM ST.FilterQueryWhere('{0}','{1}',{3},'{2}') AS _sm_;",
Table, GeometryColumn, @where, BuildEnvelope(bbox, command), ObjectIdColumn);
#pragma warning disable 612, 618
if (!String.IsNullOrEmpty(OrderQuery))
{
strSQL += " ORDER BY " + OrderQuery;
}
#pragma warning restore 612, 618
command.CommandText = strSQL;
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
var id = Convert.ToUInt32(dr[0]);
objectlist.Add(id);
}
}
}
conn.Close();
}
}
return(objectlist);
}
///// <summary>
///// Returns the features that intersects with 'geom'
///// </summary>
///// <param name="geom"></param>
///// <param name="ds">FeatureDataSet to fill data into</param>
//protected override void OnExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds)
//{
// var features = new List<IGeometry>();
// using (var conn = CreateOpenDbConnection())
// {
// string strGeom;
// if (TargetSRID > 0 && SRID > 0 && SRID != TargetSRID)
// strGeom = "ST.Transform(ST.GeomFromText('" + geom.AsText() + "'," + TargetSRID.ToString(Map.NumberFormatEnUs) + ")," +
// SRID.ToString(Map.NumberFormatEnUs) + ")";
// else
// strGeom = "ST.GeomFromText('" + geom.AsText() + "', " + SRID.ToString(Map.NumberFormatEnUs) + ")";
// string strSQL = "SELECT " + FeatureColumns + ", ST.AsBinary(" + BuildGeometryExpression() +
// ") As sharpmap_tempgeometry ";
// strSQL += "FROM ST.RelateQuery" + BuildSpatialQuerySuffix() + "(" + strGeom + ", 'intersects')";
// if (!String.IsNullOrEmpty(DefinitionQuery))
// strSQL += " WHERE " + DefinitionQuery;
// if (!String.IsNullOrEmpty(OrderQuery))
// strSQL += " ORDER BY " + OrderQuery;
// using (var adapter = new SqlDataAdapter(strSQL, conn))
// {
// conn.Open();
// adapter.Fill(ds);
// conn.Close();
// if (ds.Tables.Count > 0)
// {
// FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
// foreach (DataColumn col in ds.Tables[0].Columns)
// if (col.ColumnName != GeometryColumn &&
// !col.ColumnName.StartsWith(GeometryColumn + "_Envelope_") &&
// col.ColumnName != "sharpmap_tempgeometry")
// fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// FeatureDataRow fdr = fdt.NewRow();
// foreach (DataColumn col in ds.Tables[0].Columns)
// if (col.ColumnName != GeometryColumn &&
// !col.ColumnName.StartsWith(GeometryColumn + "_Envelope_") &&
// col.ColumnName != "sharpmap_tempgeometry")
// fdr[col.ColumnName] = dr[col];
// if (dr["sharpmap_tempgeometry"] != DBNull.Value)
// fdr.Geometry = GeometryFromWKB.Parse((byte[]) dr["sharpmap_tempgeometry"], Factory);
// fdt.AddRow(fdr);
// }
// ds.Tables.Add(fdt);
// }
// }
// }
//}
///// <summary>
///// Spacial Reference ID handling
///// </summary>
//protected override int SRID
//{
// get { return base.SRID; }
// set
// {
// if (SRID == -2)
// return;
// {
// int dotPos = Table.IndexOf(".");
// string strSQL = "";
// if (dotPos == -1)
// strSQL = "select SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_NAME='" + Table + "'";
// else
// {
// var schema = Table.Substring(0, dotPos);
// var table = Table.Substring(dotPos + 1);
// strSQL = "select SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA='" + schema +
// "' AND F_TABLE_NAME='" + table + "'";
// }
// using (var conn = (SqlConnection)CreateOpenDbConnection())
// {
// using (var command = new SqlCommand(strSQL, conn))
// {
// try
// {
// conn.Open();
// base.SRID = (int) command.ExecuteScalar();
// conn.Close();
// }
// catch
// {
// base.SRID = -1;
// }
// }
// }
// }
// }
//}
///// <summary>
///// Returns a datarow based on a RowID
///// </summary>
///// <param name="rowId"></param>
///// <returns>datarow</returns>
//protected override FeatureDataRow GetFeatureInternal(uint rowId)
//{
// using (var conn = (SqlConnection)CreateOpenDbConnection())
// {
// string strSQL = "select " + FeatureColumns + ", ST.AsBinary(" + BuildGeometryExpression() +
// ") As sharpmap_tempgeometry from " + Table + " WHERE " + ObjectIdColumn + "='" +
// rowId.ToString() + "'";
// using (var adapter = new SqlDataAdapter(strSQL, conn))
// {
// FeatureDataSet ds = new FeatureDataSet();
// conn.Open();
// adapter.Fill(ds);
// conn.Close();
// if (ds.Tables.Count > 0)
// {
// FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
// foreach (DataColumn col in ds.Tables[0].Columns)
// if (col.ColumnName != GeometryColumn &&
// !col.ColumnName.StartsWith(GeometryColumn + "_Envelope_") &&
// col.ColumnName != "sharpmap_tempgeometry")
// fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
// if (ds.Tables[0].Rows.Count > 0)
// {
// DataRow dr = ds.Tables[0].Rows[0];
// FeatureDataRow fdr = fdt.NewRow();
// foreach (DataColumn col in ds.Tables[0].Columns)
// if (col.ColumnName != GeometryColumn &&
// !col.ColumnName.StartsWith(GeometryColumn + "_Envelope_") &&
// col.ColumnName != "sharpmap_tempgeometry")
// fdr[col.ColumnName] = dr[col];
// if (dr["sharpmap_tempgeometry"] != DBNull.Value)
// fdr.Geometry = GeometryFromWKB.Parse((byte[]) dr["sharpmap_tempgeometry"]);
// return fdr;
// }
// else
// return null;
// }
// else
// return null;
// }
// }
//}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
protected override Envelope GetExtentsInternal()
{
using (var conn = (SqlConnection)CreateOpenDbConnection())
{
#pragma warning disable 612,618
var where = (String.IsNullOrEmpty(DefinitionQuery)
? FeatureColumns.GetWhereClause()
: DefinitionQuery).Replace(" WHERE ", "").Replace("'", "''");
#pragma warning restore 612,618
var strSQL = string.Format("SELECT ST.AsBinary(ST.EnvelopeQueryWhere('{0}', '{1}', '{2}'))", /*DbUtility.DecorateTable(Schema, Table)*/ /* Schema, */ Table,
GeometryColumn, where);
using (var command = new SqlCommand(strSQL, conn))
{
var result = command.ExecuteScalar();
return(result == DBNull.Value
? null :
GeometryFromWKB.Parse((byte[])result, Factory).EnvelopeInternal);
}
}
}
/// <summary>
/// Function to get a specific feature's geometry from the database.
/// </summary>
/// <param name="oid">The object id</param>
/// <returns>A geometry</returns>
protected virtual IGeometry GetGeometryByIDInternal(uint oid)
{
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, _geometryColumn, oid);
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
var geometry = GeometryFromWKB.Parse((byte[])dr.GetValue(0), Factory);
return(geometry);
}
}
}
}
}
return(null);
}
/// <summary>
/// Gets the object of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>A collection of object ids</returns>
protected virtual Collection <uint> GetObjectIDsInViewInternal(Envelope bbox)
{
var res = new Collection <uint>();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, _dbUtility.DecorateEntity(ObjectIdColumn), GetSpatialWhere(bbox, cmd));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
res.Add(Convert.ToUInt32(dr.GetValue(0)));
}
}
}
}
}
return(res);
}
/// <summary>
/// Gets the geometries of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>Geometries within the specified <see cref="GeoAPI.Geometries.Envelope"/></returns>
protected virtual Collection <IGeometry> GetGeometriesInViewInternal(Envelope bbox)
{
var res = new Collection <IGeometry>();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, FeatureColumns.GetGeometryColumn(true), GetSpatialWhere(bbox, cmd));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
res.Add(GeometryFromWKB.Parse((byte[])dr.GetValue(0), Factory));
}
}
}
}
}
return(res);
}
/// <summary>
/// Gets the object ids of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>A collection of object ids</returns>
protected virtual IEnumerable <object> GetObjectIDsInViewInternal(Envelope bbox, CancellationToken?cancellationToken = null)
{
var res = new Collection <uint>();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, _oidColumn, GetSpatialWhere(bbox, cmd));
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
yield return(dr.GetValue(0));
}
}
}
}
}
}
/// <summary>
/// Gets the object ids of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>A collection of object ids</returns>
protected virtual Collection <uint> GetObjectIDsInViewInternal(Envelope bbox)
{
var res = new Collection <uint>();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, _oidColumn, GetSpatialWhere(bbox, cmd));
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
res.Add(Convert.ToUInt32(dr.GetValue(0)));
}
}
}
}
}
return(res);
}
/// <summary>
/// Gets the geometries of features that lie within the specified <see cref="GeoAPI.Geometries.Envelope"/>
/// </summary>
/// <param name="bbox">The bounding box</param>
/// <returns>Geometries within the specified <see cref="GeoAPI.Geometries.Envelope"/></returns>
protected virtual Collection <IGeometry> GetGeometriesInViewInternal(Envelope bbox, CancellationToken?cancellationToken = null)
{
var res = new Collection <IGeometry>();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = FeatureColumns.GetSelectColumnClause(cmd, _geometryColumn, GetSpatialWhere(bbox, cmd));
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
res.Add(GeometryFromWKB.Parse((byte[])dr.GetValue(0), Factory));
}
}
}
}
}
return(res);
}
/// <summary>
/// Returns the data associated with all the geometries that are intersected by 'geom'
/// </summary>
/// <param name="spatialWhere">Geometry to intersect with</param>
/// <param name="fds">FeatureDataSet to fill data into</param>
protected virtual void ExecuteIntersectionQueryInternal(object spatialWhere, FeatureDataSet fds)
{
var fdt = CreateNewTable(true);
fdt.BeginLoadData();
using (var cn = CreateOpenDbConnection())
{
using (var cmd = cn.CreateCommand())
{
string from = null;
var spatialWhereString = string.Empty;
var env = spatialWhere as Envelope;
if (env != null)
{
from = GetFrom(env, cmd);
spatialWhereString = GetSpatialWhere(env, cmd);
}
else
{
var geom = spatialWhere as IGeometry;
if (geom != null)
{
from = GetFrom(geom, cmd);
spatialWhereString = GetSpatialWhere(geom, cmd);
}
}
cmd.CommandText = FeatureColumns.GetSelectClause(from)
#pragma warning disable 612,618
+ (string.IsNullOrEmpty(DefinitionQuery)
#pragma warning restore 612,618
? FeatureColumns.GetWhereClause(spatialWhereString)
: (" WHERE " + _definitionQuery +
(string.IsNullOrEmpty(spatialWhereString)
? ""
: " AND " + spatialWhereString)))
+ FeatureColumns.GetGroupByClause()
+ FeatureColumns.GetOrderByClause();
var numColumns = fdt.Columns.Count;
var geomIndex = numColumns;
Logger.Debug(t => t("Executing query:\n{0}", PrintCommand(cmd)));
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var data = new object[numColumns + 1];
if (dr.GetValues(data) > 0)
{
var loadData = new object[geomIndex];
Array.Copy(data, 0, loadData, 0, geomIndex);
var row = (FeatureDataRow)fdt.LoadDataRow(loadData, true);
row.Geometry = GeometryFromWKB.Parse((byte[])data[geomIndex], Factory);
}
}
}
}
}
fdt.EndLoadData();
fds.Tables.Add(fdt);
}