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 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 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 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 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); 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 DoTest() { SharpMap.Data.Providers.ShapeFile sf = new SharpMap.Data.Providers.ShapeFile("cities.shp"); SharpMap.Data.FeatureDataSet fds = new SharpMap.Data.FeatureDataSet(); sf.ExecuteIntersectionQuery(sf.GetExtents(), fds); SharpMap.Data.Providers.FeatureProvider gfp = new SharpMap.Data.Providers.FeatureProvider(fds.Tables[0]); System.Data.DataTable dt = new System.Data.DataTable("external"); dt.Columns.Add("city", typeof(string)); dt.Columns.Add("males", typeof(int)); dt.Columns.Add("females", typeof(int)); }
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 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"); } }
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 DoTest() { SharpMap.Data.Providers.ShapeFile sf = new SharpMap.Data.Providers.ShapeFile("cities.shp"); SharpMap.Data.FeatureDataSet fds = new SharpMap.Data.FeatureDataSet(); sf.ExecuteIntersectionQuery(sf.GetExtents(), fds); SharpMap.Data.Providers.FeatureProvider gfp = new SharpMap.Data.Providers.FeatureProvider(fds.Tables[0]); System.Data.DataTable dt = new System.Data.DataTable("external"); dt.Columns.Add("city", typeof (string)); dt.Columns.Add("males", typeof (int)); dt.Columns.Add("females", typeof (int)); }
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 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 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 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"); } }
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"; }