📄 postgis2.cs
字号:
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);
}
}
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count = 0;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = "SELECT COUNT(*) FROM " + this.Table;
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return count;
}
#region IProvider Members
private string _defintionQuery;
/// <summary>
/// Definition query used for limiting dataset
/// </summary>
public string DefinitionQuery
{
get { return _defintionQuery; }
set { _defintionQuery = value; }
}
/*
/// <summary>
/// Gets a collection of columns in the dataset
/// </summary>
public System.Data.DataColumnCollection Columns1
{
get {
throw new NotImplementedException();
//using (PgConnection conn = new PgConnection(this.ConnectionString))
//{
// System.Data.DataColumnCollection columns = new System.Data.DataColumnCollection();
// string strSQL = "SELECT column_name, udt_name FROM information_schema.columns WHERE table_name='" + this.Table + "' ORDER BY ordinal_position";
// using (PgCommand command = new PgCommand(strSQL, conn))
// {
// conn.Open();
// using (PgDataReader dr = command.ExecuteReader())
// {
// while (dr.Read())
// {
// System.Data.DataColumn col = new System.Data.DataColumn((string)dr["column_name"]);
// switch((string)dr["udt_name"])
// {
// case "int4":
// col.DataType = typeof(Int32);
// break;
// case "int8":
// col.DataType = typeof(Int64);
// break;
// case "varchar":
// col.DataType = typeof(string);
// break;
// case "text":
// col.DataType = typeof(string);
// break;
// case "bool":
// col.DataType = typeof(bool);
// break;
// case "geometry":
// col.DataType = typeof(SharpMap.Geometries.Geometry);
// break;
// default:
// col.DataType = typeof(object);
// break;
// }
// columns.Add(col);
// }
// }
// }
// return columns;
//}
}
}
*/
private int _srid = -2;
/// <summary>
/// Spacial Reference ID
/// </summary>
public int SRID
{
get
{
if (_srid == -2)
{
string strSQL = "select srid from geometry_columns WHERE f_table_name = @Table";
using (PgConnection conn = new PgConnection(_ConnectionString))
using (PgCommand command = new PgCommand(strSQL, conn))
{
try
{
conn.Open();
command.Parameters.Add(new PgParameter("@Table", PgDbType.VarChar));
command.Parameters[0].Value = this._Table;
_srid = (int)command.ExecuteScalar();
conn.Close();
}
catch
{
_srid = -1;
}
}
}
return _srid;
}
set
{
throw (new ApplicationException("Spatial Reference ID cannot by set on a PostGIS table"));
}
}
/// <summary>
/// Queries the PostGIS database to get the name of the Geometry Column. This is used if the columnname isn't specified in the constructor
/// </summary>
/// <remarks></remarks>
/// <returns>Name of column containing geometry</returns>
private string GetGeometryColumn()
{
string strSQL = "select f_geometry_column from geometry_columns WHERE f_table_name = @Table'";
using (PgConnection conn = new PgConnection(_ConnectionString))
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
command.Parameters.Add(new PgParameter("@Table", PgDbType.VarChar));
command.Parameters[0].Value = this._Table;
object columnname = command.ExecuteScalar();
conn.Close();
if (columnname == System.DBNull.Value)
throw new ApplicationException("Table '" + this.Table + "' does not contain a geometry column");
return (string)columnname;
}
}
/// <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>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public SharpMap.Geometries.BoundingBox GetExtents()
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("SELECT EXTENT({0}) FROM {1}",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
strSQL += ";";
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
SharpMap.Geometries.BoundingBox bbox = null;
try
{
PostgreSql.Data.PgTypes.PgBox2D result = (PostgreSql.Data.PgTypes.PgBox2D)command.ExecuteScalar();
bbox = new SharpMap.Geometries.BoundingBox(result.LowerLeft.X, result.LowerLeft.Y, result.UpperRight.X, result.UpperRight.Y);
}
catch (System.Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. " + ex.Message);
}
finally
{
conn.Close();
}
return bbox;
}
}
}
/// <summary>
/// Gets the connection ID of the datasource
/// </summary>
public string ConnectionID
{
get { return _ConnectionString; }
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
[Obsolete("Use ExecuteIntersectionQuery")]
public void GetFeaturesInView(SharpMap.Geometries.BoundingBox bbox, SharpMap.Data.FeatureDataSet ds)
{
ExecuteIntersectionQuery(bbox, ds);
}
/// <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);
}
}
}
}
#endregion
#region * Sql builder methods *
/// <summary>
/// returns the Bounding Box Sql part for PostGis Extension queries
/// </summary>
/// <param name="bbox">Bounding Box</param>
/// <param name="iSRID">Spatial Reference Id</param>
/// <returns>String</returns>
private static string GetBoundingBoxSql(SharpMap.Geometries.BoundingBox bbox, int iSRID)
{
string strBbox = String.Format("box2d('BOX3D({0} {1},{2} {3})'::box3d)",
bbox.Min.X.ToString(SharpMap.Map.numberFormat_EnUS),
bbox.Min.Y.ToString(SharpMap.Map.numberFormat_EnUS),
bbox.Max.X.ToString(SharpMap.Map.numberFormat_EnUS),
bbox.Max.Y.ToString(SharpMap.Map.numberFormat_EnUS));
if (iSRID > 0)
strBbox = String.Format(SharpMap.Map.numberFormat_EnUS, "SetSRID({0},{1})", strBbox, iSRID);
return strBbox;
}
#endregion
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -