internal NetTopologySuiteTypeHandlerResolver(
NpgsqlConnector connector,
CoordinateSequenceFactory coordinateSequenceFactory,
PrecisionModel precisionModel,
Ordinates handleOrdinates,
bool geographyAsDefault)
{
_databaseInfo = connector.DatabaseInfo;
_geographyAsDefault = geographyAsDefault;
var(pgGeometryType, pgGeographyType) = (PgType("geometry"), PgType("geography"));
// TODO: In multiplexing, these are used concurrently... not sure they're thread-safe :(
var reader = new PostGisReader(coordinateSequenceFactory, precisionModel, handleOrdinates);
var writer = new PostGisWriter();
if (pgGeometryType is not null)
{
_geometryHandler = new NetTopologySuiteHandler(pgGeometryType, reader, writer);
}
if (pgGeographyType is not null)
{
_geographyHandler = new NetTopologySuiteHandler(pgGeographyType, reader, writer);
}
}
public void WriteCoordinates(
[Values(Ordinates.XY, Ordinates.XYZ, Ordinates.None)] Ordinates writerCoords,
[Values(Ordinates.XY, Ordinates.XYZ, Ordinates.None)] Ordinates readerCoords,
[Values(Ordinates.XY, Ordinates.XYZ)] Ordinates pointCoords)
{
var writer = new PostGisWriter {
HandleOrdinates = writerCoords
};
var reader = new PostGisReader {
HandleOrdinates = readerCoords
};
byte[] bytes = writer.Write(pointCoords == Ordinates.XYZ ? new Point(1, 1, 1) : new Point(1, 1));
var output = (Point)reader.Read(bytes);
var expectedOutputCoords = pointCoords;
if (writerCoords != Ordinates.None)
{
expectedOutputCoords &= writerCoords;
}
if (readerCoords != Ordinates.None)
{
expectedOutputCoords &= readerCoords;
}
Assert.That(output.CoordinateSequence.Ordinates, Is.EqualTo(expectedOutputCoords));
}
protected override byte[] Write(Geometry gIn)
{
var pgWriter = new PostGisWriter();
byte[] b = pgWriter.Write(gIn);
using (var cn = new NpgsqlConnection(ConnectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"nts_io_postgis_2d\" VALUES(@P1, @P2, @P3);";
var p1 = new NpgsqlParameter("P1", NpgsqlDbType.Integer)
{
NpgsqlValue = this.Counter
};
var p2 = new NpgsqlParameter("P2", NpgsqlDbType.Text)
{
NpgsqlValue = gIn.AsText()
};
var p3 = new NpgsqlParameter("P3", NpgsqlDbType.Bytea)
{
NpgsqlValue = b
};
cmd.Parameters.AddRange(new[] { p1, p2, p3 });
cmd.ExecuteNonQuery();
}
}
return(b);
}
public void LineString3D()
{
const int size = 10;
Coordinate[] points = new Coordinate[size];
for (int i = 0; i < size; i++)
{
// just some arbitrary values
points[i] = new Coordinate(100 * Math.Sin(i), 200 * Math.Cos(i), 300 * Math.Tan(i));
}
ILineString source = new LineString(points);
var pgWriter = new PostGisWriter()
{
HandleOrdinates = Ordinates.XYZ
};
byte[] bytes = pgWriter.Write(source);
ILineString target = (ILineString) new PostGisReader().Read(bytes);
for (int i = 0; i < size; i++)
{
Assert.AreEqual(source.Coordinates[i].X, target.Coordinates[i].X);
Assert.AreEqual(source.Coordinates[i].Y, target.Coordinates[i].Y);
Assert.AreEqual(source.Coordinates[i].Z, target.Coordinates[i].Z);
}
}
/// <summary>
///
/// </summary>
/// <param name="wkt"></param>
/// <param name="srid"></param>
private static void General(string wkt, int srid)
{
var geom = wr.Read(wkt);
var parsed = geom.AsText();
var regeom = wr.Read(parsed);
var reparsed = regeom.AsText();
geom.SRID = srid;
regeom.SRID = srid;
Assert.IsTrue(geom.EqualsExact(regeom));
Assert.AreEqual(parsed, reparsed);
var bytesB = new PostGisWriter(ByteOrder.BigEndian).Write(regeom);
var regeom2 = br.Read(bytesB);
Assert.IsTrue(geom.EqualsExact(regeom2));
byte[] bytesL = new PostGisWriter(ByteOrder.LittleEndian).Write(regeom);
var regeom3 = br.Read(bytesL);
Assert.IsTrue(geom.EqualsExact(regeom3));
Assert.IsTrue(regeom2.EqualsExact(regeom3));
Assert.AreEqual(bytesB.Length, bytesL.Length);
}
public void GeometryCollection_empty()
{
var source = GeometryCollection.Empty;
// don't assign ordinates, needs to call `CheckOrdinates(...)`
byte[] bytes = new PostGisWriter().Write(source);
var target = (GeometryCollection) new PostGisReader().Read(bytes);
Assert.AreEqual(source.Count, target.Count);
}
public void OrdinatesContainXY(Ordinates ordinates)
{
var writer = new PostGisWriter {
HandleOrdinates = ordinates
};
var reader = new PostGisReader {
HandleOrdinates = ordinates
};
Assert.That(writer.HandleOrdinates & Ordinates.XY, Is.EqualTo(Ordinates.XY));
Assert.That(reader.HandleOrdinates & Ordinates.XY, Is.EqualTo(Ordinates.XY));
}
public void TestByEWkt(string ewkt, string ignoreReason = null)
{
// Ignore?
if (!string.IsNullOrEmpty(ignoreReason))
{
Assert.Ignore(ignoreReason);
}
// Arrange
var pgr = new PostGisReader(NtsGeometryServices.Instance.CreateGeometryFactory());
var pgw = new PostGisWriter {
HandleOrdinates = Ordinates.XYZM
};
byte[] postgisBuffer = null;
try
{
using (var cn = new NpgsqlConnection(ConnectionString))
{
cn.Open();
var cm = cn.CreateCommand();
cm.CommandText = "SELECT ST_AsEWKB(ST_GeomFromText(@P0));";
var p = cm.Parameters.Add("P0", NpgsqlDbType.Varchar);
p.Value = ewkt;
postgisBuffer = (byte[])cm.ExecuteScalar();
}
}
catch (Exception e)
{
Assert.Fail(e.Message);
}
// Reader
Geometry geom = null;
Assert.That(() => geom = pgr.Read(postgisBuffer), Throws.Nothing);
Assert.That(geom, Is.Not.Null);
// Writer
byte[] postgisWriterBuffer = null;
Assert.That(() => postgisWriterBuffer = pgw.Write(geom), Throws.Nothing);
Assert.That(postgisWriterBuffer, Is.Not.Null);
// Equality
Assert.That(postgisWriterBuffer, Is.EqualTo(postgisBuffer));
}
/// <summary>
/// Converts from GeoAPI geometry type to database geometry type.
/// </summary>
/// <param name="value">The GeoAPI geometry value.</param>
/// <returns></returns>
protected override byte[] FromGeometry(object value)
{
Geometry geometry = value as Geometry;
if (geometry == null)
{
return(null);
}
// PostGIS can't parse a WKB of any empty geometry other than GeomtryCollection
// (throws the error: "geometry requires more points")
// and parses WKT of empty geometries always as GeometryCollection
// (ie. "select AsText(GeomFromText('LINESTRING EMPTY', -1)) = 'GEOMETRYCOLLECTION EMPTY'").
// Force GeometryCollection.Empty to avoid the error.
if (!(geometry is GeometryCollection) && geometry.IsEmpty)
{
geometry = GeometryCollection.Empty;
}
this.SetDefaultSRID(geometry);
// Determine the ordinality of the geometry to ensure 3D and 4D geometries are
// correctly serialized by PostGisWriter (see issue #66)
// NOTE: Cannot use InteriorPoint here as that always returns a 2D point (see #120)
// TODO: Is there a way of getting the ordinates directly from the geometry?
var ordinates = Ordinates.XY;
var coordinate = geometry.Coordinate;
if (coordinate != null && !double.IsNaN(coordinate.Z))
{
ordinates |= Ordinates.Z;
}
if (coordinate != null && !double.IsNaN(coordinate.M))
{
ordinates |= Ordinates.M;
}
var postGisWriter = new PostGisWriter
{
HandleOrdinates = ordinates
};
return(postGisWriter.Write(geometry));
}
protected override byte[] Write(IGeometry gIn)
{
PostGisWriter pgWriter = new PostGisWriter();
byte[] b = pgWriter.Write(gIn);
using (NpgsqlConnection cn = new NpgsqlConnection(this.ConnectionString))
{
cn.Open();
using (NpgsqlCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "INSERT INTO \"nts_io_postgis_2d\" VALUES(@P1, @P2, @P3);";
NpgsqlParameter p1 = new NpgsqlParameter("P1", NpgsqlDbType.Integer) {NpgsqlValue = this.Counter};
NpgsqlParameter p2 = new NpgsqlParameter("P2", NpgsqlDbType.Text) { NpgsqlValue = gIn.AsText() };
NpgsqlParameter p3 = new NpgsqlParameter("P3", NpgsqlDbType.Bytea) { NpgsqlValue = b };
cmd.Parameters.AddRange(new[] { p1, p2, p3 });
cmd.ExecuteNonQuery();
}
}
return b;
}
public void Point3D()
{
// Warm up assertions:
IPoint point2D = new Point(1, 2);
Assert.IsTrue(Double.IsNaN(point2D.Z));
IPoint point3D = new Point(1, 2, 3);
Assert.IsFalse(Double.IsNaN(point3D.Z));
// The real thing:
IPoint source = new Point(123, 456, 789);
byte[] bytes = new PostGisWriter().Write(source);
IPoint target = (IPoint) new PostGisReader().Read(bytes);
Assert.AreEqual(source.X, target.X);
Assert.AreEqual(source.Y, target.Y);
Assert.AreEqual(source.Z, target.Z);
}
/// <summary>
/// Converts from GeoAPI geometry type to database geometry type.
/// </summary>
/// <param name="value">The GeoAPI geometry value.</param>
/// <returns></returns>
protected override string FromGeometry(object value)
{
IGeometry geometry = value as IGeometry;
if (geometry == null)
{
return(null);
}
// PostGIS can't parse a WKB of any empty geometry other than GeomtryCollection
// (throws the error: "geometry requires more points")
// and parses WKT of empty geometries always as GeometryCollection
// (ie. "select AsText(GeomFromText('LINESTRING EMPTY', -1)) = 'GEOMETRYCOLLECTION EMPTY'").
// Force GeometryCollection.Empty to avoid the error.
if (!(geometry is IGeometryCollection) && geometry.IsEmpty)
{
geometry = GeometryCollection.Empty;
}
this.SetDefaultSRID(geometry);
byte[] bytes = new PostGisWriter().Write(geometry);
return(ToString(bytes));
}
/// <summary>
///
/// </summary>
/// <param name="wkt"></param>
/// <param name="srid"></param>
private static void General(string wkt, int srid)
{
IGeometry geom = wr.Read(wkt);
string parsed = geom.AsText();
IGeometry regeom = wr.Read(parsed);
string reparsed = regeom.AsText();
geom.SRID = srid;
regeom.SRID = srid;
Assert.IsTrue(geom.Equals(regeom));
Assert.AreEqual(parsed, reparsed);
byte[] bytesB = new PostGisWriter(ByteOrder.BigEndian).Write(regeom);
regeom = br.Read(bytesB);
Assert.IsTrue(geom.Equals(regeom));
byte[] bytesL = new PostGisWriter(ByteOrder.LittleEndian).Write(regeom);
regeom = br.Read(bytesL);
Assert.IsTrue(geom.Equals(regeom));
Assert.AreEqual(bytesB.Length, bytesL.Length);
}
public void Point3D()
{
// Warm up assertions:
var point2D = new Point(1, 2);
Assert.IsTrue(double.IsNaN(point2D.Z));
var point3D = new Point(1, 2, 3);
Assert.IsFalse(double.IsNaN(point3D.Z));
// The real thing:
var source = new Point(123, 456, 789);
var pgWriter = new PostGisWriter {
HandleOrdinates = Ordinates.XYZ
};
byte[] bytes = pgWriter.Write(source);
var target = (Point) new PostGisReader().Read(bytes);
Assert.AreEqual(source.X, target.X);
Assert.AreEqual(source.Y, target.Y);
Assert.AreEqual(source.Z, target.Z);
}
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");
}
}
internal NetTopologySuiteHandlerFactory(PostGisReader reader, PostGisWriter writer)
{
_reader = reader ?? throw new ArgumentNullException(nameof(reader));
_writer = writer ?? throw new ArgumentNullException(nameof(writer));
}
internal NetTopologySuiteHandler(PostgresType postgresType, PostGisReader reader, PostGisWriter writer)
: base(postgresType)
{
_reader = reader;
_writer = writer;
}
public async Task Run()
{
// load boundaries.
var boundaries = this.LoadBoundaries();
// load areas and index them by id.
var map = new Dictionary <long, Area>();
var areas = _dbContext.Areas
.Include(x => x.AreaAttributes);
foreach (var area in areas)
{
var idAttributes = area.AreaAttributes.FirstOrDefault(x => x.Key == "id");
if (idAttributes == null)
{
continue;
}
if (!long.TryParse(idAttributes.Value, out var id))
{
continue;
}
map[id] = area;
boundaries.Remove(id);
}
// add all to db.
var postGisWriter = new PostGisWriter();
while (boundaries.Count > 0)
{
// get boundaries with all parents done.
var queue = new Queue <long>();
foreach (var(key, (_, parent)) in boundaries)
{
if (map.ContainsKey(key))
{
continue;
}
if (parent != -1 && !map.ContainsKey(parent))
{
continue;
}
queue.Enqueue(key);
}
// no more items left.
if (queue.Count == 0)
{
break;
}
// convert to areas.
while (queue.Count > 0)
{
var next = queue.Dequeue();
var nextValue = boundaries[next];
boundaries.Remove(next);
// get parent area.
if (nextValue.parent == -1 ||
!map.TryGetValue(nextValue.parent, out var parentArea))
{
parentArea = null;
}
// create area and attributes.
var geometry = postGisWriter.Write(nextValue.feature.Geometry);
var area = new Area()
{
Geometry = geometry,
ParentAreaId = parentArea?.AreaId
};
await _dbContext.Areas.AddAsync(area);
await _dbContext.SaveChangesAsync();
map[next] = area;
foreach (var name in nextValue.feature.Attributes.GetNames())
{
if (string.IsNullOrWhiteSpace(name))
{
continue;
}
if (name == "parents")
{
continue;
}
var value = nextValue.feature.Attributes[name];
if (!(value is string valueString))
{
if (value is long l)
{
valueString = l.ToString();
}
else
{
continue;
}
}
var areaAttribute = new AreaAttribute()
{
Key = name,
AreaId = area.AreaId,
Value = valueString
};
await _dbContext.AreaAttributes.AddAsync(areaAttribute);
}
await _dbContext.SaveChangesAsync();
}
}
}
