public static SharpMap.Map InitializeMap(float angle) { var dataSource = new SharpMap.Data.Providers.ShapeFile( string.Format("{0}/roads.shp", ShapefileSample.PathOsm), true); var fds = new SharpMap.Data.FeatureDataSet(); dataSource.Open(); dataSource.ExecuteIntersectionQuery(dataSource.GetExtents(), fds); dataSource.Close(); var gfp = new SharpMap.Data.Providers.GeometryFeatureProvider(fds.Tables[0]); var vl = new SharpMap.Layers.VectorLayer("roads", gfp) { CoordinateTransformation = LayerTools.Dhdn2ToWgs84 }; var ll = new SharpMap.Layers.LabelLayer("labels") { DataSource = gfp, CoordinateTransformation = LayerTools.Dhdn2ToWgs84, LabelColumn = "name", MultipartGeometryBehaviour = SharpMap.Layers.LabelLayer.MultipartGeometryBehaviourEnum.Largest, }; ll.Style.Halo = new System.Drawing.Pen(System.Drawing.Color.Red); //ll.Style.IgnoreLength = true; var map = new SharpMap.Map(); map.Layers.Add(vl); map.Layers.Add(ll); map.Layers.Add(vl); map.ZoomToExtents(); return map; }
public void TestDeleteAfterClose() { string test; CopyShapeFile(GetTestDataFilePath("SPATIAL_F_SKARVMUFF.shp"), out test); var shp = new SharpMap.Data.Providers.ShapeFile(test); shp.Open(); shp.Close(); var succeeded = true; foreach (var file in System.IO.Directory.GetFiles(System.IO.Path.GetDirectoryName(test), System.IO.Path.GetFileNameWithoutExtension(test) + ".*")) { try { System.IO.File.Delete(file); } catch (System.Exception) { System.Console.WriteLine("Failed to delete '{0}'", file); succeeded = false; } } NUnit.Framework.Assert.IsTrue(succeeded); }
public void TestExecuteIntersectionQuery() { 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 fds = new SharpMap.Data.FeatureDataSet(); var bbox = shp.GetExtents(); //narrow it down bbox.ExpandBy(-0.425*bbox.Width, -0.425*bbox.Height); //Just to avoid that initial query does not impose performance penalty shp.DoTrueIntersectionQuery = false; shp.ExecuteIntersectionQuery(bbox, fds); fds.Tables.RemoveAt(0); //Perform query once more var sw = new System.Diagnostics.Stopwatch(); sw.Start(); shp.ExecuteIntersectionQuery(bbox, fds); sw.Stop(); System.Console.WriteLine("Queried using just envelopes:\n" + fds.Tables[0].Rows.Count + " in " + sw.ElapsedMilliseconds + "ms."); fds.Tables.RemoveAt(0); shp.DoTrueIntersectionQuery = true; sw.Reset(); sw.Start(); shp.ExecuteIntersectionQuery(bbox, fds); sw.Stop(); System.Console.WriteLine("Queried using prepared geometries:\n" + fds.Tables[0].Rows.Count + " in " + sw.ElapsedMilliseconds + "ms."); }
public void TestReadValueZFromPointZShapeFile() { var file = GetTestDataFilePath("Point_With_Z.shp"); var sh = new SharpMap.Data.Providers.ShapeFile(file, true); sh.Open(); var fc = sh.GetFeatureCount(); NUnit.Framework.Assert.AreEqual(1149, fc); NUnit.Framework.Assert.AreEqual(0, sh.GetObjectIDsInView(sh.GetExtents())[0]); var featsInView = sh.GetGeometriesInView(new GeoAPI.Geometries.Envelope(sh.GetExtents())); NUnit.Framework.Assert.AreEqual(1149, featsInView.Count); foreach (var item in featsInView) { NUnit.Framework.Assert.IsNotNull(item.Coordinate.Z); } NUnit.Framework.Assert.AreEqual(featsInView[0].Coordinate.Z, 146.473); NUnit.Framework.Assert.AreEqual(featsInView[1].Coordinate.Z, 181.374); NUnit.Framework.Assert.AreEqual(featsInView[2].Coordinate.Z, 146.676); NUnit.Framework.Assert.AreEqual(featsInView[3].Coordinate.Z, 181.087); NUnit.Framework.Assert.AreEqual(featsInView[4].Coordinate.Z, 169.948); NUnit.Framework.Assert.AreEqual(featsInView[5].Coordinate.Z, 169.916); sh.Close(); }
public void TestReadValueZFromLineStringZShapeFile() { var file = GetTestDataFilePath("LineString_With_Z.shp"); var sh = new SharpMap.Data.Providers.ShapeFile(file, true); sh.Open(); var fc = sh.GetFeatureCount(); NUnit.Framework.Assert.AreEqual(1221, fc); NUnit.Framework.Assert.AreEqual(0, sh.GetObjectIDsInView(sh.GetExtents())[0]); var featsInView = sh.GetGeometriesInView(new GeoAPI.Geometries.Envelope(sh.GetExtents())); NUnit.Framework.Assert.AreEqual(1221, featsInView.Count); foreach (var item in featsInView) { NUnit.Framework.Assert.IsNotNull(item.Coordinate.Z); } NUnit.Framework.Assert.AreEqual(featsInView[0].Coordinates[0].Z, 35.865); NUnit.Framework.Assert.AreEqual(featsInView[0].Coordinates[1].Z, 35.743); NUnit.Framework.Assert.AreEqual(featsInView[1].Coordinates[0].Z, 35.518); NUnit.Framework.Assert.AreEqual(featsInView[1].Coordinates[1].Z, 35.518); NUnit.Framework.Assert.AreEqual(featsInView[2].Coordinates[0].Z, 37.438); NUnit.Framework.Assert.AreEqual(featsInView[2].Coordinates[1].Z, 37.441); NUnit.Framework.Assert.AreEqual(featsInView[3].Coordinates[0].Z, 37.441); NUnit.Framework.Assert.AreEqual(featsInView[3].Coordinates[1].Z, 37.441); sh.Close(); }
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 TestSetUp() { using (var sf = new SharpMap.Data.Providers.ShapeFile(GetTestDataFilePath("roads_ugl.shp"))) { sf.Open(); var fds = new SharpMap.Data.FeatureDataSet(); sf.ExecuteIntersectionQuery(sf.GetExtents(), fds); _provider = new SharpMap.Data.Providers.GeometryFeatureProvider(fds.Tables[0]); } }
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 TestReadPointZShapeFile() { var file = GetTestDataFilePath("SPATIAL_F_SKARVMUFF.shp"); var sh = new SharpMap.Data.Providers.ShapeFile(file, true); sh.Open(); var fc = sh.GetFeatureCount(); NUnit.Framework.Assert.AreEqual(4342, fc); NUnit.Framework.Assert.AreEqual(0, sh.GetObjectIDsInView(sh.GetExtents())[0]); var featsInView = sh.GetGeometriesInView(new GeoAPI.Geometries.Envelope(sh.GetExtents())); NUnit.Framework.Assert.AreEqual(4342, featsInView.Count); sh.Close(); }
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 void TestReadPointZShapeFile() { var file = GetTestDataFilePath("SPATIAL_F_SKARVMUFF.shp"); var sh = new SharpMap.Data.Providers.ShapeFile(file, true); var fc = sh.GetFeatureCount(); NUnit.Framework.Assert.AreEqual(4342, fc); sh.Open(); var featsInView = sh.GetGeometriesInView(new GeoAPI.Geometries.Envelope(sh.GetExtents())); NUnit.Framework.Assert.AreEqual(4342, featsInView.Count); sh.Close(); }
public void TestReadPointZShapeFile() { string file = TestUtility.GetPathToTestFile("SPATIAL_F_SKARVMUFF.shp"); var sh = new SharpMap.Data.Providers.ShapeFile(file, true); sh.Open(); int fc = sh.GetFeatureCount(); NUnit.Framework.Assert.AreEqual(4342, fc); NUnit.Framework.Assert.AreEqual(0, sh.GetObjectIDsInView(sh.GetExtents())[0]); var featsInView = sh.GetGeometriesInView(new GeoAPI.Geometries.Envelope(sh.GetExtents())); NUnit.Framework.Assert.AreEqual(4342, featsInView.Count); sh.Close(); }
public void TestExecuteIntersectionQueryWithFilterDelegate() { 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 fds = new SharpMap.Data.FeatureDataSet(); var bbox = shp.GetExtents(); //narrow it down bbox.ExpandBy(-0.425 * bbox.Width, -0.425 * bbox.Height); //Just to avoid that initial query does not impose performance penalty shp.DoTrueIntersectionQuery = false; shp.FilterDelegate = JustTracks; shp.ExecuteIntersectionQuery(bbox, fds); fds.Tables.RemoveAt(0); //Perform query once more var sw = new System.Diagnostics.Stopwatch(); sw.Start(); shp.ExecuteIntersectionQuery(bbox, fds); sw.Stop(); System.Console.WriteLine("Queried using just envelopes:\n" + fds.Tables[0].Rows.Count + " in " + sw.ElapsedMilliseconds + "ms."); fds.Tables.RemoveAt(0); shp.DoTrueIntersectionQuery = true; sw.Reset(); sw.Start(); shp.ExecuteIntersectionQuery(bbox, fds); sw.Stop(); System.Console.WriteLine("Queried using prepared geometries:\n" + fds.Tables[0].Rows.Count + " in " + sw.ElapsedMilliseconds + "ms."); }
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 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"); } }
// 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); } }
static void Main(string[] args) { string appPath = typeof(Program).Assembly.Location.Substring(0, typeof(Program).Assembly.Location.LastIndexOf("\\")); // delete old db if (System.IO.File.Exists(string.Format(@"{0}\Data\db.sqlite", appPath))) { System.IO.File.Delete(string.Format(@"{0}\Data\db.sqlite", appPath)); } var mod_spatialite_folderPath = (IntPtr.Size == 4) ? "mod_spatialite-4.4.0-RC0-win-x86" : "mod_spatialite-4.4.0-RC0-win-amd64"; //using relative path, cannot use absolute path, dll load will fail var slPath = appPath.Substring(0, appPath.LastIndexOf('\\')); slPath = slPath.Substring(0, slPath.LastIndexOf('\\')); slPath = slPath.Substring(0, slPath.LastIndexOf('\\')); string path = slPath + @"\SpatialLite\" + mod_spatialite_folderPath + ";" + Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine); Environment.SetEnvironmentVariable("Path", path, EnvironmentVariableTarget.Process); var cn = new SQLiteConnection(string.Format(@"Data Source={0}\Data\db.sqlite;Version=3;", appPath)); cn.Open(); cn.LoadExtension("mod_spatialite"); var cm = new SQLiteCommand("SELECT InitSpatialMetadata();", cn); cm.ExecuteNonQuery(); // create geometry table cm = new SQLiteCommand( @"CREATE TABLE WorldGeom (" + @"ID INTEGER PRIMARY KEY AUTOINCREMENT)", cn); cm.ExecuteNonQuery(); cm = new SQLiteCommand("SELECT AddGeometryColumn('WorldGeom', 'Geometry', 4326, 'GEOMETRY', 2)", cn); cm.ExecuteNonQuery(); // create feature data table cm = new SQLiteCommand( @"CREATE TABLE WorldData (" + @"ID INTEGER PRIMARY KEY AUTOINCREMENT, " + @"ISO2 VARCHAR(2) NOT NULL, " + @"ISO3 VARCHAR(3) NOT NULL, " + @"Name TEXT NOT NULL, " + @"Region TEXT NOT NULL, " + @"Area DOUBLE NOT NULL, " + @"Pop DOUBLE NOT NULL);", cn); cm.ExecuteNonQuery(); cm = new SQLiteCommand("SELECT CreateMbrCache('WorldGeom', 'Geometry');", cn); cm.ExecuteNonQuery(); // copy shape data to sqlite var shapeFile = appPath + @"\Data\world_countries_boundary_file_world_2002.shp"; var shp = new SharpMap.Data.Providers.ShapeFile(shapeFile); shp.Open(); FeatureDataSet ds = new FeatureDataSet(); shp.ExecuteIntersectionQuery(new SharpMap.Geometries.BoundingBox(double.MinValue, double.MinValue, double.MaxValue, double.MaxValue), ds); foreach (FeatureDataRow row in ds.Tables[0].Rows) { var bytes = SharpMap.Converters.WellKnownBinary.GeometryToWKB.Write(row.Geometry); cm = new SQLiteCommand("INSERT INTO WorldGeom (Geometry) VALUES (GeomFromWkb(@wkb, 4326))", cn); cm.Parameters.AddWithValue("@wkb", bytes); cm.ExecuteNonQuery(); cm = new SQLiteCommand("INSERT INTO WorldData (ISO2, ISO3, Name, Region, Area, Pop) VALUES (@iso2, @iso3, @name, @region, @area, @pop)", cn); cm.Parameters.AddWithValue("@iso2", row["ISO_2_CODE"]); cm.Parameters.AddWithValue("@iso3", row["ISO_3_CODE"]); cm.Parameters.AddWithValue("@name", row["NAME"]); cm.Parameters.AddWithValue("@region", row["REGION"]); cm.Parameters.AddWithValue("@area", row["AREA"]); cm.Parameters.AddWithValue("@pop", row["POP2005"]); cm.ExecuteNonQuery(); } shp.Close(); // anlyze table cm = new SQLiteCommand("ANALYZE WorldGeom;", cn); cm.ExecuteNonQuery(); cn.Close(); }
private void SHP2KML(string shpFile, string kmlFile) { SharpMap.Data.Providers.ShapeFile shp = new SharpMap.Data.Providers.ShapeFile(shpFile); SharpMap.Data.FeatureDataSet fds = new SharpMap.Data.FeatureDataSet(); shp.Open(); shp.ExecuteIntersectionQuery(shp.GetExtents(), fds); System.Data.DataTable dt = fds.Tables[0]; KMLib.KMLRoot kml = new KMLib.KMLRoot(); for (int i = 0; i < dt.Rows.Count; i++) { SharpMap.Data.FeatureDataRow row = dt.Rows[i] as SharpMap.Data.FeatureDataRow; StringBuilder description = new StringBuilder(); for (int j = 0; j < dt.Columns.Count; j++) { description.Append("<p><b>" + capitalize(dt.Columns[j].ToString()) + "</b>: " + row[j] + "</p>\n"); } if (row.Geometry is SharpMap.Geometries.Point) { var pointGeom = row.Geometry as SharpMap.Geometries.Point; var placemark = new KMLib.Feature.Placemark(); placemark.Point = new KMLib.Geometry.KmlPoint((float)pointGeom.X, (float)pointGeom.Y); placemark.description = description.ToString(); kml.Document.Add(placemark); } else if (row.Geometry is SharpMap.Geometries.Polygon) { var polygonGeom = row.Geometry as SharpMap.Geometries.Polygon; var placemark = new KMLib.Feature.Placemark(); var polygon = new KMLib.Polygon(); var boundary = new KMLib.BoundaryIs(); foreach (SharpMap.Geometries.Point vertex in polygonGeom.ExteriorRing.Vertices) { boundary.LinearRing.Coordinates.Add(new Core.Geometry.Point3D(vertex.X, vertex.Y)); } boundary.LinearRing.CloseRing(); boundary.LinearRing.Extrude = true; polygon.OuterBoundaryIs = boundary; var interiorRing = new KMLib.BoundaryIs(); for (int j = 0; j < polygonGeom.NumInteriorRing; j++) { foreach (SharpMap.Geometries.Point point in polygonGeom.InteriorRing(j).Vertices) { interiorRing.LinearRing.Coordinates.Add(new Core.Geometry.Point3D(point.X, point.Y)); } interiorRing.LinearRing.CloseRing(); } polygon.InnerBoundaryIs = interiorRing; placemark.Polygon = polygon; kml.Document.Add(placemark); } } kml.Save(kmlFile); shp.Close(); btnConvert.Content = "Done Converting"; }
static void Main(string[] args) { string appPath = typeof(Program).Assembly.Location.Substring(0, typeof(Program).Assembly.Location.LastIndexOf("\\")); // delete old db if (System.IO.File.Exists(string.Format(@"{0}\Data\db.sqlite", appPath))) { System.IO.File.Delete(string.Format(@"{0}\Data\db.sqlite", appPath)); } // initialize sqlite String slPath = appPath.Substring(0, appPath.LastIndexOf('\\')); slPath = slPath.Substring(0, slPath.LastIndexOf('\\')); slPath = slPath.Substring(0, slPath.LastIndexOf('\\')); slPath = slPath + "\\SpatialLite"; String path = Environment.GetEnvironmentVariable("path"); if (path == null) { path = ""; } if (!path.ToLowerInvariant().Contains(slPath.ToLowerInvariant())) { Environment.SetEnvironmentVariable("path", slPath + ";" + path); } var cn = new SQLiteConnection(string.Format(@"Data Source={0}\Data\db.sqlite;Version=3;", appPath)); cn.Open(); SQLiteCommand cm = new SQLiteCommand(String.Format("SELECT load_extension('{0}');", "libspatialite-4.dll"), cn); cm.ExecuteNonQuery(); // create geometry table cm = new SQLiteCommand( @"CREATE TABLE WorldGeom (" + @"ID INTEGER PRIMARY KEY AUTOINCREMENT, " + @"Geometry BLOB NOT NULL);", cn); cm.ExecuteNonQuery(); // create feature data table cm = new SQLiteCommand( @"CREATE TABLE WorldData (" + @"ID INTEGER PRIMARY KEY AUTOINCREMENT, " + @"ISO2 VARCHAR(2) NOT NULL, " + @"ISO3 VARCHAR(3) NOT NULL, " + @"Name TEXT NOT NULL, " + @"Region TEXT NOT NULL, " + @"Area DOUBLE NOT NULL, " + @"Pop DOUBLE NOT NULL);", cn); cm.ExecuteNonQuery(); // copy shape data to sqlite var shapeFile = appPath + @"\Data\world_countries_boundary_file_world_2002.shp"; var shp = new SharpMap.Data.Providers.ShapeFile(shapeFile); shp.Open(); FeatureDataSet ds = new FeatureDataSet(); shp.ExecuteIntersectionQuery(new SharpMap.Geometries.BoundingBox(double.MinValue, double.MinValue, double.MaxValue, double.MaxValue), ds); foreach (FeatureDataRow row in ds.Tables[0].Rows) { var bytes = SharpMap.Converters.WellKnownBinary.GeometryToWKB.Write(row.Geometry); cm = new SQLiteCommand("INSERT INTO WorldGeom (Geometry) VALUES (GeomFromWkb(@wkb, -1))", cn); cm.Parameters.Add("Geometry", DbType.Object); cm.Parameters.AddWithValue("@wkb", bytes); cm.ExecuteNonQuery(); cm = new SQLiteCommand("INSERT INTO WorldData (ISO2, ISO3, Name, Region, Area, Pop) VALUES (@iso2, @iso3, @name, @region, @area, @pop)", cn); cm.Parameters.AddWithValue("@iso2", row["ISO_2_CODE"]); cm.Parameters.AddWithValue("@iso3", row["ISO_3_CODE"]); cm.Parameters.AddWithValue("@name", row["NAME"]); cm.Parameters.AddWithValue("@region", row["REGION"]); cm.Parameters.AddWithValue("@area", row["AREA"]); cm.Parameters.AddWithValue("@pop", row["POP2005"]); cm.ExecuteNonQuery(); } shp.Close(); // create spatial index cm = new SQLiteCommand("SELECT CreateMbrCache('WorldGeom', 'Geometry');", cn); cm.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())) { 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"); } }
private void SHP2KML(string shpFile, string kmlFile) { SharpMap.Data.Providers.ShapeFile shp = new SharpMap.Data.Providers.ShapeFile(shpFile); SharpMap.Data.FeatureDataSet fds = new SharpMap.Data.FeatureDataSet(); shp.Open(); shp.ExecuteIntersectionQuery(shp.GetExtents(), fds); System.Data.DataTable dt = fds.Tables[0]; KMLib.KMLRoot kml = new KMLib.KMLRoot(); for (int i = 0; i < dt.Rows.Count; i++ ) { SharpMap.Data.FeatureDataRow row = dt.Rows[i] as SharpMap.Data.FeatureDataRow; StringBuilder description = new StringBuilder(); for (int j = 0; j < dt.Columns.Count; j++) { description.Append("<p><b>" + capitalize(dt.Columns[j].ToString()) + "</b>: " + row[j] + "</p>\n"); } if (row.Geometry is SharpMap.Geometries.Point) { var pointGeom = row.Geometry as SharpMap.Geometries.Point; var placemark = new KMLib.Feature.Placemark(); placemark.Point = new KMLib.Geometry.KmlPoint((float) pointGeom.X, (float) pointGeom.Y); placemark.description = description.ToString(); kml.Document.Add(placemark); } else if (row.Geometry is SharpMap.Geometries.Polygon) { var polygonGeom = row.Geometry as SharpMap.Geometries.Polygon; var placemark = new KMLib.Feature.Placemark(); var polygon = new KMLib.Polygon(); var boundary = new KMLib.BoundaryIs(); foreach (SharpMap.Geometries.Point vertex in polygonGeom.ExteriorRing.Vertices) { boundary.LinearRing.Coordinates.Add(new Core.Geometry.Point3D(vertex.X, vertex.Y)); } boundary.LinearRing.CloseRing(); boundary.LinearRing.Extrude = true; polygon.OuterBoundaryIs = boundary; var interiorRing = new KMLib.BoundaryIs(); for (int j = 0; j < polygonGeom.NumInteriorRing; j++) { foreach (SharpMap.Geometries.Point point in polygonGeom.InteriorRing(j).Vertices) { interiorRing.LinearRing.Coordinates.Add(new Core.Geometry.Point3D(point.X, point.Y)); } interiorRing.LinearRing.CloseRing(); } polygon.InnerBoundaryIs = interiorRing; placemark.Polygon = polygon; kml.Document.Add(placemark); } } kml.Save(kmlFile); shp.Close(); btnConvert.Content = "Done Converting"; }
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 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.GetFeatureByOid(i); try { var res = ToSqlServerAndBack(fdr.Geometry); Assert.AreEqual(fdr.Geometry, res); Console.WriteLine(string.Format("Feature {0} ({1}) converted!", i, fdr.Attributes[0])); } catch (SqlGeometryConverterException) { Console.WriteLine(string.Format("Feature {0} ({1}) conversion failed!", i, fdr.Attributes[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(), 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"); } }