/// <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 (OracleConnection conn = new OracleConnection(_connectionString))
{
string sql = "SELECT g." + GeometryColumn + ".Get_WKB() FROM " + Table
+ " g WHERE " + ObjectIdColumn + "='" + oid + "'";
conn.Open();
using (OracleCommand command = new OracleCommand(sql, conn))
{
using (OracleDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
if (dr[0] != DBNull.Value)
{
geom = GeometryFromWkb.Parse((byte[])dr[0]);
}
}
}
}
conn.Close();
}
return(geom);
}
private Geometry parseOgrGeometry(OgrGeometry OgrGeometry)
{
byte[] wkbBuffer = new byte[OgrGeometry.WkbSize()];
int i = OgrGeometry.ExportToWkb(wkbBuffer);
return(GeometryFromWkb.Parse(wkbBuffer));
}
/// <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 a feature based on an object id.
/// </summary>
/// <param name="oid"></param>
/// <returns>datarow</returns>
public FeatureDataRow <uint> GetFeature(uint oid)
{
using (OracleConnection conn = new OracleConnection(_connectionString))
{
string sql = "SELECT g.* , g." + GeometryColumn + ").Get_WKB() As " + RetrievedGeometryColumnName
+ " FROM " + Table + " g WHERE " + ObjectIdColumn + "='" + oid + "'";
using (OracleDataAdapter adapter = new OracleDataAdapter(sql, conn))
{
FeatureDataSet ds = new FeatureDataSet();
conn.Open();
adapter.Fill(ds);
conn.Close();
if (ds.Tables.Count > 0)
{
FeatureDataTable <uint> fdt = new FeatureDataTable <uint>(ds.Tables[0], ObjectIdColumn);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn && col.ColumnName != RetrievedGeometryColumnName)
{
fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression);
}
}
if (ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
FeatureDataRow <uint> fdr = fdt.NewRow((uint)dr[ObjectIdColumn]);
foreach (DataColumn col in ds.Tables[0].Columns)
{
if (col.ColumnName != GeometryColumn &&
col.ColumnName != RetrievedGeometryColumnName &&
col.ColumnName != ObjectIdColumn)
{
fdr[col.ColumnName] = dr[col];
}
}
fdr.Geometry = GeometryFromWkb.Parse((byte[])dr[RetrievedGeometryColumnName]);
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(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 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 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)
{
using (OracleConnection conn = new OracleConnection(_connectionString))
{
//Get bounding box string
string strBbox = getBoxFilterClause(bbox);
string sql = "SELECT g.*, g." + GeometryColumn + ".Get_WKB() AS " + RetrievedGeometryColumnName
+ "FROM " + Table + " g WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += strBbox;
using (OracleDataAdapter adapter = new OracleDataAdapter(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 an enumeration of Geometry objects which
/// intersect the specified bounding box.
/// </summary>
/// <param name="boundingBox">
/// The region to compute intersection with.
/// </param>
/// <returns>
/// A set of geometries which are at least partially
/// contained within <paramref name="boundingBox"/>.
/// </returns>
public IEnumerable <Geometry> GetGeometriesInView(BoundingBox boundingBox)
{
Collection <Geometry> features = new Collection <Geometry>();
using (PgConnection conn = new PgConnection(_connectionString))
{
string strBbox = getBoundingBoxSql(boundingBox, 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>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public IEnumerable <Geometry> GetGeometriesInView(BoundingBox bbox)
{
Collection <Geometry> features = new Collection <Geometry>();
using (OracleConnection conn = new OracleConnection(_connectionString))
{
//Get bounding box string
string strBbox = getBoxFilterClause(bbox);
//string strSQL = "SELECT AsBinary(" + this.GeometryColumn + ") AS Geom ";
string sql = "SELECT g." + GeometryColumn + ".Get_WKB() ";
sql += " FROM " + Table + " g WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
sql += DefinitionQuery + " AND ";
}
sql += strBbox;
using (OracleCommand command = new OracleCommand(sql, conn))
{
conn.Open();
using (OracleDataReader 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>
/// 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 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)
{
using (OracleConnection conn = new OracleConnection(_connectionString))
{
string strGeom = "MDSYS.SDO_GEOMETRY('" + geom.AsText() + "', #SRID#)";
if (Srid != null)
{
strGeom = strGeom.Replace("#SRID#", Srid.Value.ToString(NumberFormat_enUS));
}
else
{
strGeom = strGeom.Replace("#SRID#", "NULL");
}
strGeom = "SDO_RELATE(g." + GeometryColumn + ", " + strGeom + ", 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'";
string strSQL = "SELECT g.*, g." + GeometryColumn + ").Get_WKB() As " + RetrievedGeometryColumnName
+ " FROM " + Table + " g WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
{
strSQL += DefinitionQuery + " AND ";
}
strSQL += strGeom;
using (OracleDataAdapter adapter = new OracleDataAdapter(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 != 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);
}
}
}
}
