/// <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 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>
/// 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>
/// 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);
}
