/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public SharpMap.Geometries.Geometry GetGeometryByID(uint oid)
{
SharpMap.Geometries.Geometry geom = null;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
this.GeometryColumn,
this.Table,
this.ObjectIdColumn,
oid);
conn.Open();
using (PgCommand command = new PgCommand(strSql, conn))
{
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
object obj = dr[0];
if (typeof(PgPoint) == obj.GetType())
{
geom = new SharpMap.Geometries.Point(((PgPoint)obj).X, ((PgPoint)obj).Y);
}
else if (obj != DBNull.Value)
{
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
/// <summary>
/// Returns geometry object ids whose bounding box intersects
/// <paramref name="boundingBox"/>.
/// </summary>
/// <param name="boundingBox">The bounding box used to intersect.</param>
/// <returns></returns>
public IEnumerable <uint> GetIntersectingObjectIds(BoundingBox boundingBox)
{
using (PgConnection conn = new PgConnection(_connectionString))
{
string boundingBoxClause = getBoundingBoxSql(boundingBox, Srid);
String sql = String.Format("SELECT {0} FROM {1} WHERE ", ObjectIdColumn, Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += GeometryColumn + " && " + boundingBoxClause;
using (PgCommand command = new PgCommand(sql, conn))
{
conn.Open();
using (PgDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
yield return((uint)(int)reader[0]);
}
}
}
conn.Close();
}
}
}
/// <summary>
/// Returns a datarow based on an object id.
/// </summary>
/// <param name="oid">The id of the feature to retrieve.</param>
/// <returns>
/// A FeatureDataRow which has the feature geometry and attributes.
/// </returns>
public FeatureDataRow <uint> GetFeature(uint oid)
{
string sql = String.Format("SELECT *, AsBinary({0}) as {1} FROM {2} WHERE {3} = '{4}'",
GeometryColumn, RetrievedGeometryColumnName, Table, ObjectIdColumn, oid);
using (PgConnection conn = new PgConnection(_connectionString))
{
using (PgCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
conn.Open();
using (PgDataReader reader = cmd.ExecuteReader())
{
FeatureDataTable <uint> fdt = new FeatureDataTable <uint>(Table, ObjectIdColumn);
DataTable schemaTable = reader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
string columnName = row["ColumnName"] as string;
if (String.Compare(columnName, GeometryColumn, StringComparison.CurrentCultureIgnoreCase) ==
0 &&
columnName != RetrievedGeometryColumnName)
{
fdt.Columns.Add(columnName, row["DataType"] as Type);
}
}
while (reader.Read())
{
FeatureDataRow <uint> fdr = fdt.NewRow((uint)reader[ObjectIdColumn]);
foreach (DataColumn col in fdt.Columns)
{
if (
String.Compare(col.ColumnName, GeometryColumn,
StringComparison.CurrentCultureIgnoreCase) == 0 &&
col.ColumnName != RetrievedGeometryColumnName)
{
fdr[col.ColumnName] = reader[col.ColumnName];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])reader[RetrievedGeometryColumnName]);
return(fdr);
}
}
}
}
return(null);
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <Geometries.Geometry> GetGeometriesInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection <Geometries.Geometry> features = new Collection <SharpMap.Geometries.Geometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT AsBinary({0}) as geom FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSql += this.DefinitionQuery + " AND ";
}
strSql += String.Format("{0} && {1}", this.GeometryColumn, strBbox);
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//object obj = dr[0];
SharpMap.Geometries.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 = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
if (geom != null)
{
features.Add(geom);
}
}
}
conn.Close();
}
}
return(features);
}
/// <summary>
/// Computes a bounding box which covers all geometries in <see cref="Table"/>.
/// </summary>
/// <returns>
/// The bounding box which describes the maximum extents
/// of the data retrieved by the data source.
/// </returns>
public BoundingBox GetExtents()
{
using (PgConnection conn = new PgConnection(_connectionString))
{
string sql = String.Format("SELECT EXTENT({0}) FROM {1}",
GeometryColumn,
Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += " WHERE " + DefinitionQuery;
}
sql += ";";
using (PgCommand command = new PgCommand(sql, conn))
{
conn.Open();
BoundingBox bbox;
try
{
PgBox2D result = (PgBox2D)command.ExecuteScalar();
bbox =
new BoundingBox(result.LowerLeft.X, result.LowerLeft.Y, result.UpperRight.X,
result.UpperRight.Y);
}
catch (Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. ", ex);
}
finally
{
conn.Close();
}
return(bbox);
}
}
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>geometry</returns>
public Geometry GetGeometryById(uint oid)
{
Geometry geom = null;
using (PgConnection conn = new PgConnection(_connectionString))
{
String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
GeometryColumn,
Table,
ObjectIdColumn,
oid);
conn.Open();
using (PgCommand command = new PgCommand(strSql, conn))
{
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
object obj = dr[0];
if (obj is PgPoint)
{
PgPoint point = (PgPoint)obj;
geom = new Point(point.X, point.Y);
}
else if (obj != DBNull.Value)
{
geom = GeometryFromWkb.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
/// <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>
/// 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 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 = Convert.ToInt32(command.ExecuteScalar());
conn.Close();
}
}
return(count);
}
private void obtainSpatialReference()
{
// Get the Srid for the table
string sql = "SELECT srid FROM geometry_columns WHERE f_table_name = @Table";
using (PgConnection conn = new PgConnection(_connectionString))
{
using (PgCommand command = new PgCommand(sql, conn))
{
try
{
conn.Open();
command.Parameters.Add(new PgParameter("@Table", PgDbType.VarChar));
command.Parameters[0].Value = Table;
_srid = (int)command.ExecuteScalar();
}
catch (PgException) {}
}
}
}
/// <summary>
/// Returns the number of features in the dataset
/// </summary>
/// <returns>number of features</returns>
public int GetFeatureCount()
{
int count;
using (PgConnection conn = new PgConnection(_connectionString))
{
string sql = "SELECT COUNT(*) FROM " + Table;
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += " WHERE " + DefinitionQuery;
}
using (PgCommand command = new PgCommand(sql, conn))
{
conn.Open();
count = (int)command.ExecuteScalar();
conn.Close();
}
}
return(count);
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection <uint> GetObjectIDsInView(SharpMap.Geometries.BoundingBox bbox)
{
Collection <uint> objectlist = new Collection <uint>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT {0} FROM {1} WHERE ", this.ObjectIdColumn, this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSql += this.DefinitionQuery + " AND ";
}
strSql += this.GeometryColumn + " && " + strBbox;
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return(objectlist);
}
/// <summary>
/// Returns geometry Object IDs whose bounding box intersects 'bbox'
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection<uint> GetObjectIDsInView(IEnvelope bbox)
{
Collection<uint> objectlist = new Collection<uint>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT {0} FROM {1} WHERE ", this.ObjectIdColumn, this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSql += this.DefinitionQuery + " AND ";
strSql += this.GeometryColumn + " && " + strBbox;
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
uint ID = (uint)(int)dr[0];
objectlist.Add(ID);
}
}
}
conn.Close();
}
}
return objectlist;
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public BoundingBox GetExtents()
{
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strSQL = String.Format("SELECT EXTENT({0}) FROM {1}",
GeometryColumn,
Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += " WHERE " + DefinitionQuery;
strSQL += ";";
using (PgCommand command = new PgCommand(strSQL, conn))
{
conn.Open();
BoundingBox bbox = null;
try
{
PgBox2D result = (PgBox2D) command.ExecuteScalar();
bbox = new BoundingBox(result.LowerLeft.X, result.LowerLeft.Y, result.UpperRight.X,
result.UpperRight.Y);
}
catch (Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. " + ex.Message);
}
finally
{
conn.Close();
}
return bbox;
}
}
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public Collection<IGeometry> GetGeometriesInView(IEnvelope bbox)
{
Collection<IGeometry> features = new Collection<IGeometry>();
using (PgConnection conn = new PgConnection(_ConnectionString))
{
string strBbox = GetBoundingBoxSql(bbox, this.SRID);
String strSql = String.Format("SELECT AsBinary({0}) as geom FROM {1} WHERE ",
this.GeometryColumn,
this.Table);
if (!String.IsNullOrEmpty(_defintionQuery))
strSql += this.DefinitionQuery + " AND ";
strSql += String.Format("{0} && {1}", this.GeometryColumn, strBbox);
using (PgCommand command = new PgCommand(strSql, conn))
{
conn.Open();
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
//object obj = dr[0];
IGeometry 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 = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
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>
public IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (PgConnection conn = new PgConnection(_ConnectionString))
{
String strSql = String.Format("SELECT AsBinary({0}) As Geom FROM {1} WHERE {2} = '{3}'",
this.GeometryColumn,
this.Table,
this.ObjectIdColumn,
oid);
conn.Open();
using (PgCommand command = new PgCommand(strSql, conn))
{
using (PgDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
object obj = dr[0];
if (typeof(PgPoint) == obj.GetType())
geom = SharpMap.Converters.Geometries.GeometryFactory.CreatePoint(((PgPoint)obj).X, ((PgPoint)obj).Y);
else if (obj != DBNull.Value)
geom = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr[0]);
}
}
}
conn.Close();
}
return geom;
}
/// <summary>
/// Boundingbox of dataset
/// </summary>
/// <returns>boundingbox</returns>
public IEnvelope 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();
IEnvelope bbox = null;
try
{
PostgreSql.Data.PgTypes.PgBox2D result = (PostgreSql.Data.PgTypes.PgBox2D)command.ExecuteScalar();
bbox = SharpMap.Converters.Geometries.GeometryFactory.CreateEnvelope(result.LowerLeft.X, result.UpperRight.X, result.LowerLeft.Y, result.UpperRight.Y);
}
catch (System.Exception ex)
{
throw new Exception("Box2d couldn't fetched from table. " + ex.Message);
}
finally
{
conn.Close();
}
return bbox;
}
}
}
/// <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 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;
}
