public ProviderQueryExpression(
ProviderPropertiesExpression providerPropertiesExpression,
ProjectionExpression projection,
PredicateExpression predicate)
: this(providerPropertiesExpression, projection, predicate, null)
{
}
public ProviderQueryExpression(
ProviderPropertiesExpression providerPropertiesExpression,
ProjectionExpression projection,
PredicateExpression predicate)
: this(providerPropertiesExpression, projection, predicate, null)
{
}
public ProviderQueryExpression(
ProviderPropertiesExpression providerPropertiesExpression,
ProjectionExpression projection,
PredicateExpression predicate,
SortExpressionCollectionExpression sort)
: base(projection, predicate, sort)
{
_providerPropertiesExpression = providerPropertiesExpression;
}
public ProviderQueryExpression(
ProviderPropertiesExpression providerPropertiesExpression,
ProjectionExpression projection,
PredicateExpression predicate,
SortExpressionCollectionExpression sort)
: base(projection, predicate, sort)
{
_providerPropertiesExpression = providerPropertiesExpression;
}
public void T11_JD_Test()
{
//return;
var search = new SpatiaLite2Provider(_geometryFactory,
@"Data Source=C:\VENUS\CodePlex\sharpMap\SharpMap\TestData\VRS2386_V11.sqlite", "main",
"regions", "OID", "XGeometryX");
//search.SpatiaLiteIndexType = SpatiaLite2_IndexType.MBRCache;
var binaryExpression =
new BinaryExpression(new PropertyNameExpression("VHG5"),
BinaryOperator.GreaterThan, new LiteralExpression<int>(6));
var columns = new string[] { "Einwohner" };
search.DefaultProviderProperties =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[] { new AttributesCollectionExpression( columns ) }
);
var prov = new ProviderQueryExpression(
(ProviderPropertiesExpression)null,
new AllAttributesExpression(),
binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
SharpMap.Data.Providers.Db.SpatialDbFeatureDataReader sfdr = obj as SharpMap.Data.Providers.Db.SpatialDbFeatureDataReader;
Assert.IsNotNull(sfdr);
Int64 numRows = 0;
DateTime start = DateTime.Now;
while (sfdr.Read())
numRows++;
DateTime end = DateTime.Now;
System.Diagnostics.Debug.WriteLine(end.Subtract(start).ToString());
search.DefaultProviderProperties = null;
obj = search.ExecuteQuery(prov);
sfdr = obj as SharpMap.Data.Providers.Db.SpatialDbFeatureDataReader;
Assert.IsTrue(numRows > 1);
start = DateTime.Now;
while (sfdr.Read())
numRows++;
end = DateTime.Now;
System.Diagnostics.Debug.WriteLine(end.Subtract(start).ToString());
var providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new OrderByCollectionExpression(
new OrderByExpression("Einwohner", System.Data.SqlClient.SortOrder.Descending)),
new DataPageSizeExpression(10),
new DataPageNumberExpression(5)
});
var ape = new AttributesProjectionExpression(new String[]{"OID" ,"Einwohner", "Erwerbstätige", "Beschäftigte"});
prov = new ProviderQueryExpression(providerProps, ape, null);
sfdr = search.ExecuteQuery(prov) as SharpMap.Data.Providers.Db.SpatialDbFeatureDataReader;
Assert.IsNotNull(sfdr);
numRows = 0;
while (sfdr.Read()) numRows++;
Assert.AreEqual(10, numRows);
Assert.AreEqual(4, sfdr.FieldCount);
}
public void T11_JD_Test()
{
PostGisProvider<int> search = new PostGisProvider<Int32>(_geometryFactory,
connectionString, "public",
"vw_osm_germany_line", "osm_id", "way");
BinaryExpression binaryExpression =
new BinaryExpression(new PropertyNameExpression("z_order"),
BinaryOperator.Equals, new LiteralExpression<int>(0));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[] {});
ProviderQueryExpression prov = new ProviderQueryExpression(null as ProviderPropertiesExpression,
new AllAttributesExpression(), binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
PostGisFeatureDataReader sfdr = obj as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
Int64 numRows = 0;
while (sfdr.Read())
numRows++;
Assert.IsTrue(numRows > 1);
providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(
// new OrderByExpression("z_order", SortOrder.Descending)),
new DataPageSizeExpression(10),
new DataPageNumberExpression(5)
});
AttributesProjectionExpression ape =
new AttributesProjectionExpression(new[] {"osm_id", "name", "z_order", "way"});
prov = new ProviderQueryExpression(providerProps, ape, null);
sfdr = search.ExecuteQuery(prov) as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
numRows = 0;
while (sfdr.Read()) numRows++;
Assert.AreEqual(10, numRows);
Debug.WriteLine("\r\n*** T11 passed!");
}
public void TestSqlServer2008()
{
GeometryServices services = new GeometryServices();
MsSqlServer2008Provider<long> search = new MsSqlServer2008Provider<long>(services.DefaultGeometryFactory,
ConfigurationManager.
ConnectionStrings["sql2008"].
ConnectionString,
"dbo",
"vw_iMARS_BRANCH", "ACSId", "Geom")
{
DefaultProviderProperties
= new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
new ForceIndexExpression(true)
})
};
CollectionBinaryExpression binaryExpression =
new CollectionBinaryExpression(new PropertyNameExpression("PostCode"), CollectionOperator.In,
new CollectionExpression(new[] { 3, 4, 5, 6 }));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
//new OrderByCollectionExpression(new[] {"PostCode"}),
new ForceIndexExpression(true),
new IndexNamesExpression(new[] {"Index1", "Index2"})
});
ProviderQueryExpression prov = new ProviderQueryExpression(providerProps, new AllAttributesExpression(),
binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
}
public void TestMsSqlServer2008OrderBy()
{
GeometryServices services = new GeometryServices();
MsSqlServer2008Provider<long> search = new MsSqlServer2008Provider<long>(services.DefaultGeometryFactory,
ConfigurationManager.
ConnectionStrings["sql2008"].
ConnectionString,
"dbo",
"vwGeoCustomers", "OID", "Geom")
{
DefaultProviderProperties
= new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
new ForceIndexExpression(true)
})
};
//var binaryExpression =
// new CollectionBinaryExpression(new PropertyNameExpression("PostCode"), CollectionOperator.In, new CollectionExpression(new[] { 3, 4, 5, 6 }));
AttributeBinaryStringExpression binaryExpression = new AttributeBinaryStringExpression("PostCode",
BinaryStringOperator.
StartsWith, "W");
IGeometry testPoint = new GeometryServices()["EPSG:27700"].CreatePoint2D(500000, 180000);
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
//new OrderByCollectionExpression(
// new []
// {
// new OrderByExpression(
// new SpatialAnalysisDistanceExpression(new GeometryExpression(null),new GeometryExpression(testPoint) ),SortOrder.Ascending),
// }),
new DataPageSizeExpression(10),
new DataPageNumberExpression(0)
});
ProviderQueryExpression prov = new ProviderQueryExpression(providerProps, new AllAttributesExpression(),
binaryExpression,
new SortExpressionCollectionExpression(
new[]{new SortExpression(
new SpatialAnalysisDistanceExpression(
new GeometryExpression(null),
new GeometryExpression(testPoint)),SortOrder.Ascending)}));
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
}
public void TestSqLite()
{
GeometryServices services = new GeometryServices();
SpatiaLite2Provider search = new SpatiaLite2Provider(services.DefaultGeometryFactory,
ConfigurationManager.ConnectionStrings["sqLite"].
ConnectionString,
"main",
"regions", "OID", "XGeometryX");
search.SpatiaLiteIndexType = SpatiaLite2IndexType.MBRCache;
BinaryExpression binaryExpression =
new BinaryExpression(new PropertyNameExpression("VHG5"),
BinaryOperator.GreaterThan, new LiteralExpression<int>(6));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[] { });
ProviderQueryExpression prov = new ProviderQueryExpression(providerProps, new AllAttributesExpression(),
binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
}
public void TestMsSqlSpatialPaged()
{
GeometryServices services = new GeometryServices();
MsSqlSpatialProvider search = new MsSqlSpatialProvider(services.DefaultGeometryFactory,
ConfigurationManager.ConnectionStrings["mssqlspatial"
].
ConnectionString,
"ST",
"dbo",
"BRoads", "OID", "the_geom")
{
DefaultProviderProperties
= new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
new ForceIndexExpression(true)
})
};
//var binaryExpression =
// new CollectionBinaryExpression(new PropertyNameExpression("PostCode"), CollectionOperator.In, new CollectionExpression(new[] { 3, 4, 5, 6 }));
AttributeBinaryStringExpression binaryExpression = new AttributeBinaryStringExpression("NAME",
BinaryStringOperator.
StartsWith, "W");
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
//new OrderByCollectionExpression(new[] {"NAME"}),
new ForceIndexExpression(true),
new IndexNamesExpression(new[] {"Index1", "Index2"}),
new DataPageSizeExpression(10),
new DataPageNumberExpression(5)
});
ProviderQueryExpression prov = new ProviderQueryExpression(providerProps, new AllAttributesExpression(),
binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
}
public void TestSqlServer2008Distance()
{
GeometryServices services = new GeometryServices();
MsSqlServer2008Provider<long> search = new MsSqlServer2008Provider<long>(services.DefaultGeometryFactory,
ConfigurationManager.
ConnectionStrings["sql2008"].
ConnectionString,
"dbo",
"GeoPlaces", "OId", "Geom")
{
DefaultProviderProperties
= new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
new ForceIndexExpression(true)
})
};
IGeometry point = services["EPSG:27700"].CreatePoint2D(500000, 180000);
SpatialAnalysisExpression analysisExpresssion =
new SpatialAnalysisDistanceExpression(new GeometryExpression(null),
new GeometryExpression(point));
BinaryExpression expression = new BinaryExpression(analysisExpresssion, BinaryOperator.LessThanOrEqualTo,
new LiteralExpression<double>(2000.00));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
new ForceIndexExpression(true)
});
ProviderQueryExpression prov = new ProviderQueryExpression(providerProps, new AllAttributesExpression(),
expression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
}
public IFeatureProvider GetProvider()
{
if (EnsureTables())
{
string conn = ServerConnectionString;
conn += string.Format("Database={0};", cbDataBases.SelectedItem);
conn += "Enlist=true;";
DataRowView drv = (DataRowView) cbTables.SelectedItem;
string schema = (string) drv["Schema"];
string tableName = (string) drv["TableName"];
;
string geomColumn = (string) drv["GeometryColumn"];
int coordDimension = (int) drv["Dimension"];
string srid = (string) drv["SRID"]; //((int)).ToString();
GeometryServices gs = new GeometryServices();
IGeometryFactory gf = gs[srid]; //, coordDimension];
//if (!string.IsNullOrEmpty(spatialReference))
// gf.SpatialReference = gs.CoordinateSystemFactory.CreateFromWkt(spatialReference);
string oidColumnName = (String) dgvColumns.Rows[oidcolumn].Cells["ColumnName"].Value;
List<String> columns = new List<string>();
//List<OrderByExpression> orderby = new List<OrderByExpression>();
foreach (DataGridViewRow dgvr in dgvColumns.Rows)
{
if ((bool) dgvr.Cells["Include"].Value) columns.Add((String) dgvr.Cells["ColumnName"].Value);
//if (dgvr.Cells["SortOrder"].Value != null)
// orderby.Add(new OrderByExpression((String) dgvr.Cells[1].Value,
// (SortOrder) dgvr.Cells["SortOrder"].Value));
}
if (!columns.Contains(oidColumnName))
columns.Insert(0, oidColumnName);
columns.Add(geomColumn);
ProviderPropertiesExpression ppe = null;
//if (orderby.Count == 0)
//{
// ppe = new ProviderPropertiesExpression(
// new ProviderPropertyExpression[]
// {
// new AttributesCollectionExpression(columns)
// });
//}
//else
//{
ppe = new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(orderby),
new AttributesCollectionExpression(columns)
});
//}
IFeatureProvider prov = null;
switch ((String) dgvColumns.Rows[oidcolumn].Cells["DataType"].Value)
{
case "bigint":
prov = new PostGisProvider<long>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "integer":
prov = new PostGisProvider<int>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "character varying":
case "text":
prov = new PostGisProvider<string>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "uuid":
prov = new PostGisProvider<Guid>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
default:
return null;
}
//jd commented temporarily to get a build
//((ISpatialDbProvider)prov).DefinitionQuery =
// new ProviderQueryExpression(ppe, ape, null);
return prov;
}
return null;
}
public IFeatureProvider GetProvider()
{
if (EnsureTables())
{
string conn = ServerConnectionString;
DataRowView drv = (DataRowView) cbTables.SelectedItem;
string schema = (string) drv["Schema"];
string tableName = (string) drv["TableName"];
;
string geomColumn = (string) drv["GeometryColumn"];
int coordDimension = (int) (long) drv["Dimension"];
string srid = drv["SRID"].ToString();
string spatialReference = drv["SpatialReference"] == DBNull.Value
?
""
:
(string) drv["SpatialReference"];
GeometryServices gs = new GeometryServices();
IGeometryFactory gf = gs[srid]; //, coordDimension];
//if (!string.IsNullOrEmpty(spatialReference))
// gf.SpatialReference = gs.CoordinateSystemFactory.CreateFromWkt(spatialReference);
string oidColumnName = (String) dgvColumns.Rows[oidcolumn].Cells[2].Value;
List<String> columns = new List<string>();
//List<OrderByExpression> orderby = new List<OrderByExpression>();
foreach (DataGridViewRow dgvr in dgvColumns.Rows)
{
if ((bool) dgvr.Cells["Include"].Value) columns.Add((String) dgvr.Cells[2].Value);
//if (dgvr.Cells["SortOrder"].Value != null)
// orderby.Add(new OrderByExpression((String) dgvr.Cells[1].Value,
// (SortOrder) dgvr.Cells["SortOrder"].Value));
}
if (!columns.Contains(oidColumnName))
columns.Insert(0, oidColumnName);
columns.Insert(1, geomColumn);
ProviderPropertiesExpression ppe = null;
//if (orderby.Count == 0)
//{
// ppe = new ProviderPropertiesExpression(
// new ProviderPropertyExpression[]
// {
// new AttributesCollectionExpression(columns)
// });
//}
//else
//{
ppe = new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(orderby),
new AttributesCollectionExpression(columns)
});
//}
IFeatureProvider prov =
new SpatiaLite2Provider(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory) {DefaultProviderProperties = ppe};
//jd commented temporarily to get a build
//((ISpatialDbProvider)prov).DefinitionQuery =
// new ProviderQueryExpression(ppe, ape, null);
return prov;
}
return null;
}
