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