/// <summary>
/// Converts to GeoAPI geometry type from database geometry type.
/// </summary>
/// <param name="value">The databse geometry value.</param>
/// <returns></returns>
protected override IGeometry ToGeometry(object value)
{
string bytes = value as string;
if (string.IsNullOrEmpty(bytes))
{
return(null);
}
// Bounding boxes are not serialized as hexadecimal string (?)
const string boxToken = "BOX(";
if (bytes.StartsWith(boxToken))
{
// TODO: Optimize?
bytes = bytes.Substring(boxToken.Length, bytes.Length - boxToken.Length - 1);
string[] parts = bytes.Split(',');
string[] min = parts[0].Split(' ');
string[] max = parts[1].Split(' ');
string wkt = string.Format(
"POLYGON(({0} {1},{0} {3},{2} {3},{2} {1},{0} {1}))",
min[0], min[1], max[0], max[1]);
return(new WKTReader().Read(wkt));
}
PostGisReader reader = new PostGisReader();
IGeometry geometry = reader.Read(ToByteArray(bytes));
this.SetDefaultSRID(geometry);
return(geometry);
}
internal IEnumerable <Contribution> GetContributionsFor(Area a)
{
var postGisReader = new PostGisReader();
// query bike data, get all data in the envelope around the geometry..
var geometry = postGisReader.Read(a.Geometry);
var envelope = geometry.EnvelopeInternal;
var left = envelope.MinX;
var right = envelope.MaxX;
var top = envelope.MaxY;
var bottom = envelope.MinY;
var sql = $"select * from \"Contributions\" where \"PointsGeom\" && " +
$"ST_MakeEnvelope({left}, {bottom}, {right}, {top}, 4326)";
var contributions = _bikeDataDb.Contributions
.FromSqlRaw(sql)
.ToList();
// check if there is an overlap, if so, add to the area statistics.
foreach (var c in contributions)
{
var cGeometry = postGisReader.Read(c.PointsGeom);
if (geometry.Crosses(cGeometry))
{
yield return(c);
}
}
}
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));
}
/// <summary>
///
/// </summary>
/// <param name="wkt"></param>
/// <param name="srid"></param>
private static void General(GeometryFactory geomFactory, string wkt, int srid)
{
var wktReader = new WKTReader(geomFactory);
var geom = wktReader.Read(wkt);
string parsed = geom.AsText();
var regeom = wr.Read(parsed);
string reparsed = regeom.AsText();
geom.SRID = srid;
regeom.SRID = srid;
Assert.IsTrue(geom.EqualsExact(regeom));
Assert.AreEqual(parsed, reparsed);
var pgr = new PostGisReader(geomFactory);
byte[] bytesB = new PostGisWriter(ByteOrder.BigEndian).Write(regeom);
var regeom2 = pgr.Read(bytesB);
Assert.IsTrue(geom.EqualsExact(regeom2));
byte[] bytesL = new PostGisWriter(ByteOrder.LittleEndian).Write(regeom);
var regeom3 = pgr.Read(bytesL);
Assert.IsTrue(geom.EqualsExact(regeom3));
Assert.IsTrue(regeom2.EqualsExact(regeom3));
Assert.AreEqual(bytesB.Length, bytesL.Length);
}
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);
}
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>The geometry</returns>
public override IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSQL = "SELECT \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smGeom_\" FROM " + QualifiedTable +
" WHERE \"" + ObjectIdColumn + "\"=" + oid + ";";
if (Logger.IsDebugEnabled)
{
Logger.Debug(string.Format("{0}\n{1}", "GetGeometryByID: executing sql:", strSQL));
}
using (var command = new NpgsqlCommand(strSQL, conn))
{
using (var dr = command.ExecuteReader())
{
var reader = new PostGisReader(Factory);
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
geom = reader.Read((byte[])dr[0]);
}
}
}
}
}
return(geom);
}
private Feature ToFeature(Area area)
{
var postGisReader = new PostGisReader();
var attributes = new AttributesTable();
attributes.Add("id", area.AreaId);
attributes.Add("parent_id", area.ParentAreaId);
if (area.AreaStatistics != null)
{
foreach (var at in area.AreaStatistics)
{
if (attributes.Exists(at.Key))
{
continue;
}
attributes.Add(at.Key, at.Value);
}
}
if (!attributes.Exists(Constants.StatisticKeyCount))
{
attributes.Add(Constants.StatisticKeyCount,
Random.Next(10000));
}
if (!attributes.Exists(Constants.StatisticKeyMeter))
{
attributes.Add(Constants.StatisticKeyMeter,
Random.Next(10000));
}
if (!attributes.Exists(Constants.StatisticKeyTime))
{
attributes.Add(Constants.StatisticKeyTime,
Random.Next(10000));
}
if (area.AreaAttributes != null)
{
foreach (var at in area.AreaAttributes)
{
if (attributes.Exists(at.Key))
{
continue;
}
attributes.Add(at.Key, at.Value);
}
}
var geometry = postGisReader.Read(area.Geometry);
return(new Feature(geometry, attributes));
}
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));
}
/// <summary>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public override Collection <IGeometry> GetGeometriesInView(Envelope bbox)
{
var features = new Collection <IGeometry>();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSQL = "SELECT \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smGeom_\" ";
strSQL += "FROM " + QualifiedTable + " WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSQL += _definitionQuery + " AND ";
}
if (_supportSTIntersects)
{
strSQL += "st_intersects(\"" + GeometryColumn + "\"," + GetBoxClause(bbox) + ")";
}
else
{
strSQL += "\"" + GeometryColumn + "\" && " + GetBoxClause(bbox);
}
if (Logger.IsDebugEnabled)
{
Logger.Debug(string.Format("{0}\n{1}", "GetGeometriesInView: executing sql:", strSQL));
}
using (var command = new NpgsqlCommand(strSQL, conn))
{
using (var dr = command.ExecuteReader())
{
var reader = new PostGisReader(Factory);
while (dr.Read())
{
if (dr.IsDBNull(0))
{
continue;
}
var geom = reader.Read((byte[])dr.GetValue(0));
if (geom != null)
{
features.Add(geom);
}
}
}
}
}
return(features);
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="oid"></param>
/// <returns>datarow</returns>
public override IFeature GetFeatureByOid(object oid)
{
GetNonSpatialColumns();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smtmp_\" ";
strSql += "FROM " + QualifiedTable + " WHERE \"" + ObjectIdColumn + "\" = :POid";
using (var cmd = new NpgsqlCommand(strSql, conn))
{
cmd.Parameters.Add(new NpgsqlParameter("POid", oid));
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
{
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
}
//No geometry, no feature!
if (g == null)
{
continue;
}
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
return((FeatureDataRow)fdt.Rows[0]);
}
return(null);
}
}
}
}
/// <summary>
/// Converts to GeoAPI geometry type from database geometry type.
/// </summary>
/// <param name="value">The databse geometry value.</param>
/// <returns></returns>
protected override Geometry ToGeometry(object value)
{
var bytes = value as byte[];
if (bytes == null)
{
return(null);
}
PostGisReader reader = new PostGisReader();
Geometry geometry = reader.Read(bytes);
this.SetDefaultSRID(geometry);
return(geometry);
}
/// <summary>
/// Converts to GeoAPI geometry type from database geometry type.
/// </summary>
/// <param name="value">The databse geometry value.</param>
/// <returns></returns>
protected override IGeometry ToGeometry(object value)
{
var bytes = value as byte[];
if (bytes == null)
{
return(null);
}
var factory = new GeometryFactory(new PrecisionModel(), 4326);
PostGisReader reader = new PostGisReader(factory);
IGeometry geometry = reader.Read(bytes);
this.SetDefaultSRID(geometry);
return(geometry);
}
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>
/// Returns geometries within the specified bounding box
/// </summary>
/// <param name="bbox"></param>
/// <returns></returns>
public override Collection<IGeometry> GetGeometriesInView(Envelope bbox)
{
var features = new Collection<IGeometry>();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSQL = "SELECT \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smGeom_\" ";
strSQL += "FROM " + QualifiedTable + " WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
strSQL += _definitionQuery + " AND ";
if (_supportSTIntersects)
{
strSQL += "st_intersects(\"" + GeometryColumn + "\"," + GetBoxClause(bbox) + ")";
}
else
{
strSQL += "\"" + GeometryColumn + "\" && " + GetBoxClause(bbox);
}
if (Logger.IsDebugEnabled)
Logger.Debug(string.Format("{0}\n{1}", "GetGeometriesInView: executing sql:", strSQL));
using (var command = new NpgsqlCommand(strSQL, conn))
{
using (var dr = command.ExecuteReader())
{
var reader = new PostGisReader(Factory);
while (dr.Read())
{
if (dr.IsDBNull(0))
continue;
var geom = reader.Read((byte[])dr.GetValue(0));
if (geom != null)
features.Add(geom);
}
}
}
}
return features;
}
protected override Geometry Read(byte[] b)
{
var pgReader = new PostGisReader(RandomGeometryHelper.Factory);
return(pgReader.Read(b));
}
internal NetTopologySuiteHandlerFactory(PostGisReader reader, PostGisWriter writer)
{
_reader = reader ?? throw new ArgumentNullException(nameof(reader));
_writer = writer ?? throw new ArgumentNullException(nameof(writer));
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public override void ExecuteIntersectionQuery(Envelope bbox, FeatureDataSet ds)
{
GetNonSpatialColumns();
//List<Geometry> features = new List<Geometry>();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smtmp_\" ";
strSql += "FROM " + QualifiedTable + " WHERE ";
// Attribute constraint
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSql += DefinitionQuery + " AND ";
}
// Spatial constraint
if (_supportSTIntersects)
{
strSql += "ST_Intersects(\"" + GeometryColumn + "\"," + GetBoxClause(bbox) + ")";
}
else
{
strSql += "\"" + GeometryColumn + "\" && " + GetBoxClause(bbox);
}
if (Logger.IsDebugEnabled)
{
Logger.Debug(string.Format("{0}\n{1}\n", "ExecuteIntersectionQuery: executing sql:", strSql));
}
using (var cmd = new NpgsqlCommand(strSql, conn))
{
using (var reader = cmd.ExecuteReader())
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
{
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
}
//No geometry, no feature!
if (g == null)
{
continue;
}
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
ds.Tables.Add(fdt);
}
}
}
}
internal NetTopologySuiteHandler(PostgresType postgresType, PostGisReader reader, PostGisWriter writer)
: base(postgresType)
{
_reader = reader;
_writer = writer;
}
/// <summary>
/// Returns a datarow based on a RowID
/// </summary>
/// <param name="rowId"></param>
/// <returns>datarow</returns>
public override FeatureDataRow GetFeature(uint rowId)
{
GetNonSpatialColumns();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smtmp_\" ";
strSql += "FROM " + QualifiedTable + " WHERE \"" + ObjectIdColumn +"\" = " + rowId;
using (var cmd = new NpgsqlCommand(strSql, conn))
{
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
g = geoReader.Read((byte[]) reader.GetValue(geomIndex));
//No geometry, no feature!
if (g == null)
continue;
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow) fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
return (FeatureDataRow) fdt.Rows[0];
}
return null;
}
}
}
}
/// <summary>
/// Returns all features with the view box
/// </summary>
/// <param name="bbox">view box</param>
/// <param name="ds">FeatureDataSet to fill data into</param>
public override void ExecuteIntersectionQuery(Envelope bbox, FeatureDataSet ds)
{
GetNonSpatialColumns();
//List<Geometry> features = new List<Geometry>();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smtmp_\" ";
strSql += "FROM " + QualifiedTable + " WHERE ";
// Attribute constraint
if (!String.IsNullOrEmpty(_definitionQuery))
strSql += DefinitionQuery + " AND ";
// Spatial constraint
if (_supportSTIntersects)
{
strSql += "ST_Intersects(\"" + GeometryColumn + "\"," + GetBoxClause(bbox) + ")";
}
else
{
strSql += "\"" + GeometryColumn + "\" && " + GetBoxClause(bbox);
}
if (Logger.IsDebugEnabled)
Logger.Debug(string.Format("{0}\n{1}\n", "ExecuteIntersectionQuery: executing sql:", strSql));
using (var cmd = new NpgsqlCommand(strSql, conn))
{
using (var reader = cmd.ExecuteReader())
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
//No geometry, no feature!
if (g == null)
continue;
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
ds.Tables.Add(fdt);
}
}
}
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
protected override void OnExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds)
{
GetNonSpatialColumns();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\"::bytea AS \"_smtmp_\" ";
strSql += "FROM " + Table + " WHERE ";
// Attribute constraint
if (!String.IsNullOrEmpty(_definitionQuery))
{
strSql += DefinitionQuery + " AND ";
}
// Spatial constraint
if (_supportSTIntersects)
{
strSql += "ST_Intersects(@PGeo ,\"" + GeometryColumn + "\")";
}
else
{
strSql += "\"" + GeometryColumn + "\" && @PGeo AND " +
_prefixFunction + "distance(\"" + GeometryColumn + "\", @PGeo)<0";
}
/*
* string strGeom = _prefixFunction + "GeomFromText('" + geom.AsText() + "')";
* if (SRID > 0)
* strGeom = _prefixFunction + "setSRID(" + strGeom + "," + SRID + ")";
*
* string strSQL = "SELECT * , " + _prefixFunction + "AsBinary(\"" + GeometryColumn + "\") As sharpmap_tempgeometry FROM " +
* QualifiedTable + " WHERE ";
*
* if (!String.IsNullOrEmpty(_definitionQuery))
* strSQL += DefinitionQuery + " AND ";
*
* strSQL += "\"" + GeometryColumn + "\" && " + strGeom + " AND " + _prefixFunction + "distance(\"" + GeometryColumn + "\", " +
* strGeom + ")<0";
*
*/
if (Logger.IsDebugEnabled)
{
Logger.Debug(string.Format("{0}\n{1}", "OnExecuteIntersectionQuery: executing sql:", strSql));
}
using (var cmd = new NpgsqlCommand(strSql, conn))
{
geom.SRID = SRID;
var par = new NpgsqlParameter("PGeo", NpgsqlDbType.Bytea);
par.NpgsqlValue = new PostGisWriter().Write(geom);
cmd.Parameters.Add(par);
//cmd.Parameters.AddWithValue("PGeo", new PostGisWriter().Write(geom));
using (var reader = cmd.ExecuteReader())
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
{
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
}
//No geometry, no feature!
if (g == null)
{
continue;
}
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
ds.Tables.Add(fdt);
}
}
}
}
protected override IGeometry Read(byte[] b)
{
var pgReader = new PostGisReader(this.RandomGeometryHelper.Factory);
return pgReader.Read(b);
}
/// <summary>
/// Returns the geometry corresponding to the Object ID
/// </summary>
/// <param name="oid">Object ID</param>
/// <returns>The geometry</returns>
public override IGeometry GetGeometryByID(uint oid)
{
IGeometry geom = null;
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSQL = "SELECT \"" + GeometryColumn + "\"" + _geometryCast + "::bytea AS \"_smGeom_\" FROM " + QualifiedTable +
" WHERE \"" + ObjectIdColumn + "\"=" + oid + ";";
if (Logger.IsDebugEnabled)
Logger.Debug(string.Format("{0}\n{1}", "GetGeometryByID: executing sql:", strSQL));
using (var command = new NpgsqlCommand(strSQL, conn))
{
using (var dr = command.ExecuteReader())
{
var reader = new PostGisReader(Factory);
while (dr.Read())
{
if (!dr.IsDBNull(0))
geom = reader.Read((byte[]) dr[0]);
}
}
}
}
return geom;
}
/// <summary>
/// Returns the features that intersects with 'geom'
/// </summary>
/// <param name="geom"></param>
/// <param name="ds">FeatureDataSet to fill data into</param>
protected override void OnExecuteIntersectionQuery(IGeometry geom, FeatureDataSet ds)
{
GetNonSpatialColumns();
using (var conn = new NpgsqlConnection(ConnectionString))
{
conn.Open();
var strSql = "SELECT " + _columns + ", \"" + GeometryColumn + "\" AS \"_smtmp_\" ";
strSql += "FROM " + Table + " WHERE ";
// Attribute constraint
if (!String.IsNullOrEmpty(_definitionQuery))
strSql += DefinitionQuery + " AND ";
// Spatial constraint
if (_supportSTIntersects)
{
strSql += "ST_Intersects(@PGeo ,\"" + GeometryColumn + "\")";
}
else
{
strSql += "\"" + GeometryColumn + "\" && @PGeo AND " +
_prefixFunction + "distance(\"" + GeometryColumn + "\", @PGeo)<0";
}
/*
string strGeom = _prefixFunction + "GeomFromText('" + geom.AsText() + "')";
if (SRID > 0)
strGeom = _prefixFunction + "setSRID(" + strGeom + "," + SRID + ")";
string strSQL = "SELECT * , " + _prefixFunction + "AsBinary(\"" + GeometryColumn + "\") As sharpmap_tempgeometry FROM " +
QualifiedTable + " WHERE ";
if (!String.IsNullOrEmpty(_definitionQuery))
strSQL += DefinitionQuery + " AND ";
strSQL += "\"" + GeometryColumn + "\" && " + strGeom + " AND " + _prefixFunction + "distance(\"" + GeometryColumn + "\", " +
strGeom + ")<0";
*/
if (Logger.IsDebugEnabled)
Logger.Debug(string.Format("{0}\n{1}", "OnExecuteIntersectionQuery: executing sql:", strSql));
using (var cmd = new NpgsqlCommand(strSql, conn))
{
geom.SRID = SRID;
cmd.Parameters.AddWithValue("PGeo", new PostGisWriter().Write(geom));
using (var reader = cmd.ExecuteReader())
{
var geomIndex = reader.FieldCount - 1;
var fdt = CreateTableFromReader(reader, geomIndex);
var dataTransfer = new object[geomIndex];
var geoReader = new PostGisReader(Factory.CoordinateSequenceFactory, Factory.PrecisionModel);
fdt.BeginLoadData();
while (reader.Read())
{
IGeometry g = null;
if (!reader.IsDBNull(geomIndex))
g = geoReader.Read((byte[])reader.GetValue(geomIndex));
//No geometry, no feature!
if (g == null)
continue;
//Get all the attribute data
var count = reader.GetValues(dataTransfer);
System.Diagnostics.Debug.Assert(count == dataTransfer.Length);
var fdr = (FeatureDataRow)fdt.LoadDataRow(dataTransfer, true);
fdr.Geometry = g;
}
fdt.EndLoadData();
ds.Tables.Add(fdt);
}
}
}
}
