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 GetObjectIDsInView 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.GetObjectIDsInView(shapeFile.GetExtents()); indexes = indexes.Take(100); foreach (uint idx in indexes) { SharpMap.Data.FeatureDataRow feature = shapeFile.GetFeature(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["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 GetObjectIDsInView 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.GetObjectIDsInView(shapeFile.GetExtents()); indexes = indexes.Take(100); foreach (uint idx in indexes) { SharpMap.Data.FeatureDataRow feature = shapeFile.GetFeature(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["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 TestGetFeature() { NUnit.Framework.Assert.IsTrue(System.IO.File.Exists(GetTestDataFilePath("SPATIAL_F_SKARVMUFF.shp")), "Specified shapefile is not present!"); var shp = new SharpMap.Data.Providers.ShapeFile(GetTestDataFilePath("SPATIAL_F_SKARVMUFF.shp"), false, false); shp.Open(); var feat = shp.GetFeature(0); NUnit.Framework.Assert.IsNotNull(feat); shp.Close(); }
public static List <Area> Parse(string shapeFilePath) { var gss = new NtsGeometryServices(); var pcs = (ProjectedCoordinateSystem)ProjectedCoordinateSystem.WebMercator; GeoAPI.GeometryServiceProvider.Instance = gss; var shapeFile = new SharpMap.Data.Providers.ShapeFile(shapeFilePath, true); var areas = new List <Area>(); for (var i = 0; i < shapeFile.GetFeatureCount(); i++) { var feature = shapeFile.GetFeature((uint)i); var area = new Area(feature.ItemArray); IEnumerable <IGeometry> geometries = feature.Geometry.GetType() == typeof(MultiPolygon) ? ((MultiPolygon)feature.Geometry).Geometries : feature.Geometry.GetType() == typeof(NetTopologySuite.Geometries.Polygon) ? new IGeometry[] { feature.Geometry } : null; if (geometries == null) { throw new Exception("Don't know geometry type " + feature.Geometry.GetType().FullName); } foreach (var geometry in geometries) { area.polygons.Add(new Polygon() { borders = geometry.Coordinates.Select(CoordFormatter).ToArray() }); } areas.Add(area); } return(areas); }
public void TestShapeFile() { using (var p = new SharpMap.Data.Providers.ShapeFile(@"D:\Daten\kowg\shapefile\Police Authorities_region.shp", true)) { p.Open(); for (uint i = 0; i < p.GetFeatureCount(); i++) { var fdr = p.GetFeature(i); try { var res = ToSqlServerAndBack(fdr.Geometry); Assert.AreEqual(fdr.Geometry, res); Console.WriteLine(string.Format("Feature {0} ({1}) converted!", i, fdr[0])); } catch (SqlGeometryConverterException) { Console.WriteLine(string.Format("Feature {0} ({1}) conversion failed!", i, fdr[0])); } } } }
public void TestShapeFile() { using (var p = new SharpMap.Data.Providers.ShapeFile(GetTestFile(), true)) { p.Open(); for (uint i = 0; i < p.GetFeatureCount(); i++) { var fdr = p.GetFeature(i); try { fdr.Geometry.SRID = -1; var res = ToSqlServerAndBack(fdr.Geometry); Assert.AreEqual(fdr.Geometry, res); System.Diagnostics.Trace.WriteLine(string.Format("Feature {0} ({1}) converted!", i, fdr[0])); } catch (SqlGeometryConverterException) { System.Diagnostics.Trace.WriteLine(string.Format("Feature {0} ({1}) conversion failed!", i, fdr[0])); } } } }
public void OneTimeSetUp() { try { GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices(); var connStrBuilder = new NpgsqlConnectionStringBuilder(Properties.Settings.Default.PostGis); if (string.IsNullOrEmpty(connStrBuilder.Host) || string.IsNullOrEmpty(connStrBuilder.Database)) { Assert.Ignore("Requires PostgreSQL connectionstring"); } // 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(TestUtility.GetPathToTestFile("roads_ugl.shp"), 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 GetObjectIDsInView 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.GetObjectIDsInView(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.GetFeature(idx); @params["PId"].NpgsqlValue = (int)idx; @params["PName"].NpgsqlValue = feature["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())) { shapeFile.SRID = 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 GetObjectIDsInView 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.GetObjectIDsInView(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.GetFeature(idx); @params["PId"].NpgsqlValue = (int)idx; @params["PName"].NpgsqlValue = feature["NAME"]; @params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry); cmd.ExecuteNonQuery(); } } // Verify foreach (var pgp in GetTestProvider()) { foreach (var idx in _insertedIds) { var g1 = pgp.GetGeometryByID(idx); var g2 = shapeFile.GetGeometryByID(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 = "DROP TABLE IF EXISTS roads_ugl"; cmd.ExecuteNonQuery(); // The ID column cannot simply be int, because that would cause GetObjectIDsInView 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.GetObjectIDsInView(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.GetFeature(idx); @params["PId"].NpgsqlValue = (int)idx; @params["PName"].NpgsqlValue = feature["NAME"]; @params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry); cmd.ExecuteNonQuery(); } } // Verify foreach (var pgp in GetTestProvider()) { foreach (var idx in _insertedIds) { var g1 = pgp.GetGeometryByID(idx); var g2 = shapeFile.GetGeometryByID(idx); Assert.AreEqual(g1, g2); } } } } } catch (Exception ee) { Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server"); } }
public void OneTimeSetUp() { SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(Properties.Settings.Default.SqlServer2008); if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog)) { NUnit.Framework.Assert.Ignore("Requires SQL Server connectionstring"); } GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices(); //SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory); // Set up sample tables (Geometry + Geography) using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE roads_ugl_geom(ID int identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)"; cmd.ExecuteNonQuery(); } using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE roads_ugl_geog(ID int identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOG geography)"; cmd.ExecuteNonQuery(); } // Load data using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(TestUtility.GetPathToTestFile("roads.ugl"))) { shapeFile.Open(); _geometrySrid = shapeFile.SRID; IEnumerable <uint> indexes = shapeFile.GetObjectIDsInView(shapeFile.GetExtents()); // Note that spatial indexes may only kick in at certain number of records // so for thorough testing comment out next line and load all features (approx 3500) indexes = indexes.Take(100); var cmdGeom = new SqlCommand("INSERT INTO roads_ugl_geom(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))", conn); var cmdGeog = new SqlCommand("INSERT INTO roads_ugl_geog(NAME, GEOG) VALUES (@Name, geography::STGeomFromText(@Geog, @Srid))", conn); foreach (uint idx in indexes) { SharpMap.Data.FeatureDataRow feature = shapeFile.GetFeature(idx); string wkt; if (feature.Geometry == null || feature.Geometry.IsEmpty) { wkt = "LINESTRING EMPTY"; } else { wkt = feature.Geometry.AsText(); } if (cmdGeom.Parameters.Count == 0) { cmdGeom.Parameters.AddWithValue("@Geom", wkt); cmdGeom.Parameters.AddWithValue("@Name", feature["NAME"]); cmdGeom.Parameters.AddWithValue("@Srid", _geometrySrid); } else { cmdGeom.Parameters[0].Value = wkt; cmdGeom.Parameters[1].Value = feature["NAME"]; } cmdGeom.ExecuteNonQuery(); if (cmdGeog.Parameters.Count == 0) { cmdGeog.Parameters.AddWithValue("@Geog", wkt); cmdGeog.Parameters.AddWithValue("@Name", feature["NAME"]); cmdGeog.Parameters.AddWithValue("@Srid", GeographySrid); } else { cmdGeog.Parameters[0].Value = wkt; cmdGeog.Parameters[1].Value = feature["NAME"]; } cmdGeog.ExecuteNonQuery(); } cmdGeom.Dispose(); cmdGeog.Dispose(); } // ensure we have some features with NULL and EMPTY geometries using (var cmd = conn.CreateCommand()) { // To find invalid geometries: // SELECT {OidColumn}, {GeometryColumn}.STIsValid() AS STIsValid, {GeometryColumn}.IsValidDetailed() AS IsValidDetailed FROM {QualifiedTableName} // NULL cmd.CommandText = "INSERT INTO roads_ugl_geom(NAME, GEOM) VALUES ('Test null wkt', NULL)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO roads_ugl_geog(NAME, GEOG) VALUES ('Test null wkt', NULL)"; cmd.ExecuteNonQuery(); // EMPTY cmd.CommandText = "INSERT INTO roads_ugl_geom(NAME, GEOM) VALUES ('Test empty wkt', 'LINESTRING EMPTY')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO roads_ugl_geog(NAME, GEOG) VALUES ('Test empty wkt', 'LINESTRING EMPTY')"; cmd.ExecuteNonQuery(); // INVALID (ID 144 from shape file; see also ID 2055) cmd.CommandText = $"INSERT INTO roads_ugl_geom(NAME, GEOM) VALUES ('Test invalid wkt', geometry::STGeomFromText('LINESTRING (-84.652756071629071 42.676743004284312, -84.652924071615374 42.676624004283632, -84.652756071629071 42.676743004284312, -84.652512071649028 42.676922004285323, -84.641022072594438 42.685478004332808, -84.638779072781034 42.687271004342172, -84.636932072941363 42.689831004350026, -84.634491073153043 42.693100004360424, -84.62387107404335 42.701092004405112, -84.603256075794022 42.715752004493233, -84.603142075803831 42.715832004493734, -84.599823076091937 42.718651004508146, -84.588676077031693 42.722431004556235, -84.586021077270672 42.725533004568049)', {_geometrySrid}))"; cmd.ExecuteNonQuery(); cmd.CommandText = $"INSERT INTO roads_ugl_geog(NAME, GEOG) VALUES ('Test invalid wkt', geography::STGeomFromText('LINESTRING (-84.652756071629071 42.676743004284312, -84.652924071615374 42.676624004283632, -84.652756071629071 42.676743004284312, -84.652512071649028 42.676922004285323, -84.641022072594438 42.685478004332808, -84.638779072781034 42.687271004342172, -84.636932072941363 42.689831004350026, -84.634491073153043 42.693100004360424, -84.62387107404335 42.701092004405112, -84.603256075794022 42.715752004493233, -84.603142075803831 42.715832004493734, -84.599823076091937 42.718651004508146, -84.588676077031693 42.722431004556235, -84.586021077270672 42.725533004568049)', {GeographySrid}))"; cmd.ExecuteNonQuery(); } // Create GEOM spatial index using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = $"CREATE SPATIAL INDEX [{GeometrySpatialIndex}] ON [dbo].[roads_ugl_geom](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.CommandTimeout = 300; cmd.ExecuteNonQuery(); } // Create GEOG spatial index using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = $"CREATE SPATIAL INDEX [{GeographySpatialIndex}] ON [dbo].[roads_ugl_geog](GEOG)"; cmd.CommandTimeout = 300; cmd.ExecuteNonQuery(); } // initialise counts and test IDs using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = $"SELECT COUNT(ID) FROM roads_ugl_geom WHERE Geom.STIsEmpty() = 0 AND Geom.STIsValid() = 1"; _numValidGeoms = (int)cmd.ExecuteScalar(); cmd.CommandText = $"SELECT COUNT(ID) FROM roads_ugl_geom WHERE GEOM IS NOT NULL AND Geom.STIsEmpty() = 0 AND Geom.STIsValid() = 0"; _numInvalidGeoms = (int)cmd.ExecuteScalar(); _numValidatedGeoms = _numValidGeoms + _numInvalidGeoms; cmd.CommandText = $"SELECT COUNT(ID) FROM roads_ugl_geom"; _numFeatures = (int)cmd.ExecuteScalar(); _idNullGeom = (uint)(_numFeatures - 2); _idEmptyGeom = (uint)(_numFeatures - 1); _idInvalidGeom = (uint)(_numFeatures); } } }
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 GetObjectIDsInView 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.GetObjectIDsInView(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.GetFeature(idx); @params["PId"].NpgsqlValue = (int) idx; @params["PName"].NpgsqlValue = feature["NAME"]; @params["PGeom"].NpgsqlValue = writer.Write(feature.Geometry); cmd.ExecuteNonQuery(); } } } } } catch { Assert.Ignore("Failed to connect to PostgreSQL/PostGIS Server"); } }
public void TestShapeFile() { using (var p = new SharpMap.Data.Providers.ShapeFile(@"D:\Daten\kowg\shapefile\Police Authorities_region.shp", true)) { p.Open(); for (uint i = 0; i < p.GetFeatureCount(); i++ ) { var fdr = p.GetFeature(i); try { var res = ToSqlServerAndBack(fdr.Geometry); Assert.AreEqual(fdr.Geometry, res); Console.WriteLine(string.Format("Feature {0} ({1}) converted!", i, fdr[0])); } catch (SqlGeometryConverterException) { Console.WriteLine(string.Format("Feature {0} ({1}) conversion failed!", i, fdr[0])); } } } }
// Phuong thuc public int UpdateToDB() { try { GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices(); // Mo tep tin SharpMap.Data.Providers.ShapeFile map = new SharpMap.Data.Providers.ShapeFile(FileName); map.Open(); String dsCot = ""; int demKQ = 0; // Doc cau truc cua tep tin var feature = map.GetFeature(0); String query = "CREATE TABLE " + SQLTableName + "(ID int Identity(1,1) PRIMARY KEY, "; foreach (DataColumn col in feature.Table.Columns) { if (col.ColumnName.ToLower().Equals("id") || col.ColumnName.ToLower().Equals("oid")) { continue; } dsCot += col.ColumnName + ", "; String type = col.DataType.ToString().ToLower().Split('.')[1]; if (type.Equals("string")) { query += col.ColumnName + " nvarchar(max), "; } else if (type.Equals("int16") || type.Equals("int32") || type.Equals("int64") || type.Equals("uint16") || type.Equals("uint32") || type.Equals("uint64")) { query += col.ColumnName + " int, "; } else if (type.Equals("float") || type.Equals("double")) { query += col.ColumnName + " float, "; } } dsCot += "geom"; query += "geom Geometry)"; da.Write(query); // Doc du lieu tren ban do va cap nhat vao CSDL // INSERT INTO tenBang(ds cot) VALUES(ds gia tri) for (int i = 0; i < map.GetFeatureCount(); i++) { var featurei = map.GetFeature((uint)i); String insertQuery = "INSERT INTO " + SQLTableName + "(" + dsCot + ") VALUES("; String values = ""; foreach (DataColumn col in featurei.Table.Columns) { if (col.ColumnName.ToLower().Equals("oid") || col.ColumnName.ToLower().Equals("id")) { continue; } String type = col.DataType.ToString().ToLower().Split('.')[1]; if (type.ToLower().Equals("string")) { values += "N'" + cv.TCVN3ToUnicode(featurei.ItemArray[col.Ordinal].ToString()) + "', "; } else { if (featurei.ItemArray[col.Ordinal].ToString().Length > 0) { values += featurei.ItemArray[col.Ordinal].ToString() + ", "; } else { values += "0, "; } } } String polygon = featurei.Geometry.ToString(); values += "Geometry::STPolyFromText('" + polygon + "', 4326)"; insertQuery += values + ")"; demKQ += da.Write(insertQuery); } return(demKQ); } catch { return(0); } }