public void SetupFixture()
{
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(UnitTests.Properties.Settings.Default.SqlServer2008);
if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog))
{
Assert.Ignore("Requires SQL Server connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
// Set up sample table
using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText = "CREATE TABLE roads_ugl(ID decimal(10,0) identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)";
cmd.ExecuteNonQuery();
}
// Load data
using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile()))
{
shapeFile.Open();
IEnumerable <uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
indexes = indexes.Take(100);
foreach (uint idx in indexes)
{
var feature = shapeFile.GetFeatureByOid(idx);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO roads_ugl(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))";
cmd.Parameters.AddWithValue("@Geom", feature.Geometry.AsText());
cmd.Parameters.AddWithValue("@Name", feature.Attributes["NAME"]);
cmd.Parameters.AddWithValue("@Srid", shapeFile.SRID);
cmd.ExecuteNonQuery();
}
}
}
// Create spatial index
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE SPATIAL INDEX [IX_roads_ugl_GEOM] ON [dbo].[roads_ugl](GEOM)USING GEOMETRY_GRID WITH (BOUNDING_BOX =(-98, 40, -82, 50), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM))";
cmd.ExecuteNonQuery();
}
}
}
public void SetupFixture()
{
SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(UnitTests.Properties.Settings.Default.SqlServer2008);
if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog))
{
Assert.Ignore("Requires SQL Server connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
// Set up sample table
using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText = "CREATE TABLE roads_ugl(ID decimal(10,0) identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)";
cmd.ExecuteNonQuery();
}
// Load data
using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile()))
{
shapeFile.Open();
IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
indexes = indexes.Take(100);
foreach (uint idx in indexes)
{
var feature = shapeFile.GetFeatureByOid(idx);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO roads_ugl(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))";
cmd.Parameters.AddWithValue("@Geom", feature.Geometry.AsText());
cmd.Parameters.AddWithValue("@Name", feature.Attributes["NAME"]);
cmd.Parameters.AddWithValue("@Srid", shapeFile.SRID);
cmd.ExecuteNonQuery();
}
}
}
// Create spatial index
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE SPATIAL INDEX [IX_roads_ugl_GEOM] ON [dbo].[roads_ugl](GEOM)USING GEOMETRY_GRID WITH (BOUNDING_BOX =(-98, 40, -82, 50), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM))";
cmd.ExecuteNonQuery();
}
}
}
public void FixtureSetup()
{
var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis);
if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database))
{
Assert.Ignore("Requires PostgreSQL connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
try
{
// Set up sample table
using (var conn = new NpgsqlConnection(Properties.Settings.Default.PostGis))
{
conn.Open();
// Load data
using (var shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), false, false, 4326))
{
shapeFile.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
"SELECT COUNT(*) FROM \"geometry_columns\" WHERE \"f_table_name\" = 'roads_ugl_g' AND \"f_geometry_column\"='geom';";
var count = cmd.ExecuteScalar();
if (Convert.ToInt32(count) > 0)
{
cmd.CommandText = "SELECT DropGeometryColumn('roads_ugl_g', 'geom');";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE roads_ugl_g";
cmd.ExecuteNonQuery();
}
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText =
"CREATE TABLE roads_ugl_g(id integer primary key, name character varying(100));";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT AddGeometryColumn('roads_ugl_g', 'geom', " + shapeFile.SRID +
", 'GEOMETRY', 2);";
cmd.ExecuteNonQuery();
}
IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
_insertedIds = new List<uint>(indexes.Take(100));
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO roads_ugl_g(id, name, geom) VALUES (@PId, @PName, @PGeom);";
var @params = cmd.Parameters;
@params.AddRange(
new[]
{
new NpgsqlParameter("PId", NpgsqlDbType.Integer),
new NpgsqlParameter("PName", NpgsqlDbType.Varchar, 100),
new NpgsqlParameter("PGeom", NpgsqlDbType.Bytea)
});
var writer = new PostGisWriter();
foreach (var idx in _insertedIds)
{
var feature = shapeFile.GetFeatureByOid(idx);
@params["PId"].NpgsqlValue = (int) idx;
@params["PName"].NpgsqlValue = feature.Attributes["NAME"];
@params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry);
cmd.ExecuteNonQuery();
}
}
}
}
}
catch
{
Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server");
}
}
public void FixtureSetup()
{
var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis);
if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database))
{
Assert.Ignore("Requires PostgreSQL connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
try
{
// Set up sample table
using (var conn = new NpgsqlConnection(Properties.Settings.Default.PostGis))
{
conn.Open();
// Load data
using (var shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), false, false, 4326))
{
shapeFile.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DROP TABLE IF EXISTS roads_ugl";
cmd.ExecuteNonQuery();
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText =
"CREATE TABLE roads_ugl(id integer primary key, name character varying(100), geog geography);";
cmd.ExecuteNonQuery();
}
IEnumerable <uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
_insertedIds = new List <uint>(indexes.Take(100));
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO roads_ugl(id, name, geog) VALUES (@PId, @PName, ST_GeogFromWKB(@PGeom));";
var @params = cmd.Parameters;
@params.AddRange(
new[]
{
new NpgsqlParameter("PId", NpgsqlDbType.Integer),
new NpgsqlParameter("PName", NpgsqlDbType.Varchar, 100),
new NpgsqlParameter("PGeom", NpgsqlDbType.Bytea)
});
var writer = new PostGisWriter();
foreach (var idx in _insertedIds)
{
var feature = shapeFile.GetFeatureByOid(idx);
@params["PId"].NpgsqlValue = (int)idx;
@params["PName"].NpgsqlValue = feature.Attributes["NAME"];
@params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry);
cmd.ExecuteNonQuery();
}
}
// Verify
foreach (var pgp in GetTestProvider())
{
foreach (var idx in _insertedIds)
{
var g1 = pgp.GetGeometryByOid(idx);
var g2 = shapeFile.GetGeometryByOid(idx);
Assert.AreEqual(g1, g2);
}
}
}
}
}
catch (Exception ee)
{
Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server");
}
}
public void FixtureSetup()
{
var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis);
if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database))
{
Assert.Ignore("Requires PostgreSQL connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
try
{
// Set up sample table
using (var conn = new NpgsqlConnection(Properties.Settings.Default.PostGis))
{
conn.Open();
// Load data
using (var shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), false, false, 4326))
{
shapeFile.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
"SELECT COUNT(*) FROM \"geometry_columns\" WHERE \"f_table_name\" = 'roads_ugl_g' AND \"f_geometry_column\"='geom';";
var count = cmd.ExecuteScalar();
if (Convert.ToInt32(count) > 0)
{
cmd.CommandText = "SELECT DropGeometryColumn('roads_ugl_g', 'geom');";
cmd.ExecuteNonQuery();
cmd.CommandText = "DROP TABLE roads_ugl_g";
cmd.ExecuteNonQuery();
}
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText =
"CREATE TABLE roads_ugl_g(id integer primary key, name character varying(100));";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT AddGeometryColumn('roads_ugl_g', 'geom', " + shapeFile.SRID +
", 'GEOMETRY', 2);";
cmd.ExecuteNonQuery();
}
IEnumerable <uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
_insertedIds = new List <uint>(indexes.Take(100));
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO roads_ugl_g(id, name, geom) VALUES (@PId, @PName, @PGeom);";
var @params = cmd.Parameters;
@params.AddRange(
new[]
{
new NpgsqlParameter("PId", NpgsqlDbType.Integer),
new NpgsqlParameter("PName", NpgsqlDbType.Varchar, 100),
new NpgsqlParameter("PGeom", NpgsqlDbType.Bytea)
});
var writer = new PostGisWriter();
foreach (var idx in _insertedIds)
{
var feature = shapeFile.GetFeatureByOid(idx);
@params["PId"].NpgsqlValue = (int)idx;
@params["PName"].NpgsqlValue = feature.Attributes["NAME"];
@params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry);
cmd.ExecuteNonQuery();
}
}
}
}
}
catch
{
Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server");
}
}
public void FixtureSetup()
{
var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis);
if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database))
{
Assert.Ignore("Requires PostgreSQL connectionstring");
}
GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();
try
{
// Set up sample table
using (var conn = new NpgsqlConnection(Properties.Settings.Default.PostGis))
{
conn.Open();
// Load data
using (var shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), false, false, 4326))
{
shapeFile.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "DROP TABLE IF EXISTS roads_ugl";
cmd.ExecuteNonQuery();
// The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
cmd.CommandText =
"CREATE TABLE roads_ugl(id integer primary key, name character varying(100), geog geography);";
cmd.ExecuteNonQuery();
}
IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());
_insertedIds = new List<uint>(indexes.Take(100));
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO roads_ugl(id, name, geog) VALUES (@PId, @PName, ST_GeogFromWKB(@PGeom));";
var @params = cmd.Parameters;
@params.AddRange(
new[]
{
new NpgsqlParameter("PId", NpgsqlDbType.Integer),
new NpgsqlParameter("PName", NpgsqlDbType.Varchar, 100),
new NpgsqlParameter("PGeom", NpgsqlDbType.Bytea)
});
var writer = new PostGisWriter();
foreach (var idx in _insertedIds)
{
var feature = shapeFile.GetFeatureByOid(idx);
@params["PId"].NpgsqlValue = (int)idx;
@params["PName"].NpgsqlValue = feature.Attributes["NAME"];
@params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry);
cmd.ExecuteNonQuery();
}
}
// Verify
foreach (var pgp in GetTestProvider())
{
foreach (var idx in _insertedIds)
{
var g1 = pgp.GetGeometryByOid(idx);
var g2 = shapeFile.GetGeometryByOid(idx);
Assert.AreEqual(g1, g2);
}
}
}
}
}
catch (Exception ee)
{
Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server");
}
}