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