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"); } }