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 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 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 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 TestSqlServer2008Paged()
{
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)
})
};
//var binaryExpression =
// new CollectionBinaryExpression(new PropertyNameExpression("PostCode"), CollectionOperator.In, new CollectionExpression(new[] { 3, 4, 5, 6 }));
AttributeBinaryStringExpression binaryExpression = new AttributeBinaryStringExpression("PostCode",
BinaryStringOperator.
StartsWith, "W");
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
new WithNoLockExpression(true),
//new OrderByCollectionExpression(new[] {"PostCode"}),
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 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);
}
protected virtual void VisitProviderPropertiesExpression(StringBuilder builder, ProviderPropertiesExpression exp)
{
foreach (ProviderPropertyExpression providerPropertyExpression in exp.ProviderProperties.Collection)
{
VisitExpression(builder, providerPropertyExpression);
}
}
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);
}
