public PostGisProvider(IGeometryFactory geometryFactory, string connectionString,
string tableSchema, string tableName, string oidColumn, string geometryColumn,
ICoordinateTransformationFactory coordinateTransformationFactory)
: base(
new PostGisDbUtility(), geometryFactory, connectionString, tableSchema,
tableName,
oidColumn,
geometryColumn,
coordinateTransformationFactory)
{
using (NpgsqlConnection cn = (NpgsqlConnection)DbUtility.CreateConnection(connectionString))
{
try
{
cn.Open();
if (!PostGisProviderStatic.Has_X_Privilege(cn, "table", "\"public\".\"geometry_columns\"", "SELECT"))
{
throw new PostGisException(
"Insufficient rights to access table \"public\".\"geometry_columns\"!");
}
if (
!PostGisProviderStatic.Has_X_Privilege(cn, "table",
string.Format("\"{0}\".\"{1}\"", tableSchema, tableName),
"SELECT"))
{
throw new PostGisException(string.Format(
"Insufficient rights to access table \"{0}\".\"{1}\"!",
tableSchema, tableName));
}
NpgsqlCommand cmd = (NpgsqlCommand)DbUtility.CreateCommand();
cmd.Connection = cn;
cmd.CommandText =
@"SELECT x.""type""
FROM ""public"".""geometry_columns"" AS x
WHERE (x.""f_table_schema""=:p0 AND x.""f_table_name""=:p1 AND x.""f_geometry_column""=:p2);";
cmd.Parameters.Add(DbUtility.CreateParameter("p0", tableSchema, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("p1", tableName, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("p2", geometryColumn, ParameterDirection.Input));
NpgsqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (dr.HasRows)
{
dr.Read();
//valid geometry type
_validGeometryType = parseGeometryType(dr.GetString(0));
}
else
{
_validGeometryType = OgcGeometryType.Geometry;
}
}
catch (Exception)
{
_validGeometryType = OgcGeometryType.Unknown;
}
}
}
public void DropSridConstraint()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
DropSridConstraint(conn, TableSchema, Table, GeometryColumn);
}
}
//protected override DataTable BuildSchemaTable()
//{
// return base.BuildSchemaTable(true);
//}
public override IExtents GetExtents()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText =
string.Format(
"SELECT MIN({0}_Envelope_MinX), MIN({0}_Envelope_MinY), MAX({0}_Envelope_MaxX), MAX({0}_Envelope_MaxY) FROM {1}.{2} {3}",
GeometryColumn, TableSchema, Table,
GetWithClause(DefaultProviderProperties == null
? null
: DefaultProviderProperties.ProviderProperties.Collection));
cmd.CommandType = CommandType.Text;
conn.Open();
using (IDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (r.Read())
{
if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3))
{
return(GeometryFactory.CreateExtents());
}
double xmin = r.GetDouble(0);
double ymin = r.GetDouble(1);
double xmax = r.GetDouble(2);
double ymax = r.GetDouble(3);
return(GeometryFactory.CreateExtents2D(xmin, ymin, xmax, ymax));
}
}
return(GeometryFactory.CreateExtents());
}
}
protected override void ReadSpatialReference(out ICoordinateSystem cs, out string srid)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT TOP 1 SRID from ST.GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = @pschema and F_TABLE_NAME = @ptablename";
cmd.Parameters.Add(DbUtility.CreateParameter("pschema", TableSchema, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("ptablename", Table, ParameterDirection.Input));
conn.Open();
object result = cmd.ExecuteScalar();
if (result is int)
{
int isrid = (int)result;
cs = SridMap.DefaultInstance.Process(isrid, (ICoordinateSystem)null);
srid = !Equals(cs, default(ICoordinateSystem)) ? SridMap.DefaultInstance.Process(cs, "") : "";
return;
}
}
}
cs = default(ICoordinateSystem);
srid = "";
}
public void CreateSridConstraint()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
CreateSridConstraint(conn, TableSchema, Table, GeometryColumn, SridInt.HasValue ? SridInt.Value : 0);
}
}
public void CreateIndex(string indexName, IEnumerable <string> columnNames)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
conn.Open();
CreateIndex(conn, indexName, columnNames);
}
}
public void CreateEnvelopeColumns()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
conn.Open();
//using (IDbTransaction tran = conn.BeginTransaction())
//{
CreateEnvelopeColumns(conn);
//}
}
}
public void RebuildSpatialIndex(SqlServer2008SpatialIndexGridDensity level1,
SqlServer2008SpatialIndexGridDensity level2,
SqlServer2008SpatialIndexGridDensity level3,
SqlServer2008SpatialIndexGridDensity level4)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
conn.Open();
RebuildSpatialIndex(conn, level1, level2, level3, level4);
}
}
public virtual void Update(IEnumerable <FeatureDataRow <TOid> > features)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
{
Boolean success = true;
using (IDbTransaction tran = conn.BeginTransaction())
{
IDbCommand cmd = DbUtility.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandText = string.Format(
"UPDATE {0} SET {1} WHERE ({2});", QualifiedTableName,
UpdateClause(cmd),
WhereClause(cmd));
foreach (FeatureDataRow row in features)
{
for (int i = 0; i < cmd.Parameters.Count - 2; i++)
{
((IDataParameter)cmd.Parameters[i]).Value = row[i];
}
((IDataParameter)cmd.Parameters["@PGeo"]).Value = row.Geometry.AsBinary();
((IDataParameter)cmd.Parameters["@POldOid"]).Value =
row[OidColumn, DataRowVersion.Original];
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
success = false;
break;
}
}
if (success)
{
tran.Commit();
}
}
}
}
}
public void RegisterInGeometryColumnsTable()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
if (!DatabaseHasGeometryColumnsTable(conn, TableSchema))
{
CreateGeometryColumnsTable(conn, TableSchema);
}
RegisterInGeometryColumnsTable(conn, DbUtility, TableSchema, Table, GeometryColumn, 2,
SridInt.HasValue ? SridInt.Value : 0, "GEOMETRY");
}
}
public void FixGeometries()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
string sql = string.Format("UPDATE {0} Set {1} = {1}.MakeValid()", QualifiedTableName, GeometryColumn);
conn.Open();
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
}
protected virtual IFeatureDataReader ExecuteFeatureDataReader(IDbCommand cmd)
{
Debug.WriteLine(String.Format("executing sql : {0}", cmd.CommandText));
IDbConnection conn = DbUtility.CreateConnection(ConnectionString);
cmd.Connection = conn;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return(new SpatialDbFeatureDataReader(GeometryFactory, cmd.ExecuteReader(CommandBehavior.CloseConnection),
GeometryColumn, OidColumn)
{
CoordinateTransformation = CoordinateTransformation
});
}
public override Int32 GetFeatureCount()
{
AttributesProjectionExpression attrs = new AttributesProjectionExpression(new[] { "Count(*)" });
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
using (
IDbCommand cmd =
PrepareSelectCommand(DefinitionQuery == null
? (Expression)attrs
: new QueryExpression(attrs, DefinitionQuery)))
{
cmd.Connection = conn;
conn.Open();
return((Int32)cmd.ExecuteScalar());
}
}
}
public override IExtents GetExtents()
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
using (IDbCommand cmd = DbUtility.CreateCommand())
{
conn.Open();
cmd.Connection = conn;
/*cmd.CommandText = String.Format(
* "SELECT " +
* "st_xmin(x.ext) AS xmin, st_ymin(x.ext) AS ymin, " +
* "st_xmax(x.ext) AS xmax, st_ymax(x.ext) AS ymax " +
* "FROM "+
* "(SELECT st_box3d( st_estimated_extent('{0}', '{1}', '{2}') ) AS ext ) as x;",
* TableSchema, Table, GeometryColumn);
*
*/
cmd.CommandText = String.Format(
"SELECT " +
"st_xmin(x.ext) AS xmin, st_ymin(x.ext) AS ymin, " +
"st_xmax(x.ext) AS xmax, st_ymax(x.ext) AS ymax " +
"FROM " +
"(SELECT st_extent( \"{2}\" ) as ext FROM {0}.\"{1}\") as x;",
TableSchema, Table, GeometryColumn);
cmd.CommandType = CommandType.Text;
using (NpgsqlDataReader r = (NpgsqlDataReader)cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (r.Read())
{
if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3))
{
return(GeometryFactory.CreateExtents());
}
return(GeometryFactory.CreateExtents2D(
r.GetDouble(0), r.GetDouble(1),
r.GetDouble(2), r.GetDouble(3)));
}
}
return(GeometryFactory.CreateExtents());
}
}
public virtual void Delete(IEnumerable <FeatureDataRow <TOid> > features)
{
List <TOid> featureIds = new List <TOid>();
foreach (FeatureDataRow <TOid> fdr in features)
{
featureIds.Add(fdr.Id);
}
ExpressionTreeToSqlCompilerBase <TOid> compiler = CreateSqlCompiler(null);
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText =
String.Format(
"DELETE FROM {0} WHERE {1} in ({2})",
QualifiedTableName,
OidColumn,
String.Join(",",
Enumerable.ToArray(
Processor.Select(featureIds,
delegate(TOid o)
{
return
(compiler.CreateParameter(o).ParameterName);
})))
);
conn.Open();
foreach (IDataParameter p in compiler.ParameterCache.Values)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
}
}
protected virtual DataTable BuildSchemaTable(Boolean withGeometryColumn)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.CommandText = string.Format("SELECT * FROM {0} ", QualifiedTableName);
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
IDbDataAdapter da = DbUtility.CreateAdapter(cmd);
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
DataTable dt = ds.Tables[0];
//remove geometry column from schema
if (!withGeometryColumn)
{
dt.Columns.Remove(GeometryColumn);
}
return(dt);
}
}
public override IExtents GetExtents()
{
bool withNoLock =
GetProviderPropertyValue <WithNoLockExpression, bool>(
DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection,
false);
SqlServer2008ExtentsMode server2008ExtentsCalculationMode =
GetProviderPropertyValue <MsSqlServer2008ExtentsModeExpression, SqlServer2008ExtentsMode>(
DefaultProviderProperties == null ? null : DefaultProviderProperties.ProviderProperties.Collection,
SqlServer2008ExtentsMode.QueryIndividualFeatures);
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
switch (server2008ExtentsCalculationMode)
{
case SqlServer2008ExtentsMode.UseSqlSpatialTools:
{
cmd.CommandText =
string.Format(
@"
declare @envelope Geometry
select @envelope = dbo.GeometryEnvelopeAggregate({0}) from {1}.{2} {3}
select
@envelope.STPointN(2).STX as MinX,
@envelope.STPointN(2).STY as MinY,
@envelope.STPointN(4).STX as MaxX,
@envelope.STPointN(4).STY as MaxY",
GeometryColumn, TableSchema, Table,
withNoLock ? "WITH(NOLOCK)" : String.Empty);
break;
}
case SqlServer2008ExtentsMode.UseEnvelopeColumns:
{
cmd.CommandText = string.Format(
"SELECT MIN({0}_Envelope_MinX), MIN({0}_Envelope_MinY), MAX({0}_Envelope_MaxX), MAX({0}_Envelope_MaxY) FROM {1}.{2} {3}",
GeometryColumn, TableSchema, Table,
withNoLock ? "WITH(NOLOCK)" : String.Empty);
break;
}
default:
{
cmd.CommandText =
string.Format(
@"
select
Min({0}.STEnvelope().STPointN(1).STX)as MinX,
Min({0}.STEnvelope().STPointN(1).STY) as MinY,
Max({0}.STEnvelope().STPointN(3).STX) as MaxX,
Max({0}.STEnvelope().STPointN(3).STY) as MaxY FROM {1}.{2} {3}" ,
this.GeometryColumn, TableSchema, Table, withNoLock ? "WITH(NOLOCK)" : String.Empty);
break;
}
}
cmd.CommandType = CommandType.Text;
conn.Open();
using (IDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (r.Read())
{
if (r.IsDBNull(0) || r.IsDBNull(1) || r.IsDBNull(2) || r.IsDBNull(3))
{
return(GeometryFactory.CreateExtents());
}
double xmin = r.GetDouble(0);
double ymin = r.GetDouble(1);
double xmax = r.GetDouble(2);
double ymax = r.GetDouble(3);
return(GeometryFactory.CreateExtents2D(xmin, ymin, xmax, ymax));
}
}
}
return(GeometryFactory.CreateExtents());
}
public virtual void Insert(IEnumerable <FeatureDataRow <TOid> > features)
{
OgcGeometryType geometryType = OgcGeometryType.Unknown;
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (IDbTransaction tran = conn.BeginTransaction())
{
IDbCommand cmd = DbUtility.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandText = string.Format(
"INSERT INTO {0} {1};", QualifiedTableName, InsertClause(cmd));
int geometryParameterIndex = cmd.Parameters.Count - 1;
foreach (FeatureDataRow <TOid> row in features)
{
if (cmd.Parameters.Count > 0)
{
for (int i = 0; i < row.FieldCount; i++)
{
((IDataParameter)cmd.Parameters[i]).Value = row.GetValue(i);
}
((IDataParameter)cmd.Parameters[geometryParameterIndex]).Value =
row.Geometry.AsBinary();
}
else
{
for (int i = 0; i < row.FieldCount; i++)
{
cmd.Parameters.Add(DbUtility.CreateParameter(string.Format("P{0}", i), row[i],
ParameterDirection.Input));
}
geometryParameterIndex = cmd.Parameters.Add(
DbUtility.CreateParameter("PGeo", row.Geometry.AsBinary(),
ParameterDirection.Input));
}
//for (int i = 0; i < cmd.Parameters.Count - 1; i++)
// ((IDataParameter)cmd.Parameters[i]).Value = row[i];
//byte[] geomBinary = row.Geometry.AsBinary();
//((IDataParameter)cmd.Parameters["@PGeo"]).Value = geomBinary;
if (geometryType == OgcGeometryType.Unknown)
{
geometryType = row.Geometry.GeometryType;
}
if (row.Geometry.GeometryType == geometryType)
{
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
}
}
//TODO: add a set of strategies for reading srid
protected override void ReadSpatialReference(out ICoordinateSystem cs, out string srid)
{
using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString))
{
using (IDbCommand cmd = DbUtility.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
string.Format(
@"
DECLARE @found bit
set @found = 0;
IF EXISTS(select * from sys.objects where object_id = object_id(@p1 + '.Geometry_Columns'))
BEGIN
IF EXISTS(SELECT * FROM [{1}].[Geometry_Columns] WHERE F_Table_Catalog = @p0 AND F_Table_Schema = @p1 AND F_Table_Name = @p2 AND F_Geometry_Column = @p3)
BEGIN
SELECT DISTINCT SRID FROM [{1}].[Geometry_Columns] WHERE F_Table_Catalog = @p0 AND F_Table_Schema = @p1 AND F_Table_Name = @p2 AND F_Geometry_Column = @p3
SET @found = 1
END
END
IF @found = 0
BEGIN
SELECT DISTINCT [{0}].STSrid FROM [{1}].[{2}]
END",
GeometryColumn, TableSchema, Table);
cmd.Parameters.Add(DbUtility.CreateParameter("p0", conn.Database, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("p1", TableSchema, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("p2", Table, ParameterDirection.Input));
cmd.Parameters.Add(DbUtility.CreateParameter("p3", GeometryColumn, ParameterDirection.Input));
conn.Open();
using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
if (reader.RecordsAffected > 1)
{
throw new Exception(
string.Format(
"The Sql Server Table [{0}].[{1}] contains geometries in multiple Srids.",
TableSchema, Table));
}
object v = reader[0];
if (v is int)
{
int isrid = (int)v;
cs = SridMap.DefaultInstance.Process(isrid, default(ICoordinateSystem));
srid = !Equals(cs, default(ICoordinateSystem))
? SridMap.DefaultInstance.Process(cs, String.Empty)
: String.Empty;
return;
}
}
}
}
}
cs = default(ICoordinateSystem);
srid = String.Empty;
}
