public void TestGetFeatureInvalidGeometry(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ValidateGeometries = validateGeometries;
var feature = sq.GetFeature(_idInvalidGeom);
Assert.IsNotNull(feature);
if (providerMode == SqlServerProviderMode.NativeSqlBytes)
{
// client side conversion always attempts validation
Assert.IsTrue(!feature.Geometry.IsEmpty && feature.Geometry.IsValid);
}
else
{
if (validateGeometries)
{
Assert.IsTrue(!feature.Geometry.IsEmpty && feature.Geometry.IsValid);
}
else
{
Assert.IsTrue(feature.Geometry.IsEmpty);
}
}
}
public void TestGetFeature(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
var feature = sq.GetFeature(1);
Assert.IsNotNull(feature);
}
public void TestGetFeatureNonExisting(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
var feature = sq.GetFeature(99999999);
Assert.IsNull(feature);
}
public void TestGetFeatureCount(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
int count = sq.GetFeatureCount();
// includes NULL, EMPTY, and INVALID geoms
Assert.AreEqual(_numFeatures, count);
}
public void TestGetFeatureNullGeometry(SqlServerProviderMode providerMode, SharpMap.Data.Providers.SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
var feature = sq.GetFeature(_idNullGeom);
NUnit.Framework.Assert.IsNotNull(feature);
NUnit.Framework.Assert.IsNull(feature.Geometry);
}
public void TestGetFeatureEmptyGeometry(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
var feature = sq.GetFeature(_idEmptyGeom);
Assert.IsNotNull(feature);
Assert.IsTrue(feature.Geometry.IsEmpty);
}
public void TestGetObjectIDsInView(SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.ValidateGeometries = validateGeometries;
var objectIds = sq.GetObjectIDsInView(GetTestEnvelope(spatialType));
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, objectIds.Count);
}
public void TestGetExtentsQueryIndividualFeatures(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.ExtentsMode = SharpMap.Data.Providers.SqlServer2008ExtentsMode.QueryIndividualFeatures;
GeoAPI.Geometries.Envelope extents = sq.GetExtents();
Assert.IsNotNull(extents);
}
public void TestGetFeatureCountWithDefinitionQuery(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.WellKnownBinary, spatialType);
sq.DefinitionQuery = "NAME LIKE 'A%'";
int count = sq.GetFeatureCount();
Assert.LessOrEqual(count, _numValidGeoms);
}
public void TestGetGeometriesInViewDefinitionQuery(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.DefinitionQuery = "NAME LIKE 'A%'";
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
Assert.LessOrEqual(geometries.Count, _numValidGeoms);
}
public void TestGetGeometriesInViewNOLOCK(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.NoLockHint = true;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInView(SqlServerProviderMode providerMode, SharpMap.Data.Providers.SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
NUnit.Framework.Assert.IsNotNull(geometries);
NUnit.Framework.Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
public void TestExecuteIntersectionQuery(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ValidateGeometries = validateGeometries;
SharpMap.Data.FeatureDataSet ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(GetTestEnvelope(spatialType), ds);
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, ds.Tables[0].Rows.Count);
}
public void VerifySchemaDetection(string schemaTable, string tableSchema, string table)
{
SharpMap.Data.Providers.SqlServer2008 sq = new SharpMap.Data.Providers.SqlServer2008("", schemaTable, "oidcolumn");
Assert.AreEqual(tableSchema, sq.TableSchema);
Assert.AreEqual(table, sq.Table);
Assert.AreEqual("oidcolumn", sq.ObjectIdColumn);
System.Reflection.PropertyInfo pi = sq.GetType().GetProperty("QualifiedTable", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.GetProperty);
string qualifiedTable = (string)pi.GetValue(sq, null);
Assert.IsTrue(qualifiedTable.Contains(tableSchema));
Assert.IsTrue(qualifiedTable.Contains(table));
}
public void TestGetGeometriesInViewFORCESEEK(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ForceSeekHint = true;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// NOTE ValidateGeometries is ignored when using ForceSeek
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewFORCEINDEX(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ForceIndex = spatialType == SqlServerSpatialObjectType.Geometry ? GeometrySpatialIndex : GeographySpatialIndex;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// NOTE ValidateGeometries is ignored when using ForceIndex
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestExecuteIntersectionQueryExceedGeogMaxExtents(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType,
double x1, double x2, double y1, double y2)
{
// occurs when user zooms out beyond map extents. For Geog, when latitude approaches 90 N or S can result in
// error 24206: "The specified input cannot be accepted because it contains an edge with antipodal points."
// Longitudes exceeding -179.99999999 or 180.0 are "wrapped" resulting in unexpected polygon (also contributes to err 24206)
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
SharpMap.Data.FeatureDataSet ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(new GeoAPI.Geometries.Envelope(x1, x2, y1, y2), ds);
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, ds.Tables[0].Rows.Count);
}
public void TestGetGeometriesInViewAllHints(SqlServerProviderMode providerMode, SharpMap.Data.Providers.SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.NoLockHint = true;
sq.ForceSeekHint = true;
sq.ForceIndex = spatialType == SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry ? GeometrySpatialIndex : GeographySpatialIndex;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
NUnit.Framework.Assert.IsNotNull(geometries);
// Note: ValidateGeometries is ignored when using ForceSeek or ForceIndex
NUnit.Framework.Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewAllHints(SqlServerSpatialObjectType spatialType, string indexName, bool validateGeoms)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.NoLockHint = true;
sq.ForceSeekHint = true;
sq.ForceIndex = indexName;
sq.ValidateGeometries = validateGeoms;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// Note: ValidateGeometries is ignored when using ForceSeek or ForceIndex
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestExecuteIntersectionQueryAllHints(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.NoLockHint = true;
sq.ForceSeekHint = true;
sq.ForceIndex = spatialType == SqlServerSpatialObjectType.Geometry ? GeometrySpatialIndex : GeographySpatialIndex;;
sq.ValidateGeometries = validateGeometries;
SharpMap.Data.FeatureDataSet ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(GetTestEnvelope(spatialType), ds);
// Note: ValidateGeometries ignored when using ForceSeek or ForceIndex
Assert.AreEqual(_numValidGeoms, ds.Tables[0].Rows.Count);
}
public void VerifySchemaDetection(string schemaTable, string tableSchema, string table)
{
SharpMap.Data.Providers.SqlServer2008 sq = new SharpMap.Data.Providers.SqlServer2008("",
schemaTable, "geom", "oidcolumn",
SqlServerSpatialObjectType.Geometry, 4326, SqlServer2008ExtentsMode.SpatialIndex);
NUnit.Framework.Assert.AreEqual(tableSchema, sq.TableSchema);
NUnit.Framework.Assert.AreEqual(table, sq.Table);
NUnit.Framework.Assert.AreEqual("oidcolumn", sq.ObjectIdColumn);
System.Reflection.PropertyInfo pi = sq.GetType().GetProperty("QualifiedTable", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.GetProperty);
string qualifiedTable = (string)pi.GetValue(sq, null);
NUnit.Framework.Assert.IsTrue(qualifiedTable.Contains(tableSchema));
NUnit.Framework.Assert.IsTrue(qualifiedTable.Contains(table));
}
//[NUnit.Framework.TestCase(SqlServerSpatialObjectType.Geography, false)]
//[NUnit.Framework.TestCase(SqlServerSpatialObjectType.Geography, true)]
public void TestGetGeometriesInViewEx(SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
// http://connect.microsoft.com/SQLServer/feedback/details/685654/invalidcastexception-retrieving-sqlgeography-column-in-ado-net-data-reader
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProviderEx();
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
Assert.AreEqual(validateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
private SharpMap.Data.Providers.SqlServer2008 GetTestProvider(SqlServerProviderMode providerMode, SharpMap.Data.Providers.SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 provider;
switch (providerMode)
{
case SqlServerProviderMode.NativeSqlBytes:
//Assert.Ignore("Ignore SharpMap.Data.Providers.SqlServer2008Ex");
if (spatialType == SharpMap.Data.Providers.SqlServerSpatialObjectType.Geography)
{
// NB note forcing WGS84
provider = new SharpMap.Data.Providers.SqlServer2008Ex(UnitTests.Properties.Settings.Default.SqlServer2008,
"roads_ugl_geog", "GEOG", "ID", spatialType, GeographySrid,
SharpMap.Data.Providers.SqlServer2008ExtentsMode.QueryIndividualFeatures);
}
else
{
provider = new SharpMap.Data.Providers.SqlServer2008Ex(UnitTests.Properties.Settings.Default.SqlServer2008,
"roads_ugl_geom", "GEOM", "ID", spatialType, _geometrySrid,
SharpMap.Data.Providers.SqlServer2008ExtentsMode.QueryIndividualFeatures);
}
break;
default:
if (spatialType == SharpMap.Data.Providers.SqlServerSpatialObjectType.Geography)
{
// NB note forcing WGS84
provider = new SharpMap.Data.Providers.SqlServer2008(UnitTests.Properties.Settings.Default.SqlServer2008,
"roads_ugl_geog", "GEOG", "ID", spatialType, GeographySrid,
SharpMap.Data.Providers.SqlServer2008ExtentsMode.QueryIndividualFeatures);
}
else
{
provider = new SharpMap.Data.Providers.SqlServer2008(UnitTests.Properties.Settings.Default.SqlServer2008,
"roads_ugl_geom", "GEOM", "ID", spatialType, _geometrySrid,
SharpMap.Data.Providers.SqlServer2008ExtentsMode.QueryIndividualFeatures);
}
break;
}
//provider.ValidateGeometries = true
//provider.DefinitionQuery = "ID NOT IN (103)" // Invalid Geom
return(provider);
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerSpatialObjectType.Geometry);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProviderEx();
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(SqlServerSpatialObjectType.Geometry);
List <TimeSpan> measurements = new List <TimeSpan>(200);
List <TimeSpan> measurementsex = new List <TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
TimeSpan avg = TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
TimeSpan avgex = TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
Assert.Less(avgex, avg);
}
public void TestGetExtentsSpatialIndex(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
if (spatialType == SqlServerSpatialObjectType.Geography)
{
var ex = Assert.Throws <ArgumentOutOfRangeException>(() =>
{
sq.ExtentsMode = SharpMap.Data.Providers.SqlServer2008ExtentsMode.SpatialIndex;
});
}
else
{
sq.ExtentsMode = SharpMap.Data.Providers.SqlServer2008ExtentsMode.SpatialIndex;
GeoAPI.Geometries.Envelope extents = sq.GetExtents();
Assert.IsNotNull(extents);
}
}
private static VectorLayer CreateLayer(string connstr, string name, VectorStyle style)
{
if (connstr == null)
throw new ArgumentNullException("connstr");
if (name == null)
throw new ArgumentNullException("name");
SqlServer2008 source = new SqlServer2008(connstr, name, "geom", "ID") { ValidateGeometries = true };
ICoordinateTransformation transformation = ProjHelper.LatLonToGoogle();
VectorLayer item = new VectorLayer(name, source)
{
SRID = 4326,
TargetSRID = 900913,
CoordinateTransformation = transformation,
Style = style,
SmoothingMode = SmoothingMode.AntiAlias,
};
return item;
}
private void button3_Click(object sender, EventArgs e)
{
VectorLayer layCountries = new VectorLayer("Mssql");
SqlServer2008 MSSQLDP = new SharpMap.Data.Providers.SqlServer2008(connstr, "PT_TOWN", "geom", "ID", SqlServerSpatialObjectType.Geography, false);
MSSQLDP.Table = "gisdb.dbo.PT_TOWN";
MSSQLDP.TableSchema = String.Empty;
// layCountries.DataSource = new SharpMap.Data.Providers.SqlServer2008ExtentsMode
// (connstr, "PT_TOWN", "geom", "ID");
layCountries.DataSource = MSSQLDP;
layCountries.Style.Fill = new SolidBrush(Color.GreenYellow);
layCountries.Style.Outline = Pens.Black;
layCountries.Style.EnableOutline = true;
//layCountries.SRID = 4326;
this.mapBox1.Map.Layers.Add(layCountries);
this.mapBox1.Map.ZoomToExtents();
this.mapBox1.Refresh();
}
public void TestGetExtentsEnvelopeAggregate(SqlServerSpatialObjectType spatialType)
{
using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT SERVERPROPERTY('productversion')";
string productversion = (string)cmd.ExecuteScalar();
if (Version.Parse(productversion).Major < 11)
{
Assert.Ignore("Requires SQL Server 2012 connection");
}
}
}
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.ExtentsMode = SharpMap.Data.Providers.SqlServer2008ExtentsMode.EnvelopeAggregate;
GeoAPI.Geometries.Envelope extents = sq.GetExtents();
Assert.IsNotNull(extents);
}
public void TestSqlServer2008ExProviderOverridesValidateGeometries(SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.NativeSqlBytes, spatialType);
sq.ValidateGeometries = false;
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
var spatialType = SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry;
// testing with both providers using ExtentsMode = QueryIndividualFeatures (ie the "heaviest" lifting)
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.WellKnownBinary, spatialType);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProvider(SqlServerProviderMode.NativeSqlBytes, spatialType);
// Note: SqlServer2008Ex provider overrides ValidateGeometries behaviour, forcing this to true.
// So to compare apples-with-apples, the SqlServer2008 provider must also set ValidateGeometries = true.
// From my testing, SqlServer2008 performance is slightly faster when using ValidateGeometries = true,
// as the SQL where clause is simpler (does not require explicitly excluding invalid geometries)
sq.ValidateGeometries = true;
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(spatialType);
List <System.TimeSpan> measurements = new List <System.TimeSpan>(200);
List <System.TimeSpan> measurementsex = new List <System.TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
System.TimeSpan avg = System.TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
System.TimeSpan avgex = System.TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
// Update Nov 2018: "should".... highly dependent on client computer specs, and dataset characteristics
// may also have an affect in real-world usage (eg num records, OGC geom type, geom complexity).
// For the test dataset @ 100 records and also @ 3600 records on an AVERAGE computer, SqlServer2008 is
// consistently out-performing SqlServer2008Ex as follows:
// for local instance SqlExpress, SqlServer2008 is consistently 30% faster than SqlServer2008Ex
// for SqlServer on local database server, - I don't have one to test against
// for Azure SQL (50DTU limit, test peaking at 26DTU), SqlServer2008 is about 5% faster than SqlServer2008Ex
NUnit.Framework.Assert.Less(avgex, avg);
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
var spatialType = SqlServerSpatialObjectType.Geometry;
// testing with both providers using ExtentsMode = QueryIndividualFeatures (ie the "heaviest" lifting)
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.WellKnownBinary, spatialType);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProvider(SqlServerProviderMode.NativeSqlBytes, spatialType);
// Note: SqlServer2008Ex provider overrides ValidateGeometries behaviour, forcing this to true.
// So to compare apples-with-apples, the SqlServer2008 provider must also set ValidateGeometries = true.
// From my testing, SqlServer2008 performance is slightly faster when using ValidateGeometries = true,
// as the SQL where clause is simpler (does not require explicitly excluding invalid geometries)
sq.ValidateGeometries = true;
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(spatialType);
List <TimeSpan> measurements = new List <TimeSpan>(200);
List <TimeSpan> measurementsex = new List <TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
TimeSpan avg = TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
TimeSpan avgex = TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
// Update Nov 2018: apparently this is no longer the case. Multiple tests following recent updates have SqlServer2008
// consistently outperforming SqlServer2008ex (100 - 3600 records). I'm not sure if this is due to improvments in later
// releases of SqlServer, or perhaps WKB payload smaller than SqlBytes (even though requires database CPU for WKB conversion)
// for local instance SqlExpress, SqlServer2008 is consistently 30% faster than SqlServer2008Ex
// for SqlServer on local database server, - I don't have one to test against
// for Azure SQL (50DTU limit, test peaking at 26DTU), SqlServer2008 is about 5% faster than SqlServer2008Ex
Assert.Less(avgex, avg);
}
