/// <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(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
{
string strGeom;
if (this.TargetSRID > 0 && this.SRID > 0 && this.SRID != this.TargetSRID)
strGeom = "ST.Transform(ST.GeomFromText('" + geom.AsText() + "'," + this.TargetSRID.ToString() + ")," + this.SRID.ToString() + ")";
else
strGeom = "ST.GeomFromText('" + geom.AsText() + "', " + this.SRID.ToString() + ")";
string strSQL = "SELECT " + this.FeatureColumns + ", ST.AsBinary(" + this.BuildGeometryExpression() + ") As sharpmap_tempgeometry ";
strSQL += "FROM ST.RelateQuery" + this.BuildSpatialQuerySuffix() + "(" + strGeom + ", 'intersects')";
if (!String.IsNullOrEmpty(this.DefinitionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
if (!String.IsNullOrEmpty(this.OrderQuery))
strSQL += " ORDER BY " + this.OrderQuery;
using (SqlDataAdapter adapter = new SqlDataAdapter(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.StartsWith(this.GeometryColumn + "_Envelope_") && 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.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
if (dr["sharpmap_tempgeometry"] != DBNull.Value)
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
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(SharpMap.Geometries.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 (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 SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance)
{
//Collection<Geometries.Geometry> features = new Collection<SharpMap.Geometries.Geometry>();
using (Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection(_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 (Npgsql.NpgsqlDataAdapter adapter = new Npgsql.NpgsqlDataAdapter(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 the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public void ExecuteIntersectionQuery(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
//TODO: Convert to SQL Server
string strGeom = "geography::STGeomFromText('" + geom.AsText() + "', #SRID#)";
if (this.SRID > 0) {
strGeom = strGeom.Replace("#SRID#", this.SRID.ToString());
} else {
strGeom = strGeom.Replace("#SRID#", "0");
}
strGeom = this.GeometryColumn + ".STIntersects(" + strGeom + ") = 1";
string strSQL = "SELECT g.* , g." + this.GeometryColumn + ").STAsBinary() As sharpmap_tempgeometry FROM " + this.Table + " g WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.DefinitionQuery + " AND ";
strSQL += strGeom;
using (SqlDataAdapter adapter = new SqlDataAdapter(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 SharpMap.Data.FeatureDataTable QueryFeatures(SharpMap.Geometries.Geometry geom, double distance)
{
//List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (SqlConnection conn = new SqlConnection(_ConnectionString))
{
string strGeom = "ST.GeomFromText('" + geom.AsText() + "', " + this.SRID.ToString() + ")";
string strSQL = "SELECT *, ST.AsBinary(" + this.BuildGeometryExpression() + ") As sharpmap_tempgeometry ";
strSQL += "FROM ST.IsWithinDistanceQuery" + this.BuildSpatialQuerySuffix() + "(" + strGeom + ", " + distance.ToString(Map.numberFormat_EnUS) + ")";
if (!String.IsNullOrEmpty(_definitionQuery))
strSQL += " WHERE " + this.DefinitionQuery;
using (SqlDataAdapter adapter = new SqlDataAdapter(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.StartsWith(this.GeometryColumn + "_Envelope_") && 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.StartsWith(this.GeometryColumn + "_Envelope_") && col.ColumnName != "sharpmap_tempgeometry")
fdr[col.ColumnName] = dr[col];
if (dr["sharpmap_tempgeometry"] != DBNull.Value)
fdr.Geometry = SharpMap.Converters.WellKnownBinary.GeometryFromWKB.Parse((byte[])dr["sharpmap_tempgeometry"]);
fdt.AddRow(fdr);
}
return fdt;
}
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(SharpMap.Geometries.Geometry geom, FeatureDataSet ds)
{
List<Geometries.Geometry> features = new List<SharpMap.Geometries.Geometry>();
using (OracleConnection conn = new OracleConnection(_ConnectionString))
{
string strGeom = "MDSYS.SDO_GEOMETRY('" + geom.AsText() + "', #SRID#)";
if (this.SRID > 0)
{
strGeom = strGeom.Replace("#SRID#", this.SRID.ToString(Map.numberFormat_EnUS));
}
else
{
strGeom = strGeom.Replace("#SRID#", "NULL");
}
strGeom = "SDO_RELATE(g." + this.GeometryColumn + ", " + strGeom + ", 'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'";
string strSQL = "SELECT g.* , g." + this.GeometryColumn + ").Get_WKB() As sharpmap_tempgeometry FROM " + this.Table + " g WHERE ";
if (!String.IsNullOrEmpty(_defintionQuery))
strSQL += this.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 (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);
}
}
}
}