/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <Geometry> GetGeometriesInView(BoundingBox bbox)
{
Collection <Geometry> features = new Collection <Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, SRID);
String strSql = String.Format("SELECT AsBinary({0}) as geom FROM {1} WHERE ",
GeometryColumn,
Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSql += DefinitionQuery + " AND ";
}
strSql += String.Format("{0} && {1}", GeometryColumn, strBbox);
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//object obj = dr[0];
Geometry geom = null;
// if ( typeof(PgPoint) == obj.GetType() )
// geom = new SharpMap.Geometries.Point( ((PgPoint)obj).X, ((PgPoint)obj).Y );
// else
if (dr[0] != DBNull.Value)
{
geom = GeometryFromWKB.Parse((byte[])dr[0]);
}
if (geom != null)
{
features.Add(geom);
}
}
}
conn.Close();
}
}
return(features);
}
///// <summary>
///// Spacial Reference ID
///// </summary>
//public int SRID
//{
// get
// {
// if (_srid == -2)
// {
// string strSQL = "select SRID from USER_SDO_GEOM_METADATA WHERE TABLE_NAME='" + Table + "'";
// using (OracleConnection conn = new OracleConnection(ConnectionString))
// {
// using (OracleCommand command = new OracleCommand(strSQL, conn))
// {
// try
// {
// conn.Open();
// _srid = (int) (decimal) command.ExecuteScalar();
// conn.Close();
// }
// catch
// {
// _srid = -1;
// }
// }
// }
// }
// return _srid;
// }
// set { throw (new ApplicationException("Spatial Reference ID cannot by set on a Oracle table")); }
//}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="rowId"></param>
/// <returns>datarow</returns>
public override FeatureDataRow GetFeature(uint rowId)
{
using (var conn = new OracleConnection(ConnectionString))
{
string strSQL = "select g.* , g." + GeometryColumn + ").Get_WKB() As sharpmap_tempgeometry from " +
Table + " g WHERE " + ObjectIdColumn + "='" + rowId.ToString(NumberFormatInfo.InvariantInfo) + "'";
using (var adapter = new OracleDataAdapter(strSQL, conn))
{
var ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
var fdt = new FeatureDataTable(ds.Tables[0]);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && 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];
var fdr = fdt.NewRow();
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"], Factory);
return(fdr);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public FeatureDataRow GetFeature(uint RowID)
{
using (NpgsqlConnection conn = new NpgsqlConnection(_ConnectionString))
{
string strSQL = "select * , AsBinary(\"" + GeometryColumn + "\") As sharpmap_tempgeometry from " +
QualifiedTable + " WHERE \"" + ObjectIdColumn + "\"='" + RowID + "'";
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(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 != "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 != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return(fdr);
}
else
{
return(null);
}
}
else
{
return(null);
}
}
}
}
//public IFeature GetFeature(int index)
//{
// return GetFeature(index, null);
//}
public IFeature GetFeature(int index)
{
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
int rowId = index + 1; // index looks zero-based
string strSQL = String.Format(
"select g.* , g.{0}.STAsBinary() As sharpmap_tempgeometry from {1} g WHERE {2}={3}",
//"select g.* , g.{0}.STAsText() As sharpmap_tempgeometry from {1} g WHERE {2}={3}",
this.GeometryColumn, this.Table, this.ObjectIdColumn, rowId);
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
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 != this.GeometryColumn && 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 != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
//fdr.Geometry = SharpMap.Converters.WellKnownText.GeometryFromWKT.Parse(dr["sharpmap_tempgeometry"].ToString());
return(fdr);
}
return(null);
}
return(null);
}
}
}
public ICollection <IGeometry> GetGeometriesInView(IEnvelope bbox, double minGeometrySize)
{
if (bbox == null)
{
throw new ArgumentNullException("bbox");
}
Collection <IGeometry> features = new Collection <IGeometry>();
using (SqlConnection conn = new SqlConnection(this.connectionString))
{
string strBbox = this.GetBoxFilterStr(bbox);
string strSQL = String.Format("SELECT g.{0}.STAsBinary() FROM {0} g WHERE ", this.GeometryColumn, this.Table);
if (!String.IsNullOrEmpty(this.DefinitionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += strBbox;
Debug.WriteLine(strSQL);
Stopwatch watch = new Stopwatch();
watch.Start();
using (SqlCommand command = new SqlCommand(strSQL, conn))
{
conn.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
byte[] bytes = (byte[])dr[0];
IGeometry geom = GeometryFromWKB.Parse(bytes);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
watch.Stop();
Debug.WriteLine("Elapsed miliseconds: " + watch.ElapsedMilliseconds);
}
return(features);
}
public FeatureDataRow GetFeature(uint rowId)
{
using (SQLiteConnection conn = SpatiaLiteConnection(_connectionString))
{
string strSQL = "SELECT *, AsBinary(" + GeometryColumn + ") AS sharpmap_tempgeometry FROM " + Table +
" WHERE " + ObjectIdColumn + "='" + rowId + "'";
using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
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 != "sharpmap_tempgeometry" &&
!col.ColumnName.StartsWith("Envelope_"))
{
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 != "sharpmap_tempgeometry" &&
!col.ColumnName.StartsWith("Envelope_"))
{
fdr[col.ColumnName] = dr[col];
}
}
if (dr["sharpmap_tempgeometry"] != DBNull.Value)
{
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
}
return(fdr);
}
return(null);
}
return(null);
}
}
}
private static Geometry ParseOgrGeometry(OgrGeometry ogrGeometry, GeoAPI.Geometries.IGeometryFactory factory)
{
if (ogrGeometry != null)
{
//Just in case it isn't 2D
ogrGeometry.FlattenTo2D();
var wkbBuffer = new byte[ogrGeometry.WkbSize()];
ogrGeometry.ExportToWkb(wkbBuffer);
var geom = GeometryFromWKB.Parse(wkbBuffer, factory);
if (geom == null)
{
Debug.WriteLine("Failed to parse '{0}'", ogrGeometry.GetGeometryType());
}
return(geom);
}
return(null);
}
private static Geometry ParseOgrGeometry(OgrGeometry ogrGeometry)
{
if (ogrGeometry != null)
{
//Just in case it isn't 2D
ogrGeometry.FlattenTo2D();
byte[] wkbBuffer = new byte[ogrGeometry.WkbSize()];
ogrGeometry.ExportToWkb(wkbBuffer);
Geometry geom = GeometryFromWKB.Parse(wkbBuffer);
if (geom == null)
{
Debug.WriteLine(string.Format("Failed to parse '{0}'", ogrGeometry.GetGeometryType()));
}
return(geom);
}
return(null);
}
/// <summary>
/// Converts an FDO Geometry to a SharpMap geometry
/// </summary>
/// <param name="geom">The FDO geometry</param>
/// <returns></returns>
public static Sm.Geometry FromFdoGeometry(FdoGeometry geom, OSGeo.FDO.Geometry.FgfGeometryFactory geomFactory)
{
if (FdoGeometryUtil.Is2D(geom.InternalInstance))
{
//Get the WKB form of the geometry
byte[] wkb = FdoGeometryFactory.Instance.GetWkb(geom.InternalInstance);
return(GeometryFromWKB.Parse(wkb));
}
else
{
using (OSGeo.FDO.Geometry.IGeometry flattened = FdoGeometryUtil.Flatten(geom.InternalInstance, geomFactory))
{
//Get the WKB form of the geometry
byte[] wkb = FdoGeometryFactory.Instance.GetWkb(flattened);
return(GeometryFromWKB.Parse(wkb));
}
}
}
public void Convert()
{
IGeometry gML0 = GeometryFromWKT.Parse(multiLinestring);
IGeometry gLi0 = GeometryFromWKT.Parse(linestring);
IGeometry gPl0 = GeometryFromWKT.Parse(polygon);
IGeometry gPn0 = GeometryFromWKT.Parse(point);
IGeometry gMp0 = GeometryFromWKT.Parse(multipoint);
IGeometry gML1 = GeometryFromWKB.Parse(gML0.AsBinary());
IGeometry gLi1 = GeometryFromWKB.Parse(gLi0.AsBinary());
IGeometry gPl1 = GeometryFromWKB.Parse(gPl0.AsBinary());
IGeometry gPn1 = GeometryFromWKB.Parse(gPn0.AsBinary());
IGeometry gMp1 = GeometryFromWKB.Parse(gMp0.AsBinary());
Assert.AreEqual(gML0, gML1);
Assert.AreEqual(gLi0, gLi1);
Assert.AreEqual(gPl0, gPl1);
Assert.AreEqual(gPn0, gPn1);
Assert.AreEqual(gMp0, gMp1);
}
/// <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>
///// Spacial Reference ID
///// </summary>
//public int SRID
//{
// get
// {
// if (_srid == -2)
// {
// string strSQL = "select SRID from USER_SDO_GEOM_METADATA WHERE TABLE_NAME='" + Table + "'";
// using (OracleConnection conn = new OracleConnection(ConnectionString))
// {
// using (OracleCommand command = new OracleCommand(strSQL, conn))
// {
// try
// {
// conn.Open();
// _srid = (int) (decimal) command.ExecuteScalar();
// conn.Close();
// }
// catch
// {
// _srid = -1;
// }
// }
// }
// }
// return _srid;
// }
// set { throw (new ApplicationException("Spatial Reference ID cannot by set on a Oracle table")); }
//}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="oid"></param>
/// <returns>datarow</returns>
public override IFeature GetFeatureByOid(object oid)
{
using (var conn = new OracleConnection(ConnectionString))
{
string strSQL = "select g.* , g." + GeometryColumn + ").Get_WKB() As sharpmap_tempgeometry from " +
Table + " g WHERE " + ObjectIdColumn + "=:POid";
using (var adapter = new OracleDataAdapter(strSQL, conn))
{
adapter.SelectCommand.Parameters.Add("POid", oid);
var ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
var fdt = new FeatureDataTable(ds.Tables[0]);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && 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];
var fdr = fdt.NewRow();
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"], Factory);
return(fdr);
}
}
return(null);
}
}
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public override Collection <Geometry> GetGeometriesInView(BoundingBox bbox)
{
var features = new Collection <Geometry>();
using (var conn = new OracleConnection(ConnectionString))
{
//Get bounding box string
string strBbox = GetBoxFilterStr(bbox);
//string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
string strSQL = "SELECT g." + GeometryColumn + ".Get_WKB() ";
strSQL += " FROM " + Table + " g WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
strSQL += strBbox;
using (OracleCommand command = new OracleCommand(strSQL, conn))
{
conn.Open();
using (OracleDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
Geometry geom = GeometryFromWKB.Parse((byte[])dr[0], Factory);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
}
return(features);
}
public void Convert()
{
var gPn0 = GeometryFromWKT.Parse(point);
var gMp0 = GeometryFromWKT.Parse(multipoint);
var gLi0 = GeometryFromWKT.Parse(linestring);
var gML0 = GeometryFromWKT.Parse(multiLinestring);
var gPl0 = GeometryFromWKT.Parse(polygon);
var gPn1 = GeometryFromWKB.Parse(gPn0.AsBinary(), gPn0.Factory);
var gMp1 = GeometryFromWKB.Parse(gMp0.AsBinary(), gPn0.Factory);
var gLi1 = GeometryFromWKB.Parse(gLi0.AsBinary(), gPn0.Factory);
var gML1 = GeometryFromWKB.Parse(gML0.AsBinary(), gPn0.Factory);
var gPl1 = GeometryFromWKB.Parse(gPl0.AsBinary(), gPn0.Factory);
Assert.IsTrue(gPn0.EqualsExact(gPn1));
Assert.IsTrue(gMp0.EqualsExact(gMp1));
Assert.IsTrue(gLi0.EqualsExact(gLi1));
Assert.IsTrue(gML0.EqualsExact(gML1));
Assert.IsTrue(gPl0.EqualsExact(gPl1));
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public BoundingBox GetExtents()
{
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
string strSQL = string.Format("SELECT ST.AsBinary(ST.EnvelopeQueryWhere('{0}', '{1}', '{2}'))", Table,
GeometryColumn, DefinitionQuery.Replace("'", "''"));
using (SqlCommand command = new SqlCommand(strSQL, conn))
{
conn.Open();
object result = command.ExecuteScalar();
conn.Close();
if (result == DBNull.Value)
{
return(null);
}
BoundingBox bbox = GeometryFromWKB.Parse((byte[])result).GetBoundingBox();
return(bbox);
}
}
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <Geometry> GetGeometriesInView(BoundingBox bbox)
{
Collection <Geometry> features = new Collection <Geometry>();
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
string 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 (SqlCommand command = new SqlCommand(strSQL, conn))
{
conn.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
Geometry geom = GeometryFromWKB.Parse((byte[])dr[0]);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
}
return(features);
}
public void TestHugeGeometryCollection()
{
IGeometry geom = null;
if (!File.Exists("TestData\\Base 64.txt"))
{
throw new IgnoreException("Test file not present!", new FileNotFoundException("TestData\\Base 64.txt"));
}
using (var sr = new StreamReader("TestData\\Base 64.txt"))
{
var sb = new StringBuilder(sr.ReadLine());
while (!sr.EndOfStream)
{
sb.AppendLine(sr.ReadLine());
}
var wkb = System.Convert.FromBase64String(sb.ToString());
Assert.DoesNotThrow(() => geom = GeometryFromWKB.Parse(wkb, new GeometryFactory()));
}
Assert.IsTrue(geom.OgcGeometryType == OgcGeometryType.GeometryCollection);
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public override IEnumerable <IGeometry> GetGeometriesInView(Envelope bbox, CancellationToken?cancellationToken = null)
{
using (var conn = new OracleConnection(ConnectionString))
{
//Get bounding box string
string strBbox = GetBoxFilterStr(bbox);
//string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
string strSQL = "SELECT g." + GeometryColumn + ".Get_WKB() ";
strSQL += " FROM " + Table + " g WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
strSQL += strBbox;
using (var command = new OracleCommand(strSQL, conn))
{
conn.Open();
using (var dr = command.ExecuteReader())
{
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
var geom = GeometryFromWKB.Parse((byte[])dr[0], Factory);
if (geom != null)
{
yield return(geom);
}
}
}
}
conn.Close();
}
}
}
///// <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>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <IGeometry> GetGeometriesInView(Envelope bbox)
{
var features = new Collection <IGeometry>();
using (var conn = new SqlConnection(_connectionString))
{
var boxIntersect = GetBoxClause(bbox);
var strSql = "SELECT " + GeometryColumn + " AS Geom ";
strSql += "FROM " + Table + " WHERE ";
strSql += boxIntersect;
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSql += " AND " + DefinitionQuery;
}
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 geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <Geometry> GetGeometriesInView(BoundingBox bbox)
{
Collection <Geometry> features = new Collection <Geometry>();
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
string BoxIntersect = GetBoxClause(bbox);
string strSQL = "SELECT " + GeometryColumn + " AS Geom ";
strSQL += "FROM " + Table + " WHERE ";
strSQL += BoxIntersect;
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += " AND " + DefinitionQuery;
}
using (SqlCommand command = new SqlCommand(strSQL, conn))
{
conn.Open();
using (SqlDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
Geometry geom = GeometryFromWKB.Parse((byte[])dr[0]);
if (geom != null)
{
features.Add(geom);
}
}
}
}
conn.Close();
}
}
return(features);
}
/// <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 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 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);
}
public FeatureDataTable QueryFeatures(Geometry geom, double distance)
{
//List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
string strGeom;
if (TargetSRID > 0 && SRID > 0 && SRID != TargetSRID)
{
strGeom = "ST.Transform(ST.GeomFromText('" + geom.AsText() + "'," + TargetSRID.ToString() + ")," +
SRID.ToString() + ")";
}
else
{
strGeom = "ST.GeomFromText('" + geom.AsText() + "', " + SRID.ToString() + ")";
}
string strSQL = "SELECT " + FeatureColumns + ", ST.AsBinary(" + BuildGeometryExpression() +
") As sharpmap_tempgeometry ";
strSQL += "FROM ST.IsWithinDistanceQuery" + BuildSpatialQuerySuffix() + "(" + strGeom + ", " +
distance.ToString(Map.NumberFormatEnUs) + ")";
if (!String.IsNullOrEmpty(DefinitionQuery))
{
strSQL += " WHERE " + DefinitionQuery;
}
if (!String.IsNullOrEmpty(OrderQuery))
{
strSQL += " ORDER BY " + OrderQuery;
}
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
{
DataSet ds = new DataSet();
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"]);
}
fdt.AddRow(fdr);
}
return(fdt);
}
else
{
return(null);
}
}
}
}
public static Geometry GeomFromWKB(byte[] WKB)
{
return(GeometryFromWKB.Parse(WKB));
}
public IList GetFeatures(IEnvelope box)
{
if (box == null)
{
throw new ArgumentNullException("box");
}
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
string strBbox = this.GetBoxFilterStr(box);
string strSQL = String.Format(
"SELECT g.*, g.{0}{1}.STAsBinary() AS sharpmap_tempgeometry FROM {2} g WHERE ",
//"SELECT g.*, g.{0}{1}.STAsText() AS sharpmap_tempgeometry FROM {2} g WHERE ",
this.GeometryColumn, this.MakeValidString, this.Table);
if (!String.IsNullOrEmpty(this.DefinitionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += strBbox;
Debug.WriteLine(strSQL);
Stopwatch watch = new Stopwatch();
watch.Start();
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet dataset = new System.Data.DataSet();
adapter.Fill(dataset);
conn.Close();
if (dataset.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(dataset.Tables[0]);
foreach (DataColumn col in dataset.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (DataRow dr in dataset.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in dataset.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
//fdr.Geometry = SharpMap.Converters.WellKnownText.GeometryFromWKT.Parse(dr["sharpmap_tempgeometry"].ToString());
fdt.AddRow(fdr);
}
return(fdt);
}
}
return(null);
}
finally
{
watch.Stop();
Debug.WriteLine("Elapsed miliseconds: " + watch.ElapsedMilliseconds);
}
}
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(BoundingBox bbox, FeatureDataSet ds)
{
List <Geometry> features = new List <Geometry>();
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
string strSQL = "SELECT " + FeatureColumns + ", ST.AsBinary(" + BuildGeometryExpression() +
") AS sharpmap_tempgeometry ";
strSQL += "FROM ST.FilterQuery" + BuildSpatialQuerySuffix() + "(" + BuildEnvelope(bbox) + ")";
if (!String.IsNullOrEmpty(DefinitionQuery))
{
strSQL += " WHERE " + DefinitionQuery;
}
if (!String.IsNullOrEmpty(OrderQuery))
{
strSQL += " ORDER BY " + OrderQuery;
}
using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, conn))
{
conn.Open();
DataSet ds2 = new DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (DataColumn col in ds2.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 ds2.Tables[0].Rows)
{
FeatureDataRow fdr = fdt.NewRow();
foreach (DataColumn col in ds2.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"]);
}
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <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);
}
/// <summary>
/// Initializes a new instance of the <see cref="MemoryProvider"/>
/// </summary>
/// <param name="wellKnownBinaryGeometry"><see cref="Geometry"/> as Well-known Binary to be included in this datasource</param>
public MemoryProvider(byte[] wellKnownBinaryGeometry) : this(GeometryFromWKB.Parse(wellKnownBinaryGeometry))
{
}