public SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance)
{
Collection <Geometries.Geometry> features = new Collection <SharpMap.Geometries.Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
{
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
}
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.numberFormat_EnUS) + ")";
strSQL += " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.numberFormat_EnUS);
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
System.Data.DataSet ds = new System.Data.DataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.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);
}
}
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return(fdt);
}
else
{
return(null);
}
}
}
}
/// <summary>
/// Returns all features with the given view bounds.
/// </summary>
/// <param name="bounds">
/// The bounds of the view to query for intersection.
/// </param>
/// <param name="ds">
/// FeatureDataSet to fill data into.
/// </param>
public void ExecuteIntersectionQuery(BoundingBox bounds, FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_connectionString))
{
string strBbox = getBoundingBoxSql(bounds, Srid);
string sql = String.Format("SELECT *, AsBinary({0}) AS {1} FROM {2} WHERE ",
GeometryColumn, RetrievedGeometryColumnName, Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(sql, 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 != RetrievedGeometryColumnName)
{
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 != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])dr[RetrievedGeometryColumnName]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(Geometry geom, FeatureDataSet ds)
{
//List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (PgConnection conn = new PgConnection(_connectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (Srid > 0)
{
strGeom = "setSrid(" + strGeom + "," + Srid + ")";
}
string sql = "SELECT * , AsBinary(" + GeometryColumn + ") As " + RetrievedGeometryColumnName + " FROM " +
Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + strGeom + " AND distance(" + GeometryColumn + ", " + strGeom + ") < 0";
using (PgDataAdapter adapter = new PgDataAdapter(sql, 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 != RetrievedGeometryColumnName)
{
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 != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])dr[RetrievedGeometryColumnName]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <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(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
string strSQL = String.Format("SELECT *, AsBinary({0}) AS sharpmap_tempgeometry FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += this.DefinitionQuery + " AND ";
}
strSQL += this.GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
{
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="rowId"></param>
/// <returns>datarow</returns>
public FeatureDataRow GetFeature(uint rowId)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL =
String.Format("select * , AsBinary({0}) As sharpmap_tempgeometry from {1} WHERE {2} = '{3}'",
GeometryColumn, Table, ObjectIdColumn, rowId);
using (PgDataAdapter adapter = new PgDataAdapter(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);
}
}
}
}
/// <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(IEnvelope bbox, SharpMap.Data.FeatureDataSet ds)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
string strSQL = String.Format("SELECT *, AsBinary({0}) AS sharpmap_tempgeometry FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strBbox;
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
System.Data.DataSet ds2 = new System.Data.DataSet();
adapter.Fill(ds2);
conn.Close();
if (ds2.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
foreach (System.Data.DataRow dr in ds2.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds2.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="RowID"></param>
/// <returns>datarow</returns>
public SharpMap.Data.FeatureDataRow GetFeature(uint RowID)
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("select * , AsBinary({0}) As sharpmap_tempgeometry from {1} WHERE {2} = '{3}'",
this.GeometryColumn, this.Table, this.ObjectIdColumn, RowID);
using (PgDataAdapter adapter = new PgDataAdapter(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 (System.Data.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)
{
System.Data.DataRow dr = ds.Tables[0].Rows[0];
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
return fdr;
}
else
return null;
}
else
return null;
}
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds)
{
//List<IGeometry> features = new List<IGeometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (this.SRID > 0)
strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += this.GeometryColumn + " && " + strGeom + " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<0";
using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn))
{
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable fdt = new FeatureDataTable(ds.Tables[0]);
foreach (System.Data.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);
foreach (System.Data.DataRow dr in ds.Tables[0].Rows)
{
SharpMap.Data.FeatureDataRow fdr = fdt.NewRow();
foreach (System.Data.DataColumn col in ds.Tables[0].Columns)
if (col.ColumnName != this.GeometryColumn && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
ds.Tables.Add(fdt);
}
}
}
}
public FeatureDataTable QueryFeatures(Geometry geom, double distance)
{
Collection<Geometry> features = new Collection<Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strGeom = "GeomFromText('" + geom.AsText() + "')";
if (SRID > 0)
strGeom = "setSRID(" + strGeom + "," + SRID.ToString() + ")";
string strSQL = "SELECT * , AsBinary(" + GeometryColumn + ") As sharpmap_tempgeometry FROM " + Table +
" WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += DefinitionQuery + " AND ";
strSQL += GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.NumberFormatEnUs) +
")";
strSQL += " AND distance(" + GeometryColumn + ", " + strGeom + ")<" +
distance.ToString(Map.NumberFormatEnUs);
using (PgDataAdapter adapter = new PgDataAdapter(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 != "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 != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
fdr.Geometry = GeometryFromWKB.Parse((byte[]) dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return fdt;
}
else return null;
}
}
}
