public void T04_InsertInDataSource()
{
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
gl.Features.AcceptChanges();
FeatureDataRow fdr = gl.Features.NewRow();
fdr[0] = 999;
fdr.Geometry = gl.GeometryFactory.WktReader.Read("POINT (-2 13)");
fdr[1] = fdrLabel(fdr);
gl.Features.AddRow(fdr);
prov.Insert(gl.Features.GetChanges());
//gl.SelectedFilter = new FeatureQueryExpression(
// new AttributeBinaryExpression(
// new PropertyNameExpression("OID"),
// BinaryOperator.Equals,
// (Int64)999));
//Assert.AreEqual(5, gl.SelectedFeatures.TotalItemCount);
fdr = gl.Features.Find(999);
Assert.IsNotNull(fdr);
}
public void T11_JD_Test()
{
PostGisProvider <int> search = new PostGisProvider <Int32>(_geometryFactory,
connectionString, "public",
"vw_osm_germany_line", "osm_id", "way");
BinaryExpression binaryExpression =
new BinaryExpression(new PropertyNameExpression("z_order"),
BinaryOperator.Equals, new LiteralExpression <int>(0));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[] {});
ProviderQueryExpression prov = new ProviderQueryExpression(null as ProviderPropertiesExpression,
new AllAttributesExpression(), binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
PostGisFeatureDataReader sfdr = obj as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
Int64 numRows = 0;
while (sfdr.Read())
{
numRows++;
}
Assert.IsTrue(numRows > 1);
providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(
// new OrderByExpression("z_order", SortOrder.Descending)),
new DataPageSizeExpression(10),
new DataPageNumberExpression(5)
});
AttributesProjectionExpression ape =
new AttributesProjectionExpression(new[] { "osm_id", "name", "z_order", "way" });
prov = new ProviderQueryExpression(providerProps, ape, null);
sfdr = search.ExecuteQuery(prov) as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
numRows = 0;
while (sfdr.Read())
{
numRows++;
}
Assert.AreEqual(10, numRows);
Debug.WriteLine("\r\n*** T11 passed!");
}
public void T02_CreateGeometryLayerFromPostGis()
{
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
Assert.IsNotNull(gl);
Assert.AreEqual("test", gl.LayerName);
Assert.AreEqual(prov.Srid, gl.Srid);
}
private void Dispose(bool disposing)
{
if(IsDisposed)
{
return;
}
if(disposing)
{
_provider = null;
}
}
public void T03_SomeSelects()
{
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
gl.Features.AcceptChanges();
Assert.AreEqual(4, gl.Features.FeatureCount);
Assert.AreEqual(_geometryFactory.CreateExtents2D(0, -3, 15, 20), gl.Features.Extents);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new ExtentsExpression(prov.GeometryFactory.CreateExtents2D(-1, -1, 1, 1)),
SpatialOperation.Contains,
new ThisExpression())
);
Assert.AreEqual(1, gl.Features.Rows.Count);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new GeometryExpression(prov.GeometryFactory.WktReader.Read(("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))"))),
SpatialOperation.Contains,
new ThisExpression())
);
//SELECT testfeaturedatatable.poid,testfeaturedatatable.label,ST_AsBinary(xgeometryx)::bytea AS xgeometryx FROM public.testfeaturedatatable WHERE ( ST_Contains( ST_GeomFromWKB(:iparam0, -1), xgeometryx ))
//SELECT testfeaturedatatable.poid,testfeaturedatatable.label,ST_AsBinary(xgeometryx)::bytea AS xgeometryx FROM public.testfeaturedatatable WHERE ( ST_Contains( ST_GeomFromText('POLYGON((-1 -1, 0 1, 1 -1, -1 -1))', -1), xgeometryx ))
Assert.AreEqual(1, gl.Features.Rows.Count);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new GeometryExpression(
prov.GeometryFactory.WktReader.Read(("POLYGON((-1 -1, -1 1, 0 1, 0 -1, -1 -1))"))),
SpatialOperation.Touches,
new ThisExpression())
);
Assert.AreEqual(1, gl.Features.Rows.Count);
//gl = new GeometryLayer(prov);
//gl.Select(
// new SpatialBinaryExpression(
// new ThisExpression(),
// SpatialOperation.Within,
// new GeometryExpression(prov.GeometryFactory.WktReader.Read("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))")))
// );
//Assert.AreEqual(0, gl.Features.Rows.TotalItemCount);
}
public void T99_GeometryFromBinary()
{
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
using (NpgsqlConnection cn = new NpgsqlConnection(connectionString))
{
cn.Open();
NpgsqlCommand cm = new NpgsqlCommand(
"SELECT ST_SRID( ST_GeomFromWKB(:p0::bytea, -1) )", cn);
NpgsqlParameter par = new NpgsqlParameter(":p0", DbType.Binary);
par.Value = prov.GeometryFactory.WktReader.Read("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))").AsBinary();
cm.Parameters.Add(par);
Debug.Write(cm.ExecuteScalar());
}
}
public void T01_CreateTableFromFeatureDataTable()
{
FeatureDataTable fdt = createFeatureDataTable();
PostGisProviderStatic.CreateDataTable <Int64>(fdt, connectionString);
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString,
"public", fdt.TableName, fdt.PrimaryKey[0].ColumnName,
PostGisProviderStatic.DefaultGeometryColumnName);
Assert.IsNotNull(prov);
Assert.AreEqual(connectionString, prov.ConnectionString);
Assert.AreEqual("public", prov.TableSchema);
Assert.AreEqual(fdt.TableName, prov.Table);
Assert.AreEqual(PostGisProviderStatic.DefaultSrid, prov.Srid);
Assert.AreEqual(fdt.PrimaryKey[0].ColumnName.ToLower(), prov.OidColumn);
Assert.AreEqual(PostGisProviderStatic.DefaultGeometryColumnName, prov.GeometryColumn);
}
public void T05_DeleteFromDataSource()
{
PostGisProvider <Int64> prov = new PostGisProvider <Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer(prov);
gl.Select(new SpatialBinaryExpression(
new ExtentsExpression(prov.GetExtents()), SpatialOperation.Contains, new ThisExpression()));
gl.Features.AcceptChanges();
//gl.Features.RemoveRow(gl.Features[4]);
FeatureDataRow fdr = gl.Features.Find(999);
fdr.Delete();
//prov.Delete(gl.Features.GetChanges(DataRowState.Deleted));
gl.Features.AcceptChanges();
gl = new GeometryLayer(prov);
gl.Select(new SpatialBinaryExpression(
new ExtentsExpression(prov.GetExtents()), SpatialOperation.Contains, new ThisExpression()));
gl.Features.AcceptChanges();
//Assert.AreEqual(4, gl.Features.Rows.TotalItemCount);
}
public void T04_InsertInDataSource()
{
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
gl.Features.AcceptChanges();
FeatureDataRow fdr = gl.Features.NewRow();
fdr[0] = 999;
fdr.Geometry = gl.GeometryFactory.WktReader.Read("POINT (-2 13)");
fdr[1] = fdrLabel(fdr);
gl.Features.AddRow(fdr);
prov.Insert(gl.Features.GetChanges());
//gl.SelectedFilter = new FeatureQueryExpression(
// new AttributeBinaryExpression(
// new PropertyNameExpression("OID"),
// BinaryOperator.Equals,
// (Int64)999));
//Assert.AreEqual(5, gl.SelectedFeatures.TotalItemCount);
fdr = gl.Features.Find(999);
Assert.IsNotNull(fdr);
}
public void T05_DeleteFromDataSource()
{
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer(prov);
gl.Select(new SpatialBinaryExpression(
new ExtentsExpression(prov.GetExtents()), SpatialOperation.Contains, new ThisExpression()));
gl.Features.AcceptChanges();
//gl.Features.RemoveRow(gl.Features[4]);
FeatureDataRow fdr = gl.Features.Find(999);
fdr.Delete();
//prov.Delete(gl.Features.GetChanges(DataRowState.Deleted));
gl.Features.AcceptChanges();
gl = new GeometryLayer(prov);
gl.Select(new SpatialBinaryExpression(
new ExtentsExpression(prov.GetExtents()), SpatialOperation.Contains, new ThisExpression()));
gl.Features.AcceptChanges();
//Assert.AreEqual(4, gl.Features.Rows.TotalItemCount);
}
public void T03_SomeSelects()
{
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
gl.Features.AcceptChanges();
Assert.AreEqual(4, gl.Features.FeatureCount);
Assert.AreEqual(_geometryFactory.CreateExtents2D(0, -3, 15, 20), gl.Features.Extents);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new ExtentsExpression(prov.GeometryFactory.CreateExtents2D(-1, -1, 1, 1)),
SpatialOperation.Contains,
new ThisExpression())
);
Assert.AreEqual(1, gl.Features.Rows.Count);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new GeometryExpression(prov.GeometryFactory.WktReader.Read(("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))"))),
SpatialOperation.Contains,
new ThisExpression())
);
//SELECT testfeaturedatatable.poid,testfeaturedatatable.label,ST_AsBinary(xgeometryx)::bytea AS xgeometryx FROM public.testfeaturedatatable WHERE ( ST_Contains( ST_GeomFromWKB(:iparam0, -1), xgeometryx ))
//SELECT testfeaturedatatable.poid,testfeaturedatatable.label,ST_AsBinary(xgeometryx)::bytea AS xgeometryx FROM public.testfeaturedatatable WHERE ( ST_Contains( ST_GeomFromText('POLYGON((-1 -1, 0 1, 1 -1, -1 -1))', -1), xgeometryx ))
Assert.AreEqual(1, gl.Features.Rows.Count);
gl = new GeometryLayer(prov);
gl.Select(
new SpatialBinaryExpression(
new GeometryExpression(
prov.GeometryFactory.WktReader.Read(("POLYGON((-1 -1, -1 1, 0 1, 0 -1, -1 -1))"))),
SpatialOperation.Touches,
new ThisExpression())
);
Assert.AreEqual(1, gl.Features.Rows.Count);
//gl = new GeometryLayer(prov);
//gl.Select(
// new SpatialBinaryExpression(
// new ThisExpression(),
// SpatialOperation.Within,
// new GeometryExpression(prov.GeometryFactory.WktReader.Read("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))")))
// );
//Assert.AreEqual(0, gl.Features.Rows.TotalItemCount);
}
public void T02_CreateGeometryLayerFromPostGis()
{
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
Assert.IsNotNull(gl);
Assert.AreEqual("test", gl.LayerName);
Assert.AreEqual(prov.Srid, gl.Srid);
}
public void T01_CreateTableFromFeatureDataTable()
{
FeatureDataTable fdt = createFeatureDataTable();
PostGisProviderStatic.CreateDataTable<Int64>(fdt, connectionString);
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString,
"public", fdt.TableName, fdt.PrimaryKey[0].ColumnName,
PostGisProviderStatic.DefaultGeometryColumnName);
Assert.IsNotNull(prov);
Assert.AreEqual(connectionString, prov.ConnectionString);
Assert.AreEqual("public", prov.TableSchema);
Assert.AreEqual(fdt.TableName, prov.Table);
Assert.AreEqual(PostGisProviderStatic.DefaultSrid, prov.Srid);
Assert.AreEqual(fdt.PrimaryKey[0].ColumnName.ToLower(), prov.OidColumn);
Assert.AreEqual(PostGisProviderStatic.DefaultGeometryColumnName, prov.GeometryColumn);
}
internal PostGisFeatureDataReader(PostGisProvider provider)
{
_provider = provider;
}
public IFeatureProvider GetProvider()
{
if (EnsureTables())
{
string conn = ServerConnectionString;
conn += string.Format("Database={0};", cbDataBases.SelectedItem);
conn += "Enlist=true;";
DataRowView drv = (DataRowView) cbTables.SelectedItem;
string schema = (string) drv["Schema"];
string tableName = (string) drv["TableName"];
;
string geomColumn = (string) drv["GeometryColumn"];
int coordDimension = (int) drv["Dimension"];
string srid = (string) drv["SRID"]; //((int)).ToString();
GeometryServices gs = new GeometryServices();
IGeometryFactory gf = gs[srid]; //, coordDimension];
//if (!string.IsNullOrEmpty(spatialReference))
// gf.SpatialReference = gs.CoordinateSystemFactory.CreateFromWkt(spatialReference);
string oidColumnName = (String) dgvColumns.Rows[oidcolumn].Cells["ColumnName"].Value;
List<String> columns = new List<string>();
//List<OrderByExpression> orderby = new List<OrderByExpression>();
foreach (DataGridViewRow dgvr in dgvColumns.Rows)
{
if ((bool) dgvr.Cells["Include"].Value) columns.Add((String) dgvr.Cells["ColumnName"].Value);
//if (dgvr.Cells["SortOrder"].Value != null)
// orderby.Add(new OrderByExpression((String) dgvr.Cells[1].Value,
// (SortOrder) dgvr.Cells["SortOrder"].Value));
}
if (!columns.Contains(oidColumnName))
columns.Insert(0, oidColumnName);
columns.Add(geomColumn);
ProviderPropertiesExpression ppe = null;
//if (orderby.Count == 0)
//{
// ppe = new ProviderPropertiesExpression(
// new ProviderPropertyExpression[]
// {
// new AttributesCollectionExpression(columns)
// });
//}
//else
//{
ppe = new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(orderby),
new AttributesCollectionExpression(columns)
});
//}
IFeatureProvider prov = null;
switch ((String) dgvColumns.Rows[oidcolumn].Cells["DataType"].Value)
{
case "bigint":
prov = new PostGisProvider<long>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "integer":
prov = new PostGisProvider<int>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "character varying":
case "text":
prov = new PostGisProvider<string>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
case "uuid":
prov = new PostGisProvider<Guid>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{DefaultProviderProperties = ppe};
break;
default:
return null;
}
//jd commented temporarily to get a build
//((ISpatialDbProvider)prov).DefinitionQuery =
// new ProviderQueryExpression(ppe, ape, null);
return prov;
}
return null;
}
public void T99_GeometryFromBinary()
{
PostGisProvider<Int64> prov = new PostGisProvider<Int64>(
_geometryFactory, connectionString, "TestFeatureDataTable");
GeometryLayer gl = new GeometryLayer("test", prov);
gl.Select(new AllAttributesExpression());
using (NpgsqlConnection cn = new NpgsqlConnection(connectionString))
{
cn.Open();
NpgsqlCommand cm = new NpgsqlCommand(
"SELECT ST_SRID( ST_GeomFromWKB(:p0::bytea, -1) )", cn);
NpgsqlParameter par = new NpgsqlParameter(":p0", DbType.Binary);
par.Value = prov.GeometryFactory.WktReader.Read("POLYGON((-1 -1, 0 1, 1 -1, -1 -1))").AsBinary();
cm.Parameters.Add(par);
Debug.Write(cm.ExecuteScalar());
}
}
public void T11_JD_Test()
{
PostGisProvider<int> search = new PostGisProvider<Int32>(_geometryFactory,
connectionString, "public",
"vw_osm_germany_line", "osm_id", "way");
BinaryExpression binaryExpression =
new BinaryExpression(new PropertyNameExpression("z_order"),
BinaryOperator.Equals, new LiteralExpression<int>(0));
ProviderPropertiesExpression providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[] {});
ProviderQueryExpression prov = new ProviderQueryExpression(null as ProviderPropertiesExpression,
new AllAttributesExpression(), binaryExpression);
object obj = search.ExecuteQuery(prov);
Assert.IsNotNull(obj);
PostGisFeatureDataReader sfdr = obj as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
Int64 numRows = 0;
while (sfdr.Read())
numRows++;
Assert.IsTrue(numRows > 1);
providerProps =
new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(
// new OrderByExpression("z_order", SortOrder.Descending)),
new DataPageSizeExpression(10),
new DataPageNumberExpression(5)
});
AttributesProjectionExpression ape =
new AttributesProjectionExpression(new[] {"osm_id", "name", "z_order", "way"});
prov = new ProviderQueryExpression(providerProps, ape, null);
sfdr = search.ExecuteQuery(prov) as PostGisFeatureDataReader;
Assert.IsNotNull(sfdr);
numRows = 0;
while (sfdr.Read()) numRows++;
Assert.AreEqual(10, numRows);
Debug.WriteLine("\r\n*** T11 passed!");
}
public IFeatureProvider GetProvider()
{
if (EnsureTables())
{
string conn = ServerConnectionString;
conn += string.Format("Database={0};", cbDataBases.SelectedItem);
conn += "Enlist=true;";
DataRowView drv = (DataRowView)cbTables.SelectedItem;
string schema = (string)drv["Schema"];
string tableName = (string)drv["TableName"];
;
string geomColumn = (string)drv["GeometryColumn"];
int coordDimension = (int)drv["Dimension"];
string srid = (string)drv["SRID"]; //((int)).ToString();
GeometryServices gs = new GeometryServices();
IGeometryFactory gf = gs[srid]; //, coordDimension];
//if (!string.IsNullOrEmpty(spatialReference))
// gf.SpatialReference = gs.CoordinateSystemFactory.CreateFromWkt(spatialReference);
string oidColumnName = (String)dgvColumns.Rows[oidcolumn].Cells["ColumnName"].Value;
List <String> columns = new List <string>();
//List<OrderByExpression> orderby = new List<OrderByExpression>();
foreach (DataGridViewRow dgvr in dgvColumns.Rows)
{
if ((bool)dgvr.Cells["Include"].Value)
{
columns.Add((String)dgvr.Cells["ColumnName"].Value);
}
//if (dgvr.Cells["SortOrder"].Value != null)
// orderby.Add(new OrderByExpression((String) dgvr.Cells[1].Value,
// (SortOrder) dgvr.Cells["SortOrder"].Value));
}
if (!columns.Contains(oidColumnName))
{
columns.Insert(0, oidColumnName);
}
columns.Add(geomColumn);
ProviderPropertiesExpression ppe = null;
//if (orderby.Count == 0)
//{
// ppe = new ProviderPropertiesExpression(
// new ProviderPropertyExpression[]
// {
// new AttributesCollectionExpression(columns)
// });
//}
//else
//{
ppe = new ProviderPropertiesExpression(
new ProviderPropertyExpression[]
{
//new OrderByCollectionExpression(orderby),
new AttributesCollectionExpression(columns)
});
//}
IFeatureProvider prov = null;
switch ((String)dgvColumns.Rows[oidcolumn].Cells["DataType"].Value)
{
case "bigint":
prov = new PostGisProvider <long>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{
DefaultProviderProperties = ppe
};
break;
case "integer":
prov = new PostGisProvider <int>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{
DefaultProviderProperties = ppe
};
break;
case "character varying":
case "text":
prov = new PostGisProvider <string>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{
DefaultProviderProperties = ppe
};
break;
case "uuid":
prov = new PostGisProvider <Guid>(gf, conn, schema, tableName, oidColumnName, geomColumn,
gs.CoordinateTransformationFactory)
{
DefaultProviderProperties = ppe
};
break;
default:
return(null);
}
//jd commented temporarily to get a build
//((ISpatialDbProvider)prov).DefinitionQuery =
// new ProviderQueryExpression(ppe, ape, null);
return(prov);
}
return(null);
}
