public SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance) { Collection <Geometries.Geometry> features = new Collection <SharpMap.Geometries.Geometry>(); using (PgConnection conn = new PgConnection(_ConnectionString)) { string strGeom = "GeomFromText('" + geom.AsText() + "')"; if (this.SRID > 0) { strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")"; } string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) { strSQL += this.DefinitionQuery + " AND "; } strSQL += this.GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.numberFormat_EnUS) + ")"; strSQL += " AND distance(" + this.GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.numberFormat_EnUS); using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn)) { System.Data.DataSet ds = new System.Data.DataSet(); 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); } return(fdt); } else { return(null); } } } }
/// <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 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 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); } } } }
/// <summary> /// Returns a datarow based on a RowID /// </summary> /// <param name="rowId"></param> /// <returns>datarow</returns> public 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}'", GeometryColumn, Table, 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 (DataColumn col in ds.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdt.Columns.Add(col.ColumnName, col.DataType, col.Expression); } } if (ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; FeatureDataRow fdr = fdt.NewRow(); foreach (DataColumn col in ds.Tables[0].Columns) { if (col.ColumnName != GeometryColumn && col.ColumnName != "sharpmap_tempgeometry") { fdr[col.ColumnName] = dr[col]; } } fdr.Geometry = GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]); return(fdr); } else { return(null); } } else { 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(IEnvelope 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); } } } }
/// <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> /// Returns the features that intersects with 'geom' /// </summary> /// <param name="geom"></param> /// <param name="ds">FeatureDataSet to fill data into</param> public void ExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds) { //List<IGeometry> features = new List<IGeometry>(); using (PgConnection conn = new PgConnection(_ConnectionString)) { string strGeom = "GeomFromText('" + geom.AsText() + "')"; if (this.SRID > 0) strGeom = "setSRID(" + strGeom + "," + this.SRID.ToString() + ")"; string strSQL = "SELECT * , AsBinary(" + this.GeometryColumn + ") As sharpmap_tempgeometry FROM " + this.Table + " WHERE "; 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); } } } }
public FeatureDataTable QueryFeatures(Geometry geom, double distance) { Collection<Geometry> features = new Collection<Geometry>(); using (PgConnection conn = new PgConnection(_ConnectionString)) { string strGeom = "GeomFromText('" + geom.AsText() + "')"; if (SRID > 0) strGeom = "setSRID(" + strGeom + "," + SRID.ToString() + ")"; string strSQL = "SELECT * , AsBinary(" + GeometryColumn + ") As sharpmap_tempgeometry FROM " + Table + " WHERE "; if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += DefinitionQuery + " AND "; strSQL += GeometryColumn + " && " + "buffer(" + strGeom + "," + distance.ToString(Map.NumberFormatEnUs) + ")"; strSQL += " AND distance(" + GeometryColumn + ", " + strGeom + ")<" + distance.ToString(Map.NumberFormatEnUs); using (PgDataAdapter adapter = new PgDataAdapter(strSQL, conn)) { DataSet ds = new DataSet(); 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 != "sharpmap_tempgeometry") 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 != "sharpmap_tempgeometry") fdr[col.ColumnName] = dr[col]; fdr.Geometry = GeometryFromWKB.Parse((byte[]) dr["sharpmap_tempgeometry"]); fdt.AddRow(fdr); } return fdt; } else return null; } } }