public void SubGeometriesWithSRID()
{
var point = new PostgisPoint(1, 1)
{
SRID = 4326
};
var lineString = new PostgisLineString(new[] { new Coordinate2D(2, 2), new Coordinate2D(3, 3) })
{
SRID = 4326
};
var polygon = new PostgisPolygon(new[] { new[] { new Coordinate2D(4, 4), new Coordinate2D(5, 5), new Coordinate2D(6, 6), new Coordinate2D(4, 4) } })
{
SRID = 4326
};
var collection = new PostgisGeometryCollection(new PostgisGeometry[] { point, lineString, polygon })
{
SRID = 4326
};
using (var conn = OpenConnection())
using (var cmd = new NpgsqlCommand("SELECT :p", conn))
{
cmd.Parameters.AddWithValue("p", collection);
cmd.ExecuteNonQuery();
}
}
public void BufferTest()
{
var p = new PostgisPoint(1D, 1D);
var svcs = CreatePostgisServices();
svcs.Buffer(svcs.GeometryFromProviderValue(p), 19D);
}
public void AsTextTest()
{
var p = new PostgisPoint(1D, 1D);
var svcs = CreatePostgisServices();
svcs.AsText(svcs.GeometryFromProviderValue(p));
}
public void AsBinaryWkb()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo GEOMETRY)");
var point = new PostgisPoint(8, 8);
using (var cmd = new NpgsqlCommand("INSERT INTO data (foo) VALUES (@p)", conn))
{
cmd.Parameters.AddWithValue("p", NpgsqlDbType.Geometry, point);
cmd.ExecuteNonQuery();
}
byte[] bytes;
using (var cmd = new NpgsqlCommand("SELECT foo FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
bytes = reader.GetFieldValue <byte[]>(0);
}
conn.ExecuteNonQuery("TRUNCATE data");
using (var cmd = new NpgsqlCommand("INSERT INTO data (foo) VALUES (@p)", conn))
{
cmd.Parameters.AddWithValue("p", NpgsqlDbType.Geometry, bytes);
cmd.ExecuteNonQuery();
}
Assert.That(conn.ExecuteScalar("SELECT foo FROM data"), Is.EqualTo(point));
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public static Point2 <double> ToGeom(PostgisPoint geom)
{
if (geom != null)
{
return(factory.ConstructPoint(geom.X, geom.Y));
}
return(null);
}
/// <summary>
/// 测量两个经纬度的距离，返回单位：米
/// </summary>
/// <param name="that">经纬坐标1</param>
/// <param name="point">经纬坐标2</param>
/// <returns>返回距离（单位：米）</returns>
public static double Distance(this PostgisPoint that, PostgisPoint point)
{
double radLat1 = (double)(that.Y) * Math.PI / 180d;
double radLng1 = (double)(that.X) * Math.PI / 180d;
double radLat2 = (double)(point.Y) * Math.PI / 180d;
double radLng2 = (double)(point.X) * Math.PI / 180d;
return(2 * Math.Asin(Math.Sqrt(Math.Pow(Math.Sin((radLat1 - radLat2) / 2), 2) + Math.Cos(radLat1) * Math.Cos(radLat2) * Math.Pow(Math.Sin((radLng1 - radLng2) / 2), 2))) * 6378137);
}
public void PostgisTestArrayRead()
{
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = "Select ARRAY(select st_makepoint(1,1))";
var p = cmd.ExecuteScalar() as PostgisGeometry[];
var p2 = new PostgisPoint(1d, 1d);
Assert.IsTrue(p != null && p[0] is PostgisPoint && p2 == (PostgisPoint)p[0]);
}
}
/// <summary>
/// Convert PostGisPoint into NetTopolgyPoint
/// Throw ArgumentNullException if any of input marameter is null.
/// </summary>
/// <param name="geometry">a PostGisPoint. No restriction.</param>
/// <returns>A NetTopolgyPoint</returns>
private static Point ProcessPoint(PostgisPoint geometry)
{
if (geometry != null)
{
return(new Point(geometry.X, geometry.Y));
}
else
{
throw new ArgumentNullException();
}
}
public void PostgisTestArrayWrite()
{
using (var cmd = Conn.CreateCommand())
{
var p = new PostgisPoint[1] {
new PostgisPoint(1d, 1d)
};
cmd.Parameters.AddWithValue(":p1", NpgsqlDbType.Array | NpgsqlDbType.Geometry, p);
cmd.CommandText = "SELECT :p1 = array(select st_makepoint(1,1))";
Assert.IsTrue((bool)cmd.ExecuteScalar());
}
}
public Address(TempUser user)
{
Number = user.Number;
Street = user.Street;
State = user.State;
Zip = user.Zip;
City = user.City;
FormattedAddress = user.FormattedAddress;
GeoLocation = new PostgisPoint(user.Longitude, user.Latitude)
{
SRID = Constants.SRID
};
}
public void ContainsTest()
{
var p = new PostgisPoint(1D, 1D);
var svcs = CreatePostgisServices();
var pol = new PostgisPolygon(new Coordinate2D[1][]
{ new Coordinate2D[5]
{
new Coordinate2D(0D, 0D),
new Coordinate2D(5D, 0D),
new Coordinate2D(5D, 5D),
new Coordinate2D(0D, 5D),
new Coordinate2D(0D, 0D)
} });
Assert.True(svcs.Contains(svcs.GeometryFromProviderValue(pol), svcs.GeometryFromProviderValue(p)));
}
public void FindNearestNeighbor()
{
using (var db = GetDbConnection())
{
var point = new PostgisPoint(13.72, 51.07)
{
SRID = (uint)SRID_WGS_84
}; // Or point = db.StGeomFromText("POINT(13.72 51.07)", SRID_WGS_84);
var pointProjected = db.OwmCities.Select(gt => point.StTransform(SRID_WGS84_Web_Mercator)).First();
var nearest = db.OwmCities
.OrderBy(gt => gt.Geometry.StDistance(pointProjected)) // Or GeometryConstructors.StGeomFromText("POINT(1527303 6633685)", 3857)
.First();
Assert.AreEqual("Prague", nearest.Name);
// TODO: Check if spatial index was used
}
}
public void RoundtripGeometryGeography()
{
var point = new PostgisPoint(1d, 1d);
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (geom GEOMETRY, geog GEOGRAPHY)");
using (var cmd = new NpgsqlCommand("INSERT INTO data (geom, geog) VALUES (@p, @p)", conn))
{
cmd.Parameters.AddWithValue("@p", point);
cmd.ExecuteNonQuery();
}
using (var cmd = new NpgsqlCommand("SELECT geom, geog FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.That(reader[0], Is.EqualTo(point));
Assert.That(reader[1], Is.EqualTo(point));
}
}
}
/// <summary>
/// Helper to allow use to init an address from a <see cref="AddressWithLatLng"/>
/// </summary>
/// <param name="addre"></param>
/// <param name="user"></param>
public Address(AddressWithLatLng addre, GenericUser user)
{
if (addre == null)
{
throw new ArgumentNullException(nameof(addre));
}
if (user == null)
{
throw new ArgumentNullException(nameof(user));
}
User = user;
Number = addre.Number;
Street = addre.Street;
State = addre.State;
Zip = addre.Zip;
City = addre.City;
FormattedAddress = addre.FormattedAddress;
GeoLocation = new PostgisPoint(addre.Longitude, addre.Latitude)
{
SRID = Constants.SRID
};
}
public bool Read(out PostgisGeometry result)
{
result = default(PostgisGeometry);
if (_id == 0)
{
if (_buf.ReadBytesLeft < 5)
{
return(false);
}
_bo = (ByteOrder)_buf.ReadByte();
_id = _buf.ReadUInt32(_bo);
}
if (!_srid.HasValue)
{
if ((_id & (uint)EwkbModifier.HasSRID) != 0)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_srid = _buf.ReadUInt32(_bo);
}
else
{
_srid = 0;
}
}
switch ((WkbIdentifier)(_id & (uint)7))
{
case WkbIdentifier.Point:
if (_buf.ReadBytesLeft < 16)
{
return(false);
}
result = new PostgisPoint(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo))
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.LineString:
if (_ipts == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_points = new Coordinate2D[_buf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _points.Length; _ipts++)
{
if (_buf.ReadBytesLeft < 16)
{
return(false);
}
_points[_ipts] = new Coordinate2D(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo));
}
result = new PostgisLineString(_points)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.Polygon:
if (_irng == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_rings = new Coordinate2D[_buf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _rings.Length; _irng++)
{
if (_ipts == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_rings[_irng] = new Coordinate2D[_buf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _rings[_irng].Length; _ipts++)
{
if (_buf.ReadBytesLeft < 16)
{
return(false);
}
_rings[_irng][_ipts] = new Coordinate2D(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo));
}
_ipts = -1;
}
result = new PostgisPolygon(_rings)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiPoint:
if (_ipts == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_points = new Coordinate2D[_buf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _points.Length; _ipts++)
{
if (_buf.ReadBytesLeft < 21)
{
return(false);
}
_buf.Skip(5);
_points[_ipts] = new Coordinate2D(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo));
}
result = new PostgisMultiPoint(_points)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiLineString:
if (_irng == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_rings = new Coordinate2D[_buf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _rings.Length; _irng++)
{
if (_ipts == -1)
{
if (_buf.ReadBytesLeft < 9)
{
return(false);
}
_buf.Skip(5);
_rings[_irng] = new Coordinate2D[_buf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _rings[_irng].Length; _ipts++)
{
if (_buf.ReadBytesLeft < 16)
{
return(false);
}
_rings[_irng][_ipts] = new Coordinate2D(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo));
}
_ipts = -1;
}
result = new PostgisMultiLineString(_rings)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiPolygon:
if (_ipol == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_pols = new Coordinate2D[_buf.ReadInt32(_bo)][][];
_ipol = 0;
}
for (; _ipol < _pols.Length; _ipol++)
{
if (_irng == -1)
{
if (_buf.ReadBytesLeft < 9)
{
return(false);
}
_buf.Skip(5);
_pols[_ipol] = new Coordinate2D[_buf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _pols[_ipol].Length; _irng++)
{
if (_ipts == -1)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_pols[_ipol][_irng] = new Coordinate2D[_buf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _pols[_ipol][_irng].Length; _ipts++)
{
if (_buf.ReadBytesLeft < 16)
{
return(false);
}
_pols[_ipol][_irng][_ipts] = new Coordinate2D(_buf.ReadDouble(_bo), _buf.ReadDouble(_bo));
}
_ipts = -1;
}
_irng = -1;
}
result = new PostgisMultiPolygon(_pols)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.GeometryCollection:
if (_newGeom)
{
if (_buf.ReadBytesLeft < 4)
{
return(false);
}
_geoms.Push(new PostgisGeometry[_buf.ReadInt32(_bo)]);
_icol.Push(new Counter());
}
_id = 0;
var g = _geoms.Peek();
var i = _icol.Peek();
for (; i < g.Length; i.Increment())
{
PostgisGeometry geom;
if (!Read(out geom))
{
_newGeom = false;
return(false);
}
g[i] = geom;
Reset();
}
result = new PostgisGeometryCollection(g)
{
SRID = _srid.Value
};
_geoms.Pop();
_icol.Pop();
return(true);
default:
throw new InvalidOperationException("Unknown Postgis identifier.");
}
}
public bool Read(out PostgisPoint result) => ReadConcrete(out result);
public Task Write(PostgisPoint value, NpgsqlWriteBuffer buf, NpgsqlLengthCache lengthCache, NpgsqlParameter parameter, bool async) => Write((PostgisGeometry)value, buf, lengthCache, parameter, async);
public Task Write(PostgisPoint value, NpgsqlWriteBuffer buf, NpgsqlLengthCache?lengthCache, NpgsqlParameter?parameter, bool async, CancellationToken cancellationToken = default) => Write((PostgisGeometry)value, buf, lengthCache, parameter, async, cancellationToken);
public int ValidateAndGetLength(PostgisPoint value, ref NpgsqlLengthCache lengthCache, NpgsqlParameter parameter) => value.GetLen(true);
public override bool Read([CanBeNull] out PostgisGeometry result)
{
Contract.Assert(_inByteaMode != true);
if (!_inByteaMode.HasValue)
{
_inByteaMode = false;
}
result = default(PostgisGeometry);
if (_id == 0)
{
if (_readBuf.ReadBytesLeft < 5)
{
return(false);
}
_bo = (ByteOrder)_readBuf.ReadByte();
_id = _readBuf.ReadUInt32(_bo);
}
if (!_srid.HasValue)
{
if ((_id & (uint)EwkbModifier.HasSRID) != 0)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_srid = _readBuf.ReadUInt32(_bo);
}
else
{
_srid = 0;
}
}
switch ((WkbIdentifier)(_id & 7))
{
case WkbIdentifier.Point:
_lastId = _id;
if (_readBuf.ReadBytesLeft < 16)
{
return(false);
}
result = new PostgisPoint(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo))
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.LineString:
_lastId = _id;
if (_ipts == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_points = new Coordinate2D[_readBuf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _points.Length; _ipts++)
{
if (_readBuf.ReadBytesLeft < 16)
{
return(false);
}
_points[_ipts] = new Coordinate2D(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo));
}
result = new PostgisLineString(_points)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.Polygon:
_lastId = _id;
if (_irng == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_rings = new Coordinate2D[_readBuf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _rings.Length; _irng++)
{
if (_ipts == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_rings[_irng] = new Coordinate2D[_readBuf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _rings[_irng].Length; _ipts++)
{
if (_readBuf.ReadBytesLeft < 16)
{
return(false);
}
_rings[_irng][_ipts] = new Coordinate2D(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo));
}
_ipts = -1;
}
result = new PostgisPolygon(_rings)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiPoint:
_lastId = _id;
if (_ipts == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_points = new Coordinate2D[_readBuf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _points.Length; _ipts++)
{
if (_readBuf.ReadBytesLeft < 21)
{
return(false);
}
_readBuf.Skip(5);
_points[_ipts] = new Coordinate2D(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo));
}
result = new PostgisMultiPoint(_points)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiLineString:
_lastId = _id;
if (_irng == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_rings = new Coordinate2D[_readBuf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _rings.Length; _irng++)
{
if (_ipts == -1)
{
if (_readBuf.ReadBytesLeft < 9)
{
return(false);
}
_readBuf.Skip(5);
_rings[_irng] = new Coordinate2D[_readBuf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _rings[_irng].Length; _ipts++)
{
if (_readBuf.ReadBytesLeft < 16)
{
return(false);
}
_rings[_irng][_ipts] = new Coordinate2D(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo));
}
_ipts = -1;
}
result = new PostgisMultiLineString(_rings)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.MultiPolygon:
_lastId = _id;
if (_ipol == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_pols = new Coordinate2D[_readBuf.ReadInt32(_bo)][][];
_ipol = 0;
}
for (; _ipol < _pols.Length; _ipol++)
{
if (_irng == -1)
{
if (_readBuf.ReadBytesLeft < 9)
{
return(false);
}
_readBuf.Skip(5);
_pols[_ipol] = new Coordinate2D[_readBuf.ReadInt32(_bo)][];
_irng = 0;
}
for (; _irng < _pols[_ipol].Length; _irng++)
{
if (_ipts == -1)
{
if (_readBuf.ReadBytesLeft < 4)
{
return(false);
}
_pols[_ipol][_irng] = new Coordinate2D[_readBuf.ReadInt32(_bo)];
_ipts = 0;
}
for (; _ipts < _pols[_ipol][_irng].Length; _ipts++)
{
if (_readBuf.ReadBytesLeft < 16)
{
return(false);
}
_pols[_ipol][_irng][_ipts] = new Coordinate2D(_readBuf.ReadDouble(_bo), _readBuf.ReadDouble(_bo));
}
_ipts = -1;
}
_irng = -1;
}
result = new PostgisMultiPolygon(_pols)
{
SRID = _srid.Value
};
return(true);
case WkbIdentifier.GeometryCollection:
PostgisGeometry[] g;
int i;
if (_icol.Count == 0)
{
if (_readBuf.ReadBytesLeft < 4)
{
_lastId = _id;
return(false);
}
g = new PostgisGeometry[_readBuf.ReadInt32(_bo)];
i = 0;
if (_newGeom) // We need to know whether we're in a nested geocoll or not.
{
_id = 0;
_newGeom = false;
}
else
{
_id = _lastId;
_lastId = 0;
}
}
else
{
g = _geoms.Pop();
i = _icol.Pop();
if (_icol.Count == 0)
{
_id = _lastId;
_lastId = 0;
}
}
for (; i < g.Length; i++)
{
PostgisGeometry geom;
if (!Read(out geom))
{
_icol.Push(i);
_geoms.Push(g);
_id = (uint)WkbIdentifier.GeometryCollection;
return(false);
}
g[i] = geom;
Reset();
}
result = new PostgisGeometryCollection(g)
{
SRID = _srid.Value
};
return(true);
default:
throw new InvalidOperationException("Unknown Postgis identifier.");
}
}
public void PostgisTestArrayRead()
{
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "Select ARRAY(select st_makepoint(1,1))";
var p = cmd.ExecuteScalar() as PostgisGeometry[];
var p2 = new PostgisPoint(1d, 1d);
Assert.IsTrue(p?[0] is PostgisPoint && p2 == (PostgisPoint)p[0]);
}
}
public void AsBinaryWkb()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (foo GEOMETRY)");
var point = new PostgisPoint(8, 8);
using (var cmd = new NpgsqlCommand("INSERT INTO data (foo) VALUES (@p)", conn))
{
cmd.Parameters.AddWithValue("p", NpgsqlDbType.Geometry, point);
cmd.ExecuteNonQuery();
}
byte[] bytes;
using (var cmd = new NpgsqlCommand("SELECT foo FROM data", conn))
using (var reader = cmd.ExecuteReader())
{
reader.Read();
bytes = reader.GetFieldValue<byte[]>(0);
}
conn.ExecuteNonQuery("TRUNCATE data");
using (var cmd = new NpgsqlCommand("INSERT INTO data (foo) VALUES (@p)", conn))
{
cmd.Parameters.AddWithValue("p", NpgsqlDbType.Geometry, bytes);
cmd.ExecuteNonQuery();
}
Assert.That(conn.ExecuteScalar("SELECT foo FROM data"), Is.EqualTo(point));
Assert.That(conn.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
}
}
public void PostgisTestArrayWrite()
{
using (var conn = OpenConnection())
using (var cmd = conn.CreateCommand())
{
var p = new PostgisPoint[1] { new PostgisPoint(1d, 1d) };
cmd.Parameters.AddWithValue(":p1", NpgsqlDbType.Array | NpgsqlDbType.Geometry, p);
cmd.CommandText = "SELECT :p1 = array(select st_makepoint(1,1))";
Assert.IsTrue((bool)cmd.ExecuteScalar());
}
}